← Subjects

Beyond the Classroom · Automation

Google Apps Script

Apps Script is JavaScript that runs on Google's servers and can control Sheets, Docs, Gmail, Drive, Calendar, Forms and Slides. This course starts with your first line of code and ends with a project you deploy and present. Read each chapter here, then type the code into the real Apps Script editor.

Level 1

Basic

The editor, the JavaScript you need, and reading and writing a Google Sheet.

  1. B1

    Meet Apps Script

    What Apps Script is, bound and standalone projects, the editor, your first run and the authorization screen.

  2. B2

    JavaScript basics

    Variables, data types, operators, template literals, if/else and functions, with Logger.log to see the result.

  3. B3

    Arrays, objects and loops

    Lists and records, for loops, forEach, map and filter, and the 2D arrays every sheet turns into.

  4. B4

    Reading and writing Sheets

    SpreadsheetApp, sheets and ranges, getValues and setValues, appendRow and the last row.

  5. B5

    Custom functions

    Write your own =FORMULA(), take ranges as input, return a whole table, and the rules custom functions follow.

Level 2

Intermediate

Menus, triggers, Docs, Gmail, Drive and Calendar: scripts that do real work.

  1. I1

    Formatting and cleaning data

    Colours, bold, number formats, sorting, removing duplicates and tidying messy text in one pass.

  2. I2

    Menus, dialogs and triggers

    onOpen menus, alerts and prompts, toasts, onEdit, and installable time-driven triggers.

  3. I3

    Google Docs with DocumentApp

    Create and edit documents, headings, lists and tables, find and replace, and fill a template.

  4. I4

    Gmail and mail merge

    Send plain and HTML email, merge a sheet into personal messages, read the inbox, and respect quotas.

  5. I5

    Drive and Calendar

    Folders, files and iterators, listing Drive into a sheet, and creating and reading calendar events.

Level 3

Advanced

Forms, Slides, HTML sidebars, web apps, outside APIs and code that holds up.

  1. A1

    Forms and Slides

    Build a Google Form or quiz from code, process responses on submit, and generate a slide deck from data.

  2. A2

    HTML sidebars and dialogs

    HtmlService, sidebars and modal dialogs, templates, and google.script.run between the page and the server.

  3. A3

    Web apps

    doGet and doPost, deploying a web app, access settings, and a JSON API with ContentService.

  4. A4

    APIs, properties and cache

    UrlFetchApp and JSON, keeping keys in PropertiesService, and CacheService to avoid repeat calls.

  5. A5

    Writing scripts that last

    Batch calls, quotas and the six-minute limit, LockService, error handling, libraries, clasp and versions.

Level 4

Capstone

Plan, build, deploy and present one complete project.

  1. C1

    Capstone brief

    Four project options, the milestones, what to hand in, and the marking rubric.

  2. C2

    Guided build: Class Quiz web app

    A worked capstone from a question sheet to a deployed quiz that marks itself and emails each score.

How to work through it

  1. Sign in to a Google account. A school account works, but some admins block Gmail or web app sharing, so a personal account is easier for the later chapters.
  2. Open a Google Sheet and choose Extensions → Apps Script, or start a standalone project at script.google.com.
  3. Copy the code from a chapter, paste it into Code.gs, save, pick the function in the toolbar and press Run.
  4. Compare what you see with the "Execution log" box under each listing. Then do the Practice tasks before you open the solutions.

Each chapter has a short quiz. Finish the Basic level before Intermediate: every later chapter reads and writes a sheet.

17 chapters · 4 levels