Google Apps Script: Automate Google Sheets in 2026

If you're still manually copying data between spreadsheets, sending the same weekly summary email, or hunting through rows to timestamp when records were added — you're leaving serious time on the table. Google Apps Script automation solves all of this, and it's completely free inside every Google account.

Unlike Zapier or Make (which charge per task), Apps Script is a full JavaScript runtime baked directly into Google Workspace. It connects Sheets, Docs, Gmail, Calendar, Drive, and Forms in a single scripting environment — no API keys, no OAuth headaches, no monthly bill. In this tutorial you'll go from zero to three fully working automations, plus time-based triggers and a debugging workflow you can apply to any script you write.

What Is Google Apps Script — and Why Should You Use It?

Google Apps Script is a cloud-based JavaScript platform hosted by Google. Every script runs on Google's servers, which means:

• No local setup — no Node, no Python, no IDE to install.
• Always-on execution — triggers fire even when your laptop is closed.
• Native Workspace access — SpreadsheetApp, GmailApp, DriveApp, and DocumentApp are built-in global objects; no imports required.
• Generous free quotas — 6 minutes of script runtime per execution, 90 minutes per day (consumer), 6 hours per day (Workspace). More than enough for most business workflows.

The trade-off is that Apps Script uses a slightly older V8 runtime and has some quirks around asynchronous code (most operations are synchronous by default). Once you know those guardrails, it becomes one of the fastest automation tools available for Google-centric teams.

Opening the Script Editor

There are two ways to open the Apps Script editor:

1. From a Google Sheet: go to Extensions → Apps Script. The script is then bound to that spreadsheet and can access it directly via SpreadsheetApp.getActiveSpreadsheet().
2. Standalone: navigate to script.google.com and click New project. Standalone scripts work with any Google service but require you to pass Spreadsheet IDs explicitly.

For all three tutorials below, use the bound approach — open the Sheet you want to automate, then go to Extensions → Apps Script.

When the editor opens, you'll see a default myFunction() stub. Delete it; we're replacing it with real code.

Tutorial 1: Auto-Send a Weekly Summary Email from Google Sheets

The scenario: You maintain a project-tracking sheet with task names, owners, and statuses. Every Monday morning you want an HTML email summarising all open tasks — without touching the sheet yourself.

The Code

1function sendWeeklySummary() {
2  const ss = SpreadsheetApp.getActiveSpreadsheet();
3  const sheet = ss.getSheetByName("Tasks"); // ← change to your sheet tab name
4  const data = sheet.getDataRange().getValues();
5
6  const headers = data[0];
7  const rows = data.slice(1);
8
9  // Filter to rows where Status column (index 2) is NOT "Done"
10  const openTasks = rows.filter(row => row[2] !== "Done" && row[0] !== "");
11
12  if (openTasks.length === 0) {
13    Logger.log("No open tasks — email not sent.");
14    return;
15  }
16
17  // Build HTML table
18  let tableRows = openTasks.map(row => `
19    <tr>
20      <td style="padding:6px 12px;border:1px solid #ddd;">${row[0]}</td>
21      <td style="padding:6px 12px;border:1px solid #ddd;">${row[1]}</td>
22      <td style="padding:6px 12px;border:1px solid #ddd;color:#e65c00;">${row[2]}</td>
23    </tr>`).join("");
24
25  const htmlBody = `
26    <h2 style="font-family:sans-serif;">📋 Weekly Task Summary</h2>
27    <table style="border-collapse:collapse;font-family:sans-serif;font-size:14px;">
28      <thead>
29        <tr style="background:#4a90d9;color:#fff;">
30          <th style="padding:8px 12px;">Task</th>
31          <th style="padding:8px 12px;">Owner</th>
32          <th style="padding:8px 12px;">Status</th>
33        </tr>
34      </thead>
35      <tbody>${tableRows}</tbody>
36    </table>
37    <p style="font-family:sans-serif;color:#888;font-size:12px;">
38      Sent automatically by Google Apps Script — ${new Date().toDateString()}
39    </p>`;
40
41  GmailApp.sendEmail(
42    Session.getActiveUser().getEmail(), // sends to yourself; replace with a recipient
43    `Open Tasks as of ${new Date().toDateString()}`,
44    "Your email client doesn't support HTML.",
45    { htmlBody }
46  );
47
48  Logger.log(`Summary sent — ${openTasks.length} open tasks.`);
49}

