Google Sheets Apps Script: Automate Tasks in 5 Steps

You've probably found yourself doing the same thing in Google Sheets every Monday morning. You open the spreadsheet, copy last week's data, reformat the columns, highlight the rows over a certain threshold, and email a summary to your manager. It takes 20 minutes. Every. Single. Week.

Here's the thing: Google Sheets has a built-in scripting language — Google Apps Script — that can handle all of that automatically, on a schedule, without you lifting a finger. And unlike Excel VBA, which feels like it was designed in 1998, Apps Script uses JavaScript syntax that's approachable for anyone with basic coding experience (and manageable even without it).

In this guide, you'll learn how to automate tasks in Google Sheets using Apps Script in 5 concrete steps. By the end, you'll have a working automation that formats data, sends email alerts, includes a custom menu, and generates a weekly report — all triggered automatically.

What Is Google Apps Script?

Google Apps Script is a cloud-based scripting platform built on JavaScript that lets you automate and extend Google Workspace products: Sheets, Docs, Gmail, Calendar, Drive, and more. You write scripts directly in the browser — no installs, no compiling, no local environment to configure.

Every Google Sheet has its own attached script project. You can also create standalone scripts that run independently of any specific file. For most spreadsheet automation, you'll work with the attached (bound) script.

What can Apps Script actually do?

• Auto-format cells based on values (better than conditional formatting for complex logic)
• Send emails when data changes — alerts, summaries, notifications
• Create custom menu items that trigger your automation on demand
• Build scheduled automations that run daily, weekly, or on any timetable
• Pull data from external APIs and write it directly into your sheet
• Generate reports by aggregating data across multiple sheets or files

The learning curve is real but shallow. Let's go step by step.

Step 1: Opening the Script Editor

Every Google Sheet has an Apps Script editor built in. Here's how to access it:

1. Open any Google Sheet
2. Click Extensions in the top menu bar
3. Select Apps Script

A new browser tab opens with the Apps Script IDE. You'll see a default function skeleton:

1function myFunction() {
2  
3}

This is your blank canvas. The left sidebar shows your project files. The top bar has Run, Debug, and Save buttons. The dropdown next to Run lets you select which function to execute.

Saving your work: Apps Script auto-saves, but it's good practice to click the floppy disk icon (or Ctrl+S) before running anything. Scripts run in Google's cloud — not on your local machine — so there's nothing to "install."

Before you write anything more complex, run the default empty function once to verify your editor and sheet permissions are connected:

1. Click the Run button
2. When prompted, click Review permissions → Allow

You'll be granting the script permission to access your spreadsheet. This is normal and required for any script that reads or writes sheet data.

Step 2: Writing Your First Automation — Auto-Format on Edit

The most immediately useful thing you can build is a script that automatically formats rows when data is entered. No manual highlighting, no forgetting which colour means what — the sheet handles it.

Here's a script that highlights any row in column C where the value exceeds 1,000 in red, and clears formatting if the value drops below that threshold:

1function onEdit(e) {
2  // onEdit is a special trigger that fires whenever a cell is edited
3  const sheet = e.source.getActiveSheet();
4  const range = e.range;
5  const editedColumn = range.getColumn();
6  const editedRow    = range.getRow();
7
8  // Only apply logic to column C (column index 3), skip the header row
9  if (editedColumn !== 3 || editedRow === 1) return;
10
11  const value = range.getValue();
12  const rowRange = sheet.getRange(editedRow, 1, 1, sheet.getLastColumn());
13
14  if (typeof value === "number" && value > 1000) {
15    // Highlight the entire row red if value exceeds 1,000
16    rowRange.setBackground("#f4c7c3");
17  } else {
18    // Clear background if value is below threshold
19    rowRange.setBackground(null);
20  }
21}

How to use it:

1. Paste this code into your script editor, replacing the default myFunction
2. Save the script
3. Go back to your sheet and type a number greater than 1,000 in any cell in column C

The row should immediately highlight red. Type a smaller number — the colour clears. No button press required. The onEdit function is a simple trigger that Google runs automatically whenever any cell in the sheet is edited.

Understanding onEdit vs. onChange

onEdit fires when a user manually edits a cell value. It does not fire when a formula recalculates. For those scenarios, use onChange instead, which fires on any structural change to the sheet.

Step 3: Sending Email Alerts When a Cell Changes

Let's take it up a level. You want to receive an email notification when a specific cell — say, a budget total in cell D2 — exceeds a threshold. This is a common requirement for financial tracking sheets, KPI dashboards, and inventory alerts.

