Best Method for polling a google sheet for changes using a Google Apps Script Web App?

Question

I am working on a google apps script web app to get it to poll a google sheet for changes/updates. At the moment this can either be "content" or simply when the sheet was last updated. I cannot use a gui/browser for interaction, so consider that the command to run the web app is coming from curl on the command line, and returning text content to the same. The user will re-initiate the web app as required, which is set to run for @ 3 minutes.

I have an underlying routine that works:

1. When the app starts check/get the original state (date/content)
2. Run a counter (for each) and a timer (sleep), and on each iteration check/get the current state (date/content)
3. On each iteration test the original state against the current state. If these are different, then sent text output through the ContentService (either message, date or current content.

I have found three methods to apply to the above underlying routine:

A. Get the sheet dataRange values as an array (sh.getDataRange().getValues();)

B. Get the DriveApp LastUpdated values (DriveApp.getFileById(fileId).getLastUpdated();)

C. Get the revisions list last modified date (revisions = Drive.Revisions.list(fileId))

A works well and can be targeted at a specific grid(sheet) on the spreadsheet, but could be problematic with a large dataset ?

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 = JSON.stringify(cur);
      return ContentService.createTextOutput(msg);
    }
    Utilities.sleep(10000);
  }

  return ContentService.createTextOutput(msg);

}

B does work, but it seems you have to wait for between 1 and 5 minutes for an update on the sheet to be reported. This "could" be OK, but for a user making the changes it won't be quick enough

function doGet() {

  var lastUpdated = DriveApp.getFileById(fileId).getLastUpdated();
  var ss = SpreadsheetApp.openById(fileId);
  var sh = ss.getSheetByName('Sheet2');
  var rng = sh.getDataRange().getValues();
  var msg = '';

  for (var i = 0; i < 20; i++) {
    SpreadsheetApp.flush();
    var curUpdate = DriveApp.getFileById(fileId).getLastUpdated();
    i = i + 1;
    if (lastUpdated.toString() !== curUpdate.toString()) {
      msg = lastUpdated.toString() + "
" + curUpdate.toString();
      return ContentService.createTextOutput(msg);
    }
    Utilities.sleep(10000);
  }

  return ContentService.createTextOutput(msg);

}

C works well and reports a date change almost immediately. I have concerns though, having read that a new token is required after 1000 revisions ?

function doGet() {
  
  var msg = '';
  var revisions = Drive.Revisions.list(fileId);
  var revision = revisions.items[revisions.items.length - 1];
  var date = new Date(revision.modifiedDate);
  
for (var i = 0; i < 20; i++) {
  
    var currevs = Drive.Revisions.list(fileId);
    var currev = currevs.items[currevs.items.length - 1];
    var curdate = new Date(currev.modifiedDate);
    
    i = i + 1;
    
    if ( date.toString() !== curdate.toString() ) {
      msg = date.toString() + "
" + curdate.toString();
      return ContentService.createTextOutput(msg);
    }
    Utilities.sleep(10000);
  }

  return ContentService.createTextOutput(msg);

}

It would make sense to use B, but I cannot find a way to make getLastupdated's response to be more immediate, perhaps C, using revisions is the way to go? Have I missed a "go to" method for this, or does my web app routine require improvements?

Further to @Diego's suggestions, I also tried the "modifiedDate" through the Advanced Drive Service. This works a treat :)

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() + "
" + curUpdate.toString();
      return ContentService.createTextOutput(msg);
    }
    Utilities.sleep(10000);
  }

  return ContentService.createTextOutput(msg);

}

Answer 1

The difference I see between using the modified date on the file versus the latest revision, is that the file will also report changes to bound scripts. Revisions will be limited to changes on the spreadsheet and exclude script changes. This is can be seen in my test below where there's several minutes difference between the two.

If you want to specifically limit the scope to spreadsheet-only changes, then the most accurate value will be reported by using revisions.

function test() {
  const fileId = FILE_ID;
  console.log(getLastRevision(fileId)); // 2021-01-12T15:32:43.175Z
  console.log(getLastUpdated(fileId)); // Tue Jan 12 2021 15:53:53 GMT+0000 (Greenwich Mean Time)
  console.log(getLastUpdatedAdvanced(fileId)); // 2021-01-12T15:53:53.941Z
}

function getLastRevision(fileId) {
  let response;
  do {
    response = Drive.Revisions.list(fileId, { maxResults: 1000 });
  } while (response.nextPageToken);
  return response.items[response.items.length - 1].modifiedDate;
}

function getLastUpdated(fileId) {
  return DriveApp.getFileById(fileId).getLastUpdated();
}

function getLastUpdatedAdvanced(fileId) {
  return Drive.Files.get(fileId).modifiedDate;
}

I don't know how many revisions you may expect a spreadsheet to have, but using a nextPageToken is quite straight-forward. However, if the spreadsheet will have many thousands of revisions and you're going to be polling this frequently, then it may not be the most effective method.

I wasn't able to replicate a delay with DriveApp.getFileById(fileId).getLastUpdated(), but it doesn't strike me as unusual. You could instead try using the Advanced Drive service, as you're doing with Revisions, to get the modifiedDate. I would not expect this to return a delayed value. As you indicated, it may be your best option as you don't need to worry about the number of revisions, but keep in mind that it also reflects non-spreadsheet updates.