SStretchLearn
Sign inMembershipStart learning
Catalog / Tech & AI / Google Sheets Automation & Scripting
Tech & AIBeginnerPreview

Google Sheets Automation & Scripting

Turn manual, error-prone spreadsheet chores into one-click and scheduled automations using Google Sheets' built-in tools. You record macros, wire up formula pipelines, and set time-based triggers without learning to program.

Analysts, operations staff, founders, and admins who live in Google Sheets and want to cut manual work without becoming programmers.

Course content

Recording Your First Macro45m
Editing and Naming Macros for Reuse45m
When Macros Are the Wrong Tool45m
Self-Updating Reports With QUERY45m
FILTER, SORT, and Dynamic Arrays45m
Pulling Data In With ARRAYFORMULA and IMPORTRANGE45m
Dropdowns and Data Validation45m
Conditional Formatting That Reacts45m
Catching Errors and Duplicates Automatically45m

Workbook & downloads

Put the course into practice — a printable workbook plus editable templates you can fill in and reuse.

Download workbook (PDF)13 KBDownload (XLSX)8 KBDownload (XLSX)7 KBDownload (CSV)1 KB
Preview the workbook
This workbook turns the course into hands-on practice. Each section maps to one module and mixes exercises, fill-in worksheets, and checklists so you build a real library of macros, formula reports, validation rules, and scheduled triggers as you go. Work in a copy of a real spreadsheet you actually use, and by the end you will have automated at least one weekly task end to end.

Automating With the Macro Recorder

Record, name, and stress-test your first reusable macros so a repeated click sequence becomes a single keystroke.
Exercise: Record the Monday Cleanup macro
Open a copy of a spreadsheet you clean up regularly. Using Extensions, Macros, Record macro with relative references, capture your real cleanup steps, save it as Monday Cleanup, and assign Ctrl+Alt+Shift+1. Then paste a fresh export and run it.
  1. List the exact manual steps you take to clean this sheet each week, in order.
  2. Did you choose absolute or relative references, and why was that right for this task?
  3. Time yourself doing it by hand once, then by macro once. Record both durations.
  4. What broke or behaved oddly on the first replay, and how did you fix it?
Worksheet: Macro inventory and shortcut map
Fill in one row per macro you create. Use a verb-plus-object name and keep shortcuts memorable. This becomes the Automations tab you maintain in every workbook.
  • Macro name (verb + object)
  • What it does (one sentence)
  • Absolute or relative references
  • Keyboard shortcut
  • Range it touches (e.g. A1:F1000)
  • Last tested date
Checklist: Macro reliability check
  • Macro name follows the verb-plus-object convention
  • Reference mode (absolute vs relative) was chosen deliberately
  • Hard-coded ranges were widened to cover future rows
  • Macro re-runs cleanly on a second batch of test data
  • Macro is listed on the Automations tab with its shortcut

Formula-Driven Automation

Replace copy-paste reporting with self-updating QUERY, FILTER, and ARRAYFORMULA pipelines.
Exercise: Build a self-updating regional report
Using your own data or the practice dataset template, write a single QUERY that totals an amount column grouped by a category column, sorted highest first. Then add a new row to the source and confirm the report updates with no further action.
  1. Write your finished QUERY formula here exactly as it appears in the cell.
  2. Which clauses did you use: select, where, group by, order by, limit?
  3. Add a row to the source. Did the report update automatically? Note what changed.
  4. What error did you hit first, and which single change fixed it?
Worksheet: Formula automation planner
For each manual reporting task you currently do, decide which formula replaces it and capture the plan before you build.
  • Current manual task
  • How often you do it
  • Best-fit function (QUERY / FILTER / ARRAYFORMULA / UNIQUE)
  • Source range
  • Where the live result will live (tab and cell)
  • Blank-guard needed (IF cell is empty)?
Exercise: Auto-fill a column with ARRAYFORMULA
Pick a column you usually drag a formula down. Replace it with a single blank-guarded ARRAYFORMULA in row 2 so every current and future row fills automatically.
  1. Write the ARRAYFORMULA you used, including the IF blank-guard.
  2. Confirm the formula sits only in row 2 and spills downward.
  3. Add three new rows of data. Did they fill automatically with no dragging?
Checklist: Live-report readiness
  • QUERY references source columns by letter within the stated range
  • Spill area below and right of FILTER/SORT is kept empty
  • ARRAYFORMULA columns are blank-guarded so empty rows stay blank
  • Risky lookups are wrapped in IFERROR with a readable fallback
  • Report updates automatically when a source row is added

Clean Data With Validation and Rules

