google-apps-script

OverviewHistoryStatsSecurity

npx skills add jezweb/claude-skills --skill google-apps-script

Documentation

SKILL.md

npx skills add ...

Documentation

SKILL.md

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:

Open the Google Sheet
Extensions > Apps Script
Delete any existing code in the editor
Paste the script
Click Save
Close the Apps Script tab
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 runtime	30 seconds	30 seconds
Simultaneous executions	30	30

Block user interaction during long operations with a spinner that auto-closes. Use for any operation taking more than a few seconds.

Pattern: menu function > showProgress() > dialog calls action function > auto-close

function showProgress(message, serverFn) {
  const html = HtmlService.createHtmlOutput(`
    <style>
      body { font-family: 'Google Sans', Arial, sans-serif; display: flex;
        flex-direction: column; align-items: center; justify-content: center;
        height: 100%; margin: 0; padding: 20px; box-sizing: border-box; }
      .spinner { width: 36px; height: 36px; border: 4px solid #e0e0e0;
        border-top: 4px solid #1a73e8; border-radius: 50%;
        animation: spin 0.8s linear infinite; margin-bottom: 16px; }
      @keyframes spin { to { transform: rotate(360deg); } }
      .message { font-size: 14px; color: #333; text-align: center; }
      .done { color: #1e8e3e; font-weight: 500; }
      .error { color: #d93025; font-weight: 500; }
    </style>
    <div class="spinner" id="spinner"></div>
    <div class="message" id="msg">${message}</div>
    <script>
      google.script.run
        .withSuccessHandler(function(r) {
          document.getElementById('spinner').style.display = 'none';
          var m = document.getElementById('msg');
          m.className = 'message done';
          m.innerText = 'Done! ' + (r || '');
          setTimeout(function() { google.script.host.close(); }, 1200);
        })
        .withFailureHandler(function(err) {
          document.getElementById('spinner').style.display = 'none';
          var m = document.getElementById('msg');
          m.className = 'message error';
          m.innerText = 'Error: ' + err.message;
          setTimeout(function() { google.script.host.close(); }, 3000);
        })
        .${serverFn}();
    </script>
  `).setWidth(320).setHeight(140);
  SpreadsheetApp.getUi().showModalDialog(html, 'Working...');
}
 
// Menu calls this wrapper
function menuDoWork() {
  showProgress('Processing data...', 'doTheWork');
}
 
// MUST be public (no underscore) for the dialog to call it
function doTheWork() {
  // ... do the work ...
  SpreadsheetApp.flush();
  return 'Processed 50 rows';  // shown in success message
}

Common Patterns

Toast Notifications

SpreadsheetApp.getActiveSpreadsheet().toast('Operation complete!', 'Title', 5);
// Arguments: message, title, duration in seconds (-1 = until dismissed)

Alert and Prompt Dialogs

const ui = SpreadsheetApp.getUi();
 
// Yes/No confirmation
const response = ui.alert('Delete this data?', 'This cannot be undone.',
  ui.ButtonSet.YES_NO);
if (response === ui.Button.YES) { /* proceed */ }
 
// Prompt for input
const result = ui.prompt('Enter your name:', ui.ButtonSet.OK_CANCEL);
if (result.getSelectedButton() === ui.Button.OK) {
  const name = result.getResponseText();
}

HTML panel on the right. Use google.script.run to call server functions.

function showSidebar() {
  const html = HtmlService.createHtmlOutput(`
    <h3>Quick Entry</h3>
    <select id="worker"><option>Craig</option><option>Steve</option></select>
    <input id="suburb" placeholder="Suburb">
    <button onclick="submit()">Add Job</button>
    <script>
      function submit() {
        google.script.run.withSuccessHandler(function() { alert('Added!'); })
          .addJob(document.getElementById('worker').value,
                  document.getElementById('suburb').value);
      }
    </script>
  `).setTitle('Job Entry').setWidth(300);
  SpreadsheetApp.getUi().showSidebar(html);
}
 
function addJob(worker, suburb) { // MUST be public (no underscore)
  SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().appendRow([new Date(), worker, suburb]);
}

Triggers

onEdit (simple trigger) -- limited permissions but no auth needed:

function onEdit(e) {
  const sheet = e.source.getActiveSheet();
  if (sheet.getName() !== 'Data') return;
  if (e.range.getColumn() !== 3) return;
  // Auto-timestamp when column C is edited
  sheet.getRange(e.range.getRow(), 4).setValue(new Date());
}

Installable triggers -- create via script, run setup function once manually:

