Lookup data from Google Sheet

[Jason_Roberts]

Hi all,

After not finding an app that does what I need, I'm trying to create what is basically a customer price scanner for a small store. The Android app would scan a barcode, find the matching row in a Google Sheet, and return the data from that row.

I'm good on the Google Sheet API (which I figured would be the hard part) and the data pulls just fine. I know how to display the data from the sheet to the screen. I'm having trouble with the middle bit. I've tried both Chat GPT and Google Gemini for help, but neither is great at explaining which blocks need to be connected and how.

Both AIs got me as far as using a When Web1.GotText with a For Each Item inside, but then I get lost.

The sample data from my sheet looks like:

{
  "range": "Sheet1!A1:D1001",
  "majorDimension": "ROWS",
  "values": [
    [
      "SKU",
      "Name",
      "Price",
      "Next"
    ],
    [
      "2773163508461",
      "Ribbon Cable Rainbow Bracelet - Small",
      "10.95",
      "2024-06-24"
    ],
    [
      "2644163508461",
      "Ribbon Cable Rainbow Bracelet - Medium",
      "10.95",
      "2024-06-24"
    ],
    [
      "5264396564071",
      "Reimagined Plastics - Hinged HEarts",
      "999",
      "2024-06-29"
    ]
  ]
}

The variable for the scanned code is global BarcodeValue. Assume each SKU will show up only once, so we don't have to worry about duplicate SKUs, though it is possible a scanned SKU may not appear at all on the list.

Anyone have some time to help me parse and return this data? I figured this would be the easy part, but I'm lost.

Thanks!

[TIMAI2]

Please show your relevant blocks for getting the data from google sheet, which should show the method you are using.

It may also help to show a section of your spreadsheet so we can understand how your data is set out.

[Jason_Roberts]

Hey @TIMAI2. Thanks.

Getting the data is just a straight call Web1.Get after the barcode is scanned. Should I be doing something special?

The spreadsheet is just the four columns as shown in my first message:

[TIMAI2]

Please show the full url for Web1.Get

[Jason_Roberts]

https://sheets.googleapis.com/v4/spreadsheets/1pXB0rxOzioOtDEMhImwMUTXqVbUhGlRC2DKWcSxXpa0/values/Sheet1!A:D?key=[API-KEY]

[TIMAI2]

OK, you are calling back all the values, not just the one you need.

You could use a gviz query to return the values you need:

METRIC RAT AI2 - Use gviz to get and query google sheet data

Example URL:

https://docs.google.com/spreadsheets/d/1pXB0rxOzioOtDEMhImwMUTXqVbUhGlRC2DKWcSxXpa0/gviz/tq?tqx=out:csv&tq=SELECT * WHERE A = 2644163508461

Example output

"SKU","Name","Price","Next"
"2644163508461","Ribbon Cable Rainbow Bracelet - Medium","10.95","2024-06-24"

Your Web1.GotText should then look like this to return a list of your scanned record.

containing:
["2644163508461","Ribbon Cable Rainbow Bracelet - Medium","10.95","2024-06-24"]

If your spreadsheet is "restricted" then you could do it this way:

METRIC RAT AI2 - Query Any Google Sheet with a Web App

Or there is always the built-in Spreadsheet component

[Jason_Roberts]

Thanks, @TIMAI2!

I thought there must be an easier way than getting all the data and searching, but I couldn't find anything Googling, and Chat GPT and Gemini kept telling me to run all of the data through a loop. I'll take a look at your solution and the links you provided later tonight to see if I can get this done.

[ABG]

You can download the full sheet once and use TinyDB for your local lookups.
barcode_sheet.aia (3.9 KB)

[Jason_Roberts]

Thank you so much, @TIMAI2. That was the piece I needed!

[Jason_Roberts]

Wow! Above and beyond, @ABG! Thanks.