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.