How to automate Google Sheets with Apps Script (beginner’s guide)

Google Sheets is where most businesses actually live — invoices, stock, leads, payroll tabs nobody admits exist. Apps Script turns that spreadsheet into a programmable system. Here's the honest beginner path: three scripts that cover 80% of what clients ask me for, written so you can ship them today.

Open the editor (30 seconds)

In any Google Sheet: Extensions → Apps Script. That's it — you're in a cloud IDE bound to this spreadsheet. Scripts save to your Drive and run on Google's servers for free.

Script 1 — a custom function

Like =SUM(), but yours. Pull the current status of a project by matching its name anywhere in a sheet:

/**
 * Finds a project's status from the Projects sheet.
 * @param {string} name Project name to look up.
 * @return {string} The status.
 * @customfunction
 */
function PROJECT_STATUS(name) {
  const sheet = SpreadsheetApp.getActive().getSheetByName('Projects');
  const values = sheet.getDataRange().getValues();
  const row = values.find((r) => r[0] === name);
  return row ? row[2] : 'Not found';
}

Type =PROJECT_STATUS("Website revamp") in any cell. It recalculates like a native formula.

Script 2 — a custom menu

Give non-technical teammates a button instead of code:

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('⚡ Automation')
    .addItem('Send status report', 'sendReport')
    .addToUi();
}

function sendReport() {
  const data = SpreadsheetApp.getActiveSheet().getDataRange().getValues();
  const summary = data.slice(1).map((r) => r[0] + ': ' + r[2]).join('
');
  GmailApp.sendEmail('[email protected]', 'Weekly status', summary);
}

Reload the spreadsheet and the menu appears. Zero installations for anyone on the team.

Script 3 — a time trigger (the one that saves real hours)

Every Friday at 5pm, run the report without a human touching anything. In the Apps Script editor: Triggers → Add Trigger → sendReport → Time-driven → Weekly timer. That's the whole setup.

The gotchas that bite beginners

Quotas: free accounts get ~20 minutes of runtime per day and ~100 emails; Workspace gets far more. Design loops to be efficient and you'll never notice.
getValues() is your friend: reading cells one by one is 100× slower than reading the whole range once.
Test with small data first: a script that works on 10 rows can time out on 10,000. Batch, then batch again.

Rule of thumb: if you do it in a spreadsheet twice a week, a trigger can do it forever. That's usually a one-evening build — or a one-week fixed-price project if it touches multiple systems.

Next steps: wire the output into email with these Gmail automations, or see what a full pipeline looks like in the invoice automation walkthrough.