Google Sheets automation, 0 to 100

How to use this page. It runs from basic to advanced: the basics (levels 0 to 30), scripts and triggers (levels 40 and 50), then the 8 jobs that put them to work (invoices to dashboards), then the advanced levels 60 to 100. Find your level on the map, build the "prove it" project, then move up. Skipping levels is how most Sheets automations break: an agent (level 90) on a messy sheet (level 0) fails in ways no prompt can fix.

What Google Sheets automation is. Making a spreadsheet update itself and start work in other apps without anyone copying, pasting or checking it: formulas that calculate, scripts that run on an edit or a schedule, and tools like Make, n8n and Zapier that move rows into a CRM, an inbox or Slack.

Getting started, in 5 steps

1. Clean the sheet (level 0): headers in row 1, one record per row, an ID column, dropdowns and checkboxes.
2. Let formulas do what they can (level 10): FILTER and QUERY views cost nothing and never fail a run.
3. Connect one app (level 30): a new row creates a CRM contact in Make, Zapier or n8n.
4. Let the sheet start the workflow (level 50): an Apps Script trigger calls a webhook, so nothing polls.
5. Add AI last (level 90): it drafts, a person approves, then the workflow sends.

Most broken Sheets automations are broken here. Every tool has rules about sheet shape, and they all point the same way.

Do this

• Headers in row 1 only, one per column, unique and never blank. Zapier and Google Workspace Studio both require it.
• One record per row. No blank rows inside the data. Make's Watch New Rows: "If a sheet contains a blank row, Make doesn't process all subsequent rows." Zapier says the same about blank rows.
• No merged cells (Workspace Studio requires it, and scripts read merged cells badly).
• An ID column. Row numbers change when someone sorts or inserts a row; an ID never does. Zapier uses row numbers to spot new rows, which is why sorting or inserting mid-sheet can break a Zap. In a script, Utilities.getUuid() makes an ID. Never use RAND or NOW in a formula as an ID: they recalculate.
• Types you can trust: dropdowns for status (Data > Data validation), checkboxes for yes/no (Insert > Checkbox), real dates, plain numbers without currency text.
• Or convert the range to a table (Format > Convert to table). Each column gets a type (Number, Text, Date, Dropdown, Checkbox or Smart chips), and the sheet keeps people to it.
• Separate tabs for raw data (the automation writes here, nobody sorts it), views (formulas only) and config (settings people may change).
• Columns the automation owns, such as "Follow-up sent" or "Invoice no.", named so people know not to type in them.

Prove it: a Leads tab with ID, Lead, Email, Value, Status (dropdown), Approved (checkbox), Follow-up date, Follow-up sent. Then a View tab that never gets edited by hand.

With AI: paste your current headers and 3 fake rows and ask: "Restructure this into a raw tab and a view tab for automation. Flag merged cells, blank rows, mixed types and anything that will break Make, Zapier or Apps Script."

Before you automate anything, see how much a formula already does. A formula costs nothing and never fails a run.

