Module 0398: Google Sheets GAS Coding, Debugging, Persistence, and UI

GAS (Google App Script) is a feature that is built-in to most Google applications that are available for free, such as Doc, Sheets, Forms, etc. GAS is ECMAScript compatible, meaning that it is essentially JavaScript.

While GAS may seem like an obscure feature that has little real-life applications, it is actually quite essential as the glue to connect multiple pieces of tools that are available together.

2 GAS development environment

While GAS development can be done 100% using Google’s web interface, some may prefer more local tools. The advantage of the web-based editor is that it supports prompting, and highlights errors on-the-fly. The download, however, is that it is not vi!

For those who want to use their own command line environment, Google’s clasp tool works great. It can work along with git for revision control.

Another downside of using a command line tool environment is that the built-in debugger is no longer available. The built-in debugger offers two useful features. Being able to set break points can certainly be helpful, but the ability to see the log from Logger is also very helpful. clasp can run functions from the command line, but that requires a paid Google Cloud Project service.

2.1 A convenient way to test run a function

Warning: this method is insecure. You are encouraged to look into extra means to make this method more secure. Security is even more of an issue if the script is to be run as you by anyone!

One way to get around the problem of clasp not support the run feature with a free account is to make use of the Web App interface. A simple example is illustrated as follows (include in the GAS code):

function doGet(e)
{
  let result = ''
  if (('test' in e.parameter) && ('secret' in e.parameter) && (simpleAuth(e.parameter.secret)))
  {
    result += `<pre>eval(${e.parameter.test})</pre>`
    result += `<pre>${eval(String(e.parameters.test))}</pre>`
  }
  return HtmlService.createHtmlOutput(
    `${result}<br /><pre>${JSON.stringify(e)}</pre><br /><pre>${Logger.getLog()}</pre>`
  )
}

function simpleAuth(v)
{
  // use an external mean to get the SHA256 string (concatenated hexadecimal digits of the bytes)
  const d = "dc4a799223aac66f7672879165c2ec0d64fd633d738f43cd09e35e12092ed6fa"
  let newD = Utilities.computeDigest(Utilities.DigestAlgorithm.SHA_256,v,Utilities.Charset.US_ASCII).map(e => { return (((e < 0 ? 256+e : e).toString(16))).padStart(2,'0')}).join('')
  return d == newD
}

This allows Logger.log() be sprinkled in a function being tested, and have the log messages be reported via the web interface. Of course, this code can be made much fancier by incorporating parameters into the GET request.

Note that when deploying as a Web App, it is important to use the URL corresponding to the @HEAD deployment. Otherwise, a new deployment and a new URL are needed to reflect the latest changes. Use clasp deployments to find the ID of all the deployments, including the @HEAD deployment. Then manually craft the URL based on the URLs of versioned deployments.

To make testing more convenient, the following HTML document test.html can be used to provide a form to test an expression:

<!DOCTYPE html>
<html>
  <title>test Google Sheets</title>
  <head>
    <script type="module" src="code.js">
    </script>
  </head>
  <body>
  <form action="">
    <label for="expr">Expression</label>
    <input type="text" id="expr">
  </form>
  <br />
  <a id="link" href=''>link</a>
  </body>
</html>

import { secret, baseUrl } from './secret.js'

window.addEventListener('DOMContentLoaded', 
  event => { 
    let expr = document.getElementById('expr')
    if (expr !== undefined) {
      expr.addEventListener('focusout', 
        e =>
        {
          let expr = document.getElementById("expr").value
          let encodedExpr = encodeURIComponent(expr)
          let url = baseUrl+`&test=${encodedExpr}`+`&secret=${encodeURI(secret)}`
          document.getElementById("link").href = url
          window.open(url, '_blank').focus()
        }
      )
    }
    else
    {
      console.log('cannot find expr to add event listener')
    }
  }
)

const secret = 'secret!'
const baseUrl = "https://script.google.com/a/macros/apps.losrios.edu/s/B1ahBlalBlahBlahblahBlahBlahBlahBlahBlahBlahBlah/exec?pli=1&authuser=0"

export { secret, baseUrl }

If you are testing the GAS code locally, the browser cannot reference code.js from the HTML file. You will need to run a lightweight HTTP server to get around this problem. The easiest solution is to utilize Python’s http.server.html. If you have Python (version 3) installed, you can run the following command line in the folder that contains test.html:

Then, in the browser, point it to http://localhost:8080/test.html to load the HTML document.

2.2 A convenient way to trigger a function

Google Sheets supports hyperlinks in a cell. This is a rather crude but functional way to make “clickables” in Google Sheets.

3 Using Google Sheets as a storage backend

This requires an interaction between Google Forms, Google UI, and Google Sheets. Google UI provides the means to create a user-interface either by built-in dialogs or custom dialogs that are essentially HTML documents.

Both dialogs and Google Forms can be generated programmatically. The main difference is that Google Forms provides more flexible end-user management, while dialogs can be generated dynamically and provide more direct access to the associated Google Sheets via google.script.run. A Google Form can have a more direct connection to a Google Sheets using the onFormSubmit event handler.

1 Introduction

2 GAS development environment

2.1 A convenient way to test run a function

2.2 A convenient way to trigger a function

3 Using Google Sheets as a storage backend