Tuesday 11 January 2022

How to pass Apps Script data directly into a HTML page

The following Google Apps Script code is designed to create a HTML popup in a Google Sheet and pass some data directly into it so that the popup can display the information.

This was part of a larger project I was working on so I have stripped all of that away here and kept the bare minimum for demo purposes. I already knew how to get the HTML page to run a Function once it had loaded to then access static data from within the Google Sheet, but what was tripping me up here was if that data was coming directly from Apps Script itself - such as an error message from a try/catch.

Display Apps Script code directly in a HTML popup
Display Apps Script code directly in a HTML popup


We are going to use the 'HTMLService' in order to create our HTML dialogue box:

  // get the spreadsheet User Inteface for later displaying
  var ui = SpreadsheetApp.getUi();

  // get HTML template file
  var template = HtmlService.createTemplateFromFile('showMyMessage');

Next we pass our data directly into the HTML file - here we are passing through 2 separate bits of text:

  // add some data
  template.message1 = "I've come from Apps Script.";

  // add some data
  template.message2 = "No way! So have I.";

Finally, we evaluate and return a HTML output object which can then be passed to the Google Sheet user interface for displaying:

  // get output html
  var html = template.evaluate();

  // show dialogue box
  ui.showModalDialog(html, 'Show my message');



We have the bare minimum of HTML code here to keep things simple. The part to draw attention to is the 'Scriplets' but more specifically the 2 'Printing scriptlets':

  <p>Here is our first Apps Script message:
    <?= message1 ?>

  <p>And here is the next:
    <?= message2 ?>

They have been coloured to help them stand out and as you can see we use our keyword from the Apps Script in order to extract the specific one we want ('message1' or 'message2'). Other than that they are simply embedded in normal paragraph tags.


How to pass Apps Script data directly into a HTML page download (please use 'Overview' > 'Make a copy' for your own version).

No comments:

Post a Comment