About =AI() (Google's help page): it can generate, summarise, categorise and analyse sentiment. Only the first 350 selected cells generate per run. It does not update by itself when the source changes. It needs an eligible Google Workspace or Google AI plan. Use it for one-off clean-ups, not for anything that must run every time.

New in September 2026: sheets can hold 20 million cells, and manual calculation (File > Settings > Calculation) lets you pause recalculation on a heavy sheet while a big import runs.

Prove it: a View tab with a live "due today" list and overdue rows in red, with nothing typed by hand.

With AI: paste the headers and say: "Write one formula for <result>. Explain each part in one line. Do not use helper columns." In Gemini's side panel you can ask the same thing directly in the sheet.

Google can already do a lot before you touch another tool.

• Google Forms: link a form to the sheet and each answer arrives as a new row. In a script, the form-submit trigger fires for each response.
• Notifications: Tools > Notification settings > Edit notifications emails you when "Any changes are made" or "A user submits a form", right away or as a daily digest. No code.
• Gemini in Sheets: the side panel builds tables, formulas, charts, pivot tables, dropdowns and conditional formatting from plain language. Google reports a 70.48% success rate on the full SpreadsheetBench dataset (Google's own claim, April 2026). Eligible plans only.
• Google Workspace Studio (formerly Flows): describe a flow in plain words and Gemini builds it. For Sheets it has the starter "When a sheet changes" and the steps "Get sheet contents", "Update rows" and "Clear rows", plus "Repeat for each" to loop over rows (June 2026). It is available on Business, Enterprise and some Education plans. Google states nothing about personal accounts.
• Sheets canvas (August 2026): Gemini turns a sheet into an interactive app whose edits sync back to the sheet. Eligible plans only.

Prove it: a Google Form whose answers land in the Leads tab, with an email to you when one arrives.

With AI: Gemini's side panel for the sheet itself; Workspace Studio if your Workspace plan has it.

Here the sheet starts talking to the CRM, email, Slack and invoicing. Pick the tool by how it bills and how it triggers.

Polling costs money on Make. Make's help: "A trigger module runs once to check for new data: 1 operation." Checking every 15 minutes is 4 an hour x 24 x 30 = 2,880 operations a month, new rows or not. Make's Free plan has 1,000 credits a month. Zapier does not charge for checks ("Triggers don't count toward your task limit"). Level 50 removes the checks altogether.

Common actions at this level: add or update a row, search rows, look up a value, create a contact, send an email, post to Slack, create a calendar event.

Prove it: a new row in Leads creates a contact in your CRM and posts to Slack, and the CRM ID is written back to the row.

With AI: "Using only these modules <paste the module list from the docs>, give me the steps for <goal>, with the field mapping for each step." Then build it by hand in the editor.

Apps Script is JavaScript that runs on Google's servers, attached to your sheet. It is free with any Google account.

Learn these 6 things and you can read most scripts

1. Open it: in the sheet, Extensions > Apps Script. That script is "bound" to the sheet.
2. Read and write: SpreadsheetApp.getActive().getSheetByName('Leads').getRange('A2:H').getValues() gives a 2-D array; setValues() writes one back. One big read and one big write is far faster than a cell at a time.
3. Run and authorise: pick a function in the toolbar, click Run, allow the permissions once.
4. Logs: console.log() and the Executions page in the left panel show every run and every error.
5. Simple vs installable triggers: a function named onEdit(e) or onOpen(e) runs by itself but "can't access services that require authorization" and "can't run for longer than 30 seconds". So a simple onEdit cannot call a webhook or send an email. An installable trigger (Triggers panel, or ScriptApp.newTrigger) can.
6. The event object: an edit trigger receives e.range, e.value and e.oldValue. Google: value and oldValue are "Only available if the edited range is a single cell".

Starter: a menu with a clean-up button

function onOpen() {
  SpreadsheetApp.getUi().createMenu('Automation')
    .addItem('Trim spaces and fix emails', 'cleanSheet')
    .addToUi();
}

function cleanSheet() {
  const sheet = SpreadsheetApp.getActive().getSheetByName('Leads');
  const range = sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn());
  const values = range.getValues().map(row =>
    row.map(v => typeof v === 'string' ? v.trim() : v));
  range.setValues(values);  // one read, one write
}

Prove it: the menu appears when the sheet opens and the button cleans 500 rows in a couple of seconds.

With AI: the brief, plus the SpreadsheetApp and Triggers pages. Ask it to explain each line the first few times. That is the fastest way to learn the language.

Instead of a tool checking the sheet on a timer, the sheet calls the workflow the moment something happens. No polling and zero checks: your Make, n8n or Zapier flow runs only when there is real work.

What you need

• A webhook URL from the tool that does the work:
  • Make: "Webhooks > Custom webhook" as the first module.
  • n8n: a "Webhook" node (method POST), using the production URL with the workflow active.
  • Zapier: "Webhooks by Zapier > Catch Hook".
  • Any API that accepts a JSON POST.

The script (all 4 triggers)

Copy it into Extensions > Apps Script, replace everything there, then edit CONFIG.

/**
 * Sheets that cook: let the sheet decide when your workflow runs.
 * Prem Patel, Nex Automations. Checked against Google's Apps Script docs, 5 Oct 2026.
 *
 * One webhook URL (Make "Custom webhook", n8n "Webhook" node, Zapier "Catch Hook" or any API).
 * Four ways the sheet fires it:
 *   1. a dropdown changes to a value you choose       (onSheetEdit)
 *   2. a checkbox is ticked                            (onSheetEdit)
 *   3. a condition turns true after an edit           (onSheetEdit, fires once)
 *   4. a date or time arrives (follow-ups, reminders)  (dueFollowUps, time-driven)
 *
 * Setup: Extensions > Apps Script, paste, set CONFIG, then run installTriggers() once and allow access.
 * Row 1 must hold the column headers named in CONFIG.
 * installTriggers() replaces every trigger in THIS script project (not other scripts).
 * Keep the script time zone (Project Settings) and the sheet time zone (File > Settings) the same.
 */
const CONFIG = {
  WEBHOOK_URL: 'https://your-webhook-url',
  SHEET: 'Leads',
  STATUS_COL: 'Status',          // dropdown column
  STATUS_FIRES_ON: ['Won'],      // values that fire the webhook
  CHECKBOX_COL: 'Approved',      // checkbox column
  STOCK_COL: 'Stock',            // condition: stock at or below reorder level
  REORDER_COL: 'Reorder level',
  DUE_COL: 'Follow-up date',     // time: rows due today or earlier
  SENT_COL: 'Follow-up sent'     // the script stamps this so a row is sent once
};

function installTriggers() {
  const ss = SpreadsheetApp.getActive();
  ScriptApp.getProjectTriggers().forEach(t => ScriptApp.deleteTrigger(t));
  ScriptApp.newTrigger('onSheetEdit').forSpreadsheet(ss).onEdit().create();
  ScriptApp.newTrigger('dueFollowUps').timeBased().everyDays(1).atHour(9).create();
}

/* Installable edit trigger. Fires on edits made by people, not by scripts or the API. */
function onSheetEdit(e) {
  const sheet = e.range.getSheet();
  if (sheet.getName() !== CONFIG.SHEET || e.range.getRow() === 1) return;
  if (e.range.getNumRows() > 1 || e.range.getNumColumns() > 1) return; // single-cell edits only
  const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
  const col = headers[e.range.getColumn() - 1];
  const rec = rowAsObject(sheet, headers, e.range.getRow());

  if (col === CONFIG.STATUS_COL && CONFIG.STATUS_FIRES_ON.includes(e.value)) {
    post('status_changed', rec, { from: e.oldValue || '', to: e.value });
  }
  if (col === CONFIG.CHECKBOX_COL && e.range.isChecked() === true) {
    post('checkbox_ticked', rec);
  }
  if (col === CONFIG.STOCK_COL || col === CONFIG.REORDER_COL) {
    // fire once, when the rule turns true: not on blanks, not again on every later edit
    const filled = v => v !== '' && v !== null && v !== undefined;
    const met = r => filled(r[CONFIG.STOCK_COL]) && filled(r[CONFIG.REORDER_COL]) &&
                     Number(r[CONFIG.STOCK_COL]) <= Number(r[CONFIG.REORDER_COL]);
    const before = Object.assign({}, rec); before[col] = e.oldValue;
    if (met(rec) && !met(before)) post('condition_met', rec, { rule: 'stock at or below reorder level' });
  }
}

/* Time-driven trigger: once a day between 9 and 10 (script time zone), send only the rows that are due. */
function dueFollowUps() {
  const sheet = SpreadsheetApp.getActive().getSheetByName(CONFIG.SHEET);
  const data = sheet.getDataRange().getValues();
  const headers = data[0];
  const due = headers.indexOf(CONFIG.DUE_COL), sent = headers.indexOf(CONFIG.SENT_COL);
  if (due < 0 || sent < 0) throw new Error('Missing header: ' + CONFIG.DUE_COL + ' or ' + CONFIG.SENT_COL);
  const today = new Date(); today.setHours(23, 59, 59, 999);
  const rows = [];
  for (let r = 1; r < data.length; r++) {
    const d = data[r][due];
    if (d instanceof Date && d <= today && !data[r][sent]) {
      rows.push(Object.assign({ _row: r + 1 }, toObject(headers, data[r])));
    }
  }
  if (!rows.length) return;                       // nothing due: no webhook, no cost
  if (!post('followups_due', { rows: rows })) return;  // webhook failed: rows stay unsent, tomorrow retries
  const now = new Date();
  rows.forEach(x => sheet.getRange(x._row, sent + 1).setValue(now));  // script edits never re-fire onSheetEdit
}

function rowAsObject(sheet, headers, row) {
  return Object.assign({ _row: row }, toObject(headers, sheet.getRange(row, 1, 1, headers.length).getValues()[0]));
}
function toObject(headers, values) {
  const o = {}; headers.forEach((h, i) => { o[h || 'col' + (i + 1)] = values[i]; }); return o;
}
function post(event, record, extra) {
  const res = UrlFetchApp.fetch(CONFIG.WEBHOOK_URL, {
    method: 'post',
    contentType: 'application/json',
    payload: JSON.stringify(Object.assign({ event: event, sheet: CONFIG.SHEET, record: record, at: new Date().toISOString() }, extra || {})),
    muteHttpExceptions: true
  });
  const code = res.getResponseCode();
  if (code >= 300) { console.warn(event + ' webhook ' + code + ': ' + res.getContentText()); return false; }
  return true;
}

For a sheet you keep long term, move the webhook URL out of the code: put it in Project Settings > Script Properties as WEBHOOK_URL and read it with PropertiesService.getScriptProperties().getProperty('WEBHOOK_URL') (level 100).

Setup in 4 steps

1. Paste the script and fill in CONFIG (webhook URL, sheet name, your column names).
2. Check that Project Settings > Time zone matches File > Settings > Time zone in the sheet.
3. Select installTriggers in the toolbar and click Run once. Allow the permissions.
4. Test: change a status to Won, tick a box, drop a stock number, set a follow-up date to today.

What your workflow receives

{
  "event": "status_changed",
  "sheet": "Leads",
  "record": { "_row": 3, "Lead": "Mia, Austin", "Value": 1800, "Status": "Won" },
  "from": "Proposal",
  "to": "Won",
  "at": "2026-10-05T14:02:11.000Z"
}

Route on event in Make (a Router with a filter per event) or in n8n (a Switch node on event).

The 4 triggers, with real recipes

Trigger 1: a dropdown changes. STATUS_COL and STATUS_FIRES_ON decide which values fire. Every other change is ignored.

Recipe: Status set to Won

1. Make: Custom webhook, then a Router on event = status_changed.
2. QuickBooks: create an invoice from record.
3. Gmail: send the welcome email.
4. Slack: post to #sales.
5. Google Sheets: Update a Cell to write the invoice number back to record._row.

Trigger 2: a box is ticked. The script uses e.range.isChecked(), Google's documented check, so it works whatever value your checkbox stores.

Recipe: Approved ticked: send the contract for e-signature, create the client folder in Google Drive, book the kick-off in Google Calendar and write the contract link back to the row.

Trigger 3: a condition turns true. The stock rule fires once, at the moment it turns true. It ignores blank cells and does not fire again on every later edit while the stock stays low. Change met for any rule: an overdue invoice, a score above 80, a budget over its limit.

Recipe: stock at or below the reorder level: email the purchase order to the supplier, WhatsApp the owner and tag the product low-stock in Shopify.

Trigger 4: a date arrives. dueFollowUps runs once a day. Google picks a time between 9 and 10 in the script's time zone and keeps it from day to day. It sends only the rows due today or earlier, in one webhook call, then stamps each row as sent. If the webhook fails, nothing is stamped and tomorrow's run tries again. Time-driven triggers can run as often as every minute or as rarely as once a month.

Recipe: daily follow-ups (n8n): Webhook, then Split Out on rows, then Gmail (or WhatsApp) per lead, then a task in your CRM. No search module scans the whole sheet every hour.

The gotcha

Google: "Script executions and API requests don't cause triggers to run." Edits made by Make, n8n, Zapier or another script never fire an edit trigger. Only people do. Make's instant Watch Changes follows the same rule. That is also why the follow-up script can stamp rows safely: its own writes never re-fire the trigger.

Two more:

• In this script the edit trigger skips a paste across many cells (single-cell edits only).
• On Zapier, add new rows at the bottom and avoid sorting or inserting mid-sheet, or the trigger can miss rows.

Prove it: all 4 tests from the setup fire the right branch in your workflow, and turning the webhook off makes the follow-up run leave rows unsent.

With AI: the brief, plus the Triggers, Event objects, UrlFetchApp and Quotas pages. Then run prompt 3.

8 jobs your sheet can run for you

The data for your invoices, payslips, reports and reminders is already in the sheet, so the work is turning rows into finished documents and messages. Apps Script, the Google APIs (Sheets, Docs and Drive), a workflow tool (Make, Zapier or n8n) and AI each do one part, and the map below shows which. Every job here needs levels 0 to 50 at most; the private dashboard route uses level 80 and the AI summary level 90.

The two limits behind every recipe. Apps Script is free with a Google account, but a free account can email 100 recipients a day and create 250 documents a day; Google Workspace raises both to 1,500 (Apps Script quotas page, updated 3 Sep 2026). Each script run stops at 6 minutes, so big batches run in parts (same page).

1. A row becomes an invoice PDF, sent and logged

• Columns: a tab "Invoices" with Client, Email, Description, Amount, Invoice no, PDF, Sent. A Google Docs template holding the tags {{Client}} {{Description}} {{Amount}} {{Invoice no}} {{Date}}.
• Trigger and what runs: you pick the row and click Invoices > Send invoice for selected row. The script reserves the next invoice number under a lock, copies the template, fills the tags, saves a PDF to Drive, emails it and writes the PDF link and the send time back to the row. Google's own sample "Generate & send PDFs from Sheets" does the same job (Beginner, 15 minutes, Apps Script samples, updated 3 Sep 2026), but its invoice number is random and can repeat; the lock is the fix.
• No-code route: Make (Watch New Rows, Create a Document from a Template, Download a Document, email, Update a Row), Zapier (New Spreadsheet Row, Create Document From Template, Google Drive Export File, Gmail, Update Spreadsheet Row) or n8n (Drive Copy, Docs Find and Replace Text, Drive Download as PDF, Gmail, Update Row), all read 5 Oct 2026.
• Where AI helps: it writes and adapts the script from your brief and checks that every template tag matches a column name. Totals stay in normal formulas: AI drafts words, it does not compute money.
• Safety rule: the number is written to the row before the PDF, and a filled "Sent" cell stops a second send, so a re-run after a failure reuses the number. For a review step, swap MailApp.sendEmail for GmailApp.createDraft and press Send yourself.

Learn it in this guide: Level 40 (your first script), Level 80 (locks) and Level 50 trigger 1 if a Status change should start it.

/**
 * Invoice from one sheet row: Docs template > PDF > email. Run from a menu on the selected row.
 * Prem Patel, Nex Automations. Checked against Google's Apps Script docs, 5 Oct 2026.
 * Script Properties: TEMPLATE_ID (Docs template), FOLDER_ID (Drive folder), NEXT_INVOICE (e.g. 1001)
 * Sheet "Invoices", row 1 headers: Client, Email, Description, Amount, Invoice no, PDF, Sent
 * Template tags: {{Client}} {{Description}} {{Amount}} {{Invoice no}} {{Date}}
 */
function onOpen() {
  SpreadsheetApp.getUi().createMenu('Invoices').addItem('Send invoice for selected row', 'sendSelected').addToUi();
}
function sendSelected() { sendInvoice(SpreadsheetApp.getActiveRange().getRow()); }

function sendInvoice(row) {
  const props = PropertiesService.getScriptProperties();     // developers.google.com/apps-script/guides/properties
  const sheet = SpreadsheetApp.getActive().getSheetByName('Invoices');
  const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
  const shown = sheet.getRange(row, 1, 1, headers.length).getDisplayValues()[0]; // .../spreadsheet/range (keeps currency format)
  const col = h => headers.indexOf(h) + 1;
  const data = {};
  headers.forEach((h, i) => { data[h] = shown[i]; });
  if (row < 2 || data['Sent']) return;                       // header row, or already sent

  if (!data['Invoice no']) {                                 // reserve a number once, under a lock
    const lock = LockService.getScriptLock();                // .../reference/lock/lock-service
    lock.waitLock(10000);
    try {
      const n = Number(props.getProperty('NEXT_INVOICE'));
      props.setProperty('NEXT_INVOICE', String(n + 1));
      sheet.getRange(row, col('Invoice no')).setValue(n);
      SpreadsheetApp.flush();
      data['Invoice no'] = String(n);
    } finally { lock.releaseLock(); }
  }
  data['Date'] = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'd MMM yyyy');

  const name = 'Invoice ' + data['Invoice no'] + ' ' + data['Client'];
  const folder = DriveApp.getFolderById(props.getProperty('FOLDER_ID'));       // .../reference/drive/drive-app
  const copy = DriveApp.getFileById(props.getProperty('TEMPLATE_ID')).makeCopy(name, folder); // .../reference/drive/file
  const doc = DocumentApp.openById(copy.getId());            // .../reference/document/document-app
  const body = doc.getBody();                                // first tab only
  Object.keys(data).forEach(k => body.replaceText(escapeRe('{{' + k + '}}'), data[k])); // .../reference/document/body (RE2 regex)
  doc.saveAndClose();                                        // .../reference/document/document (flush before export)

  const pdf = copy.getAs(MimeType.PDF).setName(name + '.pdf'); // .../reference/drive/file + base/blob (setName avoids the dot gotcha)
  const pdfFile = folder.createFile(pdf);                    // .../reference/drive/folder
  if (MailApp.getRemainingDailyQuota() < 1) throw new Error('Email quota used up for today');
  MailApp.sendEmail(data['Email'], name, 'Hello ' + data['Client'] + ',\n\nYour invoice is attached.', {
    attachments: [pdf], name: 'Accounts'                     // .../reference/mail/mail-app
  });
  sheet.getRange(row, col('PDF')).setValue(pdfFile.getUrl());
  sheet.getRange(row, col('Sent')).setValue(new Date());
}

