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:
- When the app starts check/get the original state (date/content)
- Run a counter (for each) and a timer (sleep), and on each iteration check/get the current state (date/content)
- 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);
}