Both Apps and Office offer the capability of adding extra functionality by bringing up some client side JavaScript which can communicate back to main application. In the case of Apps  this would be the server based App like Sheets, and in the case of Office it could be either the desktop version or the Office 365 hosted version.

They both have the capability of multiple kinds of add-ins (Office) / Add-ons (Apps).  In fact , Office have just changed the name of this capability which emphasizes the similarity between the two solutions – this is from their developers site. This probably replaces the usage of the term Excel Add-ins, which were essentially bound VBA scripts you could add to your local Excel workbook.

Note: The name “apps for Office” is changing to “Office add-ins,” so you’ll see references to “add-ins” in the tools even though most Office applications and docs still refer to “apps for Office.”

The fundamental difference between them though, is that although superficially they seem to do the same thing, operations performed on the target workbook from Office is limited to what the API can do. In other words, from the client, you can instruct things to happen for as long as the capability exists in the API scope. In Apps Script,  the server and client are independent. The client simply calls some server function that you have written which can do anything that you want to do in any Apps Script service.

This difference means that Office Add-ins are on the one hand very limited in scope, but on the other, much more tightly integrated and responsive out of the box to changes in the document on which they are operating.

For this bakeoff we’ll take and existing sidebar Apps Add-on  and see if we can make an Office task pane add-in out of it.  I’ll just make random notes as I go along.

Development environment

Apps  Office
The usual cloud based Apps Script IDE, so nothing to learn here. However when building Client side components in an HTML type file (as opposed to a script file), using the Apps Script IDE you are pretty much on your own with it acting not much more helpfully than a text editor.

To get over this a little I write them as .gs scripts then pick them up using this technique

There are a number of options, but I’m going to try use the Napa cloud based environment to make it as equivalent as possible to the Apps IDE. If you are coming from VBA, this will all be very new to you. It seems to be a very nice editor – much better than the Apps Script one. However only limited functionality is available here. You have to open in Visual Studio to get access to some properties or run it on local (as opposed to web) Excel.

I didn’t have VS on my machine, so I entered a downloading and installation fiesta for a while, till I got all the stuff I needed. However, I’m going to try to do this without using VS if I can and make the whole thing cloud based.

Starter example: you can pick from a selection of example apps. Choosing build add-on for sheets sets up a boiler plate template. Starter example: This builds a jQuery heavy example for getting data from an open document.
 A dialog Add-on is a popup style window for one shot type actions The equivalent is the Content add-in.
 A sidebar Add-on is designed to stay permanently on the screen. Changing between add-on types is a couple of lines change in the code. The equivalent is the Task pane add-in.  It’s not immediately obvious to spot the difference in the code for how to convert from one type to the other. For now I seem committed to starting again if I change my mind.

Code visibility

I’m not planning to use jQuery, nor use the boiler plate example in Office, just as I started the Apps Script version from scratch. So I’ll get rid of all the example code and get started.

I’m using Chrome to run tests on this, and one of the first things I need is to be able to see the actual code in developer tools and trace console errors back to problems

Apps  Office
As long as you run in sandbox.iframe mode, you can see the original code in the developer tools Can see the code in developer tools. Strangely, even the pre-baked example shows some errors in the dev console. Wasted a bit of time on this thinking it was me.

Apps specifics

This app is about getting colors in a specific column, then sorting the sheet they are on based on the sort orders chosen for the colors. That leads to some specific limitations in both sides that dictate how this can be implemented. The Apps Script implementation cannot be directly applied to Office version because..

Apps  Office
There is no direct binding between what’s going on in the client with what happens to the Sheet. Data has to be retrieved, modified, then the result applied back to the sheet. This API has a much tighter linkage between client app and source data. You can make a binding of Sheet data to client objects, meaning that changes made in the sheet is automatically reflected in the client object. This is generally a better, more modern approach than apps script, but also loses some flexibility.  It also has the possibility to assign a callback to the client when changes are made in the Sheet. You cannot do this in Apps Script – there is no real time connection between what’s happening in the client and the parent app.
 I can easily get and set all data in the sheet form anywhere I want, including the cell formatting data. Using a ‘matrix’ retrieve, you can pick up data from the cells of a Sheet. However you cannot set or get cell formatting information. For that you need to create a Table definition. This means that we’ll need a complete overhaul of the communication between the server and the client and a rethink of who does what.