Hi, I have been rummaging through the Community Topics without success.
What I would like to know is, is it possible to simultaneously search more than one column on a Google Sheet from a single Search box?
And if it is, how is this done?
Example:
Suppose I had a Google Sheet Tab containing 4 columns of data, and let’s say, 10 rows/ records and by typing text into the single Search box a text string, a list of records would containing that text string from any cell in the Google Sheet Tab would appear as a clickable list.
Image
If I typed in “Mr” my list results would contain: Rob; Tom; Joe; Tim; Terry; John & Bill or,
if I typed in “Rry” my results list would contain: Jane; Joe; Terry; Bill & Paula.
If I were to click on any resulting names, I would be presented with that full record of 4 columns.
If you only have four columns, just search all four columns separately for each search term and then display the results. Four searches instead of one. Or you can loop through the row objects and that will allow for searching the whole table.
Airtable has a sophisticated “filter by formula” option as part of its API. I would personally use that but you’d need to have your data in Airtable.
You could probably do this with the Google Sheets API but I don’t have experience with that.
What about doing the search within Google Sheets? That is, you set up the formula to check one cell that is used as the search term. Then you update that one cell as needed when the user searches in Thunkable. I haven’t thought this all the way through as far as getting the resulting data back but it would be fast and very easy to code in Thunkable because all of the work is done in Google Sheets.
Depending on your data and the method you choose, you may run into a problem where multiple columns contain search results. For example, searching for “Rry” and returning “Larry, Barry, Strawberry, Gerrymandering, etc.”
If the column headers (Name, Title, Colour, Fruit) were assigned to labels using a DVL layout template, would the result of this Search result display the whole of each record in the format of their bound labels?
In other words, using “Rry” for this example, suppose we bind Label 1 to the “Name” in the DVL , Label 2 to “Title”,Label 3 to “Colour” and Label 4 to “Fruit”.
If the result of the Search for “Rry” returns the results " Jane ; Joe ; Terry ; Bill & Paula" Would all those results be displayed normally in the DVL as per their bindings?
If so, that is what I am after and how would I do this?
This topic of displaying partial results from a DVL has been discussed recently and it’s a bit tricky to do this. Some people prefer to show/hide results within the original DVL as needed. I prefer to create a separate data source and a separate “results” DVL.
Each time I search and get results, I delete all of the rows in the “results” data source and then populate it by creating rows based on the search results. With the results DVL always synced to the results data source, it will display whichever rows have been created.
Create a fifth column which consist of all four columns. Now use this new column to search and whatever your search text is it will be in that new column.
Hi @muneer/ @tatiang, as an alternative, if I used an arrayformula on a separate Google Sheet Tab to reflect the original Google Data Sheet and use that as the Source Data Sheet instead without column headers as the backend, is there a way to then use this sheet in Thunkable via a Search box?
Could you please confirm that the 5th column concatenates the first 4 columns on the Source Data Google Sheet or, is this concatenated in Thumkable? If it is in Thunkable, how is this done?
Hi @muneer & @tatiang, my apologies for this topic question. Is there anyway for me to delete this topic?
The reason being is that I have asked the wrong question. I have only just realised that this is not what I wish to know. I didn’t think far enough ahead. regarding the final outcome of this project.
In the Google sheet. Whatever your search result is it will be in the same row of the rest of the columns.
Are you asking for a search feature or a filter feature? They are not the same.
If you are trying to filter the data source from the data source itself then you will have a single user app. Once one user passes a search criteria the formula will update the data source which will effect other users.
Thanks for your reply @muneer, I realised what I was asking is wrong. What I will need is a drill down filter based on the data in the Source Data Google Sheet.
Please ignore this topic. I will be creating a new one asking the correct questions if I cannot find a solution within the Community.
I go to another sheet (sheet2) and create the same columns with additional search column
In the first row and first column of the data enter this formula
=filter(Sheet1!A2:B7,Sheet1!A2:A7=C2)
This means that Google sheet will regenerate the list in sheet2 that matches the text entered in the search column.
In Thunkable you can place a Text Input and update that row/column in Google sheet to get the filtered value which is mapped to the DVL (Data Viewer List) in your Thunkable screen.
When using the filter function directly from the sheet, I can confirm that it does work across different Google sheet files (workbooks) but will it work using the Thunkable Data Source connector, I don’t know. You might need to try it yourself.
First try a Google sheet file with different tabs and after you see it working in Thunkable try to link it with another Google sheet file to see if it works.
