Google Sheets Automation with Macros and Apps Script Guide

Automating Google Sheets comes in three tiers: record a macro for repetitive clicking, write an Apps Script for anything with logic, and call external APIs when you’re stitching tools together. Most real work stops at the first two — and most people never try either, repeating by hand every week what the sheet could do on its own. This covers the two tiers that matter for everyday work: macros and Apps Script, when each one fits, how to build them, and the triggers that make them run without you in the room.

Record a macro for repetitive clicking

A macro records a series of actions and plays them back on demand. If you reformat a report the same way every Monday — bold the header, freeze the top row, color the totals — a macro captures that sequence once and repeats it with a keystroke. No code, no setup beyond pressing record.

1. Open Extensions → Macros → Record macro.
2. Choose absolute references (same cells every time) or relative (wherever you click).
3. Perform the actions you want to repeat — formatting, sorting, inserting rows.
4. Click Save, name it, and optionally assign a keyboard shortcut.

The absolute-versus-relative choice is the one that trips people up. Absolute replays on the exact cells you used while recording — right for a fixed report layout. Relative replays starting from wherever your cursor sits — right for a formatting routine you apply to different blocks. Pick wrong and the macro either edits the wrong range or refuses to adapt; when in doubt, record it both ways and keep the one that behaves. Once saved, the macro lives under Extensions → Macros and runs from there or from its shortcut, turning a two-minute ritual into a single press.

Macros have real limits worth knowing before you lean on them. They replay UI actions exactly, so if the layout changes — a column inserted, rows in a different order — an absolute macro happily formats the wrong cells. They also can’t make decisions or react to data: a macro that bolds row 1 bolds row 1 whether or not anything is there. For a fixed, predictable routine that’s a feature, not a flaw. For anything that needs to adapt to what’s in the sheet, it’s the signal to graduate to a script.

When the macro isn’t enough, edit its script

Every macro you record is actually Apps Script under the hood — Google writes the code for you. That’s the bridge between clicking and scripting: record something close to what you want, then open the code and adjust it. Go to Extensions → Macros → Manage macros, or open the editor directly with Extensions → Apps Script, and you’ll see something like this:

function formatReport() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.getRange('A1:F1').setFontWeight('bold');
  sheet.setFrozenRows(1);
}

Reading that is easier than writing it from scratch, which is the point. You can see the recorded steps as plain instructions and tweak them — change the range, add a line, adjust a color — without learning the whole language first. Editing a recorded macro is how most people learn Apps Script: you start from working code that does almost what you want and nudge it the rest of the way. The recorded macro is your template; the editor is where it grows up.

Apps Script and your first real automation

Macros only replay UI actions. Apps Script does things macros can’t: make decisions, loop over rows, pull data from another sheet, send an email. Open Extensions → Apps Script and write a function from scratch — here’s one that stamps today’s date into a cell whenever you run it:

function stampDate() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.getRange('A1').setValue(new Date());
}

Run it once from the editor and Google asks for permission — that’s normal, since the script is acting on your sheet. After you authorize it, the function runs in a fraction of a second. From here the building blocks scale up: getRange reads and writes cells, a for loop walks down rows, an if branches on a value. The same Spreadsheet service that stamps a date can clean a column, archive completed rows to another tab, or assemble a summary — anything you’d otherwise do by hand, repeated reliably. For pure cell math that doesn’t need scripting, an array formula is often simpler; reach for Apps Script when the task needs logic or touches things outside the grid.

A concrete example shows the leap from macro to script. Say every finished row should move to an Archive tab. A macro can’t decide which rows are finished; a script can — loop the rows, check the status column, copy the matching ones to Archive, and clear them from the active sheet. That’s a dozen lines of Apps Script and a job no macro could record, because it depends on reading each row and deciding. The moment your automation contains an “if,” you’ve crossed from macro territory into scripting.

Custom functions you can call from a cell

A custom function is Apps Script you call like a built-in — type it into a cell with an equals sign and it returns a value. Define it once and it’s available across the whole spreadsheet:

function GST(amount) {
  return amount * 0.1;
}

Now =GST(B2) in any cell returns 10% of B2, exactly as if it were a native function. Custom functions are perfect for business logic you repeat — a tax rate, a commission tier, a unit conversion specific to your work — that no built-in covers. They keep the rule in one place: change the function and every cell using it updates. The limit is worth knowing up front: custom functions can read values passed to them and return a result, but they can’t modify other cells, send email, or call most Google services. Those jobs belong to a regular Apps Script function triggered another way, not to a custom function living in a cell.

One pattern bridges scripts and everyday use: a custom menu. An onOpen function adds your own menu to the toolbar, so the people using the sheet run your automations from a familiar dropdown instead of the script editor. It’s the difference between a script only you can run and a button the whole team can press, and it’s a handful of lines that make an automation feel like a built-in feature rather than a developer tool sitting off to the side.

Triggers: automation that runs without you

A script you have to open and run by hand is only half-automated. Triggers close the gap by running a function on an event — when the sheet is edited, on a schedule, or when a form is submitted. The most useful is onEdit, a simple trigger that fires every time someone changes a cell:

function onEdit(e) {
  var range = e.range;
  if (range.getColumn() == 3) {
    range.offset(0, 1).setValue(new Date());
  }
}

This stamps a timestamp in the next column whenever column 3 is edited — an automatic “last updated” log with no button to press. For scheduled work, install a time-driven trigger from the editor’s clock icon: run a cleanup every night, send a digest every Monday, archive old rows monthly.

Time-driven triggers are the other workhorse. From the editor, click the clock icon, add a trigger, and choose a function plus a schedule — every night, every Monday, the first of each month. A nightly trigger that archives completed rows or emails a summary turns a manual chore into something that happens while you sleep. Unlike onEdit, time triggers can do the heavier work — send mail, call services, rewrite large ranges — because they aren’t firing on every keystroke and don’t have to finish in an instant.

Macros or Apps Script — which to reach for

The two tiers overlap, but the choice is usually clear once you name the task.

Start with a macro to kill the worst repetitive clicking, graduate to Apps Script the first time you think “if this cell says X, then do Y,” and add a trigger once you’re tired of running the script by hand. If a no-code add-on already does the job, use it — automation you don’t have to maintain is the best kind. Google’s own macros and Apps Script documentation goes deeper on the Spreadsheet service when you outgrow the basics.

Where to begin

Pick the single task you repeat most this week and automate just that one. Record it as a macro; if it needs a decision, open the script and add it; if you’re tired of running it, attach a trigger. Each step builds on the last, and none requires learning the whole of Apps Script before it pays off. The goal isn’t to automate everything — it’s to stop spending Monday mornings on work the sheet was always capable of doing itself.