Google 表格中的自定义函数

Google 表格提供了数百个 内置函数，例如 AVERAGE、 SUM和 VLOOKUP。如果这些函数无法满足您的需求，您可以使用 Apps 脚本编写自定义函数，然后在 Google 表格中像使用内置函数一样使用这些自定义函数。

如需查看自定义函数的示例，请参阅以下教程：

使用入门

自定义函数是使用标准 JavaScript 创建的。如果您是 JavaScript 新手，Codecademy 提供了 面向初学者的课程。 此课程并非由 Google 开发，也与 Google 无关。

以下是一个名为 DOUBLE 的自定义函数，用于将输入值乘以 2：

/**
 * Multiplies an input value by 2.
 * @param {number} input The number to double.
 * @return The input multiplied by 2.
 * @customfunction
*/
function DOUBLE(input) {
  return input * 2;
}

如果您不知道如何编写 JavaScript 且没有时间学习， 请查看 Google Workspace 插件商店，看看是否有人已构建您需要的自定义函数。

创建自定义函数

如需编写自定义函数，请执行以下操作：

  1. 在 Google 表格中创建 或打开电子表格。
  2. 依次选择扩展程序 > Apps 脚本 菜单项。
  3. 删除脚本编辑器中的所有代码。对于前面显示的 DOUBLE 函数，请将代码复制并粘贴到脚本编辑器中。
  4. 点击顶部的“保存”图标 。

现在，您可以使用自定义函数了。

从 Google Workspace Marketplace 获取自定义函数

Google Workspace Marketplace 提供多个自定义函数，作为 Google Workspace 插件供 Google 表格使用。 如需使用或探索这些插件，请执行以下操作：

  1. 在 Google 表格中创建 或打开电子表格。
  2. 点击顶部的插件 > 获取插件 。
  3. Google Workspace Marketplace 打开后，点击右上角的搜索框。
  4. 输入“自定义函数”，然后按 Enter 键。
  5. 如果您找到感兴趣的自定义函数插件，请点击安装 进行安装。
  6. 系统可能会显示一个对话框，告知您该插件需要授权。如果是这样，请仔细阅读通知，然后点击允许 。
  7. 该插件会在电子表格中提供。如需在其他电子表格中使用该插件，请打开其他电子表格，然后点击顶部的插件 > 管理插件 。找到您要使用的插件，然后依次点击 “选项”图标 > 在此 文档中使用。

使用自定义函数

编写自定义函数或从 Google Workspace Marketplace 安装自定义函数后，您就可以像使用内置函数一样使用它：

  1. 点击要使用该函数的单元格。
  2. 输入等号 (=)，后跟函数名称和任何输入值 — 例如 =DOUBLE(A1) —，然后按 Enter 键。
  3. 单元格会暂时显示 Loading...，然后返回结果。

自定义函数指南

在编写自己的自定义函数之前，请先了解一些指南。

函数命名

除了 JavaScript 函数的标准命名惯例之外，请注意以下事项：

  • 自定义函数的名称必须与 内置函数的名称（例如 SUM()）不同。
  • 自定义函数的名称不能以英文下划线 (_) 结尾，因为在 Apps 脚本中，英文下划线表示私有函数。
  • 自定义函数的名称必须使用 function myFunction() 语法声明，而不是 var myFunction = new Function()。
  • 大小写无关紧要，但电子表格函数的名称传统上采用大写形式。

参数

