Google Apps Script

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  showProgress() > dialog calls action function > auto-close**

```javascript
function showProgress(message, serverFn) {
  const html = HtmlService.createHtmlOutput(`
    
      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; }
    
    
    ${message}
    
      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}();
    
  `).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();
}

Sidebar Apps

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

function showSidebar() {
  const html = HtmlService.createHtmlOutput(`
    Quick Entry
    CraigSteve
    
    Add Job
    
      function submit() {
        google.script.run.withSuccessHandler(function() { alert('Added!'); })
          .addJob(document.getElementById('worker').value,
                  document.getElementById('suburb').value);
      }
    
  `).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 = 'Weekly Schedule';
  body += 'JobSuburbTimePrice';
  for (const row of data) {
    if (row[0]) body += '' + row.map(c => '' + c + '').join('') + '';
  }
  body += '';
  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 Hi ' + name + ',Here is your update...' });
      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

• [ ] All functions called from HTML dialogs are public (no trailing underscore)
• [ ] SpreadsheetApp.flush() called before returning from modifying functions
• [ ] Error handling (try/catch) around external API calls and MailApp
• [ ] Configuration constants at the top of the file
• [ ] Header comment with install instructions
• [ ] Tested on a copy of the sheet
• [ ] Considered multi-user behaviour (different permissions, different active sheet)
• [ ] Long operations use modal progress dialogs
• [ ] No hardcoded sheet names -- use configuration constants
• [ ] Checked email quota before batch sends

---

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

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: jezweb
• Source: jezweb/claude-skills
• License: MIT
• Homepage: https://jezweb.com.au

Install and usage instructions live in the source repository linked above.