Apps Script: automate Writer and Spreadsheets

Writer and Spreadsheets come with an Apps Script equivalent: write small JavaScript programs that fill in cells, tidy a document, add your own menus, show a dialog or a sidebar, send an email or call a web service. If you've written Google Apps Script, the services, method names and trigger names are the same, so most simple scripts run unchanged.

Every script belongs to one document or spreadsheet and is saved inside it, so it's copied, versioned and synced along with the file. It runs only in the browser of someone who can edit that file, never on our servers — which shapes what scripts can and can't do, as this page explains.

New to Writer or Spreadsheets? Start with Writer: documents or Spreadsheets.

Opening the script editor

In a document or spreadsheet you can edit, choose Extensions ▸ Apps Script. The script editor opens full screen over the file; close it with the × in its top-right corner.

The Extensions menu only appears for people who can edit. Viewers, commenters and people using a view link don't see it, can't open the editor and never run a script.

The first time anyone opens the editor, the file gets a script project with one file, Code.gs, holding an empty myFunction.

Files: .gs and .html

The Files list on the left holds two kinds of file:

• Script files (.gs) — your code. Add one with the + button.
• HTML files (.html) — pages for dialogs and sidebars. Add one with the page button next to +.

Hover over a file to rename it (the pencil) or delete it (the bin). A deleted file can be brought back from the file's version history. File names may use letters, digits, spaces, _, . and -; a project holds up to 50 files of up to 500,000 characters each. Code.gs loads first, then the other .gs files in name order — like Google, every .gs file shares one set of functions and variables.

The editor keeps your typing as a draft until you save: press Ctrl+S (⌘+S on a Mac) or Save. Run saves first. The header reads "All changes saved in the document" once everything is saved, and a dot next to a file name means it has unsaved changes. Saved code syncs live to everyone else editing the file. If a collaborator saves the file you're changing, a banner — "A collaborator saved a new version of …" — offers Load their version or Keep mine.

Running a function

Pick a function in the drop-down at the top and press Run. The panel under the code switches to the Execution log, which shows:

• "Execution started: myFunction" and "Execution completed" (or "Execution failed").
• Every line your code writes with Logger.log() or console.log(), with a timestamp — console.warn() shows as a warning, console.error() as an error.
• Errors with the file and line they happened on. Click an error to jump to that line.

Clear empties the log. Scripts on one file run one at a time, in the order you start them.

A function whose name ends in _ — like helper_() — is private: it isn't offered in the drop-down, can't be put on a menu and can't be called from a dialog, but your other functions can still call it.

Custom functions

In Spreadsheets, any top-level function in your script can be used in a cell like a built-in function. Write:

function DOUBLE(input) {
  return input * 2;
}

and type =DOUBLE(A1) in a cell. Names aren't case-sensitive, so =double(A1) works too, and a built-in function or a named function of the same name always wins.

• A single cell arrives as a value; a range such as A1:B10 arrives as a list of rows.
• Return a value to fill one cell, a list to fill a column, or a list of lists to fill a block of cells. Return a Date and the cell is formatted as a date.
• If the function throws an error, the cell shows #ERROR! with the message and the line.

Custom functions can only calculate. They can't change the spreadsheet, show alerts or dialogs, pause with Utilities.sleep, or use anything that needs authorization. Each call has 30 seconds.

Custom functions run in the browser of an editor. When an editor has the spreadsheet open, the latest results are saved in the spreadsheet so viewers see the same values. A viewer who opens a cell no editor has calculated yet sees #N/A — "This custom function's result is calculated when an editor opens the spreadsheet."

Custom menus

Add your own menu to the menu bar from an onOpen function:

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Reports')
    .addItem('Build summary', 'buildSummary')
    .addSeparator()
    .addSubMenu(SpreadsheetApp.getUi().createMenu('More').addItem('Clear totals', 'clearTotals'))
    .addToUi();
}

In Writer, use DocumentApp.getUi() instead. Custom menus appear after Help, only for editors, up to 10 menus. Choosing an item runs its function as you, so it can do anything a Run from the editor can — including asking for authorization first.

Simple triggers

Give a function one of these names and it runs by itself:

The event e carries e.source (the spreadsheet or document) and, in Spreadsheets, e.range (the cells involved). For an edit to a single cell, e.value holds the new value.

A few things to know:

• Simple triggers run in the browser of the editor who opened the file or made the change. onEdit fires only for your own edits, never for a collaborator's or for changes a script makes.
• Simple triggers can't use anything that needs authorization — sending mail, fetching URLs, your private properties, your email address, dialogs and sidebars. Put that work on a custom menu item instead, so the person who clicks it can approve it.
• The Triggers tab beside the Execution log lists the simple triggers your script has, with a link to each one.

Dialogs and sidebars

For a quick question, use the built-in boxes, where ui is SpreadsheetApp.getUi() or DocumentApp.getUi():

