Talking to the Server from Apps Script’s HTML Service

In a previous post I talked about adding a sidebar to your document using Google Apps Script. Such a sidebar us written in HTML using Google Apps Script’s HTML Service. But how can you get this client-side HTML page to talk to your server-side routines behind your document or spreadsheet?

Google provides an asynchronous client-side JavaScript API called google.script.run to handle this for you. Using google.script.run you can call your Apps Script functions via JavaScript in your HTML page.

Suppose, for instance, that we have a spreadsheet with a function myFunc() defined in Apps Script. I can fire that function from my HTML page (i.e. my sidebar) by including the following in the HTML code:

<script>google.script.run.myFunc();</script>

The example I’m going to work through below is a little more complex, as we want to not only execute a server-side function, but also return a value from it to the HTML page. Here’s a simple little spreadsheet:

Click image to enlarge

Click image to enlarge

I want to include in a sidebar a button which, when clicked, will return the value of cell B2 and display it in the sidebar. This is a little more complex than the myFunc() example syntax shown above; in addition to executing a function, we want to return a value to the HTML page.

Instead of using a syntax like

google.script.run.myFunc()

I need to use

google.script.run.withSuccessHandler(onSuccess).myFunc()

which allows me to define a callback function onSuccess() to handle the returned value.

Let’s see some code. First, here’s the Apps Script running behind the spreadsheet. In addition to the code to implement the sidebar from a menu item (which you can read about in the previous post) I’ve now defined a further function returnCellValue(cell). This is the function I’m going to execute from the client-side code in the sidebar, and it simply grabs and returns a cell value from the spreadsheet.

function onOpen() {
 SpreadsheetApp.getUi()
   .createMenu('Sidebar').addItem('Open', 'openSidebar').addToUi();
}
function openSidebar() {
 var html = HtmlService.createHtmlOutputFromFile('index');
 SpreadsheetApp.getUi().showSidebar(html);
}
function returnCellValue(cell) {
 return SpreadsheetApp.getActiveSheet().getRange(cell).getValue();
}

Reloading the spreadsheet runs the onOpen() trigger (see this previous post if that doesn’t mean much to you) and generates a new menu item to launch the sidebar:

Click image to enlarge

Click image to enlarge

Now for some HTML code for our sidebar. Here I define the onSuccess() JavaScript function that handles the returned value (which I’ve called B2Value), simply writing it into the HTML at an appropriate place. The call to google.script.run.withSuccessHandler(onSuccess) is made, for simplicity, from a button’s onclick event handler (I’m sure you’d write prettier and better-structured code in your real-world applications!)

<script>
 function onSuccess(B2Value) {
 document.getElementById('output').innerHTML = B2Value;
 }
</script>
<div>
<input type="button" value="Read Cell B2" 
onclick="google.script.run.withSuccessHandler(onSuccess).returnCellValue('B2')" /> 
<br /> 
Cell B2 contains value: <div id="output"></div> 
<br /> 
<input type="button" value="Close Sidebar" onclick="google.script.host.close()" /></div>

Opening the sidebar reveals our simple HTML interface:

Click image to enlarge

Click image to enlarge

Finally, clicking on the button pulls in the required value from cell B2: 

Click image to enlarge

Click image to enlarge

Client-side calls to server-side functions are made asynchronously. After the call is made, the browser carries on immediately to the next line of code without stopping to await a response from the server. This means that your server function calls may not execute in the order you expect; if you’re making multiple calls, code some logic into your success handler functions to make sure things work as you want.

You can also handle the case where the server fails to respond or throws an error, by specifying a failure handler instead of a success handler. I’ll cover this in a future post.