function escapeRe(s) { return s.replace(/[.*+?^${}()|[\]\\]/g, '\\$&'); }

The ... in the comments stands for developers.google.com/apps-script. Four things Google's docs say that shape this script: replaceText takes a regular expression (the script escapes each tag for you); a tag split across two text styles may not match, so type each tag in one go; getBody fills the first tab's body only, not headers or footers; and converting to PDF replaces whatever follows the last full stop in a file name, which setName prevents. The script was checked against the docs, not run in a live account, so test it on a copy with a "$" amount and a client name that contains a full stop before real clients see it.

2. A quote goes out. Accepted, it becomes the invoice

• Columns: a tab "Quotes" with Quote no, Client, Email, Items, Total, Valid until, Status (dropdown: Draft, Sent, Accepted, Lost) and Copied. A Price list tab that the Items look up with XLOOKUP.
• Trigger and what runs: Status moving to Sent builds the quote PDF from a Docs template (the same chain as recipe 1). Status moving to Accepted copies the row to Invoices and stamps Copied, so recipe 1 takes over. This is an installable edit trigger, and Google says "Script executions and API requests don't cause triggers to run": if a tool changes the status, that tool has to start the next step.
• No-code route: Make, Zapier and n8n all fill a Docs template (Make "Create a Document from a Template", Zapier "Create Document From Template", n8n Drive Copy then Docs Update, read 5 Oct 2026). An accepted quote can also open a CRM deal and post to Slack.
• Where AI helps: it turns a client's emailed request into draft line items that you check before the quote goes out.
• Safety rule: copy on Accepted only when Copied is empty, so a second edit cannot make a second invoice. Valid until feeds recipe 6, so an open quote gets a reminder instead of silence.

