Google Sheets Apps Script Tutorial: Automate Everyday Tasks

Google Sheets Apps Script is Google’s JavaScript-based tool for automating spreadsheets. To start, open a sheet in a browser, choose Extensions > Apps Script, add a function in the editor, save it, and run it; the tutorial below builds a small task-tracking automation and explains permissions, triggers, and common pitfalls.

What Apps Script does in Google Sheets

Apps Script lets you write code that reads and changes spreadsheet data, adds custom menus, responds to edits, and connects Sheets with other Google services. It runs on Google’s servers, so it is not a desktop macro that depends on your computer staying open. A script can be attached to one spreadsheet, or it can be created as a standalone project and work with files it is permitted to access.

A spreadsheet-bound script is a useful starting point because its users can find it from the sheet’s Extensions menu. Typical uses include cleaning imported data, creating a report, adding timestamps, sending reminders, or building a custom menu for repetitive actions. Apps Script is more flexible than a recorded macro, but it requires basic JavaScript concepts such as functions, variables, conditions, and methods.

How Apps Script works

A script is a project containing one or more files of JavaScript code. The spreadsheet service exposes objects such as a spreadsheet, sheet, and range. A range represents one cell or a rectangular group of cells, and methods such as getValues() and setValues() read or write its contents. Spreadsheet rows and columns are numbered starting at 1 in these methods, unlike JavaScript arrays, whose indexes start at 0.

When you run a function from the editor, Apps Script executes it under the Google account that authorized it. The editor’s execution log and the Apps Script dashboard can help you inspect runs and failures. A script may also run from a custom menu or a trigger. The first run of a script that requests account access generally prompts the user to review and grant permissions.

Create a simple bound script

Open the Google spreadsheet you want to work with in a web browser.
Choose Extensions > Apps Script. The script editor opens in another tab with a project linked to the spreadsheet.
Replace the starter code in Code.gs with the example below, then click Save project. You can give the project a descriptive name, such as Task Sheet Helpers.
In the function selector above the editor, choose formatTaskHeader, then click Run. On a first run, follow the permission prompts and review which spreadsheet access is being requested.
Return to the spreadsheet and check that cells A1:D1 on the active tab have bold text and a pale green background.

function formatTaskHeader() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const header = sheet.getRange("A1:D1");

  header.setFontWeight("bold");
  header.setBackground("#d9ead3");
}

This script assumes the header occupies A1:D1 on the active sheet. getActiveSpreadsheet() refers to the spreadsheet associated with the project when run from its bound script. getActiveSheet() means the selected tab, so select the intended tab before running it. If the header is elsewhere, change the range string. This example is deliberately limited: it changes formatting only and does not delete or rearrange data.

Build an edit-trigger example

For a task list, suppose column A contains task names, column B contains a status, and column C should record when a task is marked Done. This function checks edits in column B of a tab named Tasks and writes the current date and time into column C on the same row:

function onEdit(e) {
  const range = e.range;
  const sheet = range.getSheet();

  if (sheet.getName() !== "Tasks" || range.getColumn() !== 2 || range.getRow() < 2) {
    return;
  }

  if (e.value === "Done") {
    sheet.getRange(range.getRow(), 3).setValue(new Date());
  }
}

Put the code in the bound project and save it. Then edit a single status cell in column B on the Tasks tab, entering Done. The edit trigger runs automatically; do not click Run on onEdit in the editor, because a manual run does not supply the event object e. Format column C as a date and time if you want the timestamp displayed clearly. The script records the time when the status is set; it does not automatically clear or update that time if the status later changes.

This basic example has a useful limitation to understand: e.value is available for a single-cell edit, but may be absent when a user pastes a multi-cell block. It also responds to a person’s edit in the sheet, not changes made by another script or an API request. If those cases matter, add explicit checks and test the behavior you need.

Choose between simple and installable triggers

The function named onEdit is a simple trigger: Google recognizes the name and runs it after a qualifying user edit. Simple triggers are convenient for lightweight actions, but they have restrictions, including limited execution time and limits on services that require authorization. They are not the right choice for every task.

An installable trigger is configured through the Apps Script editor’s Triggers page (the clock icon) and can run on events such as edits, form submissions, or a schedule. It runs with the authorization of the account that created the trigger, so consider who owns it and what happens if that person loses access. Installable triggers can use authorized services, but still operate under Google’s quotas and restrictions. Avoid creating duplicate triggers: each one can cause the same function to run again.

Good practices for reliable scripts

