Google Apps Script: Zero to One

Formulas live inside cells. Apps Script lives behind the spreadsheet — it's JavaScript that can read and write any cell, react to edits, and run on a schedule. This guide gets you from a blank script to three working automations.

Opening the script editor

Every Google Sheet has an attached script project. Open it from Extensions > Apps Script. You'll see a file called Code.gs (or Code.js) with an empty myFunction — that's a plain JavaScript file, running on Google's servers instead of your browser.

Your first script: reading and writing cells

Everything in Sheets is reached through SpreadsheetApp. Replace the default function with this:

function helloSheet() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const cell = sheet.getRange("A1");
  cell.setValue("Hello from Apps Script");
}

Save (Ctrl/Cmd + S), then click Run. The first time, Google asks you to authorize the script — that's expected, since it's about to modify your spreadsheet. Approve it, and A1 will show the text. getRange accepts any A1-style reference ("A1", "B2:D10", "A:A"), the same notation you already use in formulas.

Reading a range and looping over it

Scripts get useful once they process more than one cell. This reads a column of numbers and logs the total:

function sumColumnA() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const values = sheet.getRange("A1:A10").getValues(); // returns a 2D array
  let total = 0;
  for (const row of values) {
    total += row[0]; // row[0] is column A's value in that row
  }
  Logger.log(total);
}

getValues() always returns a 2D array — rows of columns — even for a single column, which is why the loop reads row[0]. Open View > Logs after running to see the result. This is the pattern behind almost every Apps Script automation: read a range into an array, transform it in JavaScript, write it back.

Writing a custom function (=YOURFUNCTION in a cell)

A function becomes usable inside a formula — like =SUM() or =XLOOKUP() — as soon as it takes cell values as arguments and returns a value:

/**
 * Converts a price and a tax rate into a total.
 * @param {number} price The base price.
 * @param {number} rate The tax rate, e.g. 0.2 for 20%.
 * @return The price including tax.
 * @customfunction
 */
function PRICEWITHTAX(price, rate) {
  return price * (1 + rate);
}

The @customfunction tag is what makes it show up as a formula. Type =PRICEWITHTAX(A2, 0.2) into any cell and it runs exactly like a built-in function — recalculating automatically when A2 changes. This is the fastest way to fill a gap the function reference doesn't cover.

Automating with triggers

The scripts above only run when you click Run. A trigger runs them automatically. The two most common:

Time-driven — runs on a schedule (hourly, daily, weekly), useful for scheduled reports:

function createDailyTrigger() {
  ScriptApp.newTrigger("sendDailyReport")
    .timeBased()
    .everyDays(1)
    .atHour(8)
    .create();
}

Run createDailyTrigger once — it registers sendDailyReport (a function you'd write separately) to run every day at 8am, without you opening the sheet.

On edit — runs whenever a cell changes, useful for validation or auto-formatting. This uses a special function name Apps Script calls automatically, no manual trigger setup needed:

function onEdit(e) {
  const range = e.range;
  if (range.getColumn() === 2 && range.getValue() < 0) {
    range.setBackground("#f8d7da"); // highlight negative values in column B
  }
}

e is the edit event — e.range is the cell that changed, e.value is its new value. This runs instantly on every edit, which makes it the right tool for lightweight validation, but keep it fast: a slow onEdit function makes typing in the sheet feel laggy.

A realistic first automation

Combine the pieces above into something useful: highlight any row in a budget sheet where the amount exceeds a threshold, every time someone edits it.

function onEdit(e) {
  const sheet = e.range.getSheet();
  if (sheet.getName() !== "Budget") return;

  const row = e.range.getRow();
  const amount = sheet.getRange(row, 2).getValue(); // column B = amount
  const rowRange = sheet.getRange(row, 1, 1, 3); // columns A:C for this row

  if (amount > 500) {
    rowRange.setBackground("#fff3cd");
  } else {
    rowRange.setBackground(null);
  }
}

Paste this into the script editor attached to your budget spreadsheet, save it, and edit any amount over 500 — the row highlights automatically, no manual formatting, no conditional-formatting rule to maintain.

Where to go next

• Browse the function reference before writing a custom function — there's a good chance the calculation you need already exists as a built-in.
• If you're new to spreadsheets generally, start with Google Sheets from Zero to One — formulas are the foundation Apps Script builds on.
• SpreadsheetApp, ScriptApp, and the other services used above are documented in Google's Apps Script reference — worth bookmarking once you start writing your own scripts.