Google Apps Script Tutorial: Automate Google Sheets Fast

One free cloud editor wired into every Workspace app: the foundation this entire 48-part series builds on.

This Google Apps Script tutorial takes you from zero to your first working Google Sheets automation — real scripts that write data, apply formatting, calculate with your own custom functions, and run on a schedule while you sleep. Google Apps Script is Google's free, cloud-based JavaScript platform, and it comes already wired into Sheets, Gmail, Drive, Docs, and Calendar. There is nothing to install, nothing to host, and nothing to pay.

If your week still contains the same three moves — copying data between tabs, reformatting the same columns every Monday, and typing out the same status email — you are doing work a ten-line script can own. The investment is small. One focused hour with this guide leaves you with three working automations, an expense ledger that builds itself, and a clear map for the rest of the journey.

I'm Mostafa Amaan, Senior IT Officer with over 16 years of experience running enterprise infrastructure — medical imaging systems, multi-branch networks, Windows and Linux servers, and e-commerce operations — and I've used Google Apps Script for years to eliminate repetitive tasks across Google Workspace. The first time a scheduled script delivered a full report without me touching a key, my weekly reporting ritual shrank to a five-minute review. This article is the anchor for the whole series; every later part links back to it.

Quick Answer: What Is Google Apps Script?

That is the short version. The rest of this guide shows you what that sentence looks like in practice: where the editor lives, three copy-ready scripts you can run today, when native spreadsheet features beat code, and how the remaining 47 parts turn these basics into a complete Workspace automation system.

What Is Google Apps Script? Show Me First, Then Explain

Picture the Monday morning it is built for. Expense receipts sit in an inbox, a ledger tab needs the same five columns formatted the same way for the ninth week running, and a manager is waiting for a category summary. In the native workflow, that is three apps, twenty minutes, and two chances to paste over the wrong row. With Apps Script, it is one function — build the ledger, total the categories, email the summary — that finishes before you finish your coffee.

Under the hood, Google Apps Script is a rapid application development platform built on modern JavaScript (the V8 runtime, the same engine family behind Chrome and Node.js). Your scripts live in your Google Drive and execute on Google's own servers, with built-in services for every Workspace product. According to Google's official Apps Script overview, that means you can:

• Read, write, and reformat data in Google Sheets automatically — the core of this article.
• Send personalized Gmail messages triggered by spreadsheet content or schedules.
• Generate Google Docs and Slides from templates — on demand or on a timer.
• Schedule scripts with triggers — daily, hourly, or on edit events.
• Add custom menus, dialogs, and sidebars to Sheets and Docs.
• Pull external API data straight into a spreadsheet, or publish scripts as web apps.

Two details trip up newcomers, so let's clear them now. First, scripts come in two flavors: bound scripts (attached to one spreadsheet, like ours today) and standalone scripts (free-floating projects that talk to many files — the web apps of Stage 6). Second, if you have used visual no-code tools, you already know this territory: I compared Apps Script with workflow platforms in the n8n automation guide, and the honest summary is that Apps Script wins on price, depth of Workspace access, and freedom to customize — at the cost of writing code instead of drawing flows.

How to Open the Editor and Run Your First Script (5 Steps)

The editor is not a download — it lives inside Google Sheets. Follow these five steps exactly and you will have running code within ten minutes, even if you have never written a line of JavaScript.

Step 1 — Open the script editor. Create a new Google Sheet (this will be our series expense workbook), then choose Extensions → Apps Script from the menu bar. The editor opens in a new browser tab, already bound to this exact spreadsheet.

Extensions → Apps Script: the entire development environment is already part of Google Sheets — no install, no setup.

Step 2 — Name the project and meet Code.gs. Click "Untitled project" at the top and name it something you will recognize in a year, like Expense Automation — Series. The editor gives you one file named Code.gs, where our JavaScript lives. Google also now embeds a Gemini assistant panel in the IDE for explaining and generating code — a genuinely useful autocomplete partner, though Part 003 of this series will give the full IDE tour.

Step 3 — Paste the first script. Delete the placeholder contents of Code.gs and paste this complete, commented script. It builds the series' expense ledger from scratch: header, sample rows, currency formatting, a "With VAT" column that uses the custom function you will add next, and a frozen header row.

/**
 * Builds the series expense ledger from scratch.
 * Run manually from the editor for now — a custom menu
 * arrives in Part 007 of the series.
 */
