Google Apps Script for Sheets: Automation and Setup

Google Apps Script is Google’s cloud-based JavaScript platform for automating Google Sheets and connecting it with other Google services. In a spreadsheet, you can use it to create custom menus, respond to edits, send information to other services, or build custom functions.

What Google Apps Script is

Apps Script lets you write code that runs on Google’s servers and works with products such as Sheets, Docs, Drive, Gmail, and Calendar. It uses JavaScript, so people who know that language may find it familiar, but the Apps Script environment also provides built-in services designed for Google products. For example, the Spreadsheet service can read and update sheet cells, while the Gmail service can send email when the script has permission to do so.

A script can be attached to a particular spreadsheet, or it can be a separate project that works with one or more files. A spreadsheet-attached project is often called a bound script. It is a practical starting point when the code is intended for one workbook, because the script can refer to its associated spreadsheet without requiring the file’s ID. A standalone project can be more suitable for work that spans several files or needs to be managed independently.

Apps Script is not a separate desktop program and is not the same thing as a spreadsheet formula. The code is stored in an Apps Script project and runs when you launch it, when a trigger runs it, or when another supported event calls it.

How Apps Script connects to Sheets

In a browser, open a Google spreadsheet and choose Extensions > Apps Script to open its bound project. The editor is where you write, save, and run code. Scripts typically use Google’s Spreadsheet service to get a spreadsheet, select a sheet or range, and read or write cell values. A range can contain one cell or a whole block, which makes it possible to process data in batches rather than handle every cell separately.

For example, a script might read a table of orders, identify rows that meet a condition, and prepare a summary. It could also create a menu item that lets a spreadsheet user run a routine without opening the editor. Apps Script is useful when the task involves a repeatable sequence of actions; it is usually unnecessary for a calculation that a normal Sheets function can already perform.

The script editor and spreadsheet are connected, but they are different interfaces. Changing code does not automatically mean it has run, and running code does not always mean the displayed sheet will immediately look different: the script might update another tab, send a notification, or stop because it needs authorization.

How to create and run a first script

Open the spreadsheet in a web browser and select Extensions > Apps Script.
In the editor, replace the starter code with a small function, then save the project. Give the project a descriptive name so it is easier to recognize later.
Choose the function in the editor’s function selector and select Run. The first run may ask you to review and grant permissions. Read the requested access carefully before approving it.
Return to the spreadsheet and check the relevant cells or tabs to confirm what changed. If the script reports an error, open the execution details in Apps Script to see the message and the line involved.

Do not try to run an event-handler function such as onEdit directly from the editor as if it were an ordinary function. When run manually, it does not receive the edit-event information that Sheets supplies when a user changes a cell. To test an event handler, make the kind of edit it is designed to handle, or write a separate test function that supplies controlled input.

Example: add a timestamp after an edit

Suppose a tab named Tasks has task names in column A, starting in row 2, and you want column B to record the time a task name is entered or changed. In the bound script, this simple example responds to a one-cell edit in column A on that tab:

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

  if (sheet.getName() !== 'Tasks' ||
      range.getColumn() !== 1 ||
      range.getRow() < 2 ||
      range.getNumRows() !== 1 ||
      range.getNumColumns() !== 1) {
    return;
  }

  const timestampCell = sheet.getRange(range.getRow(), 2);
  timestampCell.setValue(range.getValue() === '' ? '' : new Date());
}

The function uses the edit event object, e, to identify the edited range. It ignores other tabs, other columns, the header row, and multi-cell pastes. When a qualifying cell is cleared, it clears the timestamp too. To display the result as a date and time, select the relevant cells in column B and use Format > Number > Date time in Sheets. This is an illustrative starting point, not a complete audit log: a later edit replaces the earlier timestamp, and pasting several cells at once is deliberately ignored.

Triggers and scheduled automation

A trigger determines when a function runs. The simple triggers onOpen and onEdit can handle common spreadsheet events with little setup. For instance, an onOpen function can add a custom menu when an editor opens a spreadsheet. Simple triggers have restrictions, including limits on the services they can use and the authorization available to them.

Installable triggers can support additional events and services that require authorization. They are created in Apps Script’s Triggers panel, where you select a function and an event source, such as a spreadsheet event or a time-driven schedule. An installable trigger runs under the account of the person who created it. That matters if the script reads files or sends email: the creator’s permissions and account status can affect whether it continues to work.

Triggers should be designed to avoid repeated or unintended actions. An edit handler that writes to another cell may cause a workflow to interact with other automation, and a scheduled job may process the same records again unless it tracks what it has already handled. Test with a small, safe set of data before relying on an automated process.

Custom functions, menus, and integrations

