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.

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.