Use clear sheet and range checks. Check a tab name and relevant columns before changing cells. This helps prevent a script from acting on the wrong worksheet.
Handle ranges efficiently. For large areas, read values in one batch with getValues(), work with the data in JavaScript, and write results in a batch with setValues(). Repeated cell-by-cell calls are often slower.
Test on a copy. A script can change many cells quickly. Duplicate the spreadsheet or use a small test range before running code against important records.
Keep code understandable. Use descriptive function and variable names, and add short comments where the reason for a rule is not obvious.
Check date and time settings. Spreadsheet and script time zones can affect scheduled runs and displayed timestamps. Review the spreadsheet’s time zone in File > Settings and the project’s settings in Apps Script when times seem shifted.

Permissions, privacy, and safety

Before granting access, read the authorization screen and confirm that the account and requested access make sense for the task. A script shared with a spreadsheet may be visible to people who can edit the project, and an installable trigger may act using its creator’s authority. Do not paste passwords, API keys, or private data into code that other editors can see. If a project connects to an external service, assess that service and the data being sent to it.

Apps Script is not a substitute for spreadsheet access controls. Protect sensitive sheets and share the file only with appropriate people. For business-critical workflows, keep a backup, test changes, and decide how failures will be noticed. Google can change service quotas, which vary by account type and service; check the current Apps Script quotas documentation before designing a process that runs frequently or handles substantial data.

Common errors and how to troubleshoot them

“Cannot read properties of undefined” for e: The function was likely run manually. For an edit handler, test it by editing the sheet, or create a separate test function that supplies sample data.
Nothing happens after editing: Check the tab name, column number, row condition, and exact status text. “Done” and “done” do not match in the example. Also confirm that the edit was made by a user rather than another script.
Permission or authorization errors: Run an appropriate function from the editor to start the authorization flow, then review and grant the required access if you trust the project. A simple trigger cannot use every service that requires authorization.
Wrong cells change: Confirm that the code refers to the intended sheet and range. For a task list with a header row, row 1 is excluded in the example so it cannot be timestamped as a task.
A trigger runs more than once or not at all: Open the Triggers page and check for duplicates, the chosen event type, and the account that owns an installable trigger. Review execution history for errors.

Apps Script compared with macros and formulas

Google Sheets macros are a convenient way to record certain actions, and Sheets stores recorded macros as Apps Script code that can be inspected and edited. Recording is easier than writing code for a repetitive sequence, but it may not capture conditional logic or a workflow that must adapt to changing data. Formulas are usually better for calculations that should update as cells change; Apps Script is useful when the task involves actions, custom controls, or connections beyond a formula’s role.

Excel’s VBA macros are a different technology and do not run as Google Apps Script. Excel for Microsoft 365 also has Office Scripts, a separate automation tool with its own environment and capabilities. Code written for one platform generally needs rewriting for another; a JavaScript-like syntax does not make the spreadsheet services interchangeable.

Cost and alternatives

Apps Script does not generally require a separate purchase to use with Google Sheets, although access to Google Workspace features may depend on the organization’s plan. Usage quotas and service limits apply, and some external APIs or third-party services may charge separately. Check current Google documentation and any service provider’s pricing for the exact workflow rather than assuming unlimited runs.

If your need is a simple calculation, use a Sheets formula. For a one-time cleanup, built-in tools such as Find and replace or Data > Data cleanup may be enough. For a recurring workflow that requires an external system, consider that system’s integration tools or a supported add-on, while reviewing its permissions and data practices. Apps Script is a good fit when a small amount of custom logic can safely handle the job inside Google’s ecosystem.

Frequently Asked Questions

Is Google Apps Script free?
Apps Script is generally available without a separate Apps Script charge, but Google Workspace plans, usage quotas, and connected third-party services can affect cost. Check the current terms and limits for the account and services involved.

Do I need to know JavaScript to use Apps Script?
You can record simple macros without starting from scratch, but writing or adapting scripts is easier with basic JavaScript knowledge. Learn functions, variables, conditions, arrays, and how to read an error message before building a complex automation.

Can Apps Script run when the spreadsheet is closed?
Yes. An installable time-driven trigger can run on a schedule without the spreadsheet being open in a browser. A simple edit trigger instead responds to qualifying user edits, and trigger execution remains subject to authorization and quotas.

Can I undo an Apps Script change with Ctrl+Z?
Do not rely on spreadsheet undo to reverse a script’s actions. Make a copy before running code that changes important data, and consider writing a reversible process or retaining a backup.

Why does my onEdit function fail when I click Run?
Google supplies the event object only when a qualifying edit triggers the function. Clicking Run does not create that event object, so e is undefined. Test the function by editing the sheet, or create a separate test function with known sample inputs.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.