function createTriggers() {
  // Time-driven: run every day at 8am
  ScriptApp.newTrigger('dailyReport')
    .timeBased().atHour(8).everyDays(1).create();
 
  // On edit with full permissions (can send email, fetch URLs)
  ScriptApp.newTrigger('onEditFull')
    .forSpreadsheet(SpreadsheetApp.getActive()).onEdit().create();
 
  // On form submit
  ScriptApp.newTrigger('onFormSubmit')
    .forSpreadsheet(SpreadsheetApp.getActive()).onFormSubmit().create();
}

Email from Sheets

function emailWeeklySchedule() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const data = sheet.getRange('A2:E10').getDisplayValues();
  let body = '<h2>Weekly Schedule</h2><table border="1" cellpadding="8">';
  body += '<tr><th>Job</th><th>Suburb</th><th>Time</th><th>Price</th></tr>';
  for (const row of data) {
    if (row[0]) body += '<tr>' + row.map(c => '<td>' + c + '</td>').join('') + '</tr>';
  }
  body += '</table>';
  MailApp.sendEmail({ to: 'worker@example.com',
    subject: 'Schedule - Week ' + sheet.getName(), htmlBody: body });
}

PDF Export

Non-obvious URL construction -- export parameters are undocumented:

function exportSheetAsPdf() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const url = ss.getUrl().replace(/\/edit.*$/, '')
    + '/export?exportFormat=pdf&format=pdf&size=A4&portrait=true'
    + '&fitw=true&sheetnames=false&printtitle=false&gridlines=false'
    + '&gid=' + ss.getActiveSheet().getSheetId();
  const blob = UrlFetchApp.fetch(url, {
    headers: { 'Authorization': 'Bearer ' + ScriptApp.getOAuthToken() }
  }).getBlob().setName('report.pdf');
  MailApp.sendEmail({ to: 'boss@example.com', subject: 'Weekly Report PDF',
    body: 'Attached.', attachments: [blob] });
}

External API Calls

// GET
function fetchData() {
  const r = UrlFetchApp.fetch('https://api.example.com/data', {
    headers: { 'Authorization': 'Bearer ' + getApiKey() } });
  return JSON.parse(r.getContentText());
}
 
// POST (muteHttpExceptions to handle errors yourself)
function postData(payload) {
  const r = UrlFetchApp.fetch('https://api.example.com/submit', {
    method: 'post', contentType: 'application/json',
    payload: JSON.stringify(payload), muteHttpExceptions: true });
  if (r.getResponseCode() !== 200) throw new Error('API error: ' + r.getContentText());
  return JSON.parse(r.getContentText());
}

Data Validation Dropdowns

// Dropdown from list
const rule = SpreadsheetApp.newDataValidation()
  .requireValueInList(['Option A', 'Option B', 'Option C'], true)
  .setAllowInvalid(false).setHelpText('Select an option').build();
sheet.getRange('C3:C50').setDataValidation(rule);
 
// Dropdown from range (e.g. a Lookups sheet)
const rule2 = SpreadsheetApp.newDataValidation()
  .requireValueInRange(ss.getSheetByName('Lookups').getRange('A1:A100')).build();
sheet.getRange('B3:B50').setDataValidation(rule2);

Properties Service (Persistent Storage)

Three scopes: PropertiesService.getScriptProperties() (shared), .getUserProperties() (per user), .getDocumentProperties() (per spreadsheet). All use .setProperty(key, value) / .getProperty(key). 500 KB limit.

Recipes

Auto-Archive Completed Rows

Move rows with "Complete" status to an Archive sheet. Processes bottom-up to avoid shifting row indices.

function archiveCompleted() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const source = ss.getSheetByName('Active');
  const archive = ss.getSheetByName('Archive');
  const data = source.getDataRange().getValues();
  const statusCol = 4; // column E (0-indexed)
 
  for (let i = data.length - 1; i >= 1; i--) {
    if (data[i][statusCol] === 'Complete') {
      archive.appendRow(data[i]);
      source.deleteRow(i + 1); // +1 for 1-indexed rows
    }
  }
  SpreadsheetApp.flush();
}

Duplicate Detection and Highlighting

Pattern: read column with getValues(), track seen values in an object, highlight both the original and duplicate rows with setBackground('#f4cccc'). Process all data in one getValues() call, then set backgrounds individually (unavoidable for scattered highlights).

Batch Email Sender

Key pattern: check MailApp.getRemainingDailyQuota() before sending, mark status per row, wrap each send in try/catch.

function sendBatchEmails() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Recipients');
  const data = sheet.getRange('A2:C' + sheet.getLastRow()).getValues(); // Email, Name, Status
  const remaining = MailApp.getRemainingDailyQuota();
  if (remaining < data.length) {
    SpreadsheetApp.getUi().alert('Only ' + remaining + ' emails left. Need ' + data.length);
    return;
  }
  let sent = 0;
  for (let i = 0; i < data.length; i++) {
    const [email, name, status] = data[i];
    if (!email || status === 'Sent') continue;
    try {
      MailApp.sendEmail({ to: email, subject: 'Your Weekly Update',
        htmlBody: '<p>Hi ' + name + ',</p><p>Here is your update...</p>' });
      sheet.getRange(i + 2, 3).setValue('Sent'); sent++;
    } catch (e) { sheet.getRange(i + 2, 3).setValue('Error: ' + e.message); }
  }
  SpreadsheetApp.flush();
}

