Hi, I have filters on the left of the image,
And I want the input there to filter the actual table on the right.
(The table was created via a Google Sheet)
How do I for example, filter the data in the table, once I write something in the search bar?
Hi, I have filters on the left of the image,
And I want the input there to filter the actual table on the right.
(The table was created via a Google Sheet)
hey meir!
you will need to create a sql query that looks at the search bar
it should look something like:
select * from theNameOfYourGoogleSheetData where columnName ilike {{yourSearchBar.value}}
let me know if that works!
I have the same problem. Trying search filter in google spreadsheet data. I've tried your method but didn't work. Can you explain with more details, please?
Hi all, thanks for your answer @DavidD, that's spot on! @volkanduran, DavidD is suggesting you create a Query JSON with SQL query with something along the lines of select * from {{ getSheet.data }} where Name ilike {{'%' + searchByName.value + '%'}} where getSheet is your Google Sheets query and searchByName is the Text Input or Select component used for the search input. Depending on how you want your search query to work, you might want to add some wildcards (the '%'s), as I did in my example. This Query JSON with SQL query will need to be set to 'Run query automatically when inputs change':
or you could set the query to 'Run query only when manually triggered' and set an event handler on the input/select component to run the query on Change (when the select input changes):
Attached is an export of this example. You can import this JSON file by clicking Create new (home page) > From JSON.
I hope this helps!
Filter-20Google-20Sheet.json
1 Like
Hi Everett,
This is working fine, but how can it be resolved that my table to be loaded initially with the getSheet query? If I am setting it to use the searchSheet query the filter is working nice, but the table is empty until I am filtering anything.
Thanks!
Hi @Istvan, You can add some logic to your 'query JSON with SQL' query so that the search clause is only applied when searchByName.value is not null. Here's what that could look like:
select * from {{ getSheet.data }} where ({{!searchByName.value}} OR Name ilike {{'%' + searchByName.value + '%'}})
I have tried all of these suggestions and it is still not working. When I search a word nothing happens.
I got it to work when I used Retool Database and that's why the PostgreSQL queries are there. I'm wanting to use Google Sheets though due to our current work flow.
Hmmm do you have a "Name" column in that data?
Yes, on the actual Google Sheet there is a "Name" column.
Okay, thanks for confirming that. Can you share what query4.data looks like? Also, does query5 return anything at the moment or does it throw an error?
So the table in the picture is pulling the data via query4. Do you need a pic of the actual Google Sheet?
When I run query5 it just says it was ran successfully but when I type in the textInput2 box nothing happens except it saying that there are no rows to display.
Can you share a screenshot of the first object in the query4.data in the model browser? Here's an example of what would be helpful to see:
In case, it's helpful, the Model Browser is the left panel that you can open with the middle button at the top of the editor, and it's really useful for visualizing the data inside components/queries, labeled by the correct syntax to select it.
Would you be able to drop down the data property and then drop down the first object in the data property?
Ah sorry, this is query5. Can you clarify how the behavior of query5 is differing from what you expect?