Filter gsheet result table by search box

[carlos-a]

The controls for loading gsheet data don’t have an option to query based on a condition. Like the demo video, I want to be able to filter the gsheet data according to user input. All the controls related to gsheet are fixed

[Retool-Admin]

Hey @carlos-a what do you mean by controls? Have you been able to get your GSheets data into a Table component? We have a guide to working with tables available here

[carlos-a]

Is there a way to do a Select statement in here?

[justin]

@carlos-a totally! You have two options here:

1. You can pull the data into a table and use table filters (the little filter icon)
2. If you save your query (query1) that pulls the sheet's data into Retool, you can create another query that uses SQL to query that. Create a second query and as your resource (from the resource dropdown) choose Query JSON with SQL. In that query window you should be able to run something like SELECT * FROM {{ query1.data }}

Let me know if these help!

[carlos-a]

Awesome, thanks!

[bryan]

@justin option 2 there was an awesome find for me yesterday. But now it's left me trying to join this google sheet query with a MySQL query. How would I call the already run MySQL query? This is what happens to me thus far...

[Kabirdas]

Hi @bryan!

Try writing {{ formatDataAsArray(ma_schooldb.data) }} query data exists either as an object of arrays (where each key corresponds to a column name and its value is the data for that column) or an array of objects (where each object in the array corresponds to a row). The functions formatDataAsArray and formatDataAsObject let you convert between these two! In this case, Query JSON with SQL is expecting an array of objects.