This page is still being written.

In Pseudo binding in HTML service I showed how to simulate Sheets cell binding in an Apps Script Add-on. Office for apps includes binding out of the box. We’ll use that example to show how easy it is to take an Apps Script Add-on and port it to an Apps for Office Add-in – for as long as you don’t want to do too much with it. For the purposes of this discussion, we’ll limit it to Excel running in Office 365 mode and Apps Script for Sheets.

At first glance Apps for Excel (AFO) and Apps Script for Sheets (GAS) look very similar.

  • Both allow the extension of their respective apps using JavaScript and HTML
  • They each have two kinds of extensions  for Add-ons/-ins. Dialog (GAS) or Content(AFO) Add-ons/-ins give a popup dialog which are intended for a one shot type of extension, and Sidebar (GAS) or Content (AFO) are for persistent extensions.
But – there is a massive difference in philosophy.
  • Gas runs on Google Servers, with the Add-ons running on your client.
  • Afo runs on your client, whether it’s Office 365 or Excel.
What that means is this
  • With GAS you can devise any functionality you want because their is only a loose coupling between the Server based app and the client based Add-on.
  • With AFO there is no server side component, which means that the Add-on is limited to whatever Microsoft have implemented in their API. This means that you’ll get easy to create Add-ins where the capability exists in the API, but no chance of hacking together something that doesn’t. This is a very big distinction – it means you can’t run native stuff ‘server side’. When I first started looking at this I had hoped to be able to create stuff in VBA that I could communicate with via the AFO API – but no such luck. This limits the kind of applications you can build.

Bearing that in mind, lets’ get started porting this over.

The development environment

You can use either visual studio or napacloud to develop your Add-in. I’m sticking to Napacloud since I’m using a Chromebook. What you want to build for your first app is a Takspane Add-in which is the equivalent of a sidebar Add-on. Don’t worry about the boiler plate stuff it creates – we’re going to replace most of it with a direct copy of the code from Pseudo binding in HTML service.

Getting started

Unlike GAS, you have to put in all the HTML decoration, and since the AFO API runs in the client, you need to pull in the scripts for that.

  • Create an index.html, and copy in the contents of the Home.html that Napa created for you.
  • Get your index.html from Pseudo binding in HTML service and append it
  • Delete all the stuff from Napa in the head section except as below
  • Delete the body in Napa and translate your requiregs/js into script tags.
… or just  create an index.html and copy the below…
App.js

This is where the mechanics of the App are handled – like how to report and error and so on. You can delete the Napa contents and just replace it directly with the App.js code from the GAS version. It’s exactly the same.

Process.js

Create this file in the Home directory. It’s exactly the same as the GAS version.

Client.js

This is the code that is specific to Apps for Office, so although it will do roughly the same thing as the Apps Script version, we need to write it from scratch. It’s worth looking at this at the same time as the Client.js in Pseudo binding in HTML service as this is really the only place that things are significantly different.

Create a client.js in the Home directory.

Utils.js

We only need a few functions from here, but since we’ll need more of this stuff as we build up the app in future tutorials, just create Utils.js in the Home folder and copy the whole thing in. You don’t need to bother looking at this code – it’s exactly same as the Apps Script Utils.js in Pseudo binding in HTML service

I’m using my usual layout for HTMLservice so I  have more files than you would need to for such a simple demo, but you should find it a useful starting pattern for more complex apps as described in More client server code sharing

How does it work?

The client polls the server at regular intervals to get the latest data on the active sheet

How to avoid sending loads of data all the time

The server calculates a checksum of the current data. The client asks for data passing over the last checksum it got back. If they are the same, no data is transferred. If they are different, the server transfers the latest data and checksum.

Walkthrough

Let’s start with the sidebar html file. It uses the techniques described in More client server code sharing to pull in the JavaScript code. Otherwise it just has a content div – where I’m going to display the latest sheet data, and a notification div for errors.

index.html

Next the local javascript to kick off the application.

main.js.html

Next the App script file – this just sets up the content areas of the sidebar and how to communicate with them. Although this is intended to run on the client, I store this as an Apps Script .gs file.

Now the client code. This is the code that communicates with the server – every 2 seconds it checks in with the server, passing over the checksum for the data it last received.  If it’s different from the current data checksum, the server will return the latest data from the active sheet. You can see that if any data has changed, it will call Process.callback() – this simulates the Microsoft binding callback. Again this is stored as an apps script .gs file although it only runs on the client.

Client.gs

Now the process code. This is where you would do something with the sheet data. It contains the callback, which for this demo simply displays it in the sidebar. Again this is stored as an apps script .gs file although it only runs on the client.

Process.gs

Now the Server code. This executes exclusively on the server, and is responsible for picking up the data from the sheets, and deciding whether it should send any back based on the checksum value.

Server.gs

And of course the initialization server side code to expose the add -on is just boiler plate stuff

First Require.gs, which executes on the server and is used to pull in the source code of the client scripts into the HTML file. It is executed from index.html And a couple of utilities

And Utils.gs. This contains a bunch of utilites, not all of which are needed by the code. It executes on both the server and client as described in Sharing code between client and server. You don’t need to bother looking at this code, but you will need it if you are building this pattern.

For more like this, see Google Apps Scripts Snippets

Why not join our community , follow the blog and or following me on twitter