Return a value from a specific Google Sheet cell

[andos-avlog]

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)

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).

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):

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):

Please help!

Andos.

[tatiang]

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.

[muneer]

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

[andos-avlog]

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 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.

[muneer]

@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.

[tatiang]

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.

[muneer]

@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).

[Danielmaf]

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

+55 027 995254964

[tatiang]

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.

[muneer]

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.

[andos-avlog]

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.

[muneer]

@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.

[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”.

Andos.

[andos-avlog]

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”.

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)

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

Andos.

[muneer]

I’m not sure of what you exactly want. Are you saying when you use update value block, your sheet is not updated?

[andos-avlog]

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.

Andos.

[muneer]

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.

[andos-avlog]

I see. My "HEX tab is indeed connected to the “HexSreen” of my app. (See below)

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?

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.

[muneer]

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.

[andos-avlog]

Hi @muneer, the text input part to the GSheet tab works. (See below):

However, the output value of cell B2 is not being returned to the app Label (HexDVLCountryLabel) in the DVL (HexDVL). (See below:)

I believe my Design to be correct also, (below:)

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.