One of the good things about Microsoft apps for office Add-ins (these are similar to Apps Script Add-ons in that you get a sidebar where you can extend apps with HTML apps), is that data binding is built in. That means that you can set up a callback to detect any time a particular range of data changes. Apps Script doesn’t have that, but what it does have (Microsoft doesn’t), is the ability to script co-operating scenarios for both the client and server side.

That means that we can create a kind of binding by the client side asking the server side what’s changed every now and again.

Here’s a simple demo of an Add-on that polls the active sheet for changes.  You can play with it here – just start up the binding add-on.

Structure notes

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.

App.gs

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 or and follow me on Twitter.