google-apps-script

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

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.

为Google表格和Workspace应用构建自动化脚本。脚本在Google的基础设施上运行于服务器端，且提供非常优惠的免费额度。

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

可粘贴至「扩展程序 > Apps Script」的Apps Script代码
自定义菜单、对话框、侧边栏
自动化触发器（编辑触发、时间驱动、表单提交触发）
邮件通知、PDF导出、API集成

Workflow

工作流程

Step 1: Understand the Automation

步骤1：明确自动化需求

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

询问用户需要实现的自动化场景。常见场景包括：

带操作项的自定义菜单（报表生成、数据处理）
自动触发行为（编辑时、表单提交时、定时触发）
用于数据录入的侧边栏应用
基于表格数据的邮件通知
PDF导出与分发

Step 2: Generate the Script

步骤2：生成脚本

Follow the structure template below. Every script needs a header comment, configuration constants at top, and

onOpen()

for menu setup.

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

onOpen()

函数。

Step 3: Provide Installation Instructions

步骤3：提供安装说明

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)

所有脚本的安装方式一致：

打开Google表格
扩展程序 > Apps Script
删除编辑器中所有现有代码
粘贴脚本
点击保存
关闭Apps Script标签页
重新加载电子表格（onOpen函数在页面加载时运行）

Step 4: First-Time Authorisation

步骤4：首次授权

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.

用户首次运行时会看到Google OAuth授权界面。对于未验证的脚本（大多数内部脚本），用户需要点击：

高级 > 前往[项目名称]（不安全） > 允许

这是每个用户仅需执行一次的步骤。请在输出中提醒用户注意这一点。

Script Structure Template

脚本结构模板

Every script should follow this pattern:

javascript

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

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

javascript

/**
 * [项目名称] - [简要描述]
 *
 * [功能介绍、核心特性]
 *
 * 安装方式：扩展程序 > 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() {
  // 实现逻辑
}

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.

javascript

// 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.

以下划线

结尾的函数为私有函数，无法通过客户端HTML的

google.script.run

调用。这种调用会静默失败——没有任何错误提示，只是无法正常工作。

javascript

// 错误示例 - 对话框无法调用此函数，静默失败
function doWork_() { return 'done'; }

// 正确示例 - 对话框可调用此函数
function doWork() { return 'done'; }

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

Batch Operations (Critical for Performance)

批量操作（性能关键）

Read/write data in bulk, never cell-by-cell. The difference is 70x.

javascript

// 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.

批量读写数据，绝不要逐个单元格操作。两者的性能差异可达70倍。

javascript

// 慢（100x100数据需70秒）- 逐个单元格读取
for (let i = 1; i <= 100; i++) {
  const val = sheet.getRange(i, 1).getValue();
}

// 快（100x100数据需1秒）- 一次性读取所有数据
const allData = sheet.getRange(1, 1, 100, 1).getValues();
for (const row of allData) {
  const val = row[0];
}

始终使用

getRange().getValues()

setValues()

进行批量读写。

V8 Runtime

V8运行时

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

V8是唯一的运行时环境（Rhino已于2026年1月移除）。支持现代JavaScript语法：

const

、

let

、箭头函数、模板字符串、解构赋值、类、async/生成器。

不支持的API（请使用Apps Script替代方案）：

缺失的API	Apps Script替代方案
`setTimeout` / `setInterval`	`Utilities.sleep(ms)` （阻塞式）
`fetch`	`UrlFetchApp.fetch()`
`FormData`	手动构建请求体
`URL`	字符串处理
`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."

在修改表格的函数返回前调用

SpreadsheetApp.flush()

，尤其是从HTML对话框调用的函数。如果不执行此操作，当对话框显示"完成"时，修改内容可能仍未显示。

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.

特性	简单触发器（ `onEdit` ）	可安装触发器
是否需要授权	否	是
能否发送邮件	否	是
能否访问其他文件	否	是
能否调用URL	否	是
能否打开对话框	否	是
运行身份	当前活跃用户	触发器创建者

轻量级响应场景使用简单触发器。当需要发送邮件、调用外部API或跨文件访问时，使用可安装触发器（通过

ScriptApp.newTrigger()

创建）。

Custom Spreadsheet Functions

自定义电子表格函数

Functions used as

=MY_FUNCTION()

in cells have strict limitations:

javascript

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

在单元格中以

=MY_FUNCTION()

形式使用的函数有严格限制：

javascript

/**
 * 自定义计算逻辑
 * @param {string} input 输入值
 * @return {string} 计算结果
 * @customfunction
 */
function MY_FUNCTION(input) {
  // 可使用：基础JS、Utilities、CacheService
  // 不可使用：MailApp、UrlFetchApp、SpreadsheetApp.getUi()、触发器
  return input.toUpperCase();
}

必须包含
```
@customfunction
```
JSDoc标签
执行时间限制为30秒（常规函数为6分钟）
无法访问需要授权的服务

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

javascript

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
}

在长时间操作期间使用自动关闭的加载动画阻止用户交互。对于任何耗时超过几秒的操作，这都是推荐的实现模式。

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

javascript

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行数据';  // 显示在成功提示中
}

Error Handling

错误处理

Always wrap external calls in try/catch. Return meaningful messages to dialogs.

javascript

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

始终将外部调用包裹在try/catch块中。向对话框返回有意义的错误信息。

javascript

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;  // 重新抛出错误，供对话框的错误处理器捕获
  }
}

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

常见问题	解决方法
对话框无法调用函数	移除函数名称末尾的下划线 `_`
大数据量下脚本运行缓慢	使用 `getValues()` / `setValues()` 批量操作
对话框关闭后修改内容未显示	在函数返回前添加 `SpreadsheetApp.flush()`
`onEdit` 无法发送邮件	通过 `ScriptApp.newTrigger()` 创建可安装触发器
自定义函数超时	30秒限制——简化逻辑或迁移为常规函数
找不到 `setTimeout`	使用 `Utilities.sleep(ms)` （阻塞式）
脚本运行超过6分钟	拆分任务为多个子任务，使用时间驱动触发器分批执行
未弹出授权窗口	用户需点击「高级 > 前往（不安全） > 允许」

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.

查看

references/patterns.md

获取完整代码示例：

模式	使用场景
自定义菜单	为电子表格工具栏添加操作项
侧边栏应用	表单与数据录入面板
触发器	对编辑、时间或表单提交事件的自动响应
表格邮件通知	发送报表、提醒、日程安排
PDF导出	生成并邮件发送表格PDF
数据验证	基于列表或范围创建下拉菜单

查看

references/recipes.md

获取完整的自动化方案（归档行、高亮重复项、自动编号、仪表板）。

查看

references/quotas.md

获取执行限制、邮件配额及调试技巧。