1function onEdit(e) {
2  const sheet     = e.source.getActiveSheet();
3  const range     = e.range;
4  const alertCell = "D2";
5  const threshold = 50000;
6
7  // Only trigger if the edited cell is D2
8  if (range.getA1Notation() !== alertCell) return;
9
10  const currentValue = range.getValue();
11
12  if (typeof currentValue === "number" && currentValue > threshold) {
13    sendBudgetAlert(currentValue, threshold);
14  }
15}
16
17function sendBudgetAlert(value, threshold) {
18  const recipient  = "your.email@example.com"; // Replace with your email
19  const subject    = "⚠️ Budget Alert: Threshold Exceeded";
20  const body       = `Your budget tracker has exceeded the threshold.\n\n` +
21                     `Current value: $${value.toLocaleString()}\n` +
22                     `Threshold: $${threshold.toLocaleString()}\n\n` +
23                     `Please review your spreadsheet.`;
24
25  GmailApp.sendEmail(recipient, subject, body);
26  Logger.log(`Alert sent. Value: ${value}`);
27}

Important: The first time this script calls GmailApp.sendEmail, Google will prompt you for additional Gmail permissions. Click through the permission flow and allow it.

To test the email alert without waiting for an actual edit:

1. Select sendBudgetAlert from the function dropdown at the top of the editor
2. Click Run
3. Check your inbox — the alert email should arrive within seconds

Pro tip: To avoid flooding yourself with emails, add a flag that records when the last alert was sent and skips sending if it was less than an hour ago. Store the timestamp in PropertiesService.getScriptProperties().

Step 4: Building a Custom Menu

Simple triggers like onEdit are powerful, but some automations shouldn't run automatically on every keystroke — they should run on demand, when you choose. Custom menus let you add your own buttons to the Google Sheets menu bar.

Here's how to add a custom "Reports" menu with two options:

1function onOpen() {
2  // onOpen fires automatically when the spreadsheet is opened
3  const ui = SpreadsheetApp.getUi();
4
5  ui.createMenu("📊 Reports")
6    .addItem("Generate Weekly Report", "generateWeeklyReport")
7    .addSeparator()
8    .addItem("Send Summary Email", "sendWeeklySummaryEmail")
9    .addToUi();
10}
11
12function generateWeeklyReport() {
13  SpreadsheetApp.getUi().alert("Generating weekly report... (add your logic here)");
14}
15
16function sendWeeklySummaryEmail() {
17  SpreadsheetApp.getUi().alert("Sending summary email... (add your logic here)");
18}

Save the script and reload your Google Sheet. You'll see a new "📊 Reports" menu appear in the menu bar, next to Help. Clicking either item calls the corresponding function.

The onOpen trigger runs automatically whenever the spreadsheet is opened — you don't need to do anything special to activate it. This is where you always put your menu-building code.

Step 5: Scheduling Scripts with Time-Based Triggers

Here's where Apps Script gets genuinely powerful: time-based triggers let you schedule any function to run automatically — every hour, daily at a specific time, weekly on Monday mornings, monthly, or on any recurring schedule.

You can set triggers two ways: through the UI, or programmatically.

Setting Triggers via the UI

1. In the Apps Script editor, click the clock icon (Triggers) in the left sidebar
2. Click + Add Trigger in the bottom right
3. Choose the function to run (e.g., generateWeeklyReport)
4. Set event source to Time-driven
5. Choose frequency: Week timer → Every Monday → 8am–9am
6. Click Save

That's it. Your function will now run automatically every Monday between 8am and 9am, regardless of whether anyone has the spreadsheet open.

Setting Triggers Programmatically

For portability (so the trigger follows the script when you copy the sheet), you can create triggers in code:

1function createWeeklyTrigger() {
2  // Delete existing triggers first to avoid duplicates
3  const triggers = ScriptApp.getProjectTriggers();
4  triggers.forEach(trigger => ScriptApp.deleteTrigger(trigger));
5
6  // Create a new weekly trigger: every Monday at 8am
7  ScriptApp.newTrigger("generateWeeklyReport")
8    .timeBased()
9    .onWeekDay(ScriptApp.WeekDay.MONDAY)
10    .atHour(8)
11    .create();
12
13  Logger.log("Weekly trigger created successfully.");
14}

Run createWeeklyTrigger once, and the automation is set. You can verify it appeared in the Triggers panel.

Real-World Example: Auto-Populating a Weekly Report

Let's put it all together with a practical example: a script that pulls the last 7 days of data from a "Raw Data" sheet, summarises it (total, average, count), and writes the results to a "Weekly Report" sheet automatically every Monday.

