artubss/SKILLS-CLAUDE-CODE / claude-skills-main/claude-skills-main/plugins/integrations/skills/google-apps-script
google-apps-script
UnverifiedBuild 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'.
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
@customfunctionJSDoc 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 |
Modal Progress Dialog
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();
}
Sidebar Apps
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
- 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 —
onEdittrigger to auto-number column A when column B is edited - Google Chat webhooks — POST to
chat.googleapis.comwith JSON payload
Related skills
| Skill | Stars | Updated | Works with | Status | Install |
|---|---|---|---|---|---|
| canvas-designanthropics/skillsCreate beautiful visual art in .png and .pdf documents using design philosophy. You should use this skill when the user asks to create a poster, piece of art, design, or other static piece. Create original visual designs, never copying existing artists' work to avoid copyright violations. | Stars: 196k | Updated | Claude CodeCodex | Verified | Install |
| docxanthropics/skillsUse this skill whenever the user wants to create, read, edit, or manipulate Word documents (.docx files) or Word templates (.dotx files). Triggers include: any mention of 'Word doc', 'word document', '.docx', '.dotx', or requests to produce professional documents with formatting like tables of contents, headings, page numbers, or letterheads. Also use when extracting or reorganizing content from .docx or .dotx files, inserting or replacing images in documents, performing find-and-replace in Word files, working with tracked changes or comments, or converting content into a polished Word document. If the user asks for a 'report', 'memo', 'letter', 'template', or similar deliverable as a Word or .docx file, use this skill. Do NOT use for PDFs, spreadsheets, Google Docs, or general coding tasks unrelated to document generation. | Stars: 196k | Updated | Claude CodeCodex | Verified | Install |
| pdfanthropics/skillsUse this skill whenever the user wants to do anything with PDF files. This includes reading or extracting text/tables from PDFs, combining or merging multiple PDFs into one, splitting PDFs apart, rotating pages, adding watermarks, creating new PDFs, filling PDF forms, encrypting/decrypting PDFs, extracting images, and OCR on scanned PDFs to make them searchable. If the user mentions a .pdf file or asks to produce one, use this skill. | Stars: 196k | Updated | Claude CodeCodex | Verified | Install |
| pptxanthropics/skillsUse this skill any time a .pptx or .potx file is involved in any way — as input, output, or both. This includes: creating slide decks, pitch decks, or presentations; reading, parsing, or extracting text from any .pptx or .potx file (even if the extracted content will be used elsewhere, like in an email or summary); editing, modifying, or updating existing presentations; combining or splitting slide files; working with templates (.potx), layouts, speaker notes, or comments. Trigger whenever the user mentions "deck," "slides," "presentation," or references a .pptx or .potx filename, regardless of what they plan to do with the content afterward. If a .pptx or .potx file needs to be opened, created, or touched, use this skill. | Stars: 196k | Updated | Claude CodeCodex | Verified | Install |