Learn it in this guide: Level 10 (XLOOKUP), Level 50 trigger 1 (a dropdown changes).

3. Payroll tab in, private payslips out

• Columns: Name, Email, Days or hours, Gross, Deductions, Net, Payslip PDF, Sent. Deductions and net come from your payroll system or accountant; the sheet only receives them.
• Trigger and what runs: a menu click after the numbers are signed off. One payslip per person from a Docs template, saved as a PDF and emailed only to that person's own address, then one summary to the owner or accountant. Google's own sample "Collect & review timesheets from employees" collects hours with Forms, calculates weekly pay and emails each person their approval status (Beginner, 15 minutes, Apps Script samples, updated 3 Sep 2026).
• The rule: Sheets is the report and document layer, never the compliance engine. Statutory deductions, tax filings and their deadlines belong to a payroll system or your accountant. AI never decides pay; Google itself says "Don't rely on Gemini features as medical, legal, financial or other professional advice" (Sheets AI function help, read 5 Oct 2026). AI can write the formulas and a check column that flags any net that does not add up.
• Privacy: each payslip goes only to its own person. Keep the payroll sheet and the PDF folder restricted, never publish a payroll tab to the web, and send the first runs as Gmail drafts that a person checks.
• No-code route and limit: n8n's library has a template "Generate and email PDF payslips from Google Sheets with Gmail" (n8n templates, read 5 Oct 2026). From Apps Script, each payslip uses one of your 100 email recipients a day on a free account, 1,500 on Workspace (Apps Script quotas, updated 3 Sep 2026).

Learn it in this guide: Level 40, Level 90 (draft, approve, send) and Level 100 (secrets and logs).

4. Contracts and letters fill themselves from a template

• Columns: one row per person or deal: Name, Email, Role, Start date, Fee, Doc link, Status. A Docs template with {{Name}} {{Role}} {{Start date}} {{Fee}}.
• Trigger and what runs: a tick or a status change copies the template, swaps each tag for the row's value, saves a PDF to the person's folder and sends it for signature. Apps Script uses replaceText (as in recipe 1); the Docs API uses one batchUpdate with a ReplaceAllTextRequest per tag, after copying the template with Drive files.copy, and Google says "Any text formatting you want to replace is preserved" (Docs API merge guide, updated 3 Sep 2026).
• No-code route: the template steps from recipe 1 in Make, Zapier or n8n, then an e-sign app and a Drive folder. Mail merge is a mass-market job: Yet Another Mail Merge shows 16M+ users and Mail Merge 12M+ on the Google Workspace Marketplace (installs as displayed, read 5 Oct 2026).
• Where AI helps: it maps your columns to the tags and lists every row with a blank field before any document is made.
• Safety rule and limit: the legal wording stays yours; automation only fills the blanks. The Docs API allows 60 write requests a minute per user per project (Docs API limits, updated 3 Sep 2026), so send all of a document's replacements in one batchUpdate.

Learn it in this guide: Level 40, and Level 70 for how one batch request does many changes.

5. Every client gets their monthly report on the 1st

• Columns: a Clients tab with Client, Email, Summary and Approved, plus a QUERY or pivot per client that holds the month's numbers.
• Trigger and what runs: a monthly time-driven trigger; Google's time triggers run "as frequently as every minute or as infrequently as once per month" and pick a time within the hour you set. For each client it fills a Docs report, saves a PDF and emails it once Approved is ticked. Google's sample "Summarize data from multiple sheets" is a starting point (Apps Script samples, updated 3 Sep 2026).
• No-code route: Make, Zapier or n8n pull ads, Stripe or GA4 numbers into the sheet on a schedule first. Data Studio (formerly Looker Studio) can email a PDF of a report on a schedule with no code; without Pro, each report gets one schedule, at most once a day, to at most 50 addresses (Data Studio docs, updated 30 Sep 2026).
• Where AI helps: it drafts the 3-line "what changed this month" summary into the Summary column, and you approve it. The numbers stay in formulas, because the =AI() function returns text and cannot see the whole sheet (Sheets AI function help, read 5 Oct 2026).
• Limit: each run stops at 6 minutes and each report is one document out of 250 a day on a free account (Apps Script quotas, updated 3 Sep 2026), so many clients run in batches.

Learn it in this guide: Level 50 trigger 4 (a date arrives) and Level 90 (draft, approve, send).

6. Unpaid on day 7? The chaser goes out

• Columns: on the Invoices tab add Due date, Days overdue (a formula on TODAY() for unpaid rows), Paid (checkbox), Chased (count) and Last chased.
• Trigger and what runs: one daily time trigger, not a check every minute. At 7, 14 and 30 days overdue it sends the next message and stamps the row; ticked Paid rows are skipped. It is a common need: "send email based on date" is the first Google autocomplete suggestion for "google sheets send email" (Google autocomplete, US, read 5 Oct 2026).
• No-code route: Apps Script sends the email itself. For WhatsApp, SMS, Slack or a CRM task, the daily run sends the due rows to Make, Zapier or n8n in one webhook call, as trigger 4 in Level 50 already does.
• Where AI helps: it writes the friendly, firm and final versions once; the script fills in the name, number and amount. n8n's library has a template "Chase overdue invoice payments from Google Sheets with OpenAI and Gmail" (n8n templates, read 5 Oct 2026).
• Safety rule: stamp a row only after the send succeeds, so a failed run retries tomorrow, and read Paid at send time so a client who just paid is not chased.

