google-apps-script by jezweb/claude-skills
npx skills add https://github.com/jezweb/claude-skills --skill google-apps-script为 Google Sheets 和 Workspace 应用程序构建自动化脚本。脚本在 Google 的基础设施上服务器端运行,提供慷慨的免费额度。
询问用户希望自动化什么。常见场景:
遵循以下结构模板。每个脚本都需要一个头部注释、顶部的配置常量以及用于菜单设置的 onOpen() 函数。
所有脚本的安装方式相同:
onOpen 在页面加载时运行)每个用户在首次运行时都会看到一个 Google OAuth 同意屏幕。对于未经验证的脚本(大多数内部脚本),用户必须点击:
广告位招租
在这里展示您的产品或服务
触达数万 AI 开发者,精准高效
高级 > 转到 [项目名称](不安全)> 允许
这是每个用户只需执行一次的步骤。在你的输出中提醒用户注意这一点。
每个脚本都应遵循此模式:
/**
* [项目名称] - [简要描述]
*
* [它做什么,主要功能]
*
* 安装:扩展程序 > Apps Script > 粘贴此代码 > 保存 > 重新加载表格
*/
// --- 配置 ---
const SOME_SETTING = 'value';
// --- 菜单设置 ---
function onOpen() {
const ui = SpreadsheetApp.getUi();
ui.createMenu('我的菜单')
.addItem('执行操作', 'myFunction')
.addSeparator()
.addSubMenu(ui.createMenu('更多选项')
.addItem('选项 A', 'optionA'))
.addToUi();
}
// --- 函数 ---
function myFunction() {
// 实现
}
以 _(下划线)结尾的函数是私有函数,无法通过 google.script.run 从客户端 HTML 调用。这是一个静默失败——调用根本不会工作,且没有错误。
// 错误 - 对话框无法调用此函数,静默失败
function doWork_() { return 'done'; }
// 正确 - 对话框可以调用此函数
function doWork() { return 'done'; }
同样适用于:菜单项函数引用必须是作为字符串的公共函数名。
批量读写数据,切勿逐个单元格操作。性能差异可达 70 倍。
// 慢(100x100 需要 70 秒)- 一次读取一个单元格
for (let i = 1; i <= 100; i++) {
const val = sheet.getRange(i, 1).getValue();
}
// 快(1 秒)- 一次性读取所有数据
const allData = sheet.getRange(1, 1, 100, 1).getValues();
for (const row of allData) {
const val = row[0];
}
始终使用 getRange().getValues() / setValues() 进行批量读写。
V8 是唯一的运行时(Rhino 已于 2026 年 1 月移除)。支持现代 JavaScript:const、let、箭头函数、模板字面量、解构、类、async/生成器。
不可用(使用 Apps Script 替代方案):
| 缺失的 API | Apps Script 替代方案 |
|---|---|
setTimeout / setInterval | Utilities.sleep(ms)(阻塞式) |
fetch | UrlFetchApp.fetch() |
FormData | 手动构建有效载荷 |
URL | 字符串操作 |
crypto | Utilities.computeDigest() / Utilities.getUuid() |
在修改表格的函数返回之前调用 SpreadsheetApp.flush(),特别是当从 HTML 对话框调用时。如果不这样做,当对话框显示“完成”时,更改可能不可见。
| 功能 | 简单触发器(onEdit) | 可安装触发器 |
|---|---|---|
| 需要授权 | 否 | 是 |
| 发送邮件 | 否 | 是 |
| 访问其他文件 | 否 | 是 |
| URL 获取 | 否 | 是 |
| 打开对话框 | 否 | 是 |
| 运行身份 | 活跃用户 | 触发器创建者 |
轻量级响应使用简单触发器。当需要邮件、外部 API 或跨文件访问时,使用可安装触发器(通过 ScriptApp.newTrigger())。
在单元格中用作 =MY_FUNCTION() 的函数有严格的限制:
/**
* 计算自定义内容。
* @param {string} input 输入值
* @return {string} 结果
* @customfunction
*/
function MY_FUNCTION(input) {
// 可以使用:基本 JS、Utilities、CacheService
// 不能使用:MailApp、UrlFetchApp、SpreadsheetApp.getUi()、触发器
return input.toUpperCase();
}
@customfunction JSDoc 标签在长时间操作期间使用自动关闭的旋转器阻止用户交互。这是任何耗时超过几秒的操作的推荐模式。
模式:菜单函数 > showProgress() > 对话框调用操作函数 > 自动关闭
function showProgress(message, serverFn) {
const html = HtmlService.createHtmlOutput(`
<!DOCTYPE html>
<html>
<head>
<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>
</head>
<body>
<div class="spinner" id="spinner"></div>
<div class="message" id="msg">${message}</div>
<script>
google.script.run
.withSuccessHandler(function(result) {
document.getElementById('spinner').style.display = 'none';
var m = document.getElementById('msg');
m.className = 'message done';
m.innerText = '完成!' + (result || '');
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 = '错误:' + err.message;
setTimeout(function() { google.script.host.close(); }, 3000);
})
.${serverFn}();
</script>
</body>
</html>
`).setWidth(320).setHeight(140);
SpreadsheetApp.getUi().showModalDialog(html, '处理中...');
}
// 菜单调用此包装函数
function menuDoWork() {
showProgress('正在处理数据...', 'doTheWork');
}
// 必须是公共函数(无下划线)以便对话框调用
function doTheWork() {
// ... 执行操作 ...
SpreadsheetApp.flush();
return '已处理 50 行'; // 在成功消息中显示
}
始终将外部调用包装在 try/catch 中。向对话框返回有意义的错误信息。
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 返回 ' + response.getResponseCode());
}
return JSON.parse(response.getContentText());
} catch (e) {
Logger.log('错误:' + e.message);
throw e; // 重新抛出以便对话框错误处理程序捕获
}
}
| 错误 | 修复方法 |
|---|---|
| 对话框无法调用函数 | 从函数名中移除末尾的 _ |
| 处理大量数据时脚本缓慢 | 使用 getValues()/setValues() 批量操作 |
| 对话框关闭后更改不可见 | 在返回前添加 SpreadsheetApp.flush() |
onEdit 无法发送邮件 | 通过 ScriptApp.newTrigger() 使用可安装触发器 |
| 自定义函数超时 | 30 秒限制——简化或移至常规函数 |
找不到 setTimeout | 使用 Utilities.sleep(ms)(阻塞式) |
| 脚本超过 6 分钟 | 分块处理,对批次使用时间驱动触发器 |
| 授权弹窗不出现 | 用户必须点击 高级 > 转到(不安全)> 允许 |
完整代码示例请参阅 references/patterns.md:
| 模式 | 使用场景 |
|---|---|
| 自定义菜单 | 向表格工具栏添加操作 |
| 侧边栏应用 | 表单和数据输入面板 |
| 触发器 | 对编辑、时间或表单提交的自动化响应 |
| 从表格发送邮件 | 发送报告、通知、日程安排 |
| PDF 导出 | 生成表格并作为 PDF 邮件发送 |
| 数据验证 | 从列表或范围创建下拉菜单 |
完整自动化配方(归档行、高亮重复项、自动编号、仪表板)请参阅 references/recipes.md。
执行限制、邮件配额和调试技巧请参阅 references/quotas.md。
每周安装次数
490
代码仓库
GitHub 星标数
643
首次出现
2026 年 2 月 18 日
安全审计
安装于
opencode445
github-copilot440
codex440
gemini-cli439
cursor435
kimi-cli430
Build automation scripts for Google Sheets and Workspace apps. Scripts run server-side on Google's infrastructure with a generous free tier.
Ask what the user wants automated. Common scenarios:
Follow the structure template below. Every script needs a header comment, configuration constants at top, and onOpen() for menu setup.
All scripts install the same way:
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.
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
}
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.
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 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 |
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."
| 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.
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();
}
@customfunction JSDoc tagBlock 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(`
<!DOCTYPE html>
<html>
<head>
<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>
</head>
<body>
<div class="spinner" id="spinner"></div>
<div class="message" id="msg">${message}</div>
<script>
google.script.run
.withSuccessHandler(function(result) {
document.getElementById('spinner').style.display = 'none';
var m = document.getElementById('msg');
m.className = 'message done';
m.innerText = 'Done! ' + (result || '');
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>
</body>
</html>
`).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
}
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
}
}
| 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 |
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.
Weekly Installs
490
Repository
GitHub Stars
643
First Seen
Feb 18, 2026
Security Audits
Gen Agent Trust HubPassSocketPassSnykPass
Installed on
opencode445
github-copilot440
codex440
gemini-cli439
cursor435
kimi-cli430
Supabase Postgres 最佳实践指南 - 8大类别性能优化规则与SQL示例
54,100 周安装
Utilities.computeDigest() / Utilities.getUuid() |
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 |