Apps Script can add features beyond background automation. A custom function lets a user call script code from a cell in a similar way to a built-in Sheets function. It is useful for a calculation or transformation that is awkward to express with standard functions. However, custom functions have restrictions: they are intended to return values to cells, not to edit arbitrary cells, and they cannot freely use services that require user authorization.

A custom menu is another approachable feature. A script can add a menu when a spreadsheet opens, with commands such as “Refresh summary” or “Prepare report.” This gives colleagues a visible way to run a task without asking them to find the code editor. Apps Script can also connect supported Google services and external services, but integrations may need authorization, careful handling of data, and—in some cases—an API key or separate paid service.

These options suit different jobs. Use a custom function for a cell-level result, a menu for a user-invoked task, and a trigger for an event or schedule. Choosing the simplest one that meets the need makes the spreadsheet easier to maintain.

Apps Script compared with formulas and alternatives

Before writing code, check whether a built-in Sheets function, filter, pivot table, conditional formatting rule, or data validation rule can solve the problem. These tools are often easier to understand and maintain. Formulas are especially appropriate when the result should update as input values change. Apps Script is a better candidate when the task needs actions formulas cannot perform, such as sending a message, creating a menu, or applying a multi-step workflow.

Apps Script is specific to Google’s environment. Microsoft Excel has different automation options, including Office Scripts in supported Microsoft 365 environments and VBA in desktop Excel. Code written for Apps Script does not run as an Excel macro, and an Excel formula or script may not transfer directly to Sheets. If a team regularly moves files between products, favor standard spreadsheet features where possible and document any platform-specific automation.

Third-party add-ons can provide ready-made features without requiring you to write code, but they introduce another provider and may request access to spreadsheet data. Compare their permissions, maintenance, and cost with the benefit of building a small script yourself.

Limits, performance, and cost

Apps Script has service quotas and execution limits. The exact limits can vary by account type and service, and Google can change them, so check the current Apps Script quotas documentation before designing a high-volume workflow. A script that works on a small sheet may be slow or fail on a much larger one, especially if it makes many separate calls to read or write individual cells. Reading a range into an array, processing values in memory, and writing results back in a batch is often more efficient.

Apps Script is generally available without a separate Apps Script charge, but that does not mean every related service is free or unlimited. Google Workspace account policies, quotas, external APIs, and third-party services can affect availability or cost. If a process is business-critical, plan for monitoring, error handling, and a way to recover or rerun work safely rather than assuming every scheduled execution will succeed.

Permissions and safe use

Scripts may request access to the spreadsheet or other Google services. Grant only permissions that make sense for the task, and be cautious with code from an unfamiliar source. A script’s requested access can be broader than a spreadsheet’s visible formulas because it may interact with services outside the current file.

People who can edit a spreadsheet with a bound script can generally access and change its script project. Treat the code as part of the shared document, not as a private secret store. Do not put passwords or sensitive access tokens directly in code that other editors can inspect. If a script sends email, reads Drive files, or contacts an external service, consider what information it exposes and who controls the account that runs it. Make a copy of important data before testing code that changes or deletes cells.

Common problems and troubleshooting

The script asks for permission: Some services require authorization. Review the requested access and confirm that the script’s source and purpose are trustworthy.
An edit trigger does nothing: Check that the edit matches the conditions in the code. A simple edit trigger does not run when a cell changes because of a formula recalculation or a script’s own update.
The editor shows an error: Read the error message and execution details, then check the named line, spelling, sheet name, and range. A tab name must match the name used in code.
A scheduled task stops working: Check the trigger settings, the account that created it, recent executions, authorization status, and any quota-related errors.

When debugging, reduce the problem to a small example and test on a copy. Add logging where it helps identify the values a function receives, but avoid logging sensitive information. If the spreadsheet has multiple editors, also verify that an automation is not depending on a permission or trigger owned by someone whose account may later be removed.

Frequently Asked Questions

Is Google Apps Script free? Apps Script is generally available without a separate charge, but quotas, Workspace policies, and services it connects to can affect use. External APIs or add-ons may have their own fees.

Do I need to install anything? No desktop installation is required to edit a Sheets script. Open the spreadsheet in a browser and choose Extensions > Apps Script.

Can Apps Script run automatically when a cell changes? Yes. A simple onEdit(e) trigger can respond to qualifying user edits, subject to its restrictions. Use an installable trigger when the task needs additional authorization or trigger options.

Can Apps Script edit any cell from a custom function? A custom function is meant to return a value to its calling cell, not to change arbitrary cells. Use a menu command or an appropriate trigger for actions that update other parts of a spreadsheet.

Does Apps Script work in Microsoft Excel? No. Apps Script is for Google services. Excel uses its own features, such as Office Scripts in supported environments or VBA in desktop Excel; the code is not interchangeable.

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.