google-apps-script

Google Apps Script

为 Google Sheets 和 Workspace 应用构建自动化脚本。脚本在 Google 的基础设施上于服务器端运行，并提供宽裕的免费额度。

你将产出什么

• 粘贴到 Extensions > Apps Script 中的 Apps Script 代码
• 自定义菜单、对话框、侧边栏
• 自动触发器（编辑时、定时、提交表单时）
• 电子邮件通知、PDF 导出、API 集成

工作流程

第 1 步：了解自动化需求

询问用户希望自动化什么。常见场景：

• 带操作项的自定义菜单（生成报告、处理数据）
• 自动触发的行为（编辑时、提交表单时、按计划）
• 用于录入数据的侧边栏应用
• 根据工作表数据发送电子邮件通知
• 导出并分发 PDF

第 2 步：生成脚本

遵循下面的结构模板。每个脚本都需要包含头部注释、位于顶部的配置常量，以及用于设置菜单的 onOpen()。

第 3 步：提供安装说明

所有脚本都以相同方式安装：

1. 打开 Google Sheet
2. Extensions > Apps Script
3. 删除编辑器中的所有现有代码
4. 粘贴脚本
5. 点击 Save
6. 关闭 Apps Script 标签页
7. 重新加载电子表格（页面加载时会运行 onOpen）

第 4 步：首次授权

每位用户首次运行时都会看到 Google OAuth 同意屏幕。对于未经验证的脚本（大多数内部脚本），用户必须点击：

Advanced > Go to [Project Name] (unsafe) > Allow

每位用户只需执行一次。在输出中提醒用户这一点。

---

脚本结构模板

每个脚本都应遵循以下模式：

