Pseudo binding in HTML service - Desktop Liberation

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.

Page Content hide

1 Structure notes

2 How does it work?

3 How to avoid sending loads of data all the time

4 Walkthrough

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

<?!= 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.

Client.gs

/**
* 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;
})();

/**

* 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';
}

/**

* 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';

/** 
  * 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;
})();

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;

})();

For more like this, see Google Apps Scripts Snippets

Why not join our community , follow the blog or and follow me on Twitter.