function setupExpenseLedger() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  let sheet = ss.getSheetByName('Expenses') || ss.insertSheet('Expenses');

  // Clear any previous content so the script is truly idempotent
  sheet.clearContents();

  // 1) Header row: bold white text on the brand-green background
  sheet.getRange('A1:F1')
       .setValues([['Date', 'Description', 'Category', 'Amount (USD)', 'Payment Method', 'With VAT']])
       .setFontWeight('bold')
       .setBackground('#13b981')
       .setFontColor('#ffffff');

  // 2) Sample expense rows — written in ONE batch call
  sheet.getRange('A2:E6').setValues([
    [new Date(), 'Team lunch — client visit', 'Meals',    48.50,  'Company Card'],
    [new Date(), 'Taxi — airport transfer',   'Travel',   32.00,  'Cash'],
    [new Date(), 'Software subscription',     'Software', 120.00, 'Company Card'],
    [new Date(), 'Office supplies',           'Supplies', 26.75,  'Cash'],
    [new Date(), 'Conference ticket',         'Training', 250.00, 'Company Card']
  ]);

  // 3) VAT column — uses the EXPENSEVAT custom function from this project
  sheet.getRange('F2:F6').setFormulas([
    ['=EXPENSEVAT(D2)'],
    ['=EXPENSEVAT(D3)'],
    ['=EXPENSEVAT(D4)'],
    ['=EXPENSEVAT(D5)'],
    ['=EXPENSEVAT(D6)']
  ]);

  // 4) Currency formatting for both amount columns
  sheet.getRange('D2:D1000').setNumberFormat('$#,##0.00');
  sheet.getRange('F2:F1000').setNumberFormat('$#,##0.00');

  // 5) Frozen header and auto-fitted columns for readability
  sheet.setFrozenRows(1);
  sheet.autoResizeColumns(1, 6);

  Logger.log('Expense ledger is ready.');
}

Step 4 — Save and run. Press Ctrl+S, then click Run in the toolbar with setupExpenseLedger selected. On first run, Google asks for authorization — the screen every beginner asks me about. After it finishes, column F will show #NAME? errors — that is expected, because the EXPENSEVAT custom function does not exist yet. You will add it in the Custom Functions section below, and once you do, those cells will update automatically.

Step 5 — Verify the result. Return to the spreadsheet tab: a green-headed Expenses sheet now exists with formatted sample data and a sixth column headed "With VAT" (showing #NAME? for now — that resolves itself the moment you paste the EXPENSEVAT function below). Back in the editor, open Executions (or View → Logs) to see the run history and the "Expense ledger is ready" log line — this dashboard is where you will diagnose every future failure.

The result: a formatted expense ledger with a "With VAT" column — built in seconds, and it rebuilds itself identically every time the script runs.

Reading and Writing Data: The One Habit That Separates Fast Scripts from Slow Ones

Now that the ledger exists, let's make the script do something useful with it: total the expenses by category. Along the way you will learn the single most important performance habit in Apps Script — because the difference between a script that finishes in a second and one that crawls is not clever code, it is how you talk to the spreadsheet.

/**
 * Reads the ledger in ONE batch call and totals expenses by category.
 * The returned summary will feed dialogs, emails and reports
 * in later parts of the series.
 */
function summarizeExpenses() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Expenses');
  const lastRow = sheet.getLastRow();

  // Guard: nothing to summarize yet
  if (lastRow < 2) {
    Logger.log('The Expenses sheet is empty — run setupExpenseLedger first.');
    return 'No expenses recorded yet.';
  }

  // Read once: pull every row into memory together, not cell by cell
  const data = sheet.getRange(2, 1, lastRow - 1, 4).getValues();

  const totals = {};
  for (const row of data) {
    const category = row[2]; // column C
    const amount = row[3];   // column D
    if (category && amount) {
      totals[category] = (totals[category] || 0) + amount;
    }
  }

  let summary = 'Expenses by category:\n';
  for (const category in totals) {
    summary += '  ' + category + ': $' + totals[category].toFixed(2) + '\n';
  }

  Logger.log(summary);
  return summary;
}

Run it, then open the execution log: Meals: $48.50, Software: $120.00… calculated in milliseconds. The engine room is getValues(), which pulls the whole range into memory as a JavaScript array in one round trip. The common mistake I see in beginner code is calling getValue() inside a loop — each call is a separate conversation with Google's servers, and a few hundred rows later the script is crawling or dead. The rule that survives every performance lesson in this series (Part 008 measures it live): read once, compute in memory, write once.