1function generateWeeklyReport() {
2  const ss          = SpreadsheetApp.getActiveSpreadsheet();
3  const rawSheet    = ss.getSheetByName("Raw Data");
4  const reportSheet = ss.getSheetByName("Weekly Report");
5
6  if (!rawSheet || !reportSheet) {
7    Logger.log("Required sheets not found. Check sheet names.");
8    return;
9  }
10
11  // Get all data from Raw Data sheet (assumes headers in row 1)
12  const data      = rawSheet.getDataRange().getValues();
13  const headers   = data[0];
14  const rows      = data.slice(1);
15
16  // Filter rows from the last 7 days (assumes dates in column A)
17  const oneWeekAgo = new Date();
18  oneWeekAgo.setDate(oneWeekAgo.getDate() - 7);
19
20  const recentRows = rows.filter(row => {
21    const rowDate = new Date(row[0]);
22    return rowDate >= oneWeekAgo;
23  });
24
25  // Calculate summary stats for column B (numeric values)
26  const values  = recentRows.map(row => Number(row[1])).filter(v => !isNaN(v));
27  const total   = values.reduce((sum, v) => sum + v, 0);
28  const average = values.length > 0 ? total / values.length : 0;
29  const count   = values.length;
30
31  // Write report to Weekly Report sheet
32  const reportDate = new Date().toLocaleDateString("en-US", {
33    weekday: "long", year: "numeric", month: "long", day: "numeric"
34  });
35
36  reportSheet.clearContents();
37  reportSheet.getRange("A1").setValue("Weekly Summary Report");
38  reportSheet.getRange("A2").setValue(`Generated: ${reportDate}`);
39  reportSheet.getRange("A4:B4").setValues([["Metric", "Value"]]);
40  reportSheet.getRange("A5:B7").setValues([
41    ["Total",   total],
42    ["Average", average.toFixed(2)],
43    ["Count",   count]
44  ]);
45
46  // Bold the headers
47  reportSheet.getRange("A1").setFontWeight("bold").setFontSize(14);
48  reportSheet.getRange("A4:B4").setFontWeight("bold");
49
50  Logger.log(`Report generated: Total=${total}, Average=${average.toFixed(2)}, Count=${count}`);
51}

Set this function on a weekly Monday trigger, and your report writes itself. Add a sendWeeklySummaryEmail call at the end to email the summary automatically — no human in the loop required.

Common Mistakes to Avoid

Not handling missing sheets gracefully. Always check that getSheetByName() returned a valid sheet before calling methods on it — otherwise you'll get cryptic Cannot read property of null errors.

Creating duplicate triggers. If you run createWeeklyTrigger multiple times without deleting existing triggers first, you'll end up with the function running 5 times every Monday. Always clean up existing triggers before creating new ones.

Exceeding execution time limits. Apps Script has a 6-minute execution limit for most scripts (30 minutes for Workspace Business/Enterprise). For long-running operations, break your work into smaller batches and chain executions using triggers.

Hardcoding email addresses. Store recipient emails in a dedicated config sheet or in PropertiesService rather than hardcoding them in the script. This makes it easy for non-developers to update without touching code.

Conclusion

Google Apps Script turns Google Sheets from a passive data container into an active, automated system. With the five steps covered here — opening the editor, writing your first onEdit script, setting up email alerts, building a custom menu, and scheduling with time triggers — you have everything you need to eliminate the most repetitive parts of your spreadsheet workflow.

Start small: pick one Monday-morning task you do manually every week and automate it this weekend. Once you've seen the trigger fire on its own and the report populate itself, you'll wonder what else you've been doing manually that doesn't need to be.

Frequently Asked Questions

Do I need to know JavaScript to use Google Apps Script? Basic JavaScript knowledge helps, but it's not strictly required for simple automations. The code examples in this article are heavily commented and designed to be modified without deep programming experience. Copy, paste, update the sheet names and email addresses, and most scripts will work out of the box.

Is Google Apps Script free? Yes — Apps Script is included with every Google account, including free Gmail accounts. There are daily quotas (e.g., 100 emails/day on free accounts, 1,500 on Workspace accounts), but for personal and small-team use, you'll rarely hit them.

What's the difference between Google Apps Script and a macro in Google Sheets? When you record a macro in Google Sheets (Extensions → Macros → Record Macro), Google actually generates Apps Script code behind the scenes. The difference is flexibility: recorded macros capture a fixed sequence of clicks, while hand-written Apps Script can include logic, conditions, loops, API calls, and triggers that macros can't.

Can Apps Script access data from other Google services like Gmail or Calendar? Absolutely. Apps Script has built-in services for Gmail (GmailApp), Calendar (CalendarApp), Drive (DriveApp), Docs (DocumentApp), and more. You can build automations that span across Workspace — for example, creating a Calendar event whenever a specific row is added to a Sheet.

Related articles: Excel Dynamic Dashboard Without VBA, Excel Power Query: Eliminate Manual Data Cleaning