Simple server side polling

As you know, there isn't a builtin way to get notified about changes in a host Document from a client side App. Ideally there should be some kind of trigger that can be invoked when the active sheet changes, or when there are data changes (onEdit is available of course, but that doesn't allow you to call certain functions .. such as UrlFetch .. which would allow you to use mechanisms such as Ephemeral Exchange to send out push notifications to watchers). On this site there are various polling schemes that show you how to watch for changes, but they can be quite complex. 

Here's a very simplified mechanism. 

In this example, I have an add-on that has various selection boxes which are derived from the sheets present in Spreadsheet. But what if a sheet is renamed, or deleted, or a new sheet is added ? You'd like the Add-on to quickly reflect the current state of the sheets in the Spreadsheet. 

Here I have 2 sheets in my Spreadsheet, and they've been populated in my add-on select options


When I change the name of one of those sheets, I need my add-on to known about it quickly and change the selection options, without the user having to do any kind of refreshing.

Similarly, when I remove or add new sheets, I'd like the same thing to happen automatically


The Drive change API

In Drive v3, it's possible to use the API to listen for changes as I described in Polling and the Drive changes API. This won't allow you to detect changes in selection, but will allow you to notice changes in documents when the changes are flushed to Drive. This post will show a simple polling approach, and a future post will contrast that with the Drive API method - which is a better (but more complex) solution if all you are doing is looking to be notified when a file content changes.

So here's a very simple polling method to detect changes

Polling interval

You need to decide how often you look for changes - In my case, every 15 seconds seems adequate. You don't want to make it too often otherwise you'll generate unnecessary traffic, and too infrequently will make it seem unresponsive. 

Server side function


Couldn't be more straightforward. Simply returns the active sheet and the sheets present in the Spreadsheet. This will be called every 15 seconds.
  // get names of all sheets in workbook
  ns.getSheetsInBook = function () {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    return {
      active:ss.getActiveSheet().getName(),
      sheets:ss.getSheets().map(function(d) { return d.getName(); })
    }; 
  };

Client side function


This calls the server side function. It uses Provoke.run, which is my promise wrapper for google.script.run. You can get all the code for all utility functions from any of my add-ons on github.
  // get sheets that exist
  ns.getSheetsInBook = function () {
  
    return Provoke.run ("Server", "getSheetsInBook")
    .then (function (sheets) {
      ns.settings.sheets = sheets;
      ns.adaptSheets();
    })
    ['catch'](function (err) {
      App.showNotification  ("Error getting sheets", err); 
    });
    
  };

Calling this at regular intervals is done with a recursive promise
  ns.looping = function () {
    
    loopSheetManifest();
    
    function loopSheetManifest() {
      Promise.all ([ns.getSheetsInBook(), Provoke.loiter (ns.settings.pollTime)])
      .then (function () {
        loopSheetManifest();
      });
    }
    
  };
Where Provoke.loiter is a promise based wrapper for setTimeout - You can get all the code for all utility functions from any of my add-ons on github.

Since both getSheetsInBook and loiter return promises, we can wrap their calls in Promise.all. When both have been resolved, we do it over again forever ...  and that's it.

Memory leaks

There's always the chance of inadvertently causing a memory leak with never ending processes. According to this issue about the Node Promise implementation, this can be avoided by omitting the return in the .then action, as this breaks the Promise chain.

so this is bad
return loopSheetManifest();

and this is good
loopSheetManifest()

Drive API alternative

Watch this space, but the Drive API is all about changes, so we can't determine changes in the activesheet or selection with that.

For more like this, see Google Apps Scripts snippets. Why not join our community , follow the blog, twitter, G+ .You want to learn Google Apps Script?

Learning Apps Script, (and transitioning from VBA) are covered comprehensively in my my book, Going Gas - from VBA to Apps script, available All formats are available now from O'Reilly,Amazon and all good bookshops. You can also read a preview on O'Reilly

If you prefer Video style learning I also have two courses available. also published by O'Reilly.
Google Apps Script for Developers and Google Apps Script for Beginners.



Comments