/google-apps-script

name: google-apps-script
description: "Build Google Apps Script automation for Sheets and Workspace. Custom menus, triggers (onEdit / time-driven / form submit), dialogs, sidebars, email batches, PDF export, external API. Use whenever the user wants to automate a Google Sheet, build a Sheets menu / sidebar / dialog, hit a Sheets row from email or a webhook, schedule a Sheets workflow, or asks 'how do I script this in Sheets'."
compatibility: claude-code-only

Google Apps Script

Build automation scripts for Google Sheets and Workspace apps. Scripts run server-side on Google's infrastructure with a generous free tier.

What You Produce

• Apps Script code pasted into Extensions > Apps Script
• Custom menus, dialogs, sidebars
• Automated triggers (on edit, time-driven, form submit)
• Email notifications, PDF exports, API integrations

Workflow

Step 1: Understand the Automation

Ask what the user wants automated. Common scenarios:

• Custom menu with actions (report generation, data processing)
• Auto-triggered behaviour (on edit, on form submit, scheduled)
• Sidebar app for data entry
• Email notifications from sheet data
• PDF export and distribution

Step 2: Generate the Script

Follow the structure template below. Every script needs a header comment, configuration constants at top, and `onOpen()` for menu setup.

Step 3: Provide Installation Instructions

All scripts install the same way: 1. Open the Google Sheet 2. **Extensions > Apps Script** 3. Delete any existing code in the editor 4. Paste the script 5. Click **Save** 6. Close the Apps Script tab 7. **Reload the spreadsheet** (onOpen runs on page load)

Step 4: First-Time Authorisation

Each user gets a Google OAuth consent screen on first run. For unverified scripts (most internal scripts), users must click:

**Advanced > Go to [Project Name] (unsafe) > Allow**

This is a one-time step per user. Warn users about this in your output.

---

Script Structure Template

Every script should follow this pattern:

/**
 * [Project Name] - [Brief Description]
 *
 * [What it does, key features]
 *
 * INSTALL: Extensions > Apps Script > paste this > Save > Reload sheet
 */

// --- CONFIGURATION ---
const SOME_SETTING = 'value';

// --- MENU SETUP ---
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('My Menu')
    .addItem('Do Something', 'myFunction')
    .addSeparator()
    .addSubMenu(ui.createMenu('More Options')
      .addItem('Option A', 'optionA'))
    .addToUi();
}

// --- FUNCTIONS ---
function myFunction() {
  // Implementation
}

---

Critical Rules

Public vs Private Functions

Functions ending with `_` (underscore) are **private** and CANNOT be called from client-side HTML via `google.script.run`. This is a silent failure -- the call simply doesn't work with no error.

// WRONG - dialog can't call this, fails silently
function doWork_() { return 'done'; }

// RIGHT - dialog can call this
function doWork() { return 'done'; }

**Also applies to**: Menu item function references must be public function names as strings.

Batch Operations (Critical for Performance)

Read/write data in bulk, never cell-by-cell. The difference is 70x.

// SLOW (70 seconds on 100x100) - reads one cell at a time
for (let i = 1; i <= 100; i++) {
  const val = sheet.getRange(i, 1).getValue();
}

// FAST (1 second) - reads all at once
const allData = sheet.getRange(1, 1, 100, 1).getValues();
for (const row of allData) {
  const val = row[0];
}

Always use `getRange().getValues()` / `setValues()` for bulk reads/writes.

V8 Runtime

V8 is the **only** runtime (Rhino was removed January 2026). Supports modern JavaScript: `const`, `let`, arrow functions, template literals, destructuring, classes, async/generators.

**NOT available** (use Apps Script alternatives):

| Missing API | Apps Script Alternative | |-------------|------------------------| | `setTimeout` / `setInterval` | `Utilities.sleep(ms)` (blocking) | | `fetch` | `UrlFetchApp.fetch()` | | `FormData` | Build payload manually | | `URL` | String manipulation | | `crypto` | `Utilities.computeDigest()` / `Utilities.getUuid()` |

Flush Before Returning

Call `SpreadsheetApp.flush()` before returning from functions that modify the sheet, especially when called from HTML dialogs. Without it, changes may not be visible when the dialog shows "Done."

Simple vs Installable Triggers

| Feature | Simple (`onEdit`) | Installable | |---------|-------------------|-------------| | Auth required | No | Yes | | Send email | No | Yes | | Access other files | No | Yes | | URL fetch | No | Yes | | Open dialogs | No | Yes | | Runs as | Active user | Trigger creator |

Use simple triggers for lightweight reactions. Use installable triggers (via `ScriptApp.newTrigger()`) when you need email, external APIs, or cross-file access.

Custom Spreadsheet Functions

Functions used as `=MY_FUNCTION()` in cells have strict limitations:

/**
 * Calculates something custom.
 * @param {string} input The input value
 * @return {string} The result
 * @customfunction
 */
function MY_FUNCTION(input) {
  // Can use: basic JS, Utilities, CacheService
  // CANNOT use: MailApp, UrlFetchApp, SpreadsheetApp.getUi(), triggers
  return input.toUpperCase();
}

• Must include `@customfunction` JSDoc tag
• 30-second execution limit (vs 6 minutes for regular functions)
• Cannot access services requiring authorisation

---

Quotas and Limits

| Resource | Free Account | Google Workspace | |----------|-------------|-----------------| | Script runtime | 6 min / execution | 6 min / execution | | Time-driven trigger runtime | 30 min | 30 min | | Triggers total daily runtime | 90 min | 6 hours | | Triggers total | 20 per user per script | 20 per user per script | | Email recipients/day | 100 | 1,500 | | URL Fetch calls/day | 20,000 | 100,000 | | Properties storage | 500 KB | 500 KB | | Custom function