Long Polling / Data Changed Event for Google Sheets
A while ago I did some work on creating a dataChanged event for a php TinyWebDB, I have now worked up a similar solution for a google sheet. This is of benefit for apps with multiple users who may be working on the same dataset in google sheets, all other users will need to be made aware of any changes, and to update their data.
How this works:
We set up a google apps script web app
On initialisation, this checks the original state/content of the sheet
Then , with two loops, one for a timed delay, the other for the number of "tries" we get the current state/content of the sheet
and compare this to the original.
If there is a difference, then the script finishes and returns text content to App Inventor to advise of the change
If the script finishes without finding any changes, it reports back to App Inventor, which will then initiate the script again.
We set up out App Inventor app to poll the sheet
Using a web component, the app will call the web app
The web app will send one of two responses: "there has been a change", "no change"
In either case, the app will call the web app again. This can be automated for a set number of runs, or unlimited.
There are two methods that can be used in the script:
fetch the sheet content as an array and compare this (is more specific based upon a single sheet/grid)
fetch the modifiedDate of the spreadsheet using the Advanced Drive Service (this is probably better for large datasets, but will report on any changes to the spreadsheet, including scripts)
Regardless the developer will probably want to return a new set of data to the app if there have been changes
The demo blocks and scripts below will return simple text content as opposed to a full dataset, for demonstration purposes.
Note: I am aware of the getLastUpdated() function that is available in DriveApp, but this proved to be unreliable and was taking up to 2 minutes to report a change. There is also a method using Drive.Revisions, which looks at the last revision date. This works, but extra effort is needed when the number of revisions goes past the 1000 mark.
(You need to enable the Drive API in Services for this to work)
You should be able to see that the scripts are set to run 20 times, every 10 seconds = @ 3 minutes. This well within Google's execution time limit. Developers can adjust the values to their liking. Developers should be aware of possible quota issues for a large number of users / high number of script executions.
You may want to return just the records (rows) that have changes in them. Here is a google apps script function that can do that, would need to be combined with the above scripts. This may take some time to work through a large dataset....:
You will need to grab the data arrays if using the Drive.modifieddate method