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 Modal Progress Dialog Block user interaction during long operations with a spinner that auto-closes. This is the recommended pattern 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 ( `

${ message }

` ) . 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 } Error Handling Always wrap external calls in try/catch. Return meaningful messages to dialogs. 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 ; // re-throw for dialog error handler } } 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 Common Pattern Index See references/patterns.md for complete code examples: Pattern When to Use Custom menus Adding actions to the spreadsheet toolbar Sidebar apps Forms and data entry panels Triggers Automated reactions to edits, time, or form submissions Email from sheets Sending reports, notifications, schedules PDF export Generating and emailing sheet as PDF Data validation Creating dropdowns from lists or ranges See references/recipes.md for complete automation recipes (archive rows, highlight duplicates, auto-number, dashboards). See references/quotas.md for execution limits, email quotas, and debugging tips.

google-apps-script

安装