How It Works

• getDataRange().getValues() pulls the entire sheet into a 2D array in one fast call — always preferred over row-by-row reads.
• The .filter() skips completed tasks and blank rows, so the email stays concise.
• GmailApp.sendEmail() accepts an htmlBody option; the plain-text fallback keeps it RFC-compliant.
• Session.getActiveUser().getEmail() dynamically resolves the script runner's address, so you don't hard-code it.

Run it once manually (▶ Run button) to verify the email lands in your inbox, then set up the trigger in the section below.

Tutorial 2: Auto-Timestamp Rows When Data Is Added (onEdit Trigger)

The scenario: Your team logs expense submissions in a Sheet. You need to record exactly when each row was entered — without relying on anyone to type the date themselves.

The Code

1function onEdit(e) {
2  const sheet = e.source.getActiveSheet();
3
4  // Only run on the "Expenses" tab
5  if (sheet.getName() !== "Expenses") return;
6
7  const editedRow = e.range.getRow();
8  const editedCol = e.range.getColumn();
9  const timestampCol = 6; // Column F — adjust to suit your layout
10  const dataStartRow = 2; // Row 1 is the header
11
12  // Trigger only when editing columns A–E (the data columns)
13  if (editedRow < dataStartRow || editedCol >= timestampCol) return;
14
15  // Only stamp if column A (the key field) in this row is non-empty
16  const keyCell = sheet.getRange(editedRow, 1).getValue();
17  if (keyCell === "") return;
18
19  // Write timestamp only if the cell is currently empty (don't overwrite)
20  const timestampCell = sheet.getRange(editedRow, timestampCol);
21  if (timestampCell.getValue() === "") {
22    timestampCell.setValue(new Date());
23    timestampCell.setNumberFormat("yyyy-MM-dd HH:mm:ss");
24  }
25}

Key Points

• onEdit(e) is a simple trigger — it fires automatically on every cell edit without any additional setup. No trigger configuration needed.
• The event object e provides e.range (the edited cell), e.source (the Spreadsheet), and e.value (the new value). Always guard against it being undefined on programmatic edits.
• The guard clauses (return early) are critical — without them the function runs on every sheet edit, including your timestamp column itself, causing infinite loops.
• setNumberFormat() ensures the date displays as a readable string rather than a raw serial number.

Note: Simple triggers like onEdit cannot send emails or access external services. For that, you need an installable trigger — see the Triggers section below.

Tutorial 3: Import Data from One Sheet to Another Automatically

The scenario: A "Raw Data" tab receives new rows from a Google Form. You want a "Dashboard" tab to automatically pull only the rows where the region is "North America" — a live filtered view.

The Code

1function syncNorthAmericaRows() {
2  const ss = SpreadsheetApp.getActiveSpreadsheet();
3  const source = ss.getSheetByName("Raw Data");
4  const dest = ss.getSheetByName("Dashboard");
5
6  if (!source || !dest) {
7    Logger.log("Sheet not found — check tab names.");
8    return;
9  }
10
11  const sourceData = source.getDataRange().getValues();
12  const headers = sourceData[0];
13  const regionColIndex = headers.indexOf("Region"); // dynamic column lookup
14
15  if (regionColIndex === -1) {
16    Logger.log("'Region' column not found in headers.");
17    return;
18  }
19
20  const filtered = sourceData.filter((row, i) =>
21    i === 0 || row[regionColIndex] === "North America"
22  );
23
24  // Clear existing destination data and rewrite
25  dest.clearContents();
26  dest.getRange(1, 1, filtered.length, filtered[0].length).setValues(filtered);
27
28  Logger.log(`Synced ${filtered.length - 1} rows to Dashboard.`);
29}