/**
 * [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
}

---

重要规则

公有函数与私有函数

以 _（下划线）结尾的函数是私有函数，无法通过 google.script.run 从客户端 HTML 调用。这会静默失败——调用只是不起作用，也不会报错。

// WRONG - dialog can't call this, fails silently
function doWork_() { return 'done'; }

// RIGHT - dialog can call this
function doWork() { return 'done'; }

同样适用于：菜单项的函数引用必须是字符串形式的公有函数名。

批量操作（对性能至关重要）

批量读取/写入数据，绝不要逐个单元格操作。两者相差 70 倍。

// 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];
}

批量读取/写入时始终使用 getRange().getValues() / setValues()。

V8 运行时

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()

返回前刷新

对于会修改工作表的函数，尤其是从 HTML 对话框调用的函数，在返回前调用 SpreadsheetApp.flush()。否则，对话框显示“完成”时，更改可能尚不可见。

简单触发器与可安装触发器

特性	简单触发器（onEdit）	可安装触发器
是否需要授权	否	是
发送电子邮件	否	是
访问其他文件	否	是
URL 获取	否	是
打开对话框	否	是
运行身份	活动用户	触发器创建者

轻量级响应使用简单触发器。需要发送电子邮件、调用外部 API 或访问其他文件时，使用可安装触发器（通过 ScriptApp.newTrigger()）。

自定义电子表格函数

在单元格中以 =MY_FUNCTION() 形式使用的函数有严格限制：

/**
 * 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 标签
• 执行时间限制为 30 秒（普通函数为 6 分钟）
• 无法访问需要授权的服务

---

配额和限制

资源	免费账户	Google Workspace
脚本运行时间	每次执行 6 min	每次执行 6 min
时间驱动触发器运行时间	30 min	30 min
触发器每日总运行时间	90 min	6 hours
触发器总数	每位用户每个脚本 20 个	每位用户每个脚本 20 个
每日电子邮件收件人数	100	1,500
每日 URL Fetch 调用次数	20,000	100,000
属性存储空间	500 KB	500 KB
自定义函数运行时间	30 seconds	30 seconds
同时执行次数	30	30

---

模态进度对话框

在长时间操作期间阻止用户交互，并显示一个会自动关闭的旋转指示器。适用于任何耗时超过几秒的操作。

模式：菜单函数 > showProgress() > 对话框调用操作函数 > 自动关闭

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
}

---

常见模式

Toast 通知

SpreadsheetApp.getActiveSpreadsheet().toast('Operation complete!', 'Title', 5);
// Arguments: message, title, duration in seconds (-1 = until dismissed)

警告和提示对话框

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();
}

侧边栏应用

右侧的 HTML 面板。使用 google.script.run 调用服务端函数。

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]);
}

触发器

onEdit（简单触发器） -- 权限有限，但无需身份验证：

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());
}

可安装触发器 -- 通过脚本创建，手动运行一次设置函数：

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();
}

从 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: '[email protected]',
    subject: 'Schedule - Week ' + sheet.getName(), htmlBody: body });
}

PDF 导出

URL 构造方式不太直观——导出参数没有文档说明：

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: '[email protected]', subject: 'Weekly Report PDF',
    body: 'Attached.', attachments: [blob] });
}

外部 API 调用

// 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());
}

数据验证下拉列表

// 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);

属性服务（持久存储）

三个作用域：PropertiesService.getScriptProperties()（共享）、.getUserProperties()（每位用户）和 .getDocumentProperties()（每个电子表格）。都使用 .setProperty(key, value) / .getProperty(key)。限制为 500 KB。

---

实用示例

自动归档已完成的行

将状态为“Complete”的行移至 Archive 工作表。从底部向上处理，以避免行索引发生偏移。

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();
}

检测并突出显示重复项

模式：使用 getValues() 读取列，在对象中跟踪已出现的值，并使用 setBackground('#f4cccc') 为原始行和重复行设置背景色。通过一次 getValues() 调用处理所有数据，然后逐行设置背景色（对于分散的高亮，这是不可避免的）。

批量发送电子邮件

关键模式：发送前检查 MailApp.getRemainingDailyQuota()，逐行标记状态，并为每次发送操作添加 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();
}

摘要仪表板生成器

模式：循环遍历带编号的每周工作表（01-52），读取每张工作表中的摘要单元格，并将汇总行写入 Summary 工作表。使用 ss.getSheetByName(tabName) 进行遍历；如果 Summary 工作表不存在，则使用 ss.insertSheet('Summary') 创建；最后调用 summary.autoResizeColumns()，并在返回前调用 flush()。

---

错误处理

始终使用 try/catch 包装外部调用。使用 muteHttpExceptions: true 自行处理 HTTP 错误。重新抛出错误，以供对话框错误处理程序处理。

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; }
}

---

防止错误

错误	修复方法
对话框无法调用函数	从函数名称中删除末尾的 _
脚本处理大量数据时速度慢	使用 getValues()/setValues() 批量操作
对话框关闭后看不到更改	在返回前添加 SpreadsheetApp.flush()
onEdit 无法发送电子邮件	通过 ScriptApp.newTrigger() 使用可安装触发器
自定义函数超时	限制为 30s -- 简化函数或改用常规函数
找不到 setTimeout	使用 Utilities.sleep(ms)（阻塞式）
脚本运行时间超过 6 min	拆分成多个批次，并使用时间驱动触发器处理批次
未显示身份验证弹出窗口	用户必须点击 Advanced > Go to (unsafe) > Allow

调试

• Logger.log() / console.log() -- 在 Apps Script 编辑器中选择 View > Execution Log
• 手动运行 -- 在编辑器下拉列表中选择函数 > Run
• Executions 标签页 -- 显示所有近期运行记录及错误和堆栈跟踪
• 触发器失败 -- script.google.com > My Projects > Executions
• 部署前务必在工作表副本上测试

部署检查清单

• 从 HTML 对话框调用的所有函数都是公开的（末尾没有下划线）
• 修改数据的函数返回前调用了 SpreadsheetApp.flush()
• 对外部 API 调用和 MailApp 添加错误处理（try/catch）
• 在文件顶部定义配置常量
• 添加包含安装说明的头部注释
• 在电子表格副本上进行测试
• 考虑多用户行为（不同权限、不同的活动工作表）
• 长时间运行的操作使用模态进度对话框
• 不要硬编码工作表名称——使用配置常量
• 批量发送前检查电子邮件配额

---

必要时根据 Apps Script 文档重建

• 显示/隐藏行和列 — sheet.hideRows()、showRows()、isRowHiddenByUser()
• 格式设置 — setBackground()、setFontWeight()、setBorder()、setNumberFormat()、条件格式
• 数据保护 — range.protect()、setUnprotectedRanges()、编辑者管理
• 多个工作表 — getSheetByName()、循环遍历编号的标签页、copyTo()、insertSheet()
• 行自动编号 — 使用 onEdit 触发器，在编辑 B 列时自动为 A 列编号
• Google Chat Webhook — 使用 JSON 载荷向 chat.googleapis.com 发送 POST 请求