google apps script:
the fundamentals

Google Apps Script is a quick and easy-to-use “development platform” that lets you add your own bespoke functionality to Google apps such as Sheets or Classroom. Most powerfully this can be used to automate long or repetitive tasks such as creating courses in Classroom and inviting all the students.

Scripts are written in standard JavaScript (executed using the V8 runtime), supplemented with extra classes, “Google services”, that take care of the interaction with Google apps (Sheets, Forms, Classroom, Gmail, etc).

A lot of these Google services are built-in to Google Apps Script by default, but there are also “advanced” Google services that you will need to explicitly add whenever you want to make use of them, otherwise they won’t be recognised when you run your script.

A full reference for all Google Services is provided, but it isn’t always straightforward for the enthusiastic amateur to fill in the gaps when using this! This site aims to fill in those gaps, in particular by offering commonly needed code snippets.

Google Apps Script has a built-in script editor which you can access from within Google apps by clicking Extensions > Apps Script or directly at script.google.com.

You might already have a favourite code editor, but you won’t need it for writing Google Apps Script code, not least because uploading externally-edited code isn’t straightforward. More importantly, though, the in-built editor autocompletes all of the Google service objects and methods and it is also where you can add any of the advanced Google services you need. Finally, it conveniently lists all of the related files for your Apps Script project that your scripts might be using.

If you want to customise the editor, try the Black Apps Script Chrome add-on.

The editor can only open one script file at a time, but you can open multiple editor windows in your browser at the same time.

If you open the editor from within an app (Extensions menu > Apps Script), the script you write will be “container-bound” to the open file. This means the script doesn’t appear as a separate file in Google Drive (although it can be accessed via script.google.com); it is part of the Sheets, Docs, Slides, Forms document (the “container”) and it can’t be detached.

The notable difference compared to standalone Apps Script projects is that container-bound projects are able to add custom menus (to launch your functions) and show dialogs and sidebars (for interacting with the user). They can also reference their parent file without needing to use the file ID. Most, if not all, of your scripts are going to be container-bound.

If you want to copy a container-bound script, you must copy and paste the text to another editor window.

Your script is saved in the editor as “.gs” files, visible in the Files column on the left hand side of the editor window.

It is good practice to divide long code into separate files for different aspects of your Apps Scripts “project”.

(Dialogs are built from small HTML files. These will also appear in the project’s file list.)

Placing the code into separate files just makes it easier to read and maintain; when you run the script everything is “parsed” as if it was all in a single file.

You can execute your script in different ways, including:

• Automatically via a “project trigger”, e.g. the container file being opened, a cell being edited, a form being submitted.
• By clicking an option in a custom menu that your script creates.
• By clicking “Run” in the script editor.
• By creating a custom function that works in Sheets when you type “=yourFunctionName”, just like built-in functions.

When your script runs, it runs on Google’s servers, not the user’s computer. Google places limits on this. In particular, no script can take longer than 6 minutes to complete.

One of the most crucial things to understand about Google Apps Script is that every time any aspect of your script is called to run, the entire original script is executed completely afresh. There is no “memory”. Anything that happened in any previous (or concurrent) run is forgotten (e.g. the values of any variables set during the previous run) unless the outcome is saved somewhere (e.g. wrting values to a cell on the container Sheet).

Parsing is the process of turning your human-readable code into a format that the “runtime environment” can actually run.

The thing to understand here is that Apps Script files are just like any other JavaScript files. In particular, your code doesn’t have to exist only inside functions. You might conclude otherwise given that when you open a new project the editor presents you with a new myFunction() to fill in and all of Google’s example code snippets only exist within functions.

Any variables you place outside of a function have global scope and can be accessed by all functions. Remember, though, that every execution of the script is a new execution and so the values of these “globals” will have their values reset when, for instance, the user restarts the script by selecting from your custom menu.

The advanced Google services are ways for programmers to connect their (external) apps to public Google APIs (application programming interfaces). When you enable an advanced Google service in Google Apps Script, you make it available in your script much like the Google services natively built-in to Google Apps Script. However, they are a little trickier to use, as the support guides are written for the API programmers and require a little interpretation for use in Google Apps Script. Usefully, the Google Apps Script Editor autocompletes the names (which often include undocumented capitalisation; also see “delete methods”, below) and generally sorts out the permissions (see “below”authorisation scopes and permissions”, below).

The less obvious thing to understand about advanced Google services is how to pass arguments to the methods. In the reference documentation for the methods, these are separated into path parameters, optional/query parameters and request body. The latter two must be javascript objects, but the first is just stated.

E.g. path parameters (method reference page):

Classroom.Courses.Students.remove(courseId, student);

E.g. query parameters (method reference page):

Classroom.Invitations.create ({ userId: student, courseId: courseId, role: 'STUDENT' });

E.g. both together (method reference page):

let response = Classroom.Courses.Students.list(courseId, { pageSize: 40, pageToken: nextPageToken });

As the examples above indicate, some Google services only do something (remove, create), while others (list) return a “response body” that you will assign to a variable ready for further use. In general, the response body is an object and the reference page will describe its structure. E.g.

Clicking on the word “Student” allows you to drill down further into the structure:

From this you can extract the data that you want, e.g. for the list example above, an array of the returned email addresses, studentEmails, can be obtained:

for (const student of response.students) { 
  studentEmails.push(student.profile.emailAddress.toLowerCase());
}

Some of the Google Apps service classes/objects have delete methods. The reference guide is written for API developers rather than Apps Script coders, however. This is important to know, because delete is a reserved word in JavaScript and so in Apps Script all delete methods are renamed to remove!

Many of the Google services in Apps Script are capable of accessing the user’s private data or of carrying out actions in the user’s name. As such, Google Apps Script will often ask the user to grant the necessary permissions the first time they run a script.

Apps Script can often determine for itself which permissions to ask for, by scanning the script for Google services. You can see a list of the “authorisation scopes” by clicking on Overview on the left sidebar menu (“i” icon, at the top); “Project OAuth scopes” is at the bottom of the summary.

When authoring scripts yourself, you can take explicit control of these authorisation scopes for your project if you want to. This is sometimes necessary before your script will run, but it is also good practice, as scripts ought to be limited to the minimum set of permissions and the automatic detection can sometimes be very permissive.

The reference page for each Google service method finishes by listing the required scope(s) that must be granted in order for the service to work. To explicitly add the scope(s) to your project, you just need to edit your project’s “manifest file”, appsscript.json. This appears in your list of project files on the left of the Google Apps Script Editor window, but is usually hidden by default. To unhide it, go into the Project Settings (gear wheel on the left sidebar menu) and put a tick in the box.

Now just add an extra data object pair. The name must be “oauthScopes” and the value is an array of comma-separated entries copied from the reference pages of the Google services you have used. E.g.

{
  "timeZone": "Europe/London",
  "dependencies": {
    "enabledAdvancedServices": [
      {
        "userSymbol": "Sheets",
        "version": "v4",
        "serviceId": "sheets"
      },
      {
        "userSymbol": "Classroom",
        "version": "v1",
        "serviceId": "classroom"
      }
    ]
  },
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8",
  "oauthScopes": [
          "https://www.googleapis.com/auth/spreadsheets.currentonly",
          "https://www.googleapis.com/auth/classroom.rosters",
          "https://www.googleapis.com/auth/classroom.profile.emails",
          "https://www.googleapis.com/auth/classroom.courses.readonly"
        ]
}