An overview of Google Apps Script

Published on November 20, 2017 and updated on October 18, 2021.

Update October 18, 2021: on this date, it is possible to use Javascript’s ES6 features to write scripts on Google Apps Script.

I’ve used Google Apps Script for the first time when I needed to transport data from external sources to a spreadsheet on Google Sheets. The task I had to accomplish was similar to the one I’ve described on my previous post: ingest and transform data to feed the correct cells on the spreadsheet. I’ve been studying the tool since, trying to understand what else it can accomplish. I’ve figured out many interesting things, some of which I’m going to share on this post.

Google Apps Script

The tool is a script editor that can execute code from a language very similar to Javascript. With it, it is possible to create apps that are executed alongside Google’s main line of products, making those products even more personalized to any need. Those that are used with Javascript or similar languages don’t have to learn a new language to start working with Google Apps Script. The difference between the Javascript that is used inside Google Apps Script is that it implements some classes and utilities to work with the other Google apps. There are also utilities to make some tasks easier, like calls to external services or work with dates and times.

Google Apps Script can do more that modify content created on apps like Google Docs, Google Spreadsheets or Google Slides. They allow developers to do things like create new options for those apps, send emails through Gmail, create and organize documents on Google Drive, consume APIs like the one from Google Maps and build workflows, just to cite some of its resources. On the documentation page there is a showcase of applications made by users.

How to use Google Apps Script

Essentially, there are two ways to create scripts using the tool: bound to an existing Google document or a standalone script.

Bind scripts

Scripts that are created and bind to a document have all permissions to work with the document that it is bound to. Once bound, the script cannot be unbound from the document. To create a bind script, open or create a new Google document, then open the menu Tools > Script editor.

Once opened, the script editor will load with a blank file called Code.gs.

The panel interface is the same for both bind and standalone scripts. The left side panel shows all project files and the right side panel is the editor area. The file that is created with every new project, Code.gs, there is only one function predefined, called myFunction. Once the code is ready, it can be executed by going to Execute > Execute function > Function name, as funtion name being the name of a function that is declared.

The console log can be used to visualize any script outputs.

This log is different from the execution log. While the execution log register script execution outputs, the console log register data produced by the function throughout its execution. To send data to the console log, which can be used to test the output for instance, use the Logger class:

1

Logger.log("Hello World!");

Another interesting Google Apps Script resource is to setup the so called triggers, that can wait for a given event to execute tasks associated with that event. Triggers can be of two types: time based and event based. Time based triggers run periodically, and the interval is set by the user, and event based triggers run when a given event occur. Some event based triggers are:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

function onEdit(e) {
  // TODO
}

function onChange(e) {
  // TODO
}

function onOpen(e) {
  // TODO
}

Event based triggers are created with functions, and the code inside those functions is executed when the event happens. A practical application on Google Spreadsheets, for instance, is to update cells based on user input. Consider the example:

1
2
3
4
5
6

function onEdit(e) {
  if (e.range.getA1Notation() == "L8") {
    var sheet = SpreadsheetApp.getActiveSheet();
    sheet.getRange("L9").setValue(e.oldValue);
  }
}

This function listen the event associated with editing any cell of a spreadsheet, verify if the cell being edited correspond to the postiion L8, and then update another cell with the edited cell’s previous value.

Triggers that are time based can be configured on the Triggers menu option, shaped after a clock. This trigger is configured by defining the moment that it will occur (when the file is opened or changed), and the time interval at which the trigger will run.

There are many other possible uses for Google Apps Script. Suppose that you have a document containing a list of contacts stored on Google Drive. You can create a script to read all the contacts on this list, register them on Google Contacts, and then send an email to the contact list using Gmail. If this is a task that must happen with frequency, it is possible to setup a time based trigger and the process will be automated.

Standalone scripts

Standalone scripts work similar to bind scripts. Unlike the former, they are visible on Google Drive as files and can be used to create Web Apps or utilities, like a task automation utility. One example would be to organize new files that are stored on Google Drive in a given folder or with a given name.

Web Apps

Scripts can be published on the internet, with a unique address. This is useful when the script should receive data from external systems. In order to be published on the internet and to be a web app, the project must contain at least one script with two declared functions:

1
2
3
4
5
6
7
8

function doGet(e) {
  // TODO
  // Return object from types HtmlOutput or TextOutput
}

function doPost(e) {
  // TODO
}

The function doGet must return an object of either HtmlOutput or TextOutput types. To interact with the script from an external application, it must be published as a web app with proper permissions. Once it is published, external application can easily access its web address and send the data that will be used by the script.

Additional resources

These are some of the resources provided by Google Apps Script. I suggest you to bookmark the documentation site on your browser, as you will use it quite often. At the guides section there are beginner level tutorials for those who want to learn by doing, under the “5-Minute Quickstarts”. In the support section there are links to questions asked on Stack Overflow and many other resources.

Let’s automate!