Summary Dashboard Generator

Pattern: loop numbered weekly tabs (01-52), read summary cells from each, write aggregated rows into a Summary sheet. Use ss.getSheetByName(tabName) to iterate, ss.insertSheet('Summary') if it doesn't exist, summary.autoResizeColumns() at end, flush() before return.

Error Handling

Always wrap external calls in try/catch. Use muteHttpExceptions: true to handle HTTP errors yourself. Re-throw for dialog error handlers.

function fetchExternalData() {
  try {
    const response = UrlFetchApp.fetch('https://api.example.com/data', {
      headers: { 'Authorization': 'Bearer ' + getApiKey() },
      muteHttpExceptions: true
    });
    if (response.getResponseCode() !== 200)
      throw new Error('API returned ' + response.getResponseCode());
    return JSON.parse(response.getContentText());
  } catch (e) { Logger.log('Error: ' + e.message); throw e; }
}

Error Prevention

Mistake	Fix
Dialog can't call function	Remove trailing `_` from function name
Script is slow on large data	Use `getValues()`/`setValues()` batch operations
Changes not visible after dialog	Add `SpreadsheetApp.flush()` before return
`onEdit` can't send email	Use installable trigger via `ScriptApp.newTrigger()`
Custom function times out	30s limit -- simplify or move to regular function
`setTimeout` not found	Use `Utilities.sleep(ms)` (blocking)
Script exceeds 6 min	Break into chunks, use time-driven trigger for batches
Auth popup doesn't appear	User must click Advanced > Go to (unsafe) > Allow

Debugging

Logger.log() / console.log() -- View > Execution Log in Apps Script editor
Run manually -- select function in editor dropdown > Run
Executions tab -- shows all recent runs with errors and stack traces
Trigger failures -- script.google.com > My Projects > Executions
Always test on a copy of the sheet before deploying

Deployment Checklist

Reconstruct from Apps Script docs if needed

Row/Column show/hide — sheet.hideRows(), showRows(), isRowHiddenByUser()
Formatting — setBackground(), setFontWeight(), setBorder(), setNumberFormat(), conditional formatting
Data protection — range.protect(), setUnprotectedRanges(), editor management
Multiple sheets — getSheetByName(), looping numbered tabs, copyTo(), insertSheet()
Auto-numbering rows — onEdit trigger to auto-number column A when column B is edited
Google Chat webhooks — POST to chat.googleapis.com with JSON payload

google-apps-script

Documentation

Documentation

google-apps-script

Documentation

Google Apps Script

What You Produce

Workflow

Step 1: Understand the Automation

Step 2: Generate the Script

Step 3: Provide Installation Instructions

Step 4: First-Time Authorisation

Script Structure Template

Critical Rules

Public vs Private Functions

Batch Operations (Critical for Performance)

V8 Runtime

Flush Before Returning

Simple vs Installable Triggers

Custom Spreadsheet Functions

Quotas and Limits

Modal Progress Dialog

Common Patterns

Toast Notifications

Alert and Prompt Dialogs

Sidebar Apps

Triggers

Email from Sheets

PDF Export

External API Calls

Data Validation Dropdowns

Properties Service (Persistent Storage)

Recipes

Auto-Archive Completed Rows

Duplicate Detection and Highlighting

Batch Email Sender

Summary Dashboard Generator

Error Handling

Error Prevention

Debugging

Deployment Checklist

Reconstruct from Apps Script docs if needed

Documentation

Documentation

Google Apps Script

What You Produce

Workflow

Step 1: Understand the Automation

Step 2: Generate the Script

Step 3: Provide Installation Instructions

Step 4: First-Time Authorisation

Script Structure Template

Critical Rules

Public vs Private Functions

Batch Operations (Critical for Performance)

V8 Runtime

Flush Before Returning

Simple vs Installable Triggers

Custom Spreadsheet Functions

Quotas and Limits

Modal Progress Dialog

Common Patterns

Toast Notifications

Alert and Prompt Dialogs

Sidebar Apps

Triggers

Email from Sheets

PDF Export

External API Calls

Data Validation Dropdowns

Properties Service (Persistent Storage)

Recipes

Auto-Archive Completed Rows

Duplicate Detection and Highlighting

Batch Email Sender

Summary Dashboard Generator

Error Handling

Error Prevention

Debugging

Deployment Checklist