• ui.alert('Done!') or ui.alert('Clean up', 'Delete these rows?', ui.ButtonSet.YES_NO) — returns the button pressed.
• ui.prompt('Your name?') — returns a response with getResponseText() and getSelectedButton().
• SpreadsheetApp.getActive().toast('Saved') — a short notice that disappears by itself.

Time spent waiting for someone to answer an alert or a prompt doesn't count toward the 30-second limit.

For anything richer, build a page with HtmlService from an .html file and show it:

function showPanel() {
  const page = HtmlService.createHtmlOutputFromFile('Sidebar').setTitle('Helper');
  SpreadsheetApp.getUi().showSidebar(page);
}

showSidebar, showModalDialog and showModelessDialog all work, and so do templates — createTemplateFromFile with <? ?>, <?= ?> and <?!= ?> scriptlets. Inside the page, google.script.run calls back into your script: google.script.run.withSuccessHandler(done).saveRow(values), with withFailureHandler and withUserObject as on Google. google.script.host.close() closes the dialog.

Dialogs and sidebars are shown in a locked-down frame: the page can run its own inline scripts and show images, but it can't load outside scripts, send network requests or reach the document around it. Showing one needs authorization, and a page can be up to 1 MB.

Supported services

A script works on the file it belongs to. Opening or creating other files — openById, openByUrl, create — isn't allowed.

Authorization

Some services act as you, so the person running the script has to approve them first:

When you run a function from the editor, a custom menu or a dialog and the script uses any of these, a dialog titled "This script wants to act as you" lists them, with Cancel and Allow. The list comes from what the code mentions, like Google's. If you cancel, the run stops with "Authorization is required to perform that action."

Your approval is remembered in your browser, for your account and this file only. It's never shared with other editors — each person who runs the script approves it for themselves — and you're asked again whenever the script changes, because anyone who can edit the file can edit its script. Allow only scripts written by people you trust.

Simple triggers and custom functions can't use these services at all. Nobody is there to approve them, so the call fails with "You do not have permission to call …".

Limits

• 30 seconds per run. A run that takes longer is stopped with "Exceeded maximum execution time". Waiting for someone to answer an alert or a prompt doesn't count; each custom-function call also gets 30 seconds.
• Scripts only run for people who can edit, in their own browser. Nothing runs for viewers, commenters or people with a view link, and nothing runs when nobody has the file open.
• MailApp: 50 emails a day per mailbox, 50 recipients each, with your mailbox's usual sending limits on top.
• UrlFetchApp: public web addresses only, over HTTP or HTTPS; each request has 10 seconds and a 5 MB response, and requests count toward your mailbox's 200 web fetches an hour, shared with the spreadsheet web data functions.
• Storage: properties up to 9 KB per value and 500 KB in total; cached values up to 100 KB, kept for up to 6 hours; a run writes at most 2,000 lines to the Execution log.

Why there are no time-driven triggers

On Google, a script can run every hour or every morning at 9 even when nobody has the file open, because Google runs it on its own servers. Scripts never run on our servers — only in the browser of an editor who has the file open. With nobody's browser open, there's nothing to run a script at a set time, so time-driven triggers aren't available. Asking for one says so: "Time-driven triggers are not available in SingleSign".

Add-ons

An add-on is a script you package once and use in any document or spreadsheet you can edit — your own tools, or ones your organization shares. Open Extensions ▸ Add-ons:

• Get add-ons lists add-ons you've published and the ones people in your organization have shared. Search, then Install.
• Manage add-ons turns installed add-ons on or off and uninstalls them. If you published one, you can also change who can see it, update its code from the current file, or delete it.
• Publish this script as an add-on (in the script's file) packages its code with a name, a short description and an icon, visible to Only me or My organization.

An installed add-on's menus appear under Extensions ▸ add-on name. For your safety:

• An add-on can't read or change a file until you've used it there — by choosing one of its menu items, or installing it from that file.
• It asks for your approval the first time it needs to send mail, fetch from the web or store data for you, and again whenever a new version is published.
• An add-on's custom functions can't be used in cells, and each file keeps its own copy of an add-on's stored properties.
• You can install up to 50 add-ons and publish up to 100.

Differences from Google Apps Script

• No time-driven or installable triggers. Only the simple triggers onOpen, onEdit and onSelectionChange exist (see above for why).
• No public Marketplace. Add-ons are shared privately or within your organization; there's no public store, no review process and no organization-wide install by an admin.
• Dialogs can't make web requests themselves. A dialog or sidebar can load libraries such as jQuery from cdnjs, jsDelivr, unpkg, Google Hosted Libraries or code.jquery.com, but it can't call fetch or XMLHttpRequest — use google.script.run to call a script function that uses UrlFetchApp.
• noReply keeps your address. Mail with noReply: true is still sent from your own mailbox, with replies directed to a no-reply address.
• One file per script. A script can't open, create or change other documents and spreadsheets, and there are no libraries or web apps; ScriptApp.getOAuthToken() isn't available.

Related

• Writer: documents
• Spreadsheets — including macros, for repeating steps without code.
• Formulas and functions reference
• Sharing, comments and version history