扩展 Google 表格

使用 Google Apps 脚本来扩展 Google 表格。向 Google 表格添加自定义菜单、对话框和侧边栏。为 Google 表格编写自定义函数，并将其与其他 Google 服务（例如 Google 日历、Google 云端硬盘和 Gmail）集成。

大多数专为 Google 表格设计的脚本都会操纵数组，以便与电子表格中的单元格、行和列进行交互。如果您不熟悉 JavaScript 中的数组，Codecademy 提供了一个很棒的数组培训模块。此课程并非由 Google 开发，也与 Google 无关。

如需快速了解如何将 Apps 脚本与 Google 表格搭配使用，请参阅宏、菜单和自定义函数的 5 分钟快速入门指南。

开始使用

Apps 脚本包含一些特殊的 API，可用于以程序化方式创建、读取和修改 Google 表格。Apps 脚本与 Google 表格的交互方式有两种：任何脚本都可以创建或修改电子表格（前提是脚本的用户拥有相应的权限），并且脚本还可以绑定到电子表格。绑定脚本具有更改界面或在电子表格打开时做出响应的特殊能力。如需创建绑定脚本，请在 Google 表格中依次选择扩展程序 > Apps 脚本 。

Spreadsheet 服务将 Google 表格视为网格，并使用二维数组进行操作。如需从电子表格中检索数据，请获取对存储数据的电子表格的访问权限，获取包含数据的范围，然后获取单元格的值。 Apps 脚本通过读取电子表格中的结构化数据并为其创建 JavaScript 对象，来简化数据访问。

读取数据

假设您有一个产品名称和产品编号列表，并将其存储在电子表格中，如下图所示。

以下示例展示了如何检索和记录产品名称和产品编号。

function logProductInfo() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const data = sheet.getDataRange().getValues();
  for (let i = 0; i < data.length; i++) {
    Logger.log('Product name: ' + data[i][0]);
    Logger.log('Product number: ' + data[i][1]);
  }
}

查看日志

如需查看已记录的数据，请点击脚本编辑器顶部的执行日志 。

写入数据

如需将数据（例如新的商品名称和编号）存储到电子表格中，请将以下代码添加到脚本末尾。

function addProduct() {
  const sheet = SpreadsheetApp.getActiveSheet();
  sheet.appendRow(['Cotton Sweatshirt XL', 'css004']);
}

上述代码会在电子表格底部附加一个新行，其中包含指定的值。如果您运行此函数，系统会在电子表格中添加一个新行。

自定义菜单和界面

通过添加自定义菜单、对话框和侧边栏来自定义 Google 表格。如需了解创建菜单的基础知识，请参阅菜单指南。如需了解如何自定义对话框的内容，请参阅 HTML 服务指南。

将脚本函数附加到电子表格中的图片或绘图；当用户点击图片或绘图时，该函数会执行。如需了解详情，请参阅 Google 表格中的图片和绘图。

如果您打算将自定义界面作为插件的一部分发布，请遵循样式指南，以确保与 Google 表格编辑器的样式和布局保持一致。

连接到 Google 表单

通过表单和电子表格服务将 Google 表单与 Google 表格连接起来。此功能会根据电子表格中的数据自动创建 Google 表单。 Apps 脚本还允许您使用触发器，例如 onFormSubmit，以便在用户回复表单后执行特定操作。如需详细了解如何将 Google 表格连接到 Google 表单，请尝试表单管理回复 5 分钟快速入门。

设置数据格式

Range 类具有 setBackground 等方法，可用于访问和修改单元格或单元格范围的格式。以下示例设置了范围的字体样式：

function formatMySpreadsheet() {
  // Set the font style of the cells in the range of B2:C2 to be italic.
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheets()[0];
  const cell = sheet.getRange('B2:C2');
  cell.setFontStyle('italic');
}

数据验证

访问 Google 表格中现有的数据验证规则或创建新规则。例如，以下示例展示了如何设置数据验证规则，该规则仅允许在单元格中输入介于 1 到 100 之间的数字。

function validateMySpreadsheet() {
  // Set a rule for the cell B4 to be a number between 1 and 100.
  const cell = SpreadsheetApp.getActive().getRange('B4');
  const rule = SpreadsheetApp.newDataValidation()
     .requireNumberBetween(1, 100)
     .setAllowInvalid(false)
     .setHelpText('Number must be between 1 and 100.')
     .build();
  cell.setDataValidation(rule);
}

如需详细了解如何使用数据验证规则，请参阅 SpreadsheetApp.newDataValidation、 DataValidationBuilder和 Range.setDataValidation

图表

在电子表格中嵌入图表，以表示特定范围内的相关数据。以下示例会生成嵌入式条形图，前提是单元格 A1:B15 中包含可用于制作图表的数据：

function newChart() {
  // Generate a chart representing the data in the range of A1:B15.
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheets()[0];

  const chart = sheet.newChart()
     .setChartType(Charts.ChartType.BAR)
     .addRange(sheet.getRange('A1:B15'))
     .setPosition(5, 5, 0, 0)
     .build();

  sheet.insertChart(chart);
}

如需详细了解如何将图表嵌入到电子表格中，请参阅 EmbeddedChart 和特定图表构建器（例如 EmbeddedPieChartBuilder）。

Google 表格中的自定义函数

自定义函数与内置电子表格函数（例如 =SUM(A1:A5)）类似，不同之处在于，您可以使用 Apps 脚本定义函数的行为。例如，您可以创建一个自定义函数 in2mm()，用于将值从英寸转换为毫米，然后在电子表格中使用该公式，方法是在单元格中输入 =in2mm(A1) 或 =in2mm(10)。

如需详细了解自定义函数，请尝试菜单和自定义函数 5 分钟快速入门，或查看有关自定义函数的更深入指南。

宏

宏是另一种从 Google 表格界面执行 Apps 脚本代码的方式。与自定义函数不同，您可以使用键盘快捷键或通过 Google 表格菜单激活宏。如需了解更多信息，请参阅Google 表格宏。

适用于 Google 表格的插件

插件是经过特殊打包的 Apps 脚本项目，可在 Google 表格内运行，并且可以从 Google 表格插件商店安装。如果您为 Google 表格开发了脚本并想要分享，Apps 脚本允许您将脚本发布为插件，以便其他用户可以安装。

性能和扩缩

随着数据集的增长，您可能会遇到性能问题。如需优化电子表格和脚本，请执行以下操作：

遵循最佳实践：阅读最佳实践指南，了解有关最大限度减少服务调用和使用批量操作的提示。
优化公式：如果您的电子表格因复杂公式（例如 VLOOKUP、ARRAYFORMULA 或 IMPORTRANGE）而出现延迟，请考虑使用 Apps 脚本在内存中执行这些计算，然后分批写回结果。
考虑使用数据库替代方案：对于非常大的数据集（接近 10 万个单元格）或高频数据输入（例如，许多连接的表单），请考虑使用 Google Cloud SQL（使用 JDBC）或 BigQuery。

触发器

绑定到 Google 表格文件的脚本可以使用简单触发器（例如函数 onOpen() 和 onEdit()），以便在有权修改电子表格的用户打开或修改电子表格时自动做出响应。与简单触发器类似，可安装的触发器允许 Google 表格在发生特定事件时自动运行函数。不过，可安装的触发器比简单触发器更灵活，并且支持以下事件：打开、修改、更改、表单提交和时间驱动（时钟）。

扩展 Google 表格 使用集合让一切井井有条 根据您的偏好保存内容并对其进行分类。