Google 表格中的自定义函数

Google 表格提供了数百种 内置函数,例如 AVERAGESUMVLOOKUP。在这些情况下, 也可以使用 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;
}

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