google-apps-script

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Google Apps Script

Overview

概述

Cloud-based JavaScript platform for automating Google Workspace services. Server-side V8 runtime with automatic OAuth integration across Sheets, Docs, Gmail, Drive, Calendar, and more.

一款基于云的JavaScript平台，用于自动化Google Workspace服务。采用服务器端V8运行时，可在Sheets、Docs、Gmail、Drive、Calendar等服务中自动集成OAuth。

When to Use This Skill

适用场景

Invoke this skill when:

Automating Google Sheets operations (reading, writing, formatting)
Creating or editing Google Docs programmatically
Managing Gmail messages and sending emails
Working with Google Drive files and folders
Automating Google Calendar events
Implementing triggers (time-based or event-based)
Building custom functions for Sheets
Creating Google Workspace add-ons
Handling OAuth scopes and authorisation
Making HTTP requests to external APIs with UrlFetchApp
Using persistent storage with PropertiesService
Implementing caching strategies with CacheService
Optimising performance with batch operations
Debugging Apps Script code or authorisation issues

在以下场景中可使用该技能：

自动化Google Sheets操作（读取、写入、格式化）
以编程方式创建或编辑Google Docs
管理Gmail消息并发送邮件
处理Google Drive中的文件和文件夹
自动化Google Calendar事件
实现触发器（基于时间或基于事件）
为Sheets构建自定义函数
创建Google Workspace插件
处理OAuth权限范围与授权
使用UrlFetchApp向外部API发送HTTP请求
使用PropertiesService实现持久化存储
使用CacheService实现缓存策略
通过批量操作优化性能
调试Apps Script代码或授权问题

Core Services

核心服务

SpreadsheetApp - Google Sheets automation (read, write, format, data validation)
DocumentApp - Google Docs creation and editing
GmailApp & MailApp - Email operations (send, search, manage labels)
DriveApp - File and folder management, sharing, permissions
CalendarApp - Calendar events, recurring appointments, reminders
Triggers & ScriptApp - Time-based and event-driven automation

SpreadsheetApp - Google Sheets自动化（读取、写入、格式化、数据验证）
DocumentApp - Google Docs的创建与编辑
GmailApp & MailApp - 邮件操作（发送、搜索、标签管理）
DriveApp - 文件与文件夹管理、共享、权限设置
CalendarApp - 日历事件、重复预约、提醒
Triggers & ScriptApp - 基于时间和事件驱动的自动化

Quick Start

快速入门

javascript

function generateWeeklyReport() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('Data');
  const data = sheet.getRange('A2:D').getValues();

  const report = data.filter(row => row[0]);
  const summarySheet = ss.getSheetByName('Summary') || ss.insertSheet('Summary');
  summarySheet.clear();
  summarySheet.appendRow(['Name', 'Value', 'Status']);
  report.forEach(row => summarySheet.appendRow([row[0], row[1], row[2]]));

  MailApp.sendEmail({
    to: Session.getEffectiveUser().getEmail(),
    subject: 'Weekly Report Generated',
    body: `Report generated with ${report.length} records.`
  });
}

javascript

function generateWeeklyReport() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('Data');
  const data = sheet.getRange('A2:D').getValues();

  const report = data.filter(row => row[0]);
  const summarySheet = ss.getSheetByName('Summary') || ss.insertSheet('Summary');
  summarySheet.clear();
  summarySheet.appendRow(['Name', 'Value', 'Status']);
  report.forEach(row => summarySheet.appendRow([row[0], row[1], row[2]]));

  MailApp.sendEmail({
    to: Session.getEffectiveUser().getEmail(),
    subject: 'Weekly Report Generated',
    body: `Report generated with ${report.length} records.`
  });
}

Best Practices

最佳实践

Batch operations - read/write ranges in bulk, never cell-by-cell in loops
Cache data - use CacheService (25 min TTL) for frequently accessed data
Error handling - wrap operations in try/catch, log errors to a sheet for audit trails
Respect limits - 6-minute execution timeout; split large jobs across triggers
Minimise scopes - request only necessary OAuth permissions in
```
appscript.json
```
Persistent storage - use PropertiesService for configuration and state
Validate inputs - always check objects exist before accessing properties

See references/best-practices.md for detailed examples of each practice.

批量操作 - 批量读取/写入数据范围，切勿在循环中逐个单元格操作
缓存数据 - 使用CacheService（25分钟过期时间）存储频繁访问的数据
错误处理 - 将操作包裹在try/catch块中，将错误记录到表格以便审计追踪
遵守限制 - 执行超时时间为6分钟；将大型任务拆分到多个触发器中执行
最小化权限范围 - 在
```
appscript.json
```
中仅请求必要的OAuth权限
持久化存储 - 使用PropertiesService存储配置和状态
验证输入 - 在访问属性前始终检查对象是否存在

如需每个实践的详细示例，请查看references/best-practices.md。

Validation & Testing

验证与测试

Use the validation scripts in

scripts/

for pre-deployment checks:

scripts/validators.py - Validate spreadsheet operations, range notations, and data structures

Debug with

Logger.log()

and view output via View > Logs (Cmd/Ctrl + Enter). Use breakpoints in the Apps Script editor for step-through debugging.

部署前检查可使用

scripts/

目录下的验证脚本：

scripts/validators.py - 验证电子表格操作、范围表示法和数据结构

使用

Logger.log()

进行调试，通过「查看 > 日志」（Cmd/Ctrl + Enter）查看输出。可在Apps Script编辑器中使用断点进行单步调试。

Integration with Other Skills

与其他技能的集成

google-ads-scripts - Export Google Ads data to Sheets for reporting
gtm-datalayer - Coordinate with GTM for tracking events triggered by Apps Script
ga4-bigquery - Query BigQuery from Apps Script and write results to Sheets

google-ads-scripts - 将Google Ads数据导出到Sheets用于报表生成
gtm-datalayer - 与GTM配合，跟踪由Apps Script触发的事件
ga4-bigquery - 从Apps Script中查询BigQuery并将结果写入Sheets

Troubleshooting

故障排除

Issue	Solution
Execution timeout	Split work into smaller batches or use multiple triggers
Authorisation error	Check OAuth scopes in manifest file
Quota exceeded	Reduce API call frequency, use caching
Null reference error	Validate objects exist before accessing properties

问题	解决方案
执行超时	将任务拆分为更小的批次，或使用多个触发器
授权错误	检查清单文件中的OAuth权限范围
配额超限	降低API调用频率，使用缓存
空引用错误	在访问属性前验证对象是否存在

References

参考资料

Detailed content is available in reference files (loaded on demand):

references/apps-script-api-reference.md - Complete API reference for all built-in services, triggers, authorisation, and performance optimisation
references/examples.md - Production-ready code examples (spreadsheet reports, Gmail auto-responder, document generation, trigger setup)
references/best-practices.md - Detailed best practices with code blocks for batch operations, caching, error handling, scopes, and persistence
references/patterns.md - Common reusable patterns (data validation, retry logic, form response processing)

详细内容可在参考文件中查看（按需加载）：

references/apps-script-api-reference.md - 所有内置服务、触发器、授权和性能优化的完整API参考
references/examples.md - 生产就绪的代码示例（电子表格报表、Gmail自动回复器、文档生成、触发器设置）
references/best-practices.md - 详细的最佳实践，包含批量操作、缓存、错误处理、权限范围和持久化的代码块
references/patterns.md - 常见可复用模式（数据验证、重试逻辑、表单响应处理）