与内置函数一样，自定义函数可以接受参数作为输入值：

  • 如果您使用对单个单元格的引用作为参数调用函数（例如 =DOUBLE(A1)），则该参数是单元格的值。

  • 如果您使用对单元格范围的引用作为参数调用函数（例如 =DOUBLE(A1:B10)），则该参数是单元格值的二维数组。例如，在以下屏幕截图中，=DOUBLE(A1:B2) 中的参数会被 Apps 脚本解释为 double([[1,3],[2,4]])。请注意，需要修改前面介绍的 DOUBLE 示例代码，使其能够 接受数组作为输入。


  • 自定义函数参数必须是 确定性的。也就是说，每次计算时返回不同结果的内置电子表格函数（例如 NOW() 或 RAND()）不能作为自定义函数的参数。如果自定义函数尝试根据其中一个易变内置函数返回一个值，则会无限期地显示 Loading...。

  • 如需触发重新计算，您必须将引用的单元格或单元格范围直接作为参数传递给自定义函数。否则，自定义函数不会重新计算，除非您修改该函数或更改引用单元格的值。如果您在自定义函数中使用 getValue 方法，请注意，引用的范围不会直接作为参数传递给自定义函数。

返回值

每个自定义函数都必须返回一个要显示的值，具体如下：

  • 如果自定义函数返回值，则该值会显示在调用该函数的单元格中。
  • 如果自定义函数返回一个二维数组的值，则这些值会溢出到相邻的单元格中，只要这些单元格为空即可。如果这会导致数组覆盖现有单元格内容，则自定义函数会改为抛出错误。如需查看示例，请参阅有关 优化自定义函数的部分。
  • 自定义函数无法影响除其返回值所在的单元格之外的其他单元格。 换句话说，自定义函数无法修改任意单元格，只能修改调用它的单元格及其相邻单元格。如需修改任意单元格， 请改用自定义菜单来运行函数。
  • 自定义函数调用必须在 30 秒内返回。否则，单元格会显示 #ERROR!，并且单元格备注为 Exceeded maximum execution time (line 0).

数据类型

Google 表格会根据数据的性质以 不同的格式存储数据。当这些值在自定义函数中使用时， Apps 脚本会将它们视为 JavaScript 中的相应数据类型。 以下是最容易混淆的方面：

  • Google 表格中的时间和日期在 Apps 脚本中会变为 Date 对象。如果电子表格和脚本使用不同的时区（这是一个罕见的问题），则自定义函数需要进行补偿。
  • Google 表格中的时长值也会变为 Date 对象，但 使用它们可能会很复杂。
  • Google 表格中的百分比值在 Apps 脚本中会变为十进制数。例如，值为 10% 的单元格在 Apps 脚本中会变为 0.1。

自动补全

Google 表格支持自定义函数的自动补全，就像 内置函数一样。当您在单元格中输入函数名称时，系统会显示与您输入的内容匹配的内置函数和自定义函数列表。

如果自定义函数的脚本包含 JSDoc @customfunction 标记（如 DOUBLE() 示例中所示），则该函数会显示在此列表中。

/**
 * Multiplies the input value by 2.
 *
 * @param {number} input The value to multiply.
 * @return {number} The input multiplied by 2.
 * @customfunction
 */
function DOUBLE(input) {
  return input * 2;
}

高级

本部分介绍自定义函数的高级主题。

使用 Google Apps 脚本服务

自定义函数可以调用某些 Apps 脚本服务来执行 更复杂的任务。例如，自定义函数可以调用 Language服务将英语 短语翻译成西班牙语。

与大多数其他类型的 Apps 脚本不同，自定义函数绝不会要求用户授权访问个人数据。因此，它们只能调用无法访问个人数据的服务，具体如下：

支持的服务 备注
缓存 有效，但在自定义函数中不是特别有用
HTML 可以生成 HTML，但无法显示（很少有用）
JDBC
语言
锁定 有效，但在自定义函数中不是特别有用
Google 地图 可以计算路线，但无法显示地图
媒体资源 getUserProperties() 仅获取电子表格所有者的媒体资源。电子表格编辑者无法在 自定义函数中设置用户媒体资源。
电子表格 只读（可以使用大多数 get*() 方法，但不能使用 set*()）。
无法打开其他电子表格（SpreadsheetApp.openById() 或 SpreadsheetApp.openByUrl()）。
网址提取 通过提取网址访问网络上的资源。
实用程序
XML

