I am supporting CandidateX

CandidateX is a startup that focuses on creating inclusion-focused hiring solutions, designed to increase access to job opportunities for underestimated talent. Check them out if you have a few minutes to spare. They need visibility!

In Watching for server side changes from the client html service I showed a method of noticing and reacting to server side data changes from a client side Apps Script add-on.

It boiled down to this

   dataWatch.watch (function (current,pack,watcher) {     // do something - the latest data is in current.data , and the latest active selection information is in current.active.   }); Since there is no binding between the server and client-side, the way it works is to see if anything has changed from time to, and if it has then the callback is invoked.


Office binding

Office does have binding (there is no server component). The JavaScript API provides the capability to attach handlers to various bindings – where binding is an area of a sheet, or to certain events – like the selected range changing. These are exactly the kind of things that the watcher capability in Apps Script is designed to deal with.  This means that Office add-ins and Apps Script add-ons that rely on watching for changes in the main document can have exactly the same structure, even though what’s happening behind the scenes is fundamentally different. This means that you can write your apps without caring if the platform is Apps Script or Office. Here’s how I’ve implemented the watcher for the Office JavaScript API.  The Apps Script implementation is covered in Watching for server side changes from the client html service. If you are already using this in your App, then the changes required to take advantage of Office binding are fairly minimal – in fact, the ClientWatcher code is common between apps script and Office – but it might be heavy going if you are not already familiar with the ClientWatcher. In any case, it will introduce some of the Office API concepts in a practical setting.


The code for this on github.


Setting up the client watcher

That is set up in the same way as for the Apps Script version. In fact, the majority of the App code is common between the two versions, so I just leave this as it.       watching: {        watcher:ClientWatcher.addWatcher({          pollingFrequency:3000,          watch:{sheets:false,active:false},          domain:{fiddler:false,scope:elements.controls.wholeSheet.checked ? "Sheet" : "Active"}        })      } Since I have binding in Office, I can detect data and navigation changes immediately, so I could plot chart changes whenever they are provoked, instead of waiting for the next polling cycle. I do this when initializing the app, in the Server namespace.                // change the polling behavior for Office                Server.pollingBehavior(); If the client watcher is given a pollingFrequency of 0, it simply wont poll     /**     * in office, we dont need to pollFrequency     * so we'll modify the watcher to be passive     * it'll just need a poke each time ofice detects a change     */    ns.pollingBehavior = function () {        // we' going to tweak the watcher for office        Server.control.watching.watcher = Process.control.watching.watcher;        Server.control.watching.watcher.getWatching().pollFrequency = 0;    }; Instead, it has a poke function which will call it to review data changes and execute its callback if required. This gets called if Office detects any noteworthy navigation or data changes.             if (ns.control.watching.watcher) {                ns.control.watching.watcher.poke();            } If you decide that you’d rather keep the Apps Script behavior of regular polling, all that’s needed is to change the pollingBehavior function to do nothing. In this case changes will be reacted to only according to the pollingFrequency


Watching for active selection changes

The first thing to watch out for is changes in the active selection. This adds a handler that fires every time the user changes position.     /**      * if the active selection changes, then we need to check that the worksheet is still the same one     */    ns.watchForSelectionChanges = function () {        Office.context.document.addHandlerAsync("documentSelectionChanged", function (e) {            // get current worksheet and set up watching            ns.getCurrentWorksheet();        });    }; 


Getting the current worksheet and selected position.

If the change in selection includes a navigation away from the current sheet, then there’s a whole bunch of consequences, including the need for a new binding. If the navigation is within the same sheet, then the only thing that needs updated is to make a note of where we are now, and what the data in the active cell is.     /**     * get the current worksheet     * if its not the same as before, then get the used range, the active range, and watch out for changes     */    ns.getCurrentWorksheet = function () {        var scope = ns.control.current;         // first get the active worksheet        ns.getActiveWorksheet()            .then(function (sheet) {                if (!scope.sheetName || scope.sheetName !== sheet.name) {                    scope.sheetName = sheet.name;                    // get the current data for this sheet, and set up a new binding                    return Promise.all([ns.storeUsedRange(), ns.storeActiveRange(), ns.bindToRange(sheet.name + "!a:z", scope)])                }                else {                    return ns.storeActiveRange();                };            });    }; 


Getting the active worksheet

The first step in a navigation change to another sheet is to find out what sheet we’re now on. Like all operations with the JavaScript API, it happens asynchronously, so I wrap it in a promise so I can resolve the result back to the caller. The way Microsoft have implemented this is fairly ingenious. Here, the var sheet, is going to receive a ‘proxy’ for the real sheet object, which isn’t actually populated with real values  until the operation is synced. Syncing is like a conversation with a server side process, except that what’s being synced with is the Excel object model (whether online or locally). The .load() method is used to specify which properties to copy over. Minimizing these means minimizing unnecessary traffic between local and remote object model copies. Here I only want the value for the sheet name property. I’m wrapping this in a new promise which I can use to pass the sheet name to whoever has called this.     /**    * Get the active worksheet    * @return {Promise} to the sheet    */    ns.getActiveWorksheet = function () {        return new Promise(function (resolve, reject) {            Excel.run(function (ctx) {                // ask for load the name property of the current sheet                var sheet = ctx.workbook.worksheets.getActiveWorksheet().load("name");                // sync to get the data from the other side                return ctx.sync()                    .then(function () {                        resolve(sheet);                    });            });        });    }; 


Storing the used range

This like the dataRange in Apps script, and I store the current used range and the values there.     /**     * store the used range for the current sheet     * @return {Promise} used range promise     */    ns.storeUsedRange = function () {        var scope = ns.control.current;        return ns.getDataRange().then(function (rangeData) {            ns.storeData(rangeData, scope);        });     };


Getting the datarange

This is another async operation and this time I get the used range of the active worksheet and load a few properties that will be useful, along with the values, resolving a promise with these results after the sync has happened.

    /**     * returns a promise to values and other info for the data range     * of the active sheet     * Onlys selected object properties will be loaded including values     * @return {Promise} to the result     */    ns.getDataRange = function (type) {        return new Promise(function (resolve, reject) {            Excel.run(function (ctx) {                var usedRange = ctx.workbook.worksheets                    .getActiveWorksheet()                    .getUsedRange(true)                    .load("values,rowIndex,rowCount,columnIndex,columnCount,address");                return ctx.sync().then(function () {                    resolve(usedRange);                });            })        });    };


Storing the active range

This is similar to the the used range, except we want the values and properties for the currently selected range. This is the only interface with the watcher. If we are getting the active range, then something must have changes so we wake up the watcher (which has been set to not actively poll in the Office implementation). The Watcher implementation already knows how to see if there are any chartable differences in data from the last time it was called, so nothing more is needed.     /**     * store the active range for the current sheet     * @return {Promise} used range promise     */    ns.storeActiveRange = function () {        var scope = ns.control.selected;        return ns.getActiveRange().then(function (rangeData) {            ns.storeData(rangeData, scope);            // always do a poke because if we're getting the active range, then something may have changed            if (ns.control.watching.watcher) {                ns.control.watching.watcher.poke();            }        });    };    /**     * returns a promise to values and other info for the selected range     * of the active sheet     * Onlys selected object properties will be loaded including values     * @return {Promise} to the result     */    ns.getActiveRange = function (type) {        return new Promise(function (resolve, reject) {            Excel.run(function (ctx) {                var activeRange = ctx.workbook                    .getSelectedRange(true)                    .load("values,rowIndex,rowCount,columnIndex,columnCount,address");                return ctx.sync().then(function () {                    resolve(activeRange);                });            })        });    }; 


Making a binding

If the current worksheet has changed then we need to make a binding to the whole sheet, to watch for data changes anywhere in the sheet. You can’t make a binding to a dynamic usedrange (which would be ideal), so instead you need to make a binding to all the potential cells in the current worksheet (I’ve limited it to a:z), which happens like this. ns.bindToRange(sheet.name + "!a:z", scope) This first removes any previous handlers, then adds a new binding. The new binding has a handler attached which watches for data changes. If any happen, then we update the storing of the used and active ranges that I went through when the current workbook changed.     /**      * bind to a given range     * @param {string} theRange an a1 style range     * @return {Promise} when its done     */    ns.bindToRange = function (theRange, scope) {         // first need to cancel any outstanding handlers        return ns.removeDataChangeHandler(scope)            .then(function () {                return ns.addBinding(theRange, scope.name);            })            .then(function (result) {                scope.binding = result.value;                return ns.addHandler(scope.binding, function (e) {                    ns.storeUsedRange();                    ns.storeActiveRange();                });            });     }; 


Adding a handler

Even the adding of a handler is an asynchronous operation, and I wrap that in a promise since not all of the Office JavaScript API has been converted to promises yet.     /**    * add data change handler to binding     * @param {Binding} binding the binding to add to    * @param {function} handler the handler    * @return {Promise} to when its done    */    ns.addHandler = function (binding, handler) {        return new Promise(function (resolve, reject) {            binding.addHandlerAsync(Office.EventType.BindingDataChanged, handler, {}, function (result) {                if (result.status === Office.AsyncResultStatus.Succeeded) {                    resolve(result);                }                else {                    reject(result);                }            });        });    };


Storing the data

This what gets stored for each of the selected range and the usedrange when there are notable changes to them.     ns.storeData = function (eData, scope) {         scope.data = {            values: eData.values,            checksum: Utils.keyDigest(eData.values),            range: eData        };        return scope;    };


Communicating with the ClientWatcher

Now we have a place that active and used data are being stored autonomously by the functions above using the Office binding mechanism. Whether immediately through .poke() or by polling, eventually the clientWatcher will check in through the ServerWatcher component. In Apps Script, this is a server side function that is provoked from the client to go off and get the current state of the sheet data, compare a checksum with the data the client already knows about, and if it is different then it sends over the updated Sheet data. In the Office implementation, this communication between server and client is not necessary, since we will already have the latest sheet data tucked away by the handlers on the bindings. However the mechanism is exactly the same. The ClientWatcher code is the same for Office and for Apps Script. It calls this, which returns a promise to the changed sheet data. Provoke.run ("ServerWatcher", "poll", watch_).then (....) Except that in the Office version , Provoke.run doesn’t provoke a server side call as it does in the Apps Script version. Instead it runs it client side – which in the case of the office version is this. So all it has to do is to return data stored by the binding callbacks, depending on whether the chart is using a selected range or the whole sheet. /** * checks to see if there's been any binding callbacks since the last time * @param {object} watch what we're watching */ ns.poll = function (watch) {   // this is only  a minimal implementatino of serverwatcher    // it only watches for data in the active or sheet level for now    // its not fully clientwatcher compliant, as the Google Sheets one is    // since this app only needs a couple of things    // start building the result    var pack = {      checksum:watch.checksum,      changed:{}    };    // get data if requested    if (watch.watch.data) {      var scope = watch.domain.scope === "Active" ? Server.control.selected : Server.control.current;      var values = scope.data.values;      // need to play with values in case its a single cell      if (values && values.length && !Array.isArray(values[0])){        values = [values];      }      var cs = scope.data.checksum;      pack.changed.data = cs !== pack.checksum.data;      if (pack.changed.data) {        pack.data = values;        pack.checksum.data = cs;      }    }    return pack; }; 

The code for this on github.

For more like this, see  Google Apps Scripts snippets