Creating a search bar in a mobile app from Google Sheet data

[Valentin_Jardinier_Almodovar]

Hello,

I'm new to Retool and initiated a Retool Mobile app by connecting to my data source, which is a Google Sheet.
I can get its data to be displayed in my app.
Where I get stuck is trying to implement a search bar. I added a text input component. When writing a query to display only the items that contain the text input value in the "description" field, I can get the list component to display all data as long as the text input is empty, but if I write anything in the text input component, then the query returns an empty state.

All other topics I found seem to be using table components in desktop apps, and the data source is not always a Google Sheet: I've been stuck attempting to troubleshoot my issue.

I would greatly appreciate any insight to get this search bar to work.

Additional question: my Google Sheet contains many columns, it seems only the data contained within columns A to Z is retrieved. Am I limited to not retrieving data in columns AA and following?

[ScottR]

@Valentin_Jardinier_Almodovar Welcome to the forum!
Please provide more details, screenshots and code if possible so someone here can attempt to provide you with a solution.

[Valentin_Jardinier_Almodovar]

Hi ScottR, thanks for replying.

I tried to remove everything that isn't directly related to my issue for these examples.
The app is supposed to be an inventory management tool.

In this first picture, the text input is empty, the list collection is full.

In this state, the text input contains an input (that is contained in one of the first picture's items' description), yet the query returns no result.

Here is the query used:

SELECT * FROM {{getAllWines.data}}
WHERE {{ !wineSearch.value }} OR description LIKE {{ '&' + wineSearch.value + '&' }}

There's probably something obvious I'm doing wrong.

[ScottR]

Your query needs to compare the values you are looking for
SELECT * FROM {{getAllWines.data}}
WHERE description LIKE {{ wineSearch.value}}

[Valentin_Jardinier_Almodovar]

This doesn't seem to make a difference.

[ScottR]

What does {{getAllWines.data}} contain?

[Valentin_Jardinier_Almodovar]

Thanks for your help, here is a screenshot of "getAllWines" and its output

[Valentin_Jardinier_Almodovar]

And its state

[ScottR]

Try running SELECT * FROM {{getAllWines.data}} and see what what the result is

[Valentin_Jardinier_Almodovar]

Hi Scott,

This does in fact work.

In the first picture I shared, the query returns the result from "SELECT * FROM {{getAllWines.data}}", which is all items, as long as the text input is empty.
However, as soon as the text input contains any string, the query yields no results. Am I not designating the description field correctly ? What else could be causing this?

[mbruijnpff]

Hi @Valentin_Jardinier_Almodovar ,

You have an error in your select query in the where clause.
You're using '&' to search for the value within the description. These should be '%'

SELECT * FROM {{getAllWines.data}}
WHERE {{ !wineSearch.value }} OR description LIKE {{ '&' + wineSearch.value + '&' }}

Instead use:

SELECT * FROM {{getAllWines.data}}
WHERE {{ !wineSearch.value }} OR description LIKE {{ '%' + wineSearch.value + '%' }}

[Valentin_Jardinier_Almodovar]

Hi @mbruijnpff,
Thank you for spotting this, it is indeed working now.

Do you, or anyone else, know perhaps whether it is possible to retrieve data from a Google Sheet in columns beyond the Z column?

[mbruijnpff]

I’m not familiar with Google sheets, I don’t know if this is a limit within Retool or sheets

[Valentin_Jardinier_Almodovar]

Can I write a query to retrieve specific column ranges?

[jocen]

@Valentin_Jardinier_Almodovar yes you can. Use A1 notation to specify the area in the spreadsheet you wanna specify, i.e.

Summary!AA20:AD45

Something like that would still work.