Native Sheets Features vs Apps Script: Choosing the Right Tool

The first question every newcomer should ask is not "how do I script this?" but "should I script this?" Google Sheets already ships powerful native tools, and reaching for code where a two-click feature exists just adds maintenance. This comparison is the series' recurring decision lens — we will return to it in every stage.

The decision rule I give every team: if a person rebuilds it every period, script it; if it answers a one-time question, native features win. Spreadsheet formulas also have a ceiling — no formula can send an email, create a document, or react to a form submission — and that ceiling is exactly where the rest of this tutorial lives.

Custom Functions: Your Own Spreadsheet Formulas

Apps Script does not only push data around — it can extend the formula bar itself. A custom function is a script you call from a cell exactly like =SUM(), which is perfect for the calculations your team keeps recreating. Here is one for our expense workbook: a VAT adder you can use as =EXPENSEVAT(120) or =EXPENSEVAT(120, 0.05).

/**
 * Adds VAT to a net expense amount.
 * Works with a single number OR a whole range,
 * e.g. =EXPENSEVAT(120) or =EXPENSEVAT(D2:D10).
 * @param {number} amount The net expense amount or a range of amounts.
 * @param {number} [rate] Optional VAT rate as a decimal (default 0.15).
 * @return The amount(s) after VAT.
 * @customfunction
 */
function EXPENSEVAT(amount, rate) {
  // Treat "not supplied" as the default rate of 15%
  rate = (rate === undefined || rate === null || rate === '') ? 0.15 : rate;

  // The calculation applied to every value
  function applyVat(value) {
    const number = parseFloat(value);
    if (isNaN(number)) {
      return 'Enter a number, e.g. =EXPENSEVAT(120)';
    }
    return number * (1 + rate);
  }

  // A range arrives as a 2-D array — apply VAT to every cell
  if (Array.isArray(amount)) {
    return amount.map(function (row) {
      return row.map(applyVat);
    });
  }
  return applyVat(amount);
}

Paste it into the same project and save (Ctrl+S). If you already ran setupExpenseLedger(), re-run it now: column F ("With VAT") will fill instantly with the VAT-inclusive amounts — $55.78, $36.80, and so on — because the ledger script writes =EXPENSEVAT(D2) formulas into that column automatically. You can also type =EXPENSEVAT(120) into any empty cell to confirm: 138.00. The JSDoc comments (the @param and @customfunction lines) are not decoration — they are what makes autocomplete show your function and its hints inside the spreadsheet. The function also accepts a whole range (=EXPENSEVAT(D2:D10)) and handles empty or mistyped cells gracefully instead of failing the formula.

=EXPENSEVAT(120) returns 138.00 — your own formula, defined in a few lines, usable by anyone who opens the sheet.

Custom functions have documented limits worth knowing from day one, per Google's custom functions guide: they can only return a value — no sending email, no opening dialogs, no services that require authorization — and results recalculate when spreadsheet content changes, which can add up on large sheets. Design each function to be called once per range rather than dragged across a thousand cells. Part 005 builds this topic into a full lesson with the series' own currency-conversion helpers.

Triggers: Automation That Runs While You Sleep

Everything so far runs when you press Run. The real superpower starts when scripts run themselves. Apps Script calls these triggers, and they come in two families. Simple triggers — functions named onOpen() or onEdit() — fire automatically but with limited permissions: they cannot send email or touch services that require authorization. Installable triggers are created from the Triggers page, run with your approved permissions, and support more events, including form submissions and clock schedules. The distinction matters for design, and Part 009 dissects every trigger type.

Let's wire the classic starter: a morning summary email. Add this function to the project.

/**
 * Runs automatically every morning via a time-driven trigger.
 * Sends one email with the expense summary — check the
 * official email quotas before scaling this to a big team.
 */
function sendDailyExpenseSummary() {
  const summary = summarizeExpenses(); // reuse the function you already built
  MailApp.sendEmail(
    Session.getEffectiveUser().getEmail(), // the trigger owner's address — or hardcode yours, e.g. 'you@example.com'
    'Daily expense summary',
    summary
  );
}