Stop bad data at the point of entry with dropdowns, validation rules, and reactive conditional formatting.
Worksheet: Validation rule plan
Map each input column to the validation rule that should protect it before you share the sheet with others.
  • Column name
  • Allowed values or range
  • Rule type (dropdown / number between / date / checkbox / custom formula)
  • Reject or warn on invalid input?
  • Source list location (if dropdown from range)
Exercise: Make a tracker flag itself
On a task or invoice tracker, add a custom-formula conditional formatting rule that turns a row red when it is both past due and not marked Done, plus an amber rule for due within three days.
  1. Write the custom formula you used for the red overdue rule.
  2. Write the custom formula for the amber due-soon rule.
  3. Which rule did you place first, and why does order matter here?
  4. Change a status to Done. Did the red highlight clear automatically?
Exercise: Add a quality-check column
Create a Check column that labels any problem row as Review using IF and OR, and a duplicate flag using COUNTIF. Then FILTER for Review to see only rows needing attention.
  1. Write your Check formula that returns Review for bad rows.
  2. Write the COUNTIF formula you used to flag duplicates.
  3. How many rows were flagged, and what were the three most common problems?
Checklist: Data-quality guardrails
  • Every input column has a validation rule set to reject invalid entries
  • Dropdowns exist for all category and status columns
  • Conditional formatting flags overdue and duplicate values automatically
  • A Check column surfaces problem rows behind a Review filter
  • Validation was applied before the sheet was shared with others

Scheduled and Connected Automation

Run work unattended with time-driven triggers, Forms intake, and Connected Sheets, then document the whole system.
Exercise: Schedule a recorded macro
Take a macro that refreshes or re-sorts a report and attach a daily time-driven trigger via Extensions, Apps Script, Triggers, set between 6 and 7 a.m. Test it on a copy first.
  1. Which macro did you schedule, and what does it do?
  2. What trigger type and time window did you set (Day, Week, or Minutes timer)?
  3. Confirm you tested it on a copy before live data. What did you verify?
  4. What would happen if this ran on the wrong range every night, and how did you guard against it?
Worksheet: Connect a Form for live intake
Bind a Google Form to a sheet and plan the processing that runs on each submission. Keep all calculations off the columns the Form controls.
  • Form purpose (requests / signups / feedback)
  • Response tab name
  • Helper column formula (score or classify each response)
  • Summary tab QUERY
  • On-submit trigger action (confirmation email / alert)?
Worksheet: Automation system register
Document every automation in this workbook so a colleague could maintain it without you. This is the master Automations tab.
  • Automation name
  • Type (macro / formula / trigger / import / form)
  • What it does (one line)
  • Schedule or trigger (if any)
  • Source files or IMPORTRANGE links
  • Last tested date
Checklist: Ship one automation end to end
  • Chose one painful weekly task to fully automate
  • Selected the right layer (macro, formula, validation, trigger, or import)
  • Tested the automation on a copy before trusting live data
  • Documented it on the Automations register with owner and schedule
  • Estimated the annual hours saved and noted it for review

Your Action Plan

  1. Spend two weeks logging every repetitive spreadsheet task and how often you do it.
  2. Run the four-line decision rule on each task to label it macro, formula, validation, trigger, or import.
  3. Record your top three repeated click sequences as named macros with clear shortcuts.
  4. Replace your most-rebuilt report with a single self-updating QUERY or FILTER formula.
  5. Add dropdowns and validation rules to every input column before sharing the sheet.
  6. Add conditional formatting and a Check column so problems and duplicates flag themselves.
  7. Convert your most fragile copy-down column to a blank-guarded ARRAYFORMULA.
  8. Schedule one recorded macro with a time-driven trigger after testing it on a copy.
  9. Connect a Google Form or IMPORTRANGE so at least one data source flows in automatically.
  10. Fill in the Automations register and book a quarterly review to retire stale automations.

Pairs well with

Courses members commonly take alongside this one.

Flagship CoursePreview

Freelance Business Foundations: Position, Price, Sell, and Deliver High-Value Services

Freelancing · Beginner · 16h

Build a freelance business clients understand, trust, and pay for—without vague positioning, random referrals, or underpriced custom work.

Self-pacedPreview
Client GrowthPreview

Freelance Client Acquisition: Outreach, Leads, Referrals, and Deal Flow

Freelancing · Beginner · 15h 30m

Build a repeatable acquisition system that turns targeting, outreach, referrals, and follow-up into a stable freelance opportunity pipeline.

Self-pacedPreview
Sales SystemPreview

Freelance Sales & Proposals: Discovery Calls, Scoping, Objections, and Closing

Freelancing · Intermediate · 16h

Run better discovery calls, scope work properly, write proposals clients can decide on, and close without discounting your value into the floor.

Self-pacedPreview