# Search more than one column in a Google Sheet **URL:** \ **Category:** Questions about Thunkable X\ **Tags:** beginner\ **Created:** [March 4, 2022, 8:40pm UTC](https://community.thunkable.com/t/search-more-than-one-column-in-a-google-sheet/1767733 "2022-03-04T20:40:52Z")\ **Posts on this page:** 18\ **Page:** 1
**Author:** ![andos-avlog](https://sea1.discourse-cdn.com/flex015/user_avatar/community.thunkable.com/andos-avlog/32/122340_2.png) [@andos-avlog](https://community.thunkable.com/u/andos-avlog)\ **Post date:** [March 4, 2022, 8:40pm UTC](https://community.thunkable.com/t/search-more-than-one-column-in-a-google-sheet/1767733/1 "2022-03-04T20:40:52Z")
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** ![Dummy Screenshot](https://us1.discourse-cdn.com/flex015/uploads/thunkable/original/3X/7/e/7eaef0aae7b6efe2b2a5fd2772a98c8ded937154.png) 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**. How would I begin to create this? Andos. ---
**Author:** ![tatiang](https://sea1.discourse-cdn.com/flex015/user_avatar/community.thunkable.com/tatiang/32/55482_2.png) [@tatiang](https://community.thunkable.com/u/tatiang)\ **Post date:** [March 4, 2022, 9:17pm UTC](https://community.thunkable.com/t/search-more-than-one-column-in-a-google-sheet/1767733/2 "2022-03-04T21:17:42Z")
Here’s some ideas I had: 1. 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. 2. 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. 3. You could probably do this with the Google Sheets API but I don’t have experience with that. 4. 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.” ---
**Author:** ![andos-avlog](https://sea1.discourse-cdn.com/flex015/user_avatar/community.thunkable.com/andos-avlog/32/122340_2.png) [@andos-avlog](https://community.thunkable.com/u/andos-avlog)\ **Post date:** [March 4, 2022, 10:41pm UTC](https://community.thunkable.com/t/search-more-than-one-column-in-a-google-sheet/1767733/3 "2022-03-04T22:41:29Z")
Hi, @tatiang, > [@tatiang](#): > > 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? Andos. ---
**Author:** ![tatiang](https://sea1.discourse-cdn.com/flex015/user_avatar/community.thunkable.com/tatiang/32/55482_2.png) [@tatiang](https://community.thunkable.com/u/tatiang)\ **Post date:** [March 5, 2022, 12:32am UTC](https://community.thunkable.com/t/search-more-than-one-column-in-a-google-sheet/1767733/4 "2022-03-05T00:32:59Z")
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. ---
**Author:** ![muneer](https://sea1.discourse-cdn.com/flex015/user_avatar/community.thunkable.com/muneer/32/75210_2.png) [@muneer](https://community.thunkable.com/u/muneer)\ **Post date:** [March 5, 2022, 8:17am UTC](https://community.thunkable.com/t/search-more-than-one-column-in-a-google-sheet/1767733/5 "2022-03-05T08:17:24Z")
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. ---
**Author:** ![andos-avlog](https://sea1.discourse-cdn.com/flex015/user_avatar/community.thunkable.com/andos-avlog/32/122340_2.png) [@andos-avlog](https://community.thunkable.com/u/andos-avlog)\ **Post date:** [March 5, 2022, 1:02pm UTC](https://community.thunkable.com/t/search-more-than-one-column-in-a-google-sheet/1767733/6 "2022-03-05T13:02:31Z")
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? @muneer, > [@muneer](#): > > 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. 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? @tatiang, > [@tatiang](#): > > 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. Would it be easier then to use a **Viewer List** instead of a **DVL** to achieve this? Andos. ---
**Author:** ![andos-avlog](https://sea1.discourse-cdn.com/flex015/user_avatar/community.thunkable.com/andos-avlog/32/122340_2.png) [@andos-avlog](https://community.thunkable.com/u/andos-avlog)\ **Post date:** [March 5, 2022, 2:00pm UTC](https://community.thunkable.com/t/search-more-than-one-column-in-a-google-sheet/1767733/7 "2022-03-05T14:00:45Z")
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. My apologies to you both for wasting your time. Andos. ---
**Author:** ![muneer](https://sea1.discourse-cdn.com/flex015/user_avatar/community.thunkable.com/muneer/32/75210_2.png) [@muneer](https://community.thunkable.com/u/muneer)\ **Post date:** [March 5, 2022, 2:43pm UTC](https://community.thunkable.com/t/search-more-than-one-column-in-a-google-sheet/1767733/8 "2022-03-05T14:43:54Z")
> [@andos-avlog](#): > > 5th column concatenates the first 4 columns on the Source Data Google Sheet In the Google sheet. Whatever your search result is it will be in the same row of the rest of the columns. > [@andos-avlog](#): > > Would it be easier then to use a **Viewer List** instead of a **DVL** to achieve this? Are you asking for **a search feature** or **a filter feature**? They are not the same. > [@andos-avlog](#): > > 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 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. ---
**Author:** ![andos-avlog](https://sea1.discourse-cdn.com/flex015/user_avatar/community.thunkable.com/andos-avlog/32/122340_2.png) [@andos-avlog](https://community.thunkable.com/u/andos-avlog)\ **Post date:** [March 5, 2022, 2:53pm UTC](https://community.thunkable.com/t/search-more-than-one-column-in-a-google-sheet/1767733/9 "2022-03-05T14:53:29Z")
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. Many thanks, Andos. ---
**Author:** ![tatiang](https://sea1.discourse-cdn.com/flex015/user_avatar/community.thunkable.com/tatiang/32/55482_2.png) [@tatiang](https://community.thunkable.com/u/tatiang)\ **Post date:** [March 5, 2022, 4:11pm UTC](https://community.thunkable.com/t/search-more-than-one-column-in-a-google-sheet/1767733/10 "2022-03-05T16:11:49Z")
> [@andos-avlog](#): > > 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. > > My apologies to you both for wasting your time. It happens! And I appreciate you apologizing. This topic may be helpful to someone else who reads it later. There’s no need to delete it. ---
**Author:** ![andos-avlog](https://sea1.discourse-cdn.com/flex015/user_avatar/community.thunkable.com/andos-avlog/32/122340_2.png) [@andos-avlog](https://community.thunkable.com/u/andos-avlog)\ **Post date:** [March 5, 2022, 4:35pm UTC](https://community.thunkable.com/t/search-more-than-one-column-in-a-google-sheet/1767733/11 "2022-03-05T16:35:15Z")
Ok @tatiang, thanks! Once again, sorry for wasting bandwidth. Andos. ---
**Author:** ![muneer](https://sea1.discourse-cdn.com/flex015/user_avatar/community.thunkable.com/muneer/32/75210_2.png) [@muneer](https://community.thunkable.com/u/muneer)\ **Post date:** [March 5, 2022, 5:11pm UTC](https://community.thunkable.com/t/search-more-than-one-column-in-a-google-sheet/1767733/12 "2022-03-05T17:11:42Z")
Continuing to discuss the issue, see this example This is my data set in sheet1 ![image](https://us1.discourse-cdn.com/flex015/uploads/thunkable/original/3X/b/c/bc60604856959464e1b3d0b9649e70dcfb9f85bc.png) I go to another sheet (sheet2) and create the same columns with additional search column ![image](https://us1.discourse-cdn.com/flex015/uploads/thunkable/original/3X/5/4/54c3cb824defc3ca7d829e5f49129b2353bd9825.png) In the first row and first column of the data enter this formula ```auto =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. ![image](https://us1.discourse-cdn.com/flex015/uploads/thunkable/original/3X/f/f/ffb6ccad4a6d6780a8b0ee7e4f63777deafec8ed.png) ---
**Author:** ![andos-avlog](https://sea1.discourse-cdn.com/flex015/user_avatar/community.thunkable.com/andos-avlog/32/122340_2.png) [@andos-avlog](https://community.thunkable.com/u/andos-avlog)\ **Post date:** [March 5, 2022, 5:54pm UTC](https://community.thunkable.com/t/search-more-than-one-column-in-a-google-sheet/1767733/13 "2022-03-05T17:54:25Z")
Hi @muneer, is it possible for this to search across several GSheets, or GSheet Tabs? Andos. ---
**Author:** ![muneer](https://sea1.discourse-cdn.com/flex015/user_avatar/community.thunkable.com/muneer/32/75210_2.png) [@muneer](https://community.thunkable.com/u/muneer)\ **Post date:** [March 5, 2022, 6:17pm UTC](https://community.thunkable.com/t/search-more-than-one-column-in-a-google-sheet/1767733/14 "2022-03-05T18:17:25Z")
> [@andos-avlog](#): > > search across several GSheets, or GSheet Tabs? Yes, it can. Actually the example I posted is to have the data in one tab and the search function in another tab. You can of course search several tabs in one go by editing the **filter** function. ---
**Author:** ![andos-avlog](https://sea1.discourse-cdn.com/flex015/user_avatar/community.thunkable.com/andos-avlog/32/122340_2.png) [@andos-avlog](https://community.thunkable.com/u/andos-avlog)\ **Post date:** [March 5, 2022, 6:52pm UTC](https://community.thunkable.com/t/search-more-than-one-column-in-a-google-sheet/1767733/15 "2022-03-05T18:52:41Z")
Thanks @muneer, I shall try that. Oops! I forgot to confirm, will this work across several GSheets, as in several Google Spreadsheets, not tabs? Andos. ---
**Author:** ![muneer](https://sea1.discourse-cdn.com/flex015/user_avatar/community.thunkable.com/muneer/32/75210_2.png) [@muneer](https://community.thunkable.com/u/muneer)\ **Post date:** [March 5, 2022, 7:08pm UTC](https://community.thunkable.com/t/search-more-than-one-column-in-a-google-sheet/1767733/16 "2022-03-05T19:08:09Z")
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. ---
**Author:** ![andos-avlog](https://sea1.discourse-cdn.com/flex015/user_avatar/community.thunkable.com/andos-avlog/32/122340_2.png) [@andos-avlog](https://community.thunkable.com/u/andos-avlog)\ **Post date:** [March 5, 2022, 7:19pm UTC](https://community.thunkable.com/t/search-more-than-one-column-in-a-google-sheet/1767733/17 "2022-03-05T19:19:57Z")
Ok. Will do. Thanks Andos. ---
**Author:** ![system](https://us1.discourse-cdn.com/flex015/uploads/thunkable/original/3X/0/f/0f59f292712368bce16ff80133ae10de8a6f27e8.png) [@system](https://community.thunkable.com/u/system)\ **Post date:** [June 3, 2022, 7:20pm UTC](https://community.thunkable.com/t/search-more-than-one-column-in-a-google-sheet/1767733/18 "2022-06-03T19:20:17Z")
This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.