如果您的自定义函数抛出错误消息 You do not have permission to call X service.，则表示该服务需要用户授权，因此无法在自定义函数中使用。

如需使用上述列表中的服务以外的服务，请创建一个 自定义菜单，而不是编写 Apps 脚本函数。从菜单触发的函数会在必要时请求用户授权，因此可以使用所有 Apps 脚本服务。

共享自定义函数

自定义函数最初绑定到创建它们的 电子表格。这意味着，除非您使用以下方法之一，否则在一个电子表格中编写的自定义函数无法在其他电子表格中使用：

  • 依次点击扩展程序 > Apps 脚本以打开脚本编辑器，然后从原始电子表格中复制脚本文本并将其粘贴到另一个电子表格的脚本编辑器中。
  • 依次点击文件 > 复制 ，创建包含自定义函数的电子表格副本。复制电子表格时，附加到该电子表格的所有脚本也会被复制。任何有权访问该电子表格的人都可以复制脚本。（只有查看权限的协作者无法在原始电子表格中打开脚本编辑器。不过，当他们创建副本时，会成为副本的所有者，并且可以看到脚本。）
  • 将脚本发布为 Google 表格 编辑器插件。

所有容器绑定的脚本都与其容器共享相同的访问权限列表。 这意味着，任何有权修改电子表格的人也可以修改附加到该电子表格的任何 Apps 脚本代码。如需了解详情，请参阅 对绑定脚本的访问权限。

优化

每次在电子表格中使用自定义函数时，Google 表格都会单独调用 Apps 脚本服务器。如果您的电子表格包含数十个（或数百个，或数千个！）自定义函数调用，此过程可能会很慢。某些包含许多或复杂的自定义函数的项目在执行时可能会暂时延迟。

因此，如果您计划在大量数据上多次使用自定义函数，请考虑修改该函数，使其能够以二维数组的形式接受范围作为输入，然后返回一个可以溢出到相应单元格的二维数组。

例如，前面显示的 DOUBLE() 函数可以重写为接受单个单元格或单元格范围，如下所示：

/**
 * Multiplies the input value by 2.
 *
 * @param {number|Array<Array<number>>} input The value or range of cells
 *     to multiply.
 * @return The input multiplied by 2.
 * @customfunction
 */
function DOUBLE(input) {
  return Array.isArray(input) ?
      input.map(row => row.map(cell => cell * 2)) :
      input * 2;
}

此方法对单元格的二维数组使用 JavaScript 的 map 方法来获取每一行，然后对每一行再次使用 map 来返回每个单元格值的两倍。Array它会返回一个包含结果的二维数组。 这样，您只需调用一次 DOUBLE，即可让它一次计算大量单元格，如以下屏幕截图所示。您可以使用嵌套的 if 语句而不是 map 调用来完成同样的操作。

同样，以下自定义函数可以高效地从互联网提取实时内容，并使用二维数组仅通过一次函数调用即可显示两列结果。如果每个单元格都需要自己的函数调用，则该操作将花费更多时间，因为 Apps 脚本服务器每次都必须下载并解析 XML Feed。

/**
 * Show the title and date for the first page of posts on the
 * Developer blog.
 *
 * @return Two columns of data representing posts on the
 *     Developer blog.
 * @customfunction
 */
function getBlogPosts() {
  var array = [];
  var url = 'https://gsuite-developers.googleblog.com/atom.xml';
  var xml = UrlFetchApp.fetch(url).getContentText();
  var document = XmlService.parse(xml);
  var root = document.getRootElement();
  var atom = XmlService.getNamespace('http://www.w3.org/2005/Atom');
  var entries = document.getRootElement().getChildren('entry', atom);
  for (var i = 0; i < entries.length; i++) {
    var title = entries[i].getChild('title', atom).getText();
    var date = entries[i].getChild('published', atom).getValue();
    array.push([title, date]);
  }
  return array;
}

这些技巧几乎可以应用于在整个电子表格中重复使用的任何自定义函数，不过实现细节因函数的行为而异。