Why clearContents() + setValues() Instead of Appending?

Appending rows works for additive logs, but when your source data can change (edits, deletions), a full clear-and-rewrite guarantees the destination always mirrors the current filtered state. For very large datasets (10,000+ rows), consider writing only changed rows using a hash comparison — but for most business sheets, this approach is fast enough.

Setting Up Time-Based Triggers

The weekly email from Tutorial 1 and the data sync from Tutorial 3 need to run on a schedule. Here's how to configure that:

1. In the Apps Script editor, click the clock icon (Triggers) in the left sidebar.
2. Click + Add Trigger (bottom right).
3. Configure:
   • Function: sendWeeklySummary (or syncNorthAmericaRows)
   • Event source: Time-driven
   • Time-based trigger type: Week timer → Every Monday → 8am–9am
4. Click Save and authorise the script when prompted.

For the data sync, a Day timer running every hour is usually sufficient. You can also use a Minute timer (every 5 or 15 minutes) for near-real-time updates — but be mindful of daily quota limits if the sheet is large.

Installable triggers (set up via the Triggers UI) run as you, so they have permission to send emails and call external services. Simple triggers (onEdit, onOpen) have no such permissions.

Debugging Tips and Common Errors

Use Logger.log() liberally. View output via View → Logs or the newer Execution log pane. It's your console.

Common errors and fixes:

The golden performance rule: never call getValue() or setValue() inside a loop. Read the entire range at once with getValues(), process the array in memory, then write it back in a single setValues() call. This single change can turn a 5-minute script into a 5-second one.

Practical Use Cases

Once you're comfortable with the three tutorials above, Apps Script opens up a wide range of automation across teams:

• Expense tracking: Auto-timestamp submissions, flag rows over budget threshold, email the finance team a daily digest.
• Project management: Sync task statuses from a master Sheet to individual team Sheets; send Slack messages via webhook when a task moves to "Blocked".
• Automated reporting: Pull data from multiple Sheets, generate a formatted Google Doc report, and email it to stakeholders every Friday.
• Form response routing: When a Google Form is submitted, read the response, look up the right owner in a reference table, and send them a personalised email with next steps.
• CRM lite: Log every Gmail thread with a specific label to a Sheets CRM, capturing sender, date, and subject automatically.

Conclusion

Google Apps Script automation turns Google Workspace from a passive document storage layer into an active workflow engine — and it costs nothing beyond your existing Google account. The three tutorials in this guide cover the most common patterns: scheduled email summaries, event-driven timestamping, and automatic data syncing. Stack them together and you have the backbone of a functional business automation system without writing a single line of server-side infrastructure.

Start with Tutorial 2 (the onEdit timestamp) since it requires no trigger setup and gives you instant feedback. Once that's running, add the weekly email trigger and you'll have two live automations before the end of the hour.

Frequently Asked Questions

Does Google Apps Script work with the free version of Google (Gmail/Drive)? Yes. Apps Script is available on all Google accounts, including free consumer accounts. The daily execution quota is slightly lower (90 minutes/day) compared to paid Google Workspace plans (6 hours/day), but it's more than sufficient for typical personal or small-team automations.

Can I call external APIs from Apps Script? Absolutely. Use the built-in UrlFetchApp.fetch(url, options) method to make HTTP requests to any REST API — Slack, Notion, Airtable, or your own endpoints. You can pass headers, JSON bodies, and handle responses exactly as you would with the fetch API in a browser, with the exception that it's synchronous by default.

How is Google Apps Script different from Zapier or Make? Zapier and Make are no-code/low-code tools with visual editors and pre-built connectors. Apps Script is a coding environment — more flexible and free, but requires JavaScript knowledge. For straightforward linear workflows between popular apps, Zapier is faster to set up. For anything that involves conditional logic, data transformation, or native Google Workspace integration, Apps Script is more powerful and significantly cheaper at scale.

Related articles: Zapier Gmail to Google Sheets Automation, Power Automate Email Approval Workflow, Automate Weekly Reports with Python