Written by Matt Bilyeu
on 

A simple, templatized web page with Google Sheets

In the past, I’ve used Google Sheets to display data from the Google Places API and to keep score in Jeopardy!.

This post further explores the Google Sheets Apps Script integration. It shows how you can define a simple templatized web page in a sheet. You may find this useful if you’re building a small prototype, need to quickly put a customizable page on the web, or if you, like me, just like playing with Google Sheets. A web page built in this way is free to host, and is very easy to deploy and customize. That said, I wouldn’t recommend this setup for maintaining a production web application because it’s brittle and non-standard, and, since 2017, Google places a security header at the top of the web page (though the header can be hidden with a browser extension).

google security header

Google Apps Script is a powerful low-code solution for building web-apps, and it can do much more than what’s covered in my blog post here. There are other tutorials online about how to create web apps with Google Apps Script. This post instead focuses on using a minimal, generic script, and moving web page customization into the sheet itself. It does this by having the sheet store the dynamic HTML template, and cells that are fed into the template are designated within the sheet.

Data

The toy web page we’re about to build needs some data. Let’s say we’re making a company’s lunch menu that will be displayed on monitors outside the lunch room. First, navigate to https://sheet.new to create a new Google Sheet. Maybe we’ll show the following dynamic information:

menu data in sheet

A couple notes… Ignore cell A1 for now, that’s where we’ll put the steak sauce, err I mean that’s where we’ll put the HTML template. Note that the cell in B2 contains text data, not an actual date. This is necessary because the script we’ll add later doesn’t know how to format the raw data it receives, so we’ll do that formatting directly in the sheet. You can make a dynamic day of week field by using the formula =TODAY() and then changing the date format to only display the day of the week.

custom date formatting

So note that, using the sheet itself, we can do arbitrary pre-processing of the data before readying it for display. I have organized this sheet by putting labels for the data in column A and the values to be displayed in column B, but that’s incidental. In the next section we’ll see that data can come from any cell.

Template

An HTML template combines static HTML elements with special sentinels denoting variables to produce dynamic HTML output. I created an absolutely minimal templating language here, where template variables are surrounded by {curly braces}, and they must refer to cells with data to display. There are plenty of fully-featured templating languages available – Handlebars is a famous one.

As a small example, we could define a template like this:

<div>
  <i>Hello</i>, {B2}!
</div>

If the cell B2 in our sheet contained the text World, the resultant HTML would look like this:

<div>
  <i>Hello</i>, World!
</div>

The part of the template that contained {B2} was replaced with the value from cell B2 in our sheet – nice. So let’s build the HTML template for our menu, referencing the data cells defined in the previous section.

<html>
  <head>
    <style>
      body {
        background-color: skyblue;
      }
    </style>
  </head>
  <body>
    <h1>Today's Lunch Menu</h1>
    <h2>{B2}</h2>
    <h2><i>{B3}</i></h2>
    <h3>Appetizers</h3>
    <ul>
      <li>{B4}</li>
      <li>{B5}</li>
      <li>{B6}</li>
    </ul>
    <h3>Entrées</h3>
    <ul>
      <li>{B7}</li>
      <li>{B8}</li>
      <li>{B9}</li>
    </ul>
    <p>
      Thanks for dining at the cafeteria!
      Please bus your table when you're finished eating!
    </p>
  </body>
</html>

This text should go in cell A1, which is where the script will read it from.

template data in sheet

Script

Now, open the script editor by going to Tools > Script Editor. Here is the Apps Script script we’ll use to read data from the sheet, combine sheet variables with the HTML template, and serve that HTML in response to a GET request.

function letterToColumnOrdinal(letter) {
  // TODO: handle >= AA
  return letter.charCodeAt(0) - "A".charCodeAt(0);
}

function dataAtCellAddress(data, cellAddress) {
  const row = Number.parseInt(cellAddress.match(/\d+/)[0]) - 1;
  const col = letterToColumnOrdinal(cellAddress.match(/[A-Z]+/)[0]);
  return data[row][col];
}

function doGet(e) {
  const sheet = SpreadsheetApp.getActiveSheet();
  const data = sheet.getDataRange().getValues();
  const template = data[0][0];
  const html = template.replace(/{(.*?)}/g, function(match, p1) {
    return dataAtCellAddress(data, p1)
  });
  return HtmlService.createHtmlOutput(html);
}

The letterToColumnOrdinal function is just a helper function to take a letter from the cell address and convert it to a number so that we can index into the data matrix, e.g. C -> 2. dataAtCellAddress takes the sheet data and a raw cell address and returns the value stored in that cell. The doGet function is called in response to a GET request. It is responsible for extracting data from the sheet and combining the cell data with the template string. When the site’s info or structure needs change, those modifications can happen directly in the sheet, and these 20 lines of Apps Script code won’t need to be touched.

script editor

Deploy

Let’s deploy our new web page. From within the script editor, go to Publish > Deploy as web app.

deploy form

Then, fill out the dialog form and copy the URL to the web page. Here’s the example page I created: Lunch Menu.

finished page

Great! Finally, let’s say the chef was inspired by this blog post to make a new app. So he’s eighty-sixing the mezze platter in favor of a bruschetta crostini. All we need to do is update that cell in the sheet…

updating the sheet

…and reload the web page.

updated page

And that’s it. We now have a simple, templatized web page that’s easy to change via the sheet.