Sync when data on Google Sheet changes

Hi All,

I’m new to AppSheet. I’ve used Google Sheets, Google Forms & Google Apps Script before.
My main question is if I can force a table to update if remote external changes have happened to a Google sheet (or use another data storage source that can force a table update).

I wanted to make an app for staff to use to keep track info & numbers of customers in the store. Government regulations for COVID-19 restrictions means we have to record contact details of all customer that dine-in at our store (hospitality).

I made a Google Form, it updates the Google Sheet just fine. It’s a customer Check-In form where the customer checks-in to the store & records all their contact details (for the government covid tracing requirements).
I made an app in AppSheet & got most of what I needed for in store staff and it works very well.
It displays current customer Check-in’s, allow for staff to edit or Check-Out customer, and a few other niceties.

My only real problem is that if a customer checks-in on the Google Form, the sheet updates, but the staffs view on the table won’t show them the check-in. Either they have to wait for the next background update, or they’d have to manually refresh/update every time they wanted to check customer numbers, etc. Unfortunately relying on staff to remember to update every time they check the data/totals is too risky for us from a gov. regulation compliance standpoint.

Is there anyway to make a table update when external changes (not from AppSheet) have occurred on a Google Sheet?

I’ve read many support pages, blogs, etc. It looks to me like it’s not possible, but I’m going to ask the question anyway.

Thanks,
Mathew

There are two limitations here:

  1. There’s no way to force a sync on another device. Either the user syncs, or wait for background sync.
  2. Edits to the Sheet via Google Forms will not trigger anything in AppSheet. The edit has to be made through the app or API to trigger other logic/actions like a notification.

You might be able to use an OnSubmit() trigger with your Google form to call the AppSheet API and send out a notification when a new form arrives. Then clicking the notification would sync the app. But the user would still have to interact, so it’s not much different than just having them sync manually.

4 Likes

Incremental update on this stuff.
I implemented google script functions on the sheet behind the AppSheets app that communicates well with the AppSheets API. That stuff seems to work well & allows me modify values & stuff in AppSheet.
My hope was to find something that would force an GUI update on a table. E.g. like updating a calculated table value or something.
Although all that I tried seemed to work, nothing will make the GUI update (that I’ve yet found).

This end up being a sticking point for being able to use AppSheet for our app in our business. I know in our app that staff can just push something to update it, but really they just won’t do it & won’t understand that they need to do that every time before they can trust the table information they can see.

Still reading & trying stuff. Hmmm.

There is no way to push updates to the app; updates can only be pulled when the app syncs with the server. A sync is always initiated by the app, not the server.

Thanks Steve & Greenflux.
Yep, that was the conclusion I ended up coming to last night.
No matter. At least I got to work out how all the API stuff works, which might end up being useful anyway.

1 Like

Integration with real-time database such as Firebase/Firestore could solve the problem. I m on hope sometime and someday Appsheet dev team brings such a feature to make the Appsheet more and more functional.

I do have use case where I need to make sure the app users are seeing the most latest back-end data as much as possible. For now, there is no perfect solution and workaround, but as an effort to “minimise” such a risk. I do place a trick of “force sync” based on the particular view here and there in my Apps.

For instance, some list table, such as table and deck view are only accessible through the appsheet action where deeplink expression are kicked in along with “force sync” syntax. When the user tried to add new row to those table, then new form to opens up after the full sync, by replacing the original/default action button to add row.

Yes, still there is a risk …

For instance, one user open a new form under this settings. While this user keep form view open wihtout saving and then another user open new form and save new row. The first user is actually not working on the most latest data.

Can t remove a risk unless the server push the change in backend, there is a bit of workaround as an effort to miniize such a risk if it is acceptable.

1 Like