So heres another thought.. I would like to ...

So heres another thought…

I would like to have a list of groceries, and in a new form, I would like to be able to scan a bar code off an item on the shelf and have the name, size, etc “auto populate” from an API on the internet.

Im looking at UPCitemDB.com as the source, but Im having difficulty with parsing the results into the form.

thoughts?

Thanks all. .

dave

0 30 660
30 REPLIES 30

#open

@David_Hopkins can you elaborate your issue with the API?

Creating a standalone Google Apps Script which will make a UrlFetchApp call to the API and then parsing the request to the gSheet. Publishing this script as a webapp and using it as a webhook endpoint in the WorkFlow will work.

… Levent … I wasn’t even thinking that far ahead.

But it just might do the job for him.

Mary Jane that will be the quickest solution. That’s the solution that we generally use in most of our client apps where an API connection is needed.

Jonas
Participant V

@Levent_KULACOGLU No this would not provide live-data. Because the workflow rule will trigger, the Apps Script will fetch, but the result has to be pushed to the user. This will only happen with a full-sync or at least a background-sync.

@Levent_KULACOGLU That’s great Levent.

I hope this helps David and I have certainly learned a new trick.

thank you.

m.j.

Jonas
Participant V

@David_Hopkins Do you need the results to be visible instantly upon putting in the EAN or can this be processed in the background?

If background, then @Levent_KULACOGLU

solution will work for you.

@Jonas in our tests we have verified from the audit log that AppSheet is waiting the result from the script during syncing and display the result.

Jonas
Participant V

@Levent_KULACOGLU

Yes this works but as I said, a sync of some kind still has to happen.

If the user needs live-data for example to submit an open form, this does not work.

@Jonas another solution could be using an html styled form rather than an AppSheet form, execute it as an external URL, query the API and fetch the result back to the AppSheet form. This is something very alike that we do with online payment gateway solutions, but worth trying. What do you think @Bellave_Jayaram ?

Yes you’re right @Jonas sync is needed for that. For some kinda live query, my second option can be tried though.

Jonas
Participant V

processing API-calls live will be an awesome feature

Jonas
Participant V

@Levent_KULACOGLU how does the result get to the AppSheet APP in the case of the external html / php script?

manually copy & paste?

or providing a link, to the app with the results set inside the url, so they would be prefilled in the form?

Would the ap not try to reload upon clicking that link?

@Levent_KULACOGLU this is exactly what Im trying to do…

Say your in the market, and you want to add a product to your shopping list, you simply scan the barcode and the form is filled out for you.

Then you enter the rest of the data manually and save the form.

@Jonas ,

currently, Im “more or less”, filling out the form manually… then using the UPC to “price compare” while shopping.

But, in the process of building the grocery list, how awesome would it be to be able to simply “scan a barcode” and the form is filled in for you…

@Mary_Jane_Pender So Mary, what would be the difference between using the UPC DB and calling a UPC from the scanner? would it be possible to still use the script to “go out” and get the data for the form?

@Jonas for this particular scenario, having the info populate the form as the Barcode was scanned would be ideal. However, I could create a PLP style data entry where you “enter product by barcode”, which opens a form, you scan the barcode and save form, system pulls from API and opens new Product Form all filled out. Actually only adding 1 step for the user and makes it abit like a Wizard.

(Side Note: really impressed with this community! You all are attacking this like hungry animals.

It’s a beautiful thing! )

@David_Hopkins

This is pretty much exactly what I’ve been wanting! This would be awesome for anyone operating a business or sending subordinates to the store to fetch needed items that are purchased fairly regularly because you could even include exact location of items inside the store so when you send the new guy or gal to pick up a hose fitting they don’t come back 3 hours later with the wrong piece, experience!

Jonas
Participant V

@David_Hopkins still a sync has to happen between these steps…

@David_Hopkins it’s quite possible with a Webhook Workflow rule to query your API, record the request result to your sheet and then open the same records form with the EAN data filled in. If this is what you’re seeking for of course.

Very useful!

@Levent_KULACOGLU

thats about what im thinking at this point.

Scan barcode into temp table, pull from API, then open Form pre filled with data for the final record.

The API pull and Open Form would be

a grouped action.

With, as @Jonas points out, would include a sync in the process.

@David_Hopkins that depends a bit the response duration/time of the API you’re using. In general, if the service is free, you will be calling a share-point API service which might take time to respond depending on the server load but if you are on a subscribed service than (depending on the size of the API service) you might be on a dedicated server (like mailChimp API) which takes less time. But in general we tested and verified from the audit log that AS waits for the server/webhook response before ending the sync. The latter is; provided you are using delayed sync option w/automatic background updates, then possibly the form will be opened before waiting a reply from the API server. What @Jonas mentioned about the “sync” is exactly this, I believe. Anyway, it worths trying.

Jonas
Participant V

right now you can’t get live results in your app. Except for images:

for example: api.qrserver.com

I would download some of the data that you want to query and store it inside your own database. api.qrserver.com

So I guess that once you have scanned in some upc codes into your own database, you can then run a google.script to the upcitemdb.com - UPC Lookup Database with API access over 148 Million Unique UPC numbers and pull in the information.

Is that what you are suggesting Jonas?

mj UPC Lookup Database with API access over 148 Million Unique UPC numbers upcitemdb.com

Jonas
Participant V

No, because even the result of the script would not be visible in real time.

The only way right now the get “real-time-data” would be to store a portion of upctimedb to your own sheet / DB.

I guess you only need a fraction of the 148 million unique UPC/EAN numbers maybe they have some sort of category ?

I wasn’t thinking of real time, but rather building a database of upc items – could be just the numbers – that he is likely to scan and then run a google script to go up to the upcitemdb.com - UPC Lookup Database with API access over 148 Million Unique UPC numbers and get the information he needs.

UPC Lookup Database with API access over 148 Million Unique UPC numbers upcitemdb.com

@David_Hopkins

So David … does my suggestion even something to try or are you looking for a real time update to your database from AppSheet scan of a upc code?

mj

Top Labels in this Space