However they are fundamentally different.
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.
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.
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.
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
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
We'll need this to detect if the selection changes. This always fires when the active cell is changed
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.
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.
Here's how I figure out a good binding, which will adjust itself as we go along.
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.
When the BindingSelectionChange event fires it flags that the DocumentSelectionChange action should be cancelled.
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.
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
Services > Desktop Liberation - the definitive resource for Google Apps Script and Microsoft Office automation > Add-ons >