Google 表格中的自定义函数

Google 表格提供了数百种 内置函数，例如 AVERAGE、 SUM和 VLOOKUP。在这些情况下， 也可以使用 Google Apps 脚本来编写自定义函数 — 例如，将米换算为英里或获取 来自互联网的直播内容，然后使用它们 就像一个内置函数一样。

使用入门

自定义函数是使用标准 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，也没有时间学习 前往插件商店 看看其他人是否已构建您所需的自定义函数。

创建自定义函数

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

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

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

从 Google Workspace Marketplace获取自定义函数

Google Workspace Marketplace 提供了几种自定义 作为 适用于 Google 表格的插件。 如需使用或探索这些插件，请执行以下操作：

  1. 创建 或在 Google 表格中打开电子表格。
  2. 点击顶部的插件 >获取插件。
  3. Google Workspace Marketplace 启动后， 打开后，点击右上角的搜索框。
  4. 输入“custom function”然后按 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)），该参数将是二维的 单元格的数组值。例如，在下面的屏幕截图中， Apps 脚本会将 =DOUBLE(A1:B2) 中的参数解释为 double([[1,3],[2,4]])。请注意，DOUBLE 的示例代码 上述示例的值必须为 已修改为接受数组作为输入。


  • 自定义函数参数必须为 确定性：这样 内置电子表格函数，每次返回不同的结果 计算的变量（例如 NOW() 或 RAND()）不能作为参数 自定义函数。如果自定义函数尝试根据以下条件返回值 其中一个易失性内置函数，它会显示 Loading... 。

返回值

每个自定义函数都必须返回一个要显示的值，以便：

  • 如果自定义函数返回一个值，则该值将显示在单元格中 调用该函数时所处的位置。
  • 如果自定义函数返回的是值的二维数组，则这些值 溢出到相邻单元格中（只要这些单元格为空）。如果 导致数组覆盖现有单元格内容，则自定义函数 而是抛出错误。有关示例，请参见 优化自定义函数。
  • 除了会向自定义函数返回值的单元格以外，自定义函数不会影响其他单元格。 换言之，自定义函数无法修改任意单元格，只能修改 从这些单元格及其相邻单元格中调用函数。如需修改任意单元格，请按以下步骤操作： 请改用自定义菜单运行函数。
  • 自定义函数调用必须在 30 秒内返回。否则， 单元格显示“#ERROR!”，单元格备注为“Exceeded maximum execution time (line 0).”

数据类型

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

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

自动补全

Google 表格支持自动补全自定义函数，这与 内置函数。您 在单元格中输入函数名称，您会看到内置函数和自定义函数列表 函数。

如果自定义函数的脚本包含 JsDoc @customfunction 标记之间，如下面的 DOUBLE() 示例所示。

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

高级

使用 Google Apps 脚本服务

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

与大多数其他类型的 Apps 脚本不同，自定义函数从不要求用户 授权访问个人数据。因此，它们只能调用服务 无权访问个人数据，尤其是以下各方：

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

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

要使用上述服务之外的服务，请创建 运行 Apps 脚本函数的自定义菜单 而无需编写自定义函数。从菜单触发的函数 会在必要时要求用户授权，并因此使用所有 Apps 脚本服务。

共享

自定义函数最初会绑定到 创建它们所在的电子表格也就是说，使用 一个电子表格不能用于其他电子表格，除非您使用 方法：

  • 依次点击扩展程序 > Apps 脚本以执行以下操作： 打开脚本编辑器，然后将 原始电子表格中的脚本文本，并将其粘贴到脚本编辑器中 其他电子表格的模板
  • 通过点击以下链接创建包含自定义函数的电子表格的副本： 文件 >复制。复制电子表格时，附加到 也会被一并复制有权访问该电子表格的任何人都可以复制 脚本。（仅拥有查看权限的协作者无法打开脚本编辑器 原始电子表格中的链接。然而，当他们制作副本时，就会成为 并且可以查看脚本）。
  • 将脚本作为 Google 表格编辑器插件发布。
。 <ph type="x-smartling-placeholder">

优化

每次在电子表格中使用自定义函数时，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;
}

上述方法使用 map 方法，以递归方式Array 对二维单元格数组中的每个值调用 DOUBLE。它会返回一个 包含结果的二维数组。这样，您就可以调用 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;
}

这些方法几乎可以应用于 虽然具体的实施细节 因函数的行为而异。