# Return a value from a specific Google Sheet cell **URL:** \ **Category:** Questions about Thunkable X\ **Tags:** beginner\ **Created:** [March 6, 2022, 2:48pm UTC](https://community.thunkable.com/t/return-a-value-from-a-specific-google-sheet-cell/1769781 "2022-03-06T14:48:08Z")\ **Posts on this page:** 20\ **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 6, 2022, 2:48pm UTC](https://community.thunkable.com/t/return-a-value-from-a-specific-google-sheet-cell/1769781/1 "2022-03-06T14:48:08Z")
Hi, I have a Google Sheet (workbook) where I have managed to condense the lookup of 3.7 million records into a simple lookup consisting of 3 cells: A2, B2 & C2 (see below) ![Hex Lookup Sheet](https://us1.discourse-cdn.com/flex015/uploads/thunkable/original/3X/4/5/45a7b2fea0d2912b2d87f3cab32fd50c997f1c7e.png) While the " **FLAG** column is **not yet** a concern to this problem, the **SEARCH** & **COUNTRY** columns (cells **A2** & **B2** ) are. What I am trying to do is to get whatever search that is entered into my text input box (**HexTextInput)** in my Thunkable app to enter the same search into cell **A2** in my Source Data Google Sheet Tab ( **HEX** ). This in turn will call the result from my ( **REF** ) tab in the GSheet and display it in cell **B2**. This will then be displayed as a result in my DVL ( **HexDVL** ). ![Hex Search Scn](https://us1.discourse-cdn.com/flex015/uploads/thunkable/original/3X/5/6/56a02aa5606db14fd697b1f78ad7e104322486cc.png) ![Hex Design Scn](https://us1.discourse-cdn.com/flex015/uploads/thunkable/original/3X/b/8/b8b366415028467994519860cd9f6fd82b40f6f5.png) I cannot use the **REF** tab as the Source Data because the formulae used in it calculate and breakdown the hexcodes into 3.7 million records so that they can be individually searched so that they can return the correct “ **COUNTRY** ” result for cell " **B2** to display. (See below): ![Hex Ref Sheet](https://us1.discourse-cdn.com/flex015/uploads/thunkable/original/3X/0/3/03e8bd00695684149de1f44d4a1c6c9318dce8da.png) As Google Sheets won’t allow a sheet of more than 1 million cells, this was the only way to resolve that issue. (Plus, I was not prepared to type out 3.7 million records). As you can see you, I am quite lost with the blocks (see below): ![Hex Scn Blocks](https://us1.discourse-cdn.com/flex015/uploads/thunkable/original/3X/8/c/8c925d93f468db0e3c713dd143d8ddc5368a7f61.png) Please help! 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 8, 2022, 8:00am UTC](https://community.thunkable.com/t/return-a-value-from-a-specific-google-sheet-cell/1769781/2 "2022-03-08T08:00:08Z")
What is the context for this? Why are you needing to convert hex values to decimal values to countries? And why 3.7 million records? Can you explain this as if I didn’t know anything about Thunkable? I’m just trying to understand the bigger picture. ---
**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 8, 2022, 9:48am UTC](https://community.thunkable.com/t/return-a-value-from-a-specific-google-sheet-cell/1769781/3 "2022-03-08T09:48:54Z")
I’m not sure what you are trying to achieve but converting a HEX value to DECIMAL does not require 3.7m of records. See this demo to convert a HEX color code to RGB which is DECIMAL. [https://x.thunkable.com/projectPage/61742edbc7e59500107319d4](https://x.thunkable.com/projectPage/61742edbc7e59500107319d4) ---
**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 8, 2022, 1:35pm UTC](https://community.thunkable.com/t/return-a-value-from-a-specific-google-sheet-cell/1769781/4 "2022-03-08T13:35:53Z")
Hi @muneer & @tatiang, the purpose of this the conversion is for aviation. Every airliner and a lot of private aircraft have what is called a "Mode-S code. This allows aircraft to be identified by Air Traffic Control on a radar screen and allows other data such as height; callsign; tail number; etc… to be displayed by association to this code. This code is a six-character hexadecimal code (hexcode). Each country is allocated a range of specific hexcodes codes which are in turn are re-allocated to the aircraft of that country. One hexcode per aircraft. Unless it is a military aircraft, this code stays with this aircraft for the whole of its lifetme while it its registered to that particular country. If the aircraft is sold, or registered to another country, it will be allocated a new hexcode by that country. The 3.7 million is the result of 16 to the power of 6. I hope this has explained things a little better. **Example** In the Source GSheet, I type “ **123def** ” into cell **A2** then press ENTER, I instantly get “ **RUSSIAN FEDERATION** ” returned in cell **B2**. > [@andos-avlog](#): > > ![Hex Lookup Sheet](https://us1.discourse-cdn.com/flex015/uploads/thunkable/original/3X/4/5/45a7b2fea0d2912b2d87f3cab32fd50c997f1c7e.png) > > Hex Lookup Sheet836×218 13.3 KB Through the app, I would like to type a hexcode into the Text Input Search box (**HexTextInput)**, which will enter the “Searched For” hexcode into the Source GSheet cell “ **A2** ” and have the value from cell **B2** appear on the “ **HexDVLCountryLabel** ”. I would also like to create a “ **Clear Button** ” which will remove the **Searched** text (“ **123def** ”) from cell **A2** and so consequentially remove the **Found Result** ( in this case “ **RUSSIAN FEDERATION** ” )from the DVL. How would I do this? 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 8, 2022, 2:22pm UTC](https://community.thunkable.com/t/return-a-value-from-a-specific-google-sheet-cell/1769781/5 "2022-03-08T14:22:00Z")
> [@andos-avlog](#): > > How would I do this? This question should be answered by **you**. You are using your own formula in Google sheet to get the results and to get **Not Found** should be managed from the same formula. ---
**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 8, 2022, 5:42pm UTC](https://community.thunkable.com/t/return-a-value-from-a-specific-google-sheet-cell/1769781/6 "2022-03-08T17:42:58Z")
Thank you for the explanation! I always like to learn something new and I didn’t understand what you were trying to do but now I think I do. When the user enters a hex code, are you checking a list of 3.7 million existing hex codes to see if it’s valid? Or do you just want to know which country it applies to? Because if it’s the country, you can use a range of values, I assume. ---
**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 8, 2022, 6:06pm UTC](https://community.thunkable.com/t/return-a-value-from-a-specific-google-sheet-cell/1769781/7 "2022-03-08T18:06:32Z")
> [@tatiang](#): > > do you just want to know which country it applies to He is explaining about countries in his post so I assume the current lookup function is looking up countries so you need a table of 3 columns (country, start of range, end of range). ---
**Author:** ![Danielmaf](https://avatars.discourse-cdn.com/v4/letter/d/9e8a1a/32.png) [@Danielmaf](https://community.thunkable.com/u/Danielmaf)\ **Post date:** [March 8, 2022, 6:12pm UTC](https://community.thunkable.com/t/return-a-value-from-a-specific-google-sheet-cell/1769781/8 "2022-03-08T18:12:05Z")
olá Muneer, gostaria de te contratar para produzir um app web com algumas integrações com Api. voce tem interese em fazer? ou poderia me indcar alguém? [danielmaf@icloud.com](mailto:danielmaf@icloud.com) +55 027 995254964 ---
**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 8, 2022, 7:12pm UTC](https://community.thunkable.com/t/return-a-value-from-a-specific-google-sheet-cell/1769781/9 "2022-03-08T19:12:29Z")
Google Translation: > Hi Muneer, I would like to hire you to produce a web app with some Api integrations. are you interested in doing? Or could you point me to someone? @Danielmaf Please don’t hijack someone else’s topic. You can start a new topic or in this case, it’s best to just send a PM to @muneer. ---
**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 8, 2022, 7:21pm UTC](https://community.thunkable.com/t/return-a-value-from-a-specific-google-sheet-cell/1769781/10 "2022-03-08T19:21:54Z")
Thank you @tatiang No one can answer such questions without the necessary info. What if the required features are something I am not familiar with or cannot do. @Danielmaf I sent you a PM requesting more info to be able to reply to you. ---
**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 8, 2022, 7:25pm UTC](https://community.thunkable.com/t/return-a-value-from-a-specific-google-sheet-cell/1769781/11 "2022-03-08T19:25:51Z")
Hi @tatiang & @muneer, you are both correct. I wish for the search to return a result ( a country) from a range of values so that the user will know which country this hexcode belongs to. @muneer, I am using five columns because of the range conversion into decimal so that the search can be carried out more accurately. This is because in the future I may include the aircraft identities in this app. I have already (thanks to formulae) listed the individual hexcodes across 21 Google Workbooks in preparation for this. Though I am **still not sure** if I wish to pursue this route. If I did, the search would then return the aircraft’s tail number and consequentially, other details such as airline operator; aircraft type; etc…! but it that will be a huge undertaking for the future. 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 8, 2022, 7:38pm UTC](https://community.thunkable.com/t/return-a-value-from-a-specific-google-sheet-cell/1769781/12 "2022-03-08T19:38:37Z")
> [@andos-avlog](#): > > listed the individual hexcodes across 21 Google Workbooks in preparation for this I would suggest using Firebase for such huge operation. Use the Google worksheet for the app requirements and for the country search but use Firebase to store airplane info. > [@andos-avlog](#): > > I am using five columns because of the range conversion into decimal so that the search can be carried out more accurately I’m not sure I get what you mean. Whether decimal or hex it is the same accuracy and Google `Lookup` will never mind. ---
**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 8, 2022, 8:46pm UTC](https://community.thunkable.com/t/return-a-value-from-a-specific-google-sheet-cell/1769781/13 "2022-03-08T20:46:47Z")
Hi @muneer, for the time being, I am only interested in getting the entered data in the **App Text input** box (**HexText\_Input)** to enter into the Source GSheet cell **A2.** Once that has been entered and **Enter** has been clicked on the GSheet **somehow using the Enter/ Search Button on the App**. The cell **B2** on the GSheet will display the result on the App’s “ **HexDVLCountryLabel** ”. 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 10, 2022, 11:47am UTC](https://community.thunkable.com/t/return-a-value-from-a-specific-google-sheet-cell/1769781/14 "2022-03-10T11:47:50Z")
Hi @muneer, @tatiang, did you see my previous reply? Also, can the Thunkable blocks convert and search for hexcodes in the same way that Google Sheets formulas do? ie. =ARRAYFORMULA(IF(A2:A=“”,VLOOKUP(HEX2DEC(A2:A),REF!C:F,{3,4},1))) Also, by using the Thunkable blocks to calculate and search for the result at the front end, will it take longer at the user end than the calculations and search being done at the backend? Below, is an image from GSheet tab “ **HEX** ” showing cell “ **A2** ” being populated with the hexcode " **028022** and returning the value (COUNTRY) “ **Tunisia** ” in cell “ **B2** ”. ![HEXSheet-Screenshot 2022-03-10 11.06.08](https://us1.discourse-cdn.com/flex015/uploads/thunkable/original/3X/7/7/77361a96a1e3e9a7b7546b0c2a6293f063a17f2c.png) This result was found by searching the ranges in **columns A and B** on GSheet tab “ **REF** ” and found the result in the range between “ **028000** ” and “ **02FFFF** ” and then returning the value in column “ **E** ” (“ **COUNTRY** ”) (see below) ![REFSheet](https://us1.discourse-cdn.com/flex015/uploads/thunkable/original/3X/e/8/e8d76ff4907f547106c6aba7aef24b14952b7af2.png) to cell “ **B2** ” on the GSheet “ **HEX** ” I know I am now repeating myself, but I purposely want to contain my request in one message… Through the app, I would like to type a hexcode into the Text Input Search box (**HexTextInput)**, which will enter the “Searched For” hexcode into the Source GSheet cell “ **A2** ” and have the value from cell **B2** appear on the “ **HexDVLCountryLabel** ”. I would also like to create a “ **Clear Button** ” which will remove the **Searched** text (“ **123def** ”) from cell **A2** and so consequentially remove the **Found Result** ( in this case “ **RUSSIAN FEDERATION** ” )from the DVL. How would I do this? > [@andos-avlog](#): > > Hi @muneer, for the time being, I am only interested in getting the entered data in the **App Text input** box (**HexText\_Input)** to enter into the Source GSheet cell **A2.** Once that has been entered and **Enter** has been clicked on the GSheet **somehow using the Enter/ Search Button on the App**. The cell **B2** on the GSheet will display the result on the App’s “ **HexDVLCountryLabel** ”. Here is the link to my project: [https://x.thunkable.com/copy/ad51ae0e41203ab278b8ae0e511b21c8](https://x.thunkable.com/copy/ad51ae0e41203ab278b8ae0e511b21c8) 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 10, 2022, 1:06pm UTC](https://community.thunkable.com/t/return-a-value-from-a-specific-google-sheet-cell/1769781/15 "2022-03-10T13:06:12Z")
I’m not sure of what you exactly want. Are you saying when you use `update value` block, your sheet is not updated? ---
**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 10, 2022, 7:14pm UTC](https://community.thunkable.com/t/return-a-value-from-a-specific-google-sheet-cell/1769781/16 "2022-03-10T19:14:58Z")
Hi @muneer, yes that is correct. Partly because I’m not sure what values to enter into the “ **row id** ” and “ **value** ” sockets in the “ **update value** ” block. ![HexBlocks-Screenshot 2022-03-10 19.09.37](https://us1.discourse-cdn.com/flex015/uploads/thunkable/original/3X/1/e/1e1aa0248d7ebaf615da8c0f48dc6f61a2543c44.png) 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 10, 2022, 8:50pm UTC](https://community.thunkable.com/t/return-a-value-from-a-specific-google-sheet-cell/1769781/17 "2022-03-10T20:50:52Z")
First of all, Is the Google sheet tab **HEX** connected to your app? If yes then the `update block` should have **row id** = 1. You should update Tab **HEX** , column **SEARCH**. The image you shared you are trying to update column **COUNTRY** which is wrong. The `value` is the input from the user. ---
**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 10, 2022, 10:18pm UTC](https://community.thunkable.com/t/return-a-value-from-a-specific-google-sheet-cell/1769781/18 "2022-03-10T22:18:06Z")
I see. My " **HEX** tab is indeed connected to the “ **HexSreen** ” of my app. (See below) ![HexTabExample-Screenshot 2022-03-10 21.27.55](https://us1.discourse-cdn.com/flex015/uploads/thunkable/original/3X/e/c/ec3235117529a68bf05e26980eae230b0d27ea1c.png) I am little confused though. > [@muneer](#): > > The `value` is the input from the user. Does that mean this should be “ **Search** ”? Or does it refer to the app’s “ **HexText\_Input** ” box? Is the image below now correct? ![HexSheetExample2-Screenshot 2022-03-10 22.11.13](https://us1.discourse-cdn.com/flex015/uploads/thunkable/original/3X/0/7/07e97ba6d021b9d0de4ecf27ea61e42a817b1212.png) I was also wondering why should the " **row id** = 1? Thanks @muneer. I shall try that and I let you know how get on. 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 11, 2022, 8:41am UTC](https://community.thunkable.com/t/return-a-value-from-a-specific-google-sheet-cell/1769781/19 "2022-03-11T08:41:34Z")
The row I’d will always be 1 so you don’t need to assign a variable to it. The `update value` block is correct this way. Try and let me know. ---
**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 11, 2022, 12:04pm UTC](https://community.thunkable.com/t/return-a-value-from-a-specific-google-sheet-cell/1769781/20 "2022-03-11T12:04:18Z")
Hi @muneer, the text input part to the **GSheet tab** works. (See below): ![HexSheetImage](https://us1.discourse-cdn.com/flex015/uploads/thunkable/original/3X/d/4/d4e4a7e283bafbb33eaffd2f6051f361afcb541c.png) However, the output value of cell **B2** is not being returned to the app Label ( **HexDVLCountryLabel** ) in the DVL ( **HexDVL** ). (See below:) ![HEXScreenImage-Screenshot 2022-03-11 11.41.07](https://us1.discourse-cdn.com/flex015/uploads/thunkable/original/3X/9/0/909d8419edc1684a15b11b1a648e5c98741913bd.png) I believe my Design to be correct also, (below:) ![HEXDesignImage](https://us1.discourse-cdn.com/flex015/uploads/thunkable/original/3X/f/d/fdf3a3af44c3ae9949382d9c96ed624e4a99b094.png) Can you find the error? Also @muneer, I have noticed that when I close the “ **Live test** ” then re-open it, the result “ **Morocco** ” appears. I have tried this with a few searches now and they all appear after the screen has been reloaded. Is there a way to **“Refresh” the screen** via the user **clicking an “Enter” button** or something? Andos. [Next page](https://community.thunkable.com/t/return-a-value-from-a-specific-google-sheet-cell/1769781.md?page=2)