Google Apps Script

I’ve been using Google Apps Script for a long time now. I really enjoy it.

Editor setup

These note are likely to be very brittle and break over time.

Code completion

For VS Code, just installing the @types package allows the editor to pickup on the shape of the objects in Google Apps Script. You will also need to install the ESLint plugin as well.

Types: https://www.npmjs.com/package/@types/google-apps-script

npm install --save @types/google-apps-script

Linter

ESLint:

ESLint: Quick start

npm init @eslint/config@latest

ESLint plugin:

eslint-plugin-googleappsscript

npm install eslint-plugin-googleappsscript --save-dev

Note that the instructions for the config are out of date. My suggestion here works for me, but I am not deeply familiar with the ESLint config system. With the default eslint --init config, defining the globals seems to be the key:

import globals from "globals";
import pluginJs from "@eslint/js";
import googleappsscript from "eslint-plugin-googleappsscript";

export default [
  {
    files: ["**/*.js"],
    languageOptions: {
      sourceType: "script",
      globals: googleappsscript.environments.googleappsscript.globals,
    },
  },
  { languageOptions: { globals: { ...globals.browser, ...globals.node } } },
  pluginJs.configs.recommended,
];

Syncing with clasp

clasp is a tool that Google maintains to sync your local editor with Google Apps Script. It stands for “Command Line Apps Script Projects.”

Authentication is done with

clasp login

which will use your browser to authenticate with your Google credentials and then save a token on your local machine to send with future clasp commands.

For the initial download:

clasp clone SCRIPT_ID

You can find the SCRIPT_ID in the web editor under “Project Settings”.

After you make local changes, sync your work back to the cloud with

clasp push

If you make changes on the web editor and need to sync your local files, use

clasp pull

Versioning

Apps Script’s versioning has improved over the years. The “Project History” feature in the online editor is probably enough for small projects.

For larger or collaborative projects, you will probably want the features of git or similar version control software.

After your have run clasp clone SCRIPT_ID, you can run git init as well.

If you are starting from an existing repository, you will need to create a .clasp.json file to direct clasp to your project on the cloud.

For personal projects on a single development computer, you could commit the .clasp.json file to the repository, but this can include a hard path to the project root.

Alteratively you can add .clasp.json to the .gitignore file and just locally create the .clasp.json file on each development computer with contents like this:

{ "scriptId": "1a2...3z" }

Sheets

Using a Sheet like a relational database table:

• columns are the same for all rows
• no gaps in the rows

The pattern becomes

• read the sheet into objects
• work with the objects
• write the objects back to the sheet

I like to make a class to represent each row/record, defining the constructor as a copy constructor (see example below), and using a static method fromArray, defined to take a row of sheet data and translate it to the constructor.

You can also just define the constructor to take a row of sheet data, but then you may need to define a separate copy constructor. Experience has taught me to use copy constructors by default.

Avoid having the constructor take both arrays and objects as input. This is a thing JavaScript allows you to do, but it’s a trap. You’ll end up writing a big if/else to handle the two options. The conditional will make it harder for you and your IDE tools to catch mistakes the first time. Defining two separate functions helps your IDE tools to catch your mistakes for you.

// you would *NOT* name it 'Data', but whatever your particular sheet data is representing
// i.e. items in an inventory, calendar events, descriptions of people/users, etc
class Data {
  constructor(data) {
    // copy constructor: is able to create new instances by copying the same properties
    // validate all inputs
    // example of validating first:
    if (typeof data.name !== "string" || !data.name)
      throw "Data must have a name.";
    this.name = data.name;
    // note that String(undefined) => 'undefined', which is probably not what you want!
    // example of validating after forcing a type:
    this.value = Number(data.value); // enforce types; undefined -> NaN
    if (!Number.isInteger(this.value) || this.value < 1)
      throw "Data must be integer > 0.";
  }

  // when your write to a sheet, you invoke this function:
  toArray() {
    // return the same format as the columns in your spreadsheet
    return [this.name, this.value];
  }

  // when your read from a sheet, you invoke this function:
  fromArray([name, value]) {
    // invoke the copy constructor
    return new Data({ name, value });
  }

  // you would apply all your business logic for these objects as functions here...
}

function readSheet() {
  return SpreadsheetApp.getActiveSheet()
    .getDataRange()
    .getValues() // use .getDisplayValues() if necessary
    .slice(1) // if you have headers and want to skip the first row
    .map(Data.fromArray); // or .map(row => new MyDataClass(row))
}

// replace 'data' and 'datum' with appropriate names for your objects
// i.e. 'users' and 'user', 'inventory' and 'item', etc.
function writeObjects(data) {
  setSheet(data.map((datum) => datum.toArray())); // toArray() returns (string|number|Date)[]
}

function setSheet(values) {
  // use getRange(1, ...) if you do not have a header row
  SpreadsheetApp.getActiveSheet()
    .getRange(2, 1, values.length, values[0].length)
    .setValues(values);
}

A very easy but risky way to do a copy constructor is

class LazyCopierDoNotUse {
  constructor(data) {
    // risky! By the time you make this 'safe', you won't need it anymore.
    Object.assign(this, data);
  }
}

TypeScript will not play nice with the above lazy copy constructor for good reason. It is too easy for the caller to pass in bad data, and the constructor won’t catch it, and then it could be much later that your mistyped camel-case variable (e.g. userID vs. userId) throws the classic error cannot read properties of undefined (reading 'userId').