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