Skip to content
Tutorials Center
My learning

Topics

Lesson 25 of 29 ยท Automation and Collaboration

Automating with Apps Script

Start automating Google Sheets with Apps Script: write custom functions, read and write ranges, add menus and set up triggers, with safe examples.

Formulas cover a lot, but some jobs need real code: send an email when a status changes, clean imported data with one click, add your own menu, or create a function like =TAXED(price). Google Sheets includes a built-in programming environment for this called Apps Script. It is JavaScript that runs on Google's servers and can read and change your spreadsheet.

This lesson introduces the editor, custom functions, reading and writing ranges, menus and triggers. Apps Script needs your Google account, so the code is shown in read-only blocks. JavaScript stand-ins on this page imitate the spreadsheet objects, so you can run the same logic and see the results.

What Apps Script can do

Custom functions Your own formulas.
Menus Buttons for your macros.
Triggers Run on edit or on a schedule.
Services Gmail, Calendar, Drive.
1 / 2

Opening the editor

Inside a spreadsheet choose Extensions โ†’ Apps Script. A new browser tab opens with a project bound to that file and a starter function. Name the project, edit Code.gs and press Save. To run a function, choose it in the dropdown and press Run. The first time, Google asks you to authorize the script: it lists what the script may access, such as your spreadsheets or email. Only authorize code you understand, and be careful with code from strangers.

The language is modern JavaScript running on Google's V8 engine, so const, arrow functions, classes and template strings all work. The difference is the services: global objects such as SpreadsheetApp that talk to Google products. There is no browser, so no document or window.

Apps Script cannot run on this page. The .gs snippets are read-only, and each lesson example has a JavaScript stand-in with a tiny fake spreadsheet. The stand-ins show your own logic, not Google's services.

Custom functions

Any function in your project can be used in a cell as a formula. Add @customfunction in the comment to describe it in the autocomplete help.

JavaScript
/**
 * Adds sales tax to a price.
 *
 * @param {number} price The price before tax.
 * @param {number} rate The tax rate, for example 0.2.
 * @return {number} The price including tax.
 * @customfunction
 */
function TAXED(price, rate) {
  return Math.round(price * (1 + rate) * 100) / 100;
}

In a sheet, =TAXED(B2, 0.2) calls it. Custom functions can also take a range, which arrives as a two-dimensional array (an array of rows), and can return an array that spills like a built-in:

JavaScript
function DOUBLE_ALL(range) {
  return range.map((row) => row.map((value) => value * 2));
}

There are limits. A custom function used in a cell cannot change other cells or send email, it runs in a restricted mode, it has a time limit of about 30 seconds, and it recalculates when its inputs change. Make it pure: the same inputs always give the same output. For actions, use a menu or trigger instead.

JS Stand-in: a custom function over a range

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Reading and writing ranges

Scripts open the active spreadsheet, pick a sheet, then use ranges. The key methods are getValues() (a 2D array of rows) and setValues() (write a 2D array of the same size).

JavaScript
function markLowStock() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Inventory");
  const lastRow = sheet.getLastRow();
  const data = sheet.getRange(2, 1, lastRow - 1, 3).getValues(); // rows 2..last, columns A..C

  const status = data.map(([item, stock, minimum]) => [stock < minimum ? "REORDER" : "OK"]);

  sheet.getRange(2, 4, status.length, 1).setValues(status); // column D
}

Important points:

  • getRange(row, column, numRows, numColumns) uses 1-based numbers. getRange("A1:C5") also works with A1 notation.
  • Values come back as numbers, strings, booleans and Date objects. Empty cells are empty strings.
  • Read once, write once. Every call to a service is slow, so loop over an in-memory array, not over cells with getValue() inside the loop. Writing a 2D array in one setValues call is hundreds of times faster than writing cell by cell.
  • The array you write must exactly match the range's height and width, or the call fails.
  • Other helpful methods: appendRow([..]) adds a row at the bottom, getLastRow(), clearContents(), setBackground("yellow"), setNumberFormat("0.00") and sort().
JS Stand-in: read once, transform, write once

Press Run (or Ctrl + Enter) to see the console output here.

No output.

A function can be attached to a custom menu. The special function onOpen() is a simple trigger that Sheets runs every time the file is opened:

JavaScript
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu("Tools")
    .addItem("Mark low stock", "markLowStock")
    .addSeparator()
    .addItem("Say hello", "sayHello")
    .addToUi();
}

function sayHello() {
  SpreadsheetApp.getUi().alert("Hello from Apps Script!");
}

Reload the spreadsheet and a Tools menu appears next to Help. The strings in addItem are the label and the name of a function.

Another simple trigger is onEdit(e), which runs when a user edits a cell. The event object e holds the edited range, the new value, and the oldValue:

JavaScript
function onEdit(e) {
  const range = e.range;
  const sheet = range.getSheet();
  if (sheet.getName() === "Tasks" && range.getColumn() === 3 && e.value === "Done") {
    sheet.getRange(range.getRow(), 4).setValue(new Date());
  }
}

This stamps the date in column D when column C becomes "Done". Simple triggers run in a restricted mode: they cannot send email or access services that need authorization. Keep them fast, because a trigger has a short time limit.

Installable triggers

For more power, create an installable trigger. In the Apps Script editor open the clock icon (Triggers), press Add Trigger, pick the function and the event: From spreadsheet (on open, on edit, on change, on form submit) or Time-driven (every minute, hourly, daily at a given hour, weekly, monthly). Installable triggers run with the authorization of the person who created them, so they can send email with MailApp.sendEmail(...) or call other services.

You can also create triggers in code with ScriptApp.newTrigger("myFunction").timeBased().everyDays(1).atHour(8).create(). Be careful not to create duplicates each time a script runs: check ScriptApp.getProjectTriggers() first.

JS Stand-in: an onEdit trigger and its event object

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Good practice and limits

  • Test with copies. A script can overwrite thousands of cells, so run it on a copy first. Version history helps if something goes wrong (see Sharing, Comments and Version History).
  • Use Logger.log() (or console.log) and open Execution log to debug. For reading errors, use the Executions page.
  • Batch your calls. Read ranges with getValues, write with setValues.
  • Mind quotas. Google limits script run time per execution (about 6 minutes), the number of emails per day, and total trigger time per day. Consumer and Workspace accounts have different limits.
  • Protect secrets. Keep API keys in Project Settings โ†’ Script Properties, not in cells or in code you share.
  • Prefer formulas when they can do the job, such as QUERY for reporting. Scripts are for actions and for logic formulas cannot express.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. How do you open Apps Script from a spreadsheet?

    Extensions โ†’ Apps Script opens a project bound to the current spreadsheet.
  2. 2. What does range.getValues() return?

    getValues returns an array of rows, each row an array of cell values. setValues needs the same shape.
  3. 3. Why should you avoid calling getValue() for each cell inside a loop?

    Calls to services have overhead. Reading and writing in bulk is much faster.
  4. 4. Which restriction applies to a custom function used in a cell?

    Custom functions run in a restricted mode and should only return values. Use menus or triggers for actions.

Summary

  • Apps Script is JavaScript on Google's servers; open it from Extensions โ†’ Apps Script and authorize it once.
  • A custom function is a normal function used in a cell, with @customfunction for help; keep it pure.
  • getRange(...).getValues() and .setValues() move 2D arrays; read once, write once.
  • onOpen builds menus; onEdit reacts to edits; installable triggers add schedules and email.
  • Test on copies and watch quotas.

Next: Real-World Projects