Google Apps Script is a scripting platform built on JavaScript that lets you automate Google Sheets and other Google Workspace tools. With a few lines of code you can add custom menu items, tidy data with one click, send emails from rows or run tasks on a schedule. You do not need to be a programmer to start.
This guide covers where to write a script, a first working example, custom menus, triggers and the habits that keep automation safe.
When Google Apps Script is the right tool
Formulas calculate values, but they cannot take actions, such as sorting a range, sending a message or copying rows to another tab. A script can. It makes sense when you repeat the same sequence of clicks often, when a task needs to run at a set time, or when you want a button for a colleague who is not comfortable with spreadsheets.
If a formula can do the job, use a formula first. Our guide to Google Sheets formulas is a good place to check. Scripts are for the jobs formulas cannot do.
Opening the editor and writing your first script
In a spreadsheet, open Extensions and choose Apps Script. A new browser tab opens with the script editor, and the script is attached to that spreadsheet, which is called a container-bound script. It starts with an empty function. Replace it with this:
function addTimestamp() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Log');
sheet.appendRow([new Date(), 'Script ran']);
}Create a tab named Log in your spreadsheet, save the script, and press Run. The first time, Google asks you to authorize the script, because it needs permission to edit your spreadsheet. After you approve, a new row with the date and message appears in the Log tab.
What the code is doing
- SpreadsheetApp is the service that lets scripts work with Sheets.
- getActiveSpreadsheet() returns the spreadsheet the script is attached to.
- getSheetByName(‘Log’) picks one tab by its name.
- appendRow([…]) adds a row after the last row that contains data.
The words const and function are ordinary JavaScript. If you want to learn the language itself, any beginner JavaScript tutorial will transfer directly.
Reading and writing cells
Most scripts read values, change them and write them back. Here is a small example that trims spaces in the first column:
function trimColumnA() {
const sheet = SpreadsheetApp.getActiveSheet();
const range = sheet.getRange('A2:A' + sheet.getLastRow());
const values = range.getValues();
const cleaned = values.map(row => [String(row[0]).trim()]);
range.setValues(cleaned);
}Notice that it reads all values at once with getValues() and writes them back at once with setValues(). This matters for speed. Reading or writing one cell at a time inside a loop is slow and can make a script time out on larger sheets. The values come back as a two-dimensional array: a list of rows, each a list of cells.
Adding a custom menu
A special function named onOpen runs each time the spreadsheet is opened. You can use it to add a menu so that anyone can run your scripts without opening the editor:
function onOpen() {
SpreadsheetApp.getUi()
.createMenu('My Tools')
.addItem('Trim column A', 'trimColumnA')
.addToUi();
}Reload the spreadsheet and a My Tools menu appears. The text in quotes after the item name must match your function name exactly. A button on a sheet is another option: insert a drawing, then use the three-dot menu on it to assign a script.
Running scripts automatically with triggers
In the Apps Script editor, the clock icon opens Triggers. A time-driven trigger can run a function every hour, day or week. An on-edit or on-form-submit trigger runs a function when something changes. Simple triggers such as onOpen and onEdit work without setup but have restrictions, for example they cannot perform actions that need authorization. Installable triggers, which you add yourself in the Triggers page, can do more.
This is how a weekly summary email or a nightly data cleanup runs without you opening the file. If you are weighing how much to automate, our overview of Google Sheets add-ons worth trying covers ready-made options that need no code.
Debugging and staying safe
- Use
Logger.log(value)and open the Execution log to see what your script is doing. - Test on a copy of your spreadsheet, because scripts can overwrite data and there is no undo for every change.
- Read the authorization screen carefully. A script that sends email or reaches your Drive needs broader permission.
- Be careful with scripts copied from the internet. Read what they do before you run them.
- Remember that quotas apply, such as daily email limits and a maximum run time for each execution.
When you get an error message, copy it into a search engine. Apps Script has a large community, and most beginner errors, such as a misspelled tab name or a null range, have been solved many times. For more articles like this one, see our spreadsheets section.
Where to start
Choose one small, boring task you repeat each week, such as trimming a column or copying finished rows to an archive tab. Write it in plain steps first, then build the script one step at a time, testing on a copy as you go. A script that saves you five minutes a week is a better first project than an ambitious one that never gets finished.
Frequently asked questions
Do I need to know JavaScript to use Google Apps Script?
Not to begin. Copying and adapting short examples works well, and learning basic JavaScript gradually will make scripts easier to write.
Is Google Apps Script free?
It is available with a Google account, subject to daily quotas on things like emails sent and total run time. Workspace accounts usually have higher limits.
Where do I find the script editor?
Open a spreadsheet, choose Extensions, then Apps Script. The editor opens in a new tab and is attached to that file.
Why does my script ask for authorization?
Scripts need your permission to read or change your data. Review what access is requested and only approve scripts you understand.
