Quick answer: Google Apps Script is a free scripting platform built into Google Sheets, Docs, Gmail and the rest of Google Workspace. With a little JavaScript you can add custom menus and functions to a spreadsheet, send emails when data changes, generate scheduled reports, clean up imported data and pull information from other services through their APIs. Open it from the Tools menu, then Script editor, in any Google Sheet. It is ideal for small, spreadsheet-centered automations. For high-volume or business-critical integrations, a proper integration service is usually safer.
What Apps Script is good for
Many small businesses run important processes in Google Sheets: lead lists, order logs, stock counts, job schedules, timesheets. Those sheets often involve repetitive manual steps, such as copying data in, formatting it, emailing someone when a row changes, or building the same summary every Monday.
Apps Script handles those steps inside Google’s own environment. There is no server to set up and nothing to install. Common uses include:
- Sending an email or chat alert when a row meets a condition
- Building a weekly summary tab and emailing it as a PDF
- Cleaning and reformatting data pasted from another system
- Creating Google Docs from a template, filled in with row data
- Pulling data from an external API into a sheet on a schedule
- Adding a custom menu with one-click actions for your team
Since Google moved Apps Script to the V8 runtime earlier in 2020, you can write modern JavaScript, including arrow functions, let and const, and template literals.
Getting started
- Open the Google Sheet you want to automate.
- Open the script editor from Tools, then Script editor.
- A new project opens with an empty function.
- Write your code, save, and click Run.
- The first time, Google will ask you to authorize the script to access your data. Review the permissions carefully.
Scripts created this way are bound to that spreadsheet, so they can easily read and change it.
Example 1: A custom function
Custom functions work like built-in formulas. This one adds a sales tax amount:
/**
* Calculates tax for an amount.
* @customfunction
*/
function ADD_TAX(amount, rate) {
return amount * (1 + rate);
}
In any cell you can now type =ADD_TAX(B2, 0.2). Custom functions are handy for business logic that is awkward to express in normal formulas. They cannot send emails or change other cells, which keeps them safe for everyday use.
Example 2: A custom menu
A menu gives your team buttons instead of asking them to run scripts:
function onOpen() {
SpreadsheetApp.getUi()
.createMenu('Team tools')
.addItem('Archive completed jobs', 'archiveCompleted')
.addToUi();
}
function archiveCompleted() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const jobs = ss.getSheetByName('Jobs');
const archive = ss.getSheetByName('Archive');
const rows = jobs.getDataRange().getValues();
for (let i = rows.length - 1; i >= 1; i--) {
if (rows[i][4] === 'Done') {
archive.appendRow(rows[i]);
jobs.deleteRow(i + 1);
}
}
}
onOpen runs automatically when the sheet opens. The loop works from the bottom up so deleting rows does not skip any.
Example 3: Email alert when a status changes
This script emails the sales lead when someone marks a row as “Hot” in column F:
function onStatusEdit(e) {
const range = e.range;
if (range.getSheet().getName() !== 'Leads') return;
if (range.getColumn() !== 6 || e.value !== 'Hot') return;
const row = range.getSheet().getRange(range.getRow(), 1, 1, 6).getValues()[0];
MailApp.sendEmail({
to: '[email protected]',
subject: `Hot lead: ${row[0]}`,
body: `Name: ${row[0]}\nEmail: ${row[1]}\nNotes: ${row[4]}`
});
}
Because sending email needs authorization, this must run from an installable trigger, not a simple trigger. In the script editor, open Triggers, add a trigger for onStatusEdit, choose “From spreadsheet” and “On edit.”
Triggers explained
Triggers decide when scripts run without someone clicking Run.
- Simple triggers like
onOpenandonEditrun automatically but cannot do anything that requires authorization, such as sending email. - Installable triggers run as the user who created them and can do more. Options include on edit, on change, on form submit and time-driven.
- Time-driven triggers run on a schedule: every minute, hourly, daily at a set hour, or weekly.
A time-driven trigger is how you build scheduled reports, nightly data imports and weekly reminders.
Example 4: Pulling data from an API
UrlFetchApp lets a script call external APIs. This example fetches JSON and writes it to a sheet:
function importOrders() {
const key = PropertiesService.getScriptProperties().getProperty('API_KEY');
const res = UrlFetchApp.fetch('https://api.example.com/orders?status=new', {
headers: { Authorization: `Bearer ${key}` }
});
const orders = JSON.parse(res.getContentText());
const sheet = SpreadsheetApp.getActive().getSheetByName('Orders');
orders.forEach(o => sheet.appendRow([o.id, o.customer, o.total, o.created_at]));
}
Store API keys in Script Properties rather than in the code, so they are not visible to everyone who can view the script. Our guide to the Google Sheets API covers the opposite direction, where outside systems write into Sheets.
Limits to know about
Apps Script has quotas that vary between free Gmail accounts and paid Google Workspace accounts (the new name G Suite took on in October). Key ones:
- A single script execution can run for up to 6 minutes.
- There are daily limits on emails sent, URL fetch calls and total trigger runtime.
- Time-driven triggers are not exact. A daily trigger set for 9 a.m. runs some time within that hour.
For large datasets, read and write ranges in bulk with getValues and setValues rather than cell by cell. Bulk operations are far faster and use fewer resources.
Good habits
- Name things clearly. Future you, or a colleague, will need to understand the script.
- Add a log. Write a row to a “Log” tab each time an important script runs, with a timestamp and result.
- Handle errors. Wrap risky steps in
tryandcatch, and email yourself when something fails. - Use a test copy. Try changes on a copy of the sheet before running them on live data.
- Watch ownership. Installable triggers run as the person who created them. If that person leaves and their account is removed, the automation stops. Use a shared business account for important triggers.
- Review permissions. Only grant access the script actually needs.
When to use something else
Apps Script is excellent for small automations around Google tools. Consider other options when:
- The process connects many apps. Tools like Zapier offer ready-made connectors and need no code. Our article on no-code automation compares options.
- Volume is high. Quotas and the 6 minute limit make heavy processing difficult.
- The sheet has become a database. If many people edit thousands of rows and other systems depend on it, a real database and application may serve you better.
- Reliability is critical. Payments, orders and anything customer-facing deserve proper monitoring, testing and error handling, which a custom integration provides more easily.
Our guide to workflow automation covers how to choose the right level of tool for each process.
FAQ
Is Google Apps Script free?
Yes, it is included with Google accounts and Google Workspace. Paid Workspace accounts get higher quotas.
Do I need to know JavaScript?
Basic JavaScript helps a lot. Many useful scripts are short, and you can adapt examples once you understand variables, loops and functions.
Can Apps Script send emails from my Gmail account?
Yes, using MailApp or GmailApp, within daily sending limits.
Can other people use my script?
Anyone with edit access to a bound spreadsheet can run its scripts after authorizing them. For wider distribution, scripts can be published as add-ons, which takes more work.
Next step
Pick one repetitive task in a sheet you use every week and automate it with a menu or trigger. Start small, add logging, and build from there.
If you would like help automating spreadsheet-driven processes, or moving them to something sturdier, see our Automate the busywork services.