Then, in the editor, click the Triggers icon (the clock, in the left sidebar) → Add Trigger → choose sendDailyExpenseSummary, event source Time-driven, type Day timer, hour 7am–8am → Save. Authorize once, and from tomorrow morning the summary arrives in your inbox with nobody involved. One deliberate detail: the script uses Session.getEffectiveUser() — the identity that owns the trigger — because the similar getActiveUser() can return an empty address for personal accounts, which would crash the email call. If you prefer zero ambiguity, replace that argument with your own address between quotes. Because it runs on Google's servers, it fires even when your computer is off — though note that the Sheets mobile app cannot run scripts or click custom buttons on demand; only server-side triggers keep working on the move.

The Triggers panel: one function, one schedule, and the automation stops depending on anyone remembering to run it.

The Four Mistakes Every Beginner Hits First (and the Fixes)

After years of setting up scripts for non-technical colleagues, I can predict where the first week goes wrong. Fix these four now and you will skip most of the frustration.

1. Panic at the authorization screen

The first run of every script asks for permission, and the "Google hasn't verified this app" warning convinces many readers they broke something. You did not. For your own scripts, walk through Advanced → Go to project once per script. What actually deserves caution is a script someone else sent you — read the permission list before approving anything you cannot explain.

2. Editing the code, running the wrong function

The editor runs whichever function is selected in the toolbar dropdown, and after pasting several functions beginners keep re-running the old one and wondering why nothing changed. Before you press Run, check the dropdown. This single habit prevents most "my code is broken" messages.

3. Talking to the spreadsheet inside a loop

You met this one above, but it is worth repeating because it causes both slow scripts and quota errors: every getValue() or setValue() inside a loop is a separate server round trip. Batch with getValues() and setValues(), compute in memory, and write the result back in one call.

4. Testing on live, shared data

The first version of any script should run against a copy of the spreadsheet, never the ledger your accountant already uses. Destructive experiments (deleting rows, overwriting ranges, mass emails) belong in a sandbox copy — and when you eventually schedule destructive jobs, Part 020's dry-run pattern will keep you safe.

The Series Map: 8 Stages from First Script to Full Workspace Automation

Most Apps Script content teaches disconnected tricks; this series teaches one connected build. Every part advances the same case study — a small business automating its expense submission, approval, and reporting workflow — so what you construct today still runs, upgraded rather than rewritten, in the final parts. Here is the full 48-part route across eight stages:

Along the way, each stage ships downloadable support — starter spreadsheets, script snippet libraries, document templates, and stage quizzes — announced inside the relevant parts as they are published. Gmail users get an early bonus too: when Stage 3 arrives, we will connect it to the manual Gmail labels and filters guide already on this blog, so non-scripters on your team have a path too.

Quick Knowledge Check & Practical Challenge

Before the summary, two short exercises to convert this article from reading material into muscle memory. The first tests the trigger concept; the second prepares the raw material for Part 002.

Summary & Actionable Checklist

You now hold the complete foundation of the series: the platform model, a working development environment, three production-ready scripts, and the judgment to know when not to script. Before moving on, confirm every box:

• Opened the editor from Extensions → Apps Script inside your expense workbook.
• Ran setupExpenseLedger(), authorized once, and saw the formatted ledger appear.
• Ran summarizeExpenses() and read the category totals in the execution log.
• Kept data access batched — one getValues(), not a loop of getValue().
• Added EXPENSEVAT() and used it inside a real cell as a custom function.
• Scheduled one time-driven trigger and confirmed the run in the Triggers/Executions dashboard.
• Checked the official quotas page before dreaming up anything that emails or runs long.
• Bookmarked this article — it is the anchor every later part links back to.

Every future part assumes these eight checkboxes. If any of them feels shaky, re-run the matching step — the scripts are idempotent by design, so rebuilding the ledger costs you nothing.

Frequently Asked Questions

The Bottom Line: One Script, Hours Back

Google Apps Script turns the work you repeat into work that runs itself — and you now have proof in your own spreadsheet: a ledger that builds itself, a summary that computes itself, a custom function your colleagues can type like any formula, and a trigger that delivers the morning report without anyone opening a browser. None of it required installing anything or spending anything.

Your next step is deliberate, not accidental: bring your three most-repeated spreadsheet tasks to Part 002: Spreadsheet Functions vs Google Apps Script (coming soon), where we settle — with side-by-side builds — when a formula is the right answer and when code earns its keep. The habit you install today, scripting the things a person rebuilds every period, is the same habit the entire 48-part series scales into a complete Workspace automation system.