Apps for Office - binding example comparison

THIS IS BEING WRITTEN

In Pseudo binding in HTML service I showed how to simulate Sheets cell binding in an Apps Script Add-on. Office for apps includes binding out of the box. We'll use that example to show how easy it is to take an Apps Script Add-on and port it to an Apps for Office Add-in - for as long as you don't want to do too much with it. For the purposes of this discussion, we'll limit it to Excel running in Office 365 mode and Apps Script for Sheets.

At first glance Apps for Excel (AFO) and Apps Script for Sheets (GAS) look very similar.

  • Both allow the extension of their respective apps using JavaScript and HTML
  • They each have two kinds of extensions  for Add-ons/-ins. Dialog (GAS) or Content(AFO) Add-ons/-ins give a popup dialog which are intended for a one shot type of extension, and Sidebar (GAS) or Content (AFO) are for persistent extensions.
But - there is a massive difference in philosophy. 
  • Gas runs on Google Servers, with the Add-ons running on your client.
  • Afo runs on your client, whether it's Office 365 or Excel.
What that means is this
  • With GAS you can devise any functionality you want because their is only a loose coupling between the Server based app and the client based Add-on.
  • With AFO there is no server side component, which means that the Add-on is limited to whatever Microsoft have implemented in their API. This means that you'll get easy to create Add-ins where the capability exists in the API, but no chance of hacking together something that doesn't. This is a very big distinction - it means you can't run native stuff 'server side'. When I first started looking at this I had hoped to be able to create stuff in VBA that I could communicate with via the AFO API - but no such luck. This limits the kind of applications you can build.

Bearing that in mind, lets' get started porting this over. 

The development environment

You can use either visual studio or napacloud to develop your Add-in. I'm sticking to Napacloud since I'm using a Chromebook. What you want to build for your first app is a Takspane Add-in which is the equivalent of a sidebar Add-on. Don't worry about the boiler plate stuff it creates - we're going to replace most of it with a direct copy of the code from Pseudo binding in HTML service.

Getting started

Unlike GAS, you have to put in all the HTML decoration, and since the AFO API runs in the client, you need to pull in the scripts for that. 

  • Create an index.html, and copy in the contents of the Home.html that Napa created for you.
  • Get your index.html from Pseudo binding in HTML service and append it
  • Delete all the stuff from Napa in the head section except as below
  • Delete the body in Napa and translate your requiregs/js into script tags.
... or just  create an index.html and copy the below...

<!DOCTYPE html>
<html>


<head>
  <meta charset="UTF-8" />
  <meta http-equiv="X-UA-Compatible" content="IE=Edge" />
  <title>binding</title>
  <link href="../../Content/Office.css" rel="stylesheet" type="text/css" />
  <script src="https://appsforoffice.microsoft.com/lib/1.1/hosted/office.js" type="text/javascript"></script>
</head>


<body>
  <link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons.css">
  <div id="content"></div>
  <div id="notification"></div>

  <script src ="App.js" type="text/javascript"></script>
  <script src ="Home/Client.js" type="text/javascript"></script>
  <script src ="Home/Utils.js" type="text/javascript"></script>

  <script src ="Home/Process.js" type="text/javascript"></script>
  <script src ="Home/main.js" type="text/javascript"></script>

</body>

</html>

App.js

This is where the mechanics of the App are handled - like how to report and error and so on. You can delete the Napa contents and just replace it directly with the App.js code from the GAS version. It's exactly the same.

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


Process.js

Create this file in the Home directory. It's exactly the same as the GAS version.

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

Client.js

This is the code that is specific to Apps for Office, so although it will do roughly the same thing as the Apps Script version, we need to write it from scratch. It's worth looking at this at the same time as the Client.js in Pseudo binding in HTML service as this is really the only place that things are significantly different.

Create a client.js in the Home directory.


Utils.js

We only need a few functions from here, but since we'll need more of this stuff as we build up the app in future tutorials, just create Utils.js in the Home folder and copy the whole thing in. You don't need to bother looking at this code - it's exactly same as the Apps Script Utils.js in Pseudo binding in HTML service

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



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.

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


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 utilites



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


For more like this, see  Google Apps Scripts snippets. Why not join our community , follow the blogtwitterG+  .

You want to learn Google Apps Script?

Learning Apps Script, (and transitioning from VBA) are covered comprehensively in my my book, Going Gas - from VBA to Apps script, available All formats are available now from O'Reilly,Amazon and all good bookshops. You can also read a preview on O'Reilly

If you prefer Video style learning I also have two courses available. also published by O'Reilly.
Google Apps Script for Developers and Google Apps Script for Beginners.



Comments