Office Add-ins – first attempt

Office has the concept of an Add-in (previously these were called Apps for Office as well as the JavaScript API for office). At first sight they look pretty much the same as Apps Script Add-ons – either a dialog (called content) app or a sidebar (called task pane) app – all written in Html/JavaScript with the capability to interact with a host document

However they are fundamentally different.

  • Apps Script consists of a server component and a client component. You can execute any server based script you can imagine writing from the client using google.script.run(). So you have access to the full range of native Apps Script functionality. There is only loose coupling between the document and the client app – you have to write that conversation yourself.
  • Apps for Office does not have a server component, so you can’t script things accessing the local document services (perhaps in VBA). You can only do what the API knows how to do – and frankly, that’s not too much.
Page Content hide
1 Converting an Apps Script Add-on
2 Binding
3 Async
4 Data changed handler
5 Selection changed handler
6 Document selection changed handler
7 Creating a binding
8 Noticing the selection has changed
9 The final result
10 Conclusion

 

Converting an Apps Script Add-on

As a learning exercise, I tried to port Color Arranger over to Office but soon discovered that the properties of cells (background colors) were not accessible via the API. Since that is at the heart of the app, I had to give up on that. I had more luck with Sankey Snip, but there were still some fundamental capabilities missing that I had to hack around. I’ll publish the Add-in on the Office store soon, but for now here’s how I got round some of the binding limitations of the Office API.

 

Binding

A binding is a mapping between the client app and a particular range in the document. This concept doesn’t exist in Apps Script. It’s up to you to notice when the activecell changes or if data changes on a sheet. Of course since you can do whatever you want from the client with a co-operating server piece, it’s easy to simulate binding as described in Pseudo binding in HTML service. Since Office does have the concept of Binding, I was expecting this to be a breeze. There are some gaps in the Office API though that made it very hard to implement this Add-in – where I need to dynamically discover and build a chart from whatever data is currently being looked at.

  • The concept of DataRange() does not exist. You can’t easily set up a binding to all ‘data on the sheet’
  • Bindings can only be made to tables (which can resize and still stay together), a selection (the user has selected some cells), or a given range (which won’t resize as more rows are added). So you have to know in advance where your data is, rely on the user creating a table of it or selecting it.
  • You can’t tell where you are – the address of the currently active cell. You can set up event callbacks for when selections change either in a binding or in the Spreadsheet as a whole, but that callback doesn’t tell you where you’ve moved to. This omission is the most startling of all. I still believe I must be missing something.

 

Async

Everything is Async. It’s good that it’s non- blocking, but without bringing promises into it (which are not supported by all browsers yet), it makes things fairly complicated. Even adding and removing handlers is async.

 

Data changed handler