Learn it in this guide: Level 10 (the overdue formula) and Level 50 trigger 4.

7. Forms, Gmail and ads fill the rows for you

• Columns: one clean list with headers in row 1, plus Source and Received at, so you can see where each row came from.
• Trigger and what runs: Google Forms writes responses into the sheet by itself, and the sheet's form submit trigger can clean and route each new row with no extra tool. A response submitted by a script does not fire it ("Script executions and API requests don't cause triggers to run").
• No-code route: Zapier's most popular apps paired with Google Sheets start with Gmail, Facebook Lead Ads, Google Forms, Slack and HubSpot (zapier.com, read 5 Oct 2026). Google Sheets is in 4,520 of 12,920 n8n templates, more than Gmail (2,858) or Slack (2,129) (n8n template library, read 5 Oct 2026).
• API route: values.append in batches. A batch counts as one request against 60 write requests a minute per user (Sheets API limits, read 5 Oct 2026).
• Where AI helps: it pulls the fields out of an emailed invoice or a PDF into the right columns. Have it leave a field blank and flag the row rather than guess.

Learn it in this guide: Level 20 (forms), Level 30 (connect apps) and Level 60 (batches).

8. Your sheet, live on a dashboard page

• Columns: a Summary tab that holds only what you are happy to show, headers in row 1 (the page below reads Category and Amount).
• Public route: File > Share > Publish to web, pick the one tab and CSV, then paste the link into the page below. It shows 3 tiles (rows, total and average) and a bar chart, and re-reads every 5 minutes. Google says published changes "might take a few minutes" and warns "Be careful when publishing private or sensitive info" (Publish to the web help, read 5 Oct 2026). Google's help lists no publishing formats; CSV worked when tested on 5 Oct 2026.
• Filtered public route: /gviz/tq?tqx=out:csv&tq=select ... on the sheet's URL is documented (Google Charts Query Language, updated 10 Jul 2024) and needs only "anyone with the link" sharing, not publishing. The /export?format=csv URL also works today but is undocumented, so it can change without notice.
• Private routes: an Apps Script web app (doGet) that returns the Summary tab as JSON behind a long token, cached for 5 minutes with CacheService (Level 80; a token in a URL stops casual access, not a determined attacker). Or the Sheets API values.get with OAuth (an API key reads public sheets only). Or Data Studio (formerly Looker Studio): free, with a Sheets connector whose fastest data refresh is every 15 minutes, and an iframe embed (Data Studio docs, updated 30 Sep 2026).
• Where AI helps: give it your Summary tab's headers and this page, and it rebuilds the tiles and chart for your columns. Inside Sheets, Sheets canvas builds an interactive dashboard, but its access is the same as the sheet's, so it is not a public page (Sheets canvas help, read 5 Oct 2026).

Learn it in this guide: Level 80 (Sheets as a small backend).

<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>Sheet Dashboard</title>
<!--
  A one-file dashboard fed by a Google Sheet. No build step.
  1. In the sheet: File > Share > Publish to web > pick ONE tab > format "Comma-separated values (.csv)" > Publish.
  2. Paste the link into CSV_URL below. Row 1 must be headers.
  WARNING: published data is PUBLIC. Anyone with the link can read every cell of that tab.
  Publish a summary tab that holds only what you are happy to show the world.
  Google says changes reach the published copy in "a few minutes"; this page re-reads every 5 minutes.
