One of the good things about Microsoft apps for office Add-ins (these are similar to Apps Script Add-ons in that you get a sidebar where you can extend apps with HTML apps), is that data binding is built in. That means that you can set up a callback to detect any time a particular range of data changes. Apps Script doesn’t have that, but what it does have (Microsoft doesn’t), is the ability to script co-operating scenarios for both the client and server-side.
That means that we can create a kind of binding by the client-side asking the server side what’s changed every now and again.
Here’s a simple demo of an Add-on that polls the active sheet for changes. You can play with it here – just start up the binding add-on.
Structure notes
I’m using my usual layout for HTMLservice so I have more files than you would need to for such a simple demo, but you should find it a useful starting pattern for more complex apps as described in More client-server code sharing
How does it work?
The client polls the server at regular intervals to get the latest data on the active sheet
How to avoid sending loads of data all the time
The server calculates a checksum of the current data. The client asks for data passing over the last checksum it got back. If they are the same, no data is transferred. If they are different, the server transfers the latest data and checksum.
Walkthrough
Let’s start with the sidebar html file. It uses the techniques described in More client server code sharing to pull in the JavaScript code. Otherwise it just has a content div – where I’m going to display the latest sheet data, and a notification div for errors.
index.html
<!-- This CSS package applies Google styling; it should always be included. --> <link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons.css"> <div id="content"></div> <div id="notification"></div> <?!= requireGs(['App','Client','Utils','Process']); ?> <?!= requireJs(['main']); ?>
Next the local javascript to kick off the application.
main.js.html
// the app
window.onload = function () {
// set up client app structure
App.initialize();
// get some data to the client and start
Client.getData();
};
Next the App script file – this just sets up the content areas of the sidebar and how to communicate with them. Although this is intended to run on the client, I store this as an Apps Script .gs file.
App.gs
// the mechanics of the app
var App = (function () {
'use strict';
var app = {};
// any settings/options for this app.
app.settings = {
divs: {
notification:undefined,
content:undefined
}
};
// initialize the app
app.initialize = function () {
app.settings.divs.notification = document.getElementById('notification');
app.settings.divs.content = document.getElementById('content');
return app;
};
// display a message
app.showNotification = function (title, message) {
app.settings.divs.notification.innerHTML = title + '<br>' + message;
};
// clear message
app.clearNotification = function (message) {
app.settings.divs.notification.innerHTML = "";
};
return app;
})();
Now the client code. This is the code that communicates with the server – every 2 seconds it checks in with the server, passing over the checksum for the data it last received. If it’s different from the current data checksum, the server will return the latest data from the active sheet. You can see that if any data has changed, it will call Process.callback() – this simulates the Microsoft binding callback. Again this is stored as an apps script .gs file although it only runs on the client.
/**
* communicate with the server from the client
*/
var Client = (function() {
var client = {};
// this is where the latest result will be stored
client.settings = {
result:{},
polling: {
interval:2000
}
};
/**
* gets data from the server
*/
client.getData = function () {
// clear any outstanding messages
App.clearNotification();
// get data from sheet
google.script.run
.withFailureHandler(function(error) {
App.showNotification ("data retrieval error", JSON.stringify(error));
})
.withSuccessHandler(function(result){
// the use of a co-operating checksum will avoid transferring data that hasnr changed
// the server won;t bother to send any data if nothing has changed
if(result.data) {
client.settings.result = result;
// this is the pseudo call back
Process.callback ( result );
}
// poll again for next look
client.startPolling();
})
.getData(client.settings.result.checksum);
};
/**
* every now and again, go and get the latest data
*/
client.startPolling = function () {
setTimeout(function(){
Client.getData();
}, client.settings.polling.interval);
};
return client;
})();
Now the process code. This is where you would do something with the sheet data. It contains the callback, which for this demo simply displays it in the sidebar. Again this is stored as an apps script .gs file although it only runs on the client.
Process.gs
var Process = (function() {
var process = {};
// this is called if any data changes
process.callback = function (result) {
// this is where we' process the data
// for this demo, we'll just display it
App.settings.divs.content.innerHTML = JSON.stringify (result.data);
};
return process;
})();
Now the Server code. This executes exclusively on the server, and is responsible for picking up the data from the sheets, and deciding whether it should send any back based on the checksum value.
Server.gs
/**
* called to return latest active sheet data
* @param {number} checksum the checksum for the last data we got
* @return {object} object with new checksum and potentially the data if anything has changed
*/
function getData (checksum) {
return Server.getData (checksum);
}
var Server = (function() {
var server = {};
/**
* get the data from the active sheet
* @param {number} previousChecksum if its the same then no point in returning any data
* @return {[[]]} sheet data
*/
server.getData = function (previousChecksum) {
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getDataRange();
var data = range.getValues();
var p = {id:sheet.getSheetId(), range:range.getA1Notation() , data:range.getValues() };
var thisChecksum = Utils.checksum (p);
return (!Utils.isUndefined(previousChecksum) && previousChecksum !== thisChecksum) ? {checksum :thisChecksum,data:data} : {checksum:thisChecksum};
};
return server;
})();
And of course the initialization server side code to expose the add -on is just boiler plate stuff
'use strict';
/**
* Adds a custom menu with items to show the sidebar and dialog.
*
* @param {Object} e The event parameter for a simple onOpen trigger.
*/
function onOpen(e) {
SpreadsheetApp.getUi()
.createAddonMenu()
.addItem('binding', 'showBinding')
.addToUi();
}
/**
* Runs when the add-on is installed; calls onOpen() to ensure menu creation and
* any other initializion work is done immediately.
*
* @param {Object} e The event parameter for a simple onInstall trigger.
*/
function onInstall(e) {
onOpen(e);
}
/**
* Opens a sidebar.
*/
function showBinding() {
var ui = HtmlService.createTemplateFromFile('index.html')
.evaluate()
.setSandboxMode(HtmlService.SandboxMode.IFRAME)
.setTitle('binding demo');
SpreadsheetApp.getUi().showSidebar(ui);
}
First Require.gs, which executes on the server and is used to pull in the source code of the client scripts into the HTML file. It is executed from index.html And a couple of utilities
/**
* given an array of .html file names, it will get the source and return them concatenated for insertion into htmlservice
* like this you can share the same code between client and server side, and use the Apps Script IDE to manage your js code
* @param {string[]} scripts the names of all the scripts needed
* @return {string} the code inside script tags
*/
function requireJs (scripts) {
return '<script>\n' + scripts.map (function (d) {
return HtmlService.createHtmlOutputFromFile(d+".js").getContent();
})
.join('\n\n') + '</script>\n';
}
/**
* given an array of .gs file names, it will get the source and return them concatenated for insertion into htmlservice
* like this you can share the same code between client and server-side, and use the Apps Script IDE to manage your js code
* @param {string[]} scripts the names of all the scripts needed
* @return {string} the code inside script tags
*/
function requireGs (scripts) {
return '<script>\n' + scripts.map (function (d) {
return ScriptApp.getResource(d).getDataAsString();
})
.join('\n\n') + '</script>\n';
}
And Utils.gs. This contains a bunch of utilities, not all of which are needed by the code. It executes on both the server and client as described in Sharing code between client and server. You don’t need to bother looking at this code, but you will need it if you are building this pattern.
var Utils= (function () {
'use strict';
var utils = {};
/**
* a little like the jquery.extend() function
* the first object is extended by the 2nd and subsequent objects - its always deep
* @param {object} ob to be extended
* @param {object...} repeated for as many objects as there are
* @return {object} the first object extended
*/
utils.extend = function extend () {
// we have a variable number of arguments
if (!arguments.length) {
// default with no arguments is to return undefined
return undefined;
}
// validate we have all objects
var extenders = [],targetOb;
for (var i = 0; i < arguments.length; i++) {
if(arguments[i]) {
if (!utils.isObject(arguments[i])) {
throw 'extend arguments must be objects not ' + arguments[i];
}
if (i ===0 ) {
targetOb = arguments[i];
}
else {
extenders.push (arguments[i]);
}
}
};
// set defaults from extender objects
extenders.forEach(function(d) {
recurse(targetOb, d);
});
return targetOb;
// run do a deep check
function recurse(tob,sob) {
Object.keys(sob).forEach(function (k) {
// if target ob is completely undefined, then copy the whole thing
if (utils.isUndefined(tob[k])) {
tob[k] = sob[k];
}
// if source ob is an object then we need to recurse to find any missing items in the target ob
else if (utils.isObject(sob[k])) {
recurse (tob[k] , sob[k]);
}
});
}
};
/**
* check if item is undefined
* @param {*} item the item to check
* @return {boolean} whether it is undefined
**/
utils.isUndefined = function (item) {
return typeof item === 'undefined';
};
/**
* check if item is undefined
* @param {*} item the item to check
* @param {*} defaultValue the default value if undefined
* @return {*} the value with the default applied
**/
utils.applyDefault = function (item,defaultValue) {
return utils.isUndefined(item) ? defaultValue : item;
};
/**
* isObject
* check if an item is an object
* @param {object} obj an item to be tested
* @return {boolean} whether its an object
**/
utils.isObject = function (obj) {
return obj === Object(obj);
};
/**
* clone
* clone a stringifyable object
* @param {object} obj an item to be cloned
* @return {object} the cloned object
**/
utils.clone = function (obj) {
return utils.isObject(obj) ? JSON.parse(JSON.stringify(obj)) : obj;
};
/**
* convenience getbyid
* @param {string} id element id
* @return {element} the element
*/
utils.el = function(id) {
return document.getElementById(id);
};
/**
* convenience aqdd element
* @param {element} parent the parent
* @param {string} type the element type
* @param {string} aclass the optional class list
* @return {element} the element
*/
utils.elAdd = function (parent, type, aclass) {
var elem = document.createElement(type || "div");
if(aclass) elem.className = aclass;
parent.appendChild(elem);
return elem;
};
/**
* convenience aqdd text element
* @param {element} parent the parent
* @param {string} text the text to assign
* @return {element} the element
*/
utils.textAdd = function (parent, text) {
var elem = document.createTextNode(text);
parent.appendChild(elem);
return elem;
};
/**
* create a column label for sheet address, starting at 1 = A, 27 = AA etc..
* @param {number} columnNumber the column number
* @return {string} the address label
*/
utils.columnLabelMaker = function (columnNumber,s) {
s = String.fromCharCode(((columnNumber-1) % 26) + 'A'.charCodeAt(0)) + ( s || '' );
return columnNumber > 26 ? shared.columnLabelMaker ( Math.floor( (columnNumber-1) /26 ) , s ) : s;
};
/**
* hide an element
* @param {element} element to hide
* @return {element} for chaining
*/
utils.hide = function (element) {
return utils.show(element,"none");
};
/**
* show an element
* @param {element} element to hide
* @param {string} display style (default block)
* @return {element} for chaining
*/
utils.show = function (element,display) {
element.style.display=display || "block";
return element;
};
/**
* checksum
* create a checksum on some string or object
* @param {*} o the thing to generate a checksum for
* @return {number} the checksum
**/
utils.checksum = function (o) {
// just some random start number
var c = 42;
if (!utils.isUndefined(o)){
var s = (utils.isObject(o) || Array.isArray(o)) ? JSON.stringify(o) : o.toString();
for (var i = 0; i < s.length; i++) {
c += (s.charCodeAt(i) * (i + 1));
}
}
return c;
}
return utils;
})();