Long Polling / Data Changed Event for Google Sheets

INTRO

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.

SCRIPTS

Comparing Arrays

function doGet() {

 var ss = SpreadsheetApp.openById('fileID');

 var sh = ss.getSheetByName('SheetName');

 var rng = sh.getDataRange().getValues();

 var msg = '';

 for (var i = 0; i < 20; i++) {

   SpreadsheetApp.flush();

   var cur = sh.getDataRange().getValues();

   i = i + 1;

   if (JSON.stringify(rng) !== JSON.stringify(cur)) {

     msg = 'Data has changed';

     return ContentService.createTextOutput(msg);

   }

   Utilities.sleep(10000);

 }

 return ContentService.createTextOutput(msg);

}

Comparing modifiedDate

(You need to enable the Drive API in Services for this to work)

function doGet() {

 var lastUpdated = Drive.getFileById('fileID').modifiedDate;

 var msg = '';

 for (var i = 0; i < 20; i++) {

   var curUpdate = Drive.getFileById('fileID').modifiedDate;

   i = i + 1;

   if (lastUpdated.toString() !== curUpdate.toString()) {

     msg = lastUpdated.toString() + "\n" + curUpdate.toString();

     return ContentService.createTextOutput(msg);

   }

   Utilities.sleep(10000);

 }

 return ContentService.createTextOutput(msg);

}

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.

BLOCKS

ADDITIONAL

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

 var changes = [];

 for (var k = 0; k < rng.length; k++) {

  if (JSON.stringify(rng[k]) !== JSON.stringify(cur[k])) { 

     changes.push(cur[k]); 

     } 

  }

 msg =  changes;

You will need to grab the data arrays if using the Drive.modifieddate method