-->
<style>
  :root { --bg:#f6f7f9; --card:#fff; --ink:#14171c; --muted:#5b6472; --line:#e3e6eb; --accent:#2f6fed; }
  @media (prefers-color-scheme: dark) {
    :root { --bg:#0f1216; --card:#171b21; --ink:#eef1f5; --muted:#9aa4b2; --line:#262c35; --accent:#6c9bff; }
  }
  * { box-sizing: border-box; }
  body { margin:0; padding:24px 16px; background:var(--bg); color:var(--ink); font:15px/1.4 system-ui, sans-serif; }
  main { max-width:960px; margin:0 auto; }
  h1 { font-size:20px; margin:0 0 4px; }
  #status { color:var(--muted); font-size:13px; margin:0 0 16px; }
  .tiles { display:grid; grid-template-columns:repeat(auto-fit, minmax(180px, 1fr)); gap:12px; margin-bottom:16px; }
  .tile, .panel { background:var(--card); border:1px solid var(--line); border-radius:10px; padding:16px; }
  .tile span { display:block; color:var(--muted); font-size:13px; }
  .tile strong { display:block; font-size:28px; margin-top:4px; font-variant-numeric:tabular-nums; }
  .panel { height:360px; }
</style>
</head>
<body>
<main>
  <h1>Sheet dashboard</h1>
  <p id="status">Loading...</p>
  <section class="tiles">
    <div class="tile"><span>Rows</span><strong id="kpi-rows">-</strong></div>
    <div class="tile"><span id="kpi-sum-label">Total</span><strong id="kpi-sum">-</strong></div>
    <div class="tile"><span id="kpi-avg-label">Average</span><strong id="kpi-avg">-</strong></div>
  </section>
  <div class="panel"><canvas id="chart" aria-label="Total by category"></canvas></div>
</main>
<script src="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/4.5.1/chart.umd.min.js"
  integrity="sha512-WoViKhKD4qI2WruSZqv9+kvM4WfFhUMQCLN4QlDTt5aU56fLQy2gYoxWIqlEnXqJy/+Ac5q/hk1oWfqnMDhwMA=="
  crossorigin="anonymous" referrerpolicy="no-referrer"></script>
<script>
// The only line you must change. Published-to-web CSV link (.../pub?gid=0&single=true&output=csv).
const CSV_URL = 'https://docs.google.com/spreadsheets/d/e/YOUR_PUBLISHED_ID/pub?gid=0&single=true&output=csv';
const LABEL_COL = 'Category';   // header of the column to group by (bar labels)
const VALUE_COL = 'Amount';     // header of the numeric column to add up
const REFRESH_MS = 5 * 60 * 1000;

// RFC 4180 style parser: handles "quoted, commas", "" escaped quotes and line breaks inside quotes.
function parseCSV(text) {
  const rows = []; let row = [], field = '', inQuotes = false;
  for (let i = 0; i < text.length; i++) {
    const c = text[i];
    if (inQuotes) {
      if (c === '"' && text[i + 1] === '"') { field += '"'; i++; }
      else if (c === '"') inQuotes = false;
      else field += c;
    } else if (c === '"') inQuotes = true;
    else if (c === ',') { row.push(field); field = ''; }
    else if (c === '\n' || c === '\r') {
      if (c === '\r' && text[i + 1] === '\n') i++;
      row.push(field); rows.push(row); row = []; field = '';
    } else field += c;
  }
  if (field !== '' || row.length) { row.push(field); rows.push(row); }
  return rows.filter(r => r.some(v => v.trim() !== ''));   // drop blank lines
}

// "1,234.50", "$99", "12%" -> number. Returns NaN for text.
const toNumber = v => parseFloat(String(v).replace(/[^0-9.\-]/g, ''));
const fmt = n => Number.isFinite(n) ? n.toLocaleString(undefined, { maximumFractionDigits: 2 }) : '-';
let chart;

async function load() {
  const status = document.getElementById('status');
  try {
    const res = await fetch(CSV_URL, { cache: 'no-store' });
    if (!res.ok) throw new Error('HTTP ' + res.status);
    const [header, ...data] = parseCSV(await res.text());
    const li = header.indexOf(LABEL_COL), vi = header.indexOf(VALUE_COL);
    if (li < 0 || vi < 0) throw new Error('Headers not found: ' + LABEL_COL + ', ' + VALUE_COL);

    const totals = new Map(); let sum = 0, count = 0;
    for (const r of data) {
      const n = toNumber(r[vi]);
      if (!Number.isFinite(n)) continue;
      sum += n; count++;
      const key = (r[li] || '(blank)').trim();
      totals.set(key, (totals.get(key) || 0) + n);
    }
    // textContent only: sheet values are never parsed as HTML.
    document.getElementById('kpi-rows').textContent = fmt(data.length);
    document.getElementById('kpi-sum-label').textContent = 'Total ' + VALUE_COL;
    document.getElementById('kpi-sum').textContent = fmt(sum);
    document.getElementById('kpi-avg-label').textContent = 'Average ' + VALUE_COL;
    document.getElementById('kpi-avg').textContent = fmt(count ? sum / count : NaN);

    const sorted = [...totals.entries()].sort((a, b) => b[1] - a[1]).slice(0, 12);
    const accent = getComputedStyle(document.documentElement).getPropertyValue('--accent').trim();
    const cfg = { type: 'bar',
      data: { labels: sorted.map(e => e[0]), datasets: [{ label: VALUE_COL, data: sorted.map(e => e[1]), backgroundColor: accent }] },
      options: { maintainAspectRatio: false, plugins: { legend: { display: false } }, scales: { y: { beginAtZero: true } } } };
    if (chart) { chart.data = cfg.data; chart.update(); } else chart = new Chart(document.getElementById('chart'), cfg);
    status.textContent = 'Updated ' + new Date().toLocaleTimeString() + '. Next check in 5 minutes.';
  } catch (err) {
    status.textContent = 'Could not load the sheet (' + err.message + '). Showing the last good data.';
  }
}
load();
setInterval(load, REFRESH_MS);
</script>
</body>
</html>

To try it, save the code as dashboard.html, change CSV_URL, LABEL_COL and VALUE_COL, and open it in a browser or upload it to any static host. It writes sheet values with textContent only, so a cell can never inject HTML into the page.

At 5 rows a day, one run per row is fine. At 500 it costs real money, runs into Google's limits and fills Slack with noise. The fix is the same everywhere: gather first, act once.

Make: aggregate before you act

Iterating 40 order rows and creating an invoice and a Slack message for each one means 80 module runs, and 40 invoices nobody wants.

The Level 5 way

1. Google Sheets: Search Rows (today's orders).
2. Array aggregator (source module: Search Rows) to merge the rows into one bundle holding an array.
3. QuickBooks: one invoice, mapping the array to its line items.
4. Slack: one summary (or a Text aggregator for a tidy list).
5. Google Sheets: Bulk Update Rows (Advanced) to mark every row invoiced.

Make's own help shows an aggregator cutting one run from 11 operations to 3. Make has two bulk Sheets modules: Bulk Add Rows (Advanced) and Bulk Update Rows (Advanced).

n8n: aggregate before you act

Handling 50 new tickets one by one means 50 AI calls and 50 sheet writes. It also runs into Google's limit of 60 writes a minute per user, which returns 429 errors.

The better flow

1. Google Sheets Trigger (or the level 50 webhook).
2. Aggregate node, "All Item Data": groups the 50 items into a single list.
3. One AI step: "rank these tickets by urgency and summarise each in one line".
4. Slack: one digest.
5. HTTP Request to write every priority back in one call (Google counts a batch as one request):

POST https://sheets.googleapis.com/v4/spreadsheets/YOUR_SHEET_ID/values:batchUpdate
{
  "valueInputOption": "USER_ENTERED",
  "data": [
    { "range": "Tickets!E2", "values": [["High"]] },
    { "range": "Tickets!E3", "values": [["Low"]] }
  ]
}

n8n bills per workflow run, not per step ("It doesn't matter how many steps in the workflow"), so the win here is speed and no rate-limit retries.

Apps Script: read once, write once

getValues() on the whole range, change the array in memory, then setValues() once. A loop that calls getValue() or setValue() per cell is the most common reason a script hits the 6-minute limit.

Prove it: today's orders become one invoice with line items and one Slack message, and every row is marked in one write.

With AI: "Here is my per-row flow and my daily volume. Rewrite it to aggregate first. Show the operation or request count before and after, using the billing rules in these docs."

None of these are in Make's 27 Sheets modules or n8n's 10 operations. Zapier has the first three as ready-made actions. Everything else needs one API call:

• Make: Google Sheets > Make an API Call, URL spreadsheets/YOUR_SHEET_ID:batchUpdate, method POST (Make adds https://sheets.googleapis.com/v4/).
• n8n: an HTTP Request node with your Google Sheets credential, POST https://sheets.googleapis.com/v4/spreadsheets/YOUR_SHEET_ID:batchUpdate.
• Zapier: Google Sheets > API Request (Beta).

sheetId is the number after #gid= in the sheet's URL. Indexes start at 0, and end values are exclusive.

One call, four changes (dropdown on column C, freeze row 1, protect the header, sort by column D):

{
  "requests": [
    { "setDataValidation": {
        "range": { "sheetId": 0, "startRowIndex": 1, "startColumnIndex": 2, "endColumnIndex": 3 },
        "rule": { "condition": { "type": "ONE_OF_LIST", "values": [
          { "userEnteredValue": "New" }, { "userEnteredValue": "Proposal" },
          { "userEnteredValue": "Won" }, { "userEnteredValue": "Lost" } ] },
          "showCustomUi": true, "strict": true } } },
    { "updateSheetProperties": {
        "properties": { "sheetId": 0, "gridProperties": { "frozenRowCount": 1 } },
        "fields": "gridProperties.frozenRowCount" } },
    { "addProtectedRange": {
        "protectedRange": { "range": { "sheetId": 0, "startRowIndex": 0, "endRowIndex": 1 },
          "description": "Header row", "warningOnly": false } } },
    { "sortRange": {
        "range": { "sheetId": 0, "startRowIndex": 1 },
        "sortSpecs": [ { "dimensionIndex": 3, "sortOrder": "ASCENDING" } ] } }
  ]
}

A batchUpdate is all or nothing: if one request fails, none of the changes are written.

Read two tabs in one call: GET https://sheets.googleapis.com/v4/spreadsheets/YOUR_SHEET_ID/values:batchGet?ranges=Leads!A1:D200&ranges=Orders!A1:F500

What batchUpdate is, and all 74 things it can do

There are two calls with "batchUpdate" in the name, and mixing them up is the most common API mistake.

How spreadsheets.batchUpdate behaves (Google's batchUpdate guide, updated 3 Sep 2026):

• All or nothing: "if one request is unsuccessful, none of the other (potentially dependent) changes are written."
• Replies in order: the replies come back "in an array, with each response occupying the same index as the corresponding request". That is how you get the new tab's sheetId after an addSheet.
• Field masks: update requests take fields, "a comma-delimited list of fields used to indicate which fields in an object to update, while leaving all other fields unchanged".
• One request against your quota, however many changes are inside it (see Limits).

Every request type it accepts (Google's Request reference, updated 30 Sep 2026), grouped by job:

The values calls (spreadsheets.values) cover reading and writing cell contents: get, batchGet, update, batchUpdate, append, clear and batchClear, plus batchGetByDataFilter, batchUpdateByDataFilter and batchClearByDataFilter.

The ones that earn their place in automations:

• repeatCell to show state: green when done, red when failed (below).
• setDataValidation and addProtectedRange when a workflow creates a client sheet from a template.
• appendCells to add a row with its formatting in one request.
• deleteDuplicates and trimWhitespace as a clean-up step before an import.
• sortRange after a bulk write, so the view stays in order.
• insertComment to leave a note on the exact cell that failed, for a person to see.
• addSheet with the reply's sheetId to start a new monthly tab, then format it in the same flow.

Format cells from a workflow: bold, text colour, cell colour

A workflow that finishes a job can show it in the sheet: the won deal turns green and bold, a failed row turns red, an overdue invoice gets an amber cell. People see the state without reading a column.

First ask: does a rule do it? If the colour depends only on a value in the row (Status = Won, date in the past), set conditional formatting once (Format > Conditional formatting). It costs nothing per row and never fails a run. Make has modules for it (Add a Conditional Format Rule, Delete a Conditional Format Rule) and Zapier has Create Conditional Formatting Rule. Use the calls below when the format marks something the workflow did, such as "invoice sent" or "error".

Apps Script (inside the sheet, for example at the end of onSheetEdit or dueFollowUps):

// Row 5, columns A to H: bold, dark green text, light green cell
SpreadsheetApp.getActive().getSheetByName('Leads')
  .getRange(5, 1, 1, 8)
  .setFontWeight('bold')
  .setFontColor('#1E6B2E')
  .setBackground('#E6F4EA');

Sheets API (the same change from Make, n8n or Zapier): POST https://sheets.googleapis.com/v4/spreadsheets/YOUR_SHEET_ID:batchUpdate

{
  "requests": [
    { "repeatCell": {
        "range": { "sheetId": 0, "startRowIndex": 4, "endRowIndex": 5, "startColumnIndex": 0, "endColumnIndex": 8 },
        "cell": { "userEnteredFormat": {
          "backgroundColorStyle": { "rgbColor": { "red": 0.90, "green": 0.96, "blue": 0.92 } },
          "textFormat": {
            "bold": true,
            "foregroundColorStyle": { "rgbColor": { "red": 0.12, "green": 0.42, "blue": 0.18 } } } } },
        "fields": "userEnteredFormat.backgroundColorStyle,userEnteredFormat.textFormat.bold,userEnteredFormat.textFormat.foregroundColorStyle" } }
  ]
}

How to read it:

• Row 5 is startRowIndex: 4. Indexes start at 0 and the end is exclusive, so 4 to 5 is one row and columns 0 to 8 are A to H.
• Colours are 0 to 1, not 0 to 255. Divide each hex pair by 255: #1E6B2E is 30, 107, 46, which is 0.12, 0.42, 0.18.
• fields lists only what you change. Anything left out (font, borders, number format) stays as it was. Leave fields too wide and you wipe formatting you wanted to keep.
• Use the ...ColorStyle fields. Google's reference marks the older backgroundColor and foregroundColor as deprecated.
• Many rows, one call: put one repeatCell per row in the same requests list. Google counts the whole batch as one request.

In each tool

{
  "requests": [
    {
      "repeatCell": {
        "range": {
          "sheetId": 0,
          "startRowIndex": 4,
          "endRowIndex": 5,
          "startColumnIndex": 0,
          "endColumnIndex": 8
        },
        "cell": {
          "userEnteredFormat": {
            "backgroundColorStyle": {
              "rgbColor": {
                "red": 0.9,
                "green": 0.96,
                "blue": 0.92
              }
            },
            "textFormat": {
              "foregroundColorStyle": {
                "rgbColor": {
                  "red": 0.12,
                  "green": 0.42,
                  "blue": 0.18
                }
              },
              "bold": true
            }
          }
        },
        "fields": "userEnteredFormat.backgroundColorStyle,userEnteredFormat.textFormat.bold,userEnteredFormat.textFormat.foregroundColorStyle"
      }
    }
  ]
}

Recipe: colour the result

1. The level 50 script fires status_changed for a Won row.
2. Make creates the invoice and sends the email.
3. On success, a Make an API Call module turns the row bold and green and writes the invoice number.
4. An error handler on the invoice step turns the row red instead and writes the error to a Notes column.

The sheet now shows the state of every deal at a glance, and nobody has to open Make to see what failed.

Who is calling the API. In Make, n8n and Zapier you connect Google once and the API step uses that connection. If you call the API from your own server with a service account, share the sheet with it like a person (Google: "directly share individual files with the service account's email address"). Ask for the narrowest scope that works: drive.file is non-sensitive and recommended by Google; spreadsheets reaches every sheet the account can open and is a sensitive scope.

Prove it: one API step sets up a brand-new client sheet (dropdowns, frozen header, protection, sorting) the moment it is created from a template.

With AI: this is where AI saves the most time, because request bodies are long and fussy. Give it the Request types reference and say: "Write one batchUpdate body that does <list>. sheetId is <n>. Use only request types from this page and explain each index."

A sheet can stand behind a website form, a mini app or an internal tool. That is fine for a small business and hundreds of rows a day. Past that, move the data to a real database and keep the sheet as the view.

A web app that receives data. Apps Script can publish a function as a web address: doPost(e) receives a POST, doGet(e) a GET. Deploy > New deployment > Web app.

// Script Properties: SHEET_ID, TOKEN (a long random string you share with the sender)
function doPost(e) {
  const props = PropertiesService.getScriptProperties();
  const body = JSON.parse(e.postData.contents);
  if (body.token !== props.getProperty('TOKEN')) return json({ ok: false, error: 'unauthorized' });

  const lock = LockService.getScriptLock();
  lock.waitLock(10000);                       // two submissions at once never collide
  try {
    const sheet = SpreadsheetApp.openById(props.getProperty('SHEET_ID')).getSheetByName('Orders');
    const id = Utilities.getUuid();
    sheet.appendRow([id, new Date(), body.name, body.email, body.amount, 'New']);
    return json({ ok: true, id: id });
  } finally {
    lock.releaseLock();
  }
}

function json(obj) {
  return ContentService.createTextOutput(JSON.stringify(obj))
    .setMimeType(ContentService.MimeType.JSON);
}

How it behaves (Google's web apps and lock guides, read 5 Oct 2026):

• Deploy settings: "Execute as" (you, or the person visiting) and "Who has access" (only you, your domain, anyone with a Google account, or anyone). A public form needs "anyone", which is why the token check comes first.
• No custom status codes. The text output has no method for an HTTP status or headers, and the response comes back through a redirect to script.googleusercontent.com. Put success or failure in the JSON (ok: true or false), and have the sender follow redirects and read that field.
• The token travels in the body. The event object gives you the body (e.postData.contents) and the query string.
• Locks: waitLock waits and throws if it runs out of time; tryLock returns true or false so you can answer "busy, try again".
• Script Properties hold up to 9 KB per value and 500 KB per store.
• Every code change needs a new deployment version (Deploy > Manage deployments), or the web address keeps running the old code.

No-code alternative: AppSheet Core, included with Google Workspace Business, Enterprise and several Education and Frontline plans, builds an app on top of a sheet without a line of code.

Patterns that make a sheet behave like a backend

• A config tab (key and value columns) that the workflow reads on every run: on/off switch, thresholds, email templates, the AI prompt. The owner changes behaviour without touching the workflow.
• A status column as a queue: New > Processing > Done or Failed. A worker picks only New rows and sets Processing first, so two runs never take the same row.
• A log tab: every run appends time, event, row ID, result and error. Cheap, searchable and the first place to look.
• IDs everywhere: write back the CRM ID, invoice number and message ID so nothing is created twice.

Prove it: a form on your website posts to the web app, the row appears with an ID and two quick submissions both land.

With AI: the brief, plus the Web apps, Content service, Properties and Lock pages. Prompt 3 matters most here, because this code faces the internet.

An AI agent is a model that can call tools in a loop: read rows, look things up, write results. The sheet becomes the agent's task list, memory and control panel, and the place a person approves its work.

Five ways an agent uses a sheet

Where to build it

My rule: give the agent a small workflow as its tool, not the whole sheet. A sub-workflow or scenario called "write draft to row" does exactly one thing with exactly one column. You decide what the agent can touch, and every call is logged by the tool.

The pattern I use: draft, approve, send

1. Trigger: a new row (form or webhook) sets AI status = Queued.
2. Agent: reads the row, looks up the Prices and FAQ tabs, writes AI draft and AI note, then sets AI status = Needs review. It sends nothing.
3. Person: reads the draft in the sheet, edits it if needed, ticks Approved.
4. Send: the level 50 checkbox trigger fires the webhook; the workflow sends the approved text and logs it.

The agent's instructions (system prompt)

You work on one Google Sheet.
You may READ the tabs: Leads, Prices, FAQ.
You may WRITE only these columns in Leads: "AI draft", "AI note", "AI status".
For each row where AI status = Queued:
  1. Read the enquiry. Look up the matching price in Prices and any answer in FAQ.
  2. Write a reply under 120 words in "AI draft", using only facts from the sheet.
  3. In "AI note", list anything you were unsure of.
  4. Set AI status to "Needs review".
If key data is missing, set AI status to "Needs info" and say what is missing in "AI note".
Never delete rows. Never edit other columns. Never send anything.

Guardrails that matter more than the prompt

• Least access: the agent's connection can reach this one file, not your whole Drive.
• Write to draft columns only. The agent never overwrites data people typed.
• A person approves anything that leaves the building: emails, invoices, messages.
• Batch, then call the model once where you can (level 60): 50 rows in one call costs less and keeps answers consistent.
• Log every tool call to the log tab.
• A kill switch: Config!B2 = OFF, checked at the start of every run.

Prove it: 10 test enquiries get drafts with the right prices, nothing is sent until you tick Approved and turning the switch OFF stops the next run.

With AI: give the brief, the tool's agent docs and the sheet shape, then ask: "Design the agent: its tools, the exact columns it reads and writes, the system prompt and the failure cases. Then list how a bad input could make it do something I did not want."

The same automation, made safe to hand to a client or leave alone for a month.

Checklist

• No secrets in code. Webhook URLs, API keys and tokens live in Script Properties, or in the tool's credential store in Make, n8n and Zapier.

• Fires once. Every write-back carries an ID or a "sent" stamp. Re-running a day sends nothing twice.

• Fails loudly. A failed run alerts someone (Slack or email) and leaves the row in a state the next run retries. Apps Script emails failure notices for triggers (from noreply-apps-scripts-notifications@google.com), and its Executions page filters Failed and Timed out runs.

• Errors handled in the tool. Make: add error handlers (Skip, Retry, Resume, Commit, Rollback) and turn on Incomplete executions (off by default) so a failed run waits to be fixed and retried. n8n: set an error workflow that alerts you.

• Inside the quotas. You know your daily webhook calls and trigger minutes against 20,000 and 90 minutes (personal) or 100,000 and 6 hours (Workspace).

• Locks on anything two runs could touch at once (LockService).

• Versioned. Use clasp, Google's command-line tool for Apps Script (v3.4.1, released 28 Aug 2026; its README says it is "not an officially supported Google product"). It pulls the script into a folder on your computer:

  1. Turn on the Apps Script API at script.google.com/home/usersettings.
  2. Install Node.js 22, then run npm install -g @google/clasp and clasp login.
  3. Run clasp clone <script ID>, edit, then clasp push.

  Keep that folder in git. A coding agent such as Claude Code can then read, change and push the script, and every version stays recoverable.

• Owned by the right account. Triggers run as the person who installed them. If that person leaves, the triggers stop. Install them from a shared or client-owned account.

• Documented in the sheet. A README tab: what runs, when, which columns are automation-owned and who to call.

• Tested on a copy before every change.

Limits to know (Google, read 5 Oct 2026)

Sheets API calls over the limit return "429: Too many requests"; Google recommends retrying with exponential backoff. "Each batch request, including any subrequest, is counted as one API request."

Where to use what

My default: a clean sheet, the sheet fires the webhook, Make or n8n does the work, you aggregate before you act and an agent only drafts while a person approves.

Sources

Sources, all read 5 Oct 2026:

• Google Apps Script: triggers developers.google.com/apps-script/guides/triggers · installable triggers developers.google.com/apps-script/guides/triggers/installable · event objects developers.google.com/apps-script/guides/triggers/events · quotas developers.google.com/apps-script/guides/services/quotas · properties developers.google.com/apps-script/guides/properties · lock developers.google.com/apps-script/reference/lock · web apps developers.google.com/apps-script/guides/web
• Google Sheets API: batchUpdate guide developers.google.com/workspace/sheets/api/guides/batchupdate · request types developers.google.com/workspace/sheets/api/reference/rest/v4/spreadsheets/request · values guide developers.google.com/workspace/sheets/api/guides/values · usage limits developers.google.com/workspace/sheets/api/limits
• Google, other: Google Workspace Updates (Apr, Jun, Aug, 10 and 21 Sep 2026); Sheets help on the AI function and Gemini; Workspace Studio help.
• Make: Google Sheets modules apps.make.com/google-sheets-modules · operations help.make.com/operations · aggregators and pricing.
• Zapier: Google Sheets integrations, help docs and pricing.
• n8n: Google Sheets node docs.n8n.io/integrations/builtin/app-nodes/n8n-nodes-base.googlesheets/ · Aggregate docs.n8n.io/integrations/builtin/core-nodes/n8n-nodes-base.aggregate/ · HTTP Request docs.n8n.io/integrations/builtin/core-nodes/n8n-nodes-base.httprequest/ · pricing.
• Anthropic: the Claude help pages named above.