At the heart of this (and I guess most) interactive apps is the ability to know when some data has changed. This sets a callback to be executed if any data within a given binding (which is associated with a range) changes  binding.addHandlerAsync(Office.EventType.BindingDataChanged, function(e) { // do something });

 

Selection changed handler

We also need to know when the selection is changed (moving around inside the binding). This will not fire if selection is changed to somewhere outside the binding  binding.addHandlerAsync(Office.EventType.BindingSelectionChanged, function (e) { // do something });

 

Document selection changed handler

We’ll need this to detect if the selection changes. This always fires when the active cell is changed       Office.context.document.addHandlerAsync(Office.EventType.DocumentSelectionChanged, function (e) {         // do something       });

 

Creating a binding

None of the supported ways of making a binding to a region is going to work for me. The Apps Script app simply detects whatever data is on the current sheet and creates a chart from it. You can’t seem do that with the Office API. For this solution, I’ll create an arbitrary binding, and adjust it till I find a complete block of data, leaving a few blank rows and columns around it to allow for expansion. I can’t really believe I need to do this to ask these simple questions, and I’m almost certain that when I’ve done a few more of these I’ll have a ‘duh’ moment. If anyone knows what I’m missing – please let me know. Here are my questions.

  • Give me all the data on the current sheet
  • If any data on the sheet changes, let me know
  • Tell me where I am, especially if I change sheet

In any case, using this hack,  the BindingDataChanged event will fire both when existing data is changed, and new columns or rows are added. Here’s some settings we’ll use throughout. client.settings = {  binding: { active: null, changed:false,initialRange:'a1:e50',rowExtend:10,columnExtend:2,rowGrace:5,/code>columnGrace:2,/code>resetDelay:2000/code>}}; Here’s how I figure out a good binding, which will adjust itself as we go along.

  • Start with a reasonably sized range. If we already have a binding active, then extend it a bit.
  • Get the data in that binding. If it’s a block of data surrounded by spaces then we’ve got a good basis. If not, then extend it again till we have

    /**     * you cant tell where you are, or even get the whole sheet using this API     * I want to be able to guess where the data is on a sheet, so i'll set a binding     * bigger than any data I find - adjusting the binding till i have some space to allow adding new data     */    client.guessTheBinding = function () {        var cs = client.settings.binding;                // we start with a binding likely to encompass the data        // then extend the current binding till we are surrounded by blank space        var nameTarget = cs.active ?            'a1:' + (Utils.columnLabelMaker(cs.active.columnCount + cs.columnExtend) + (cs.active.rowCount + cs.rowExtend)) : cs.initialRange;         // get rid of any watches on existing bindings as we're making a new one         if (cs.active) {            Office.context.document.bindings.releaseByIdAsync(cs.active.id, function (result) {                if (result.status == Office.AsyncResultStatus.Failed) {                    App.showNotification('binding release failure', result.error.message);                }            });        }         // don't need to wait for that to finish .. get on and create a new one        Office.context.document.bindings.addFromNamedItemAsync(nameTarget, Office.BindingType.Matrix, null, function (result) {             if (result.status == Office.AsyncResultStatus.Failed) {                App.showNotification('binding failure to ' + nameTarget, result.error.message);            } else {                 // set this as the current binding                cs.active = result.value;                // watch for changes                client.watch();                // go off and see if its big enough - if not it will call us back                client.checkTheBindingThenExecute(true);             }         });    };     /**     * the data that we've retrieved will determine whether the current binding is big enough     * if it is then it will execute it, if not it will extend then execute     */    client.checkTheBindingThenExecute = function (clearOnNoData) {        var cs = client.settings.binding;         // get its data        cs.active.getDataAsync(function (dataResult) {            if (dataResult.status == Office.AsyncResultStatus.Failed) {                // it could have been removed in the meantime            } else {                // find the data edges                var edges = client.findData(dataResult.value);                // if we dont have enough padding, then go again                if (dataResult.value.length - edges.data.length < cs.rowGrace || (edges.data.length && dataResult.value[0].length - edges.data[0].length < cs.columnGrace)) {                    client.guessTheBinding();                } else {                    // happy with the result                     Process.syncResult({                        data: edges.data,                        clear: clearOnNoData                    });                }            }        });     } 

 

Noticing the selection has changed

As I mentioned earlier, the API doesn’t tell you where you are. However, you can deduce whether you are in a binding or not, since if the DocumentSelectionChange fires, but the BindingSelectionChange does not then you’ll know that you are not currently in the Binding and may have even moved to a new sheet. However, they fire in the wrong order. The DocumentSelectionChange goes first – so I don’t at that time know whether the BindingSelectionChange is going to fire – so I do this. Defer the action associated with something like a sheet change to allow the BindingSelectionChange time to cancel it.     Office.context.document.addHandlerAsync(Office.EventType.DocumentSelectionChanged, function (e) {             // if this is not followed soon by a binding change event, then we must have gone to another sheet            setTimeout(function () {                if (!client.settings.binding.changed) {                    client.guessTheBinding();                } else {                    client.settings.binding.changed = false;                }            }, client.settings.binding.resetDelay);         }); When the BindingSelectionChange event fires it flags that the DocumentSelectionChange action should be cancelled.    // This is a kind of a hack to decide whether we are still over the bound area        // if you change selection the document selection changed gets fired        // if you are still in the bound area, then the binding selection change will get fires as well        // however the document event will fire first, so we flag here and it wil defer its actioin waiting for this        client.settings.binding.active.addHandlerAsync(Office.EventType.BindingSelectionChanged, function (e) {            client.settings.binding.changed = true;        });

 

The final result

Almost all of the Apps Script client side code transferred over without modification, and gave me a result that looks just like the Sankey Snip Add-on. Here’s the the Office Add-in screenshot. 

Conclusion

 It seems there are only selected kinds of projects that can be implemented as an Office Add-in, and as I found, doing something even slightly out of the ordinary meant jumping through all kinds of hoops. Some of this is probably down to my inexperience with this platform – this is my first app – , but I think it’s a big mistake providing such a limited API and not allowing access to the rich native object model in Office documents. I’ll publish this Add-in at some point (if I can figure out how) so watch this space.
Subpages