Google Sheets Automation: Formulas to Scripts

Google Sheets automation comes in three layers. Formulas pull and reshape data on their own. Apps Script, Google's built-in JavaScript, runs code on a schedule or when something changes. And workflow tools like Zapier, Make or n8n connect the sheet to the rest of your apps. Most teams need the first two and add the third later. This guide covers each with working examples, Google's real limits, and the one trigger mistake that makes most emailed-from-Sheets automations fail without an error.

Which layer to use

Formulas that remove manual work

These four do most of the work in a well-built sheet:

=IMPORTRANGE("spreadsheet_url", "Sheet1!A1:D100")

Pulls a range from another spreadsheet and keeps it in sync, which ends copy-and-paste between files. You approve access once per source file.

=QUERY(A1:D100, "select A, sum(C) where B = 'Completed' group by A", 1)

Filters and summarizes in one step, using a SQL-like language. Pair it with IMPORTRANGE to build a summary tab from several sheets.

=ARRAYFORMULA(IF(B2:B="", "", C2:C*D2:D))

Fills a whole column from one cell, so new rows get the calculation without anyone dragging a formula down.

=IMPORTHTML("https://example.com/page", "table", 1)

Pulls a table from a public web page. Useful for simple tracking, but it breaks the moment the page layout changes, so do not build anything important on it.

Apps Script: triggers and working examples

Open your sheet, then Extensions, then Apps Script. Scripts run when a trigger fires, and the trigger type matters more than anything else here.

Simple triggers run when the function has a reserved name like onEdit or onOpen. They need no setup, but they cannot use services that require authorization. That includes sending email. A function named onEdit that calls MailApp.sendEmail will never send anything, and nothing visible tells you why.

Installable triggers are set up from the Triggers panel (the clock icon in Apps Script) or in code. They run with your authorization, so they can send email and call other services. They also come in more types: on edit, on change, on form submit, and time-driven.

So for an email on edit, give the function a normal name and attach an installable "on edit" trigger to it:

// Attach with an installable "On edit" trigger (Triggers panel), not the reserved onEdit name.
function emailWhenComplete(e) {
  const range = e.range;
  const sheet = range.getSheet();
  if (sheet.getName() !== 'Tasks' || range.getColumn() !== 3) return;
  if (range.getValue() !== 'Complete') return;

  const row = range.getRow();
  const task = sheet.getRange(row, 1).getValue();
  const email = sheet.getRange(row, 2).getValue();
  MailApp.sendEmail(email, 'Task completed', `"${task}" was marked complete.`);
}

A daily report, run by a time-driven trigger every morning:

function sendDailyReport() {
  const sheet = SpreadsheetApp.getActive().getSheetByName('Dashboard');
  const rows = sheet.getRange('A1:D10').getDisplayValues();
  const html = '<table border="1">' +
    rows.map(r => '<tr>' + r.map(c => `<td>${c}</td>`).join('') + '</tr>').join('') +
    '</table>';
  MailApp.sendEmail({
    to: 'team@yourcompany.com',
    subject: `Daily report ${new Date().toLocaleDateString()}`,
    htmlBody: html,
  });
}

And a weekly archive that moves rows older than 30 days in one batch, instead of deleting them one at a time:

function archiveOldRows() {
  const ss = SpreadsheetApp.getActive();
  const active = ss.getSheetByName('Active');
  const archive = ss.getSheetByName('Archive');
  const [header, ...rows] = active.getDataRange().getValues();
  const cutoff = Date.now() - 30 * 24 * 60 * 60 * 1000;

  const keep = rows.filter(r => new Date(r[3]).getTime() >= cutoff);  // date in column D
  const old = rows.filter(r => new Date(r[3]).getTime() < cutoff);
  if (old.length === 0) return;

  archive.getRange(archive.getLastRow() + 1, 1, old.length, old[0].length).setValues(old);
  active.clearContents();
  active.getRange(1, 1, keep.length + 1, header.length).setValues([header, ...keep]);
}

Reading and writing whole ranges at once, as this does, is far faster than working cell by cell, and it keeps you inside the runtime limit.

Google's limits

From Google's Apps Script quotas page, October 2026:

The email cap is the one small teams hit first. A report sent to 10 people every hour uses 240 recipients a day, which is over the limit on a personal Gmail account.

Two more behaviors catch people out. An on-edit trigger fires for edits a person makes, not for changes made by other scripts, the API or a formula recalculating. And when a triggered script fails, Apps Script emails the person who created the trigger, which is easy to miss if that person has left the company.

Connecting Sheets to other apps

When data has to move between the sheet and other tools (a new form lead into the CRM, a closed deal into a Slack channel), a workflow tool is usually cleaner than more Apps Script. Zapier and Make connect to Sheets without code. n8n does too and can be self-hosted; our n8n tutorial builds a lead workflow you could point at a sheet. For choosing between them, see Make vs n8n vs Claude Code.

When to move off Sheets

Sheets is a great tool for a team's working data and a poor one for a business's system of record. The signs you have outgrown it: the file takes ages to load or will not open at all, several people overwrite each other's changes, formulas break when someone sorts a range, and reports pull from copies of copies. Moving to Airtable or Notion swaps those problems for a different set of automation limits, covered in Airtable automation run limits and what Notion automations can do.

The first one is common enough that I recorded a walkthrough of the fix: moving the data into Supabase, which is a hosted Postgres database, and connecting it to Looker Studio for reporting. The data stays shareable with the whole team, and the sheet goes back to being a working view instead of the database.

If you want help building automations around Sheets, or moving the data into a proper database with reporting on top, that is what our AI workflow automation and data and insights services cover.