Setting up onChange trigger

Installable change trigger

Sheets has both an onEdit and an onChange trigger to detect changes in a sheet. We’ll be using the onChange trigger, which is an installable trigger. The important difference between an installable trigger and a simple one is to do with the sequence of installation in your script. Since Ephemeral Exchange is an external service, it needs authorization from to be able to run, therefore needs to be specifically installed.

What happens onChange

When the onChange trigger fires, an item is written to the efx store describing the change. Efx push notification wakes up the client running in the browser to inform of the changes. This is the onChange event. We’ll look into that in more detail throughout this and subsequent articles.

/** 
   * @param {object} event the event from an onchange (needs at least source:(the spreadsheetapp) & changeType: properties)
   * @param {string} [method=getDisplayValues]  for future expansion, the type of data to get
   */
  ns.efxChanger = function(event, method) {
  
    // get a shortcut
    const sf = Server.sfInit();
  
    // get the digest package - summarize current state of spreadsheet
    const pack = sf.ssChangePack (event,method);
  
    // and write it to efx
    var result = sf.update (pack);

    return result;
  }

Installing the trigger

This method install the onChange trigger, tidying up any previous versions as it goes. If you are creating an add-on (as opposed to a menu sidebar), there are limitations on installing triggers in add-on test mode – you can’t do it. Since publishing an add-on to test it seems too much work, I have a workaround for the testing phase – described here When test add-ons doesn’t work. We’ll use that approach later when we come to assemble the whole application

 * install onchange trigger
   * @param {string} name the name
   * @return {Trigger}
   */
  ns.installChangeTrigger = function (name) {
  
    if (typeof this[name] !== "function") throw name + " needs to be a function to be installed on change";
    
  // first delete any instances already installed
    ScriptApp.getProjectTriggers().slice()
    .forEach (function (d) {
      if (d.getHandlerFunction() === name) ScriptApp.deleteTrigger(d);
    });
  
  // now install freshly
    return ScriptApp.newTrigger(name)
      .forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet())
      .onChange()
      .create();
  };

What gets written to efx

When a change is detected, this object is written to the store, and can be retrieved by the client. Note that no data is stored, just metadata about the current state of the sheet. This minimizes the amount of traffic that is written on each change.

{
  "content": {
    "activeSheet": {
      "name": "Sheet1",
      "sheetId": 0,
      "activeRange": {
        "a1": "A2:A9",
        "startRowIndex": 2,
        "startColumnIndex": 1,
        "numOfRows": 8,
        "numOfColumns": 1
      },
      "dataRange": {
        "a1": "A1:J17",
        "startRowIndex": 1,
        "startColumnIndex": 1,
        "numOfRows": 17,
        "numOfColumns": 10
      },
      "digest": "scgr8BuJzsVrIlNHbG1KobteSPg="
    },
    "spreadsheet": {
      "id": "15LW0WuQe8X_enPqnuRIQ_osSgg_dKh32ijI1cnmTHq8",
      "sheets": [
        {
          "name": "Sheet1",
          "sheetId": 0
        },
        {
          "name": "Sheet2",
          "sheetId": 1198037846
        }
      ]
    },
    "changeType": "INITIALISE",
    "packDigest": "jadL0lGzMAG1a7-Ra3vOeKQE0wg="
  }
}

It’s fairly self explanatory, but let’s look at the content.activeSheet.digest properties and the content.packDigest properties.

• content.activeSheet.digest contains a checksum that summarizes the data in the active range. Maintaining a current value in the Client and comparing it against this, determines whether the data content has actually changed. If the two are different, then your client can follow up with a data fetch to the server.
• content.packDigest contains a checksum to indicate whether anything has changed (for example the activeRange). It’s possible then that packDigest will have changed, but digest doesnt – in which case you don’t need to bother getting the updated data.

Complete Trigger namespace

Just a repeat of the two methods above

var Triggers = (function(ns) {

  /*
   * install onchange trigger
   * @param {string} name the name
   * @return {Trigger}
   */
  ns.installChangeTrigger = function (name) {
  
    if (typeof this[name] !== "function") throw name + " needs to be a function to be installed on change";
    
  // first delete any instances already installed
    ScriptApp.getProjectTriggers().slice()
    .forEach (function (d) {
      if (d.getHandlerFunction() === name) ScriptApp.deleteTrigger(d);
    });
  
  // now install freshly
    return ScriptApp.newTrigger(name)
      .forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet())
      .onChange()
      .create();
  };
  
   
 /** 
   * @param {object} event the event from an onchange (needs at least source:(the spreadsheetapp) & changeType: properties)
   * @param {string} [method=getDisplayValues]  for future expansion, the type of data to get
   */
  ns.efxChanger = function(event, method) {
  
    // get a shortcut
    const sf = Server.sfInit();
  
    // get the digest package - summarize current state of spreadsheet
    const pack = sf.ssChangePack (event,method);
  
    // and write it to efx
    var result = sf.update (pack);

    return result;
  }

  return ns;
} ) ({});

For more like this, see Google Apps Scripts snippets. 

Continue reading about Pushing changes from Google sheets to client here