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
1 2 3 4 5 6 7 8 |
<!-- 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
1 2 3 4 5 6 7 8 9 10 |
// 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
// 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; })(); |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 |
/** * 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; })(); |
Process.gs
1 2 3 4 5 6 7 8 9 10 11 12 |
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; })(); |
Server.gs
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
/** * 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
'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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
/** * 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'; } |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 |
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; })(); |