Google 表格中的自定义函数

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

Google 表格提供了数百个内置函数,例如 AVERAGESUMVLOOKUP。如果这还不能满足您的需求,您可以使用 Google Apps 脚本编写自定义函数(例如,将米转换为英里从互联网获取实时内容),然后像内置函数一样在 Google 表格中使用这些函数。

开始使用

自定义函数使用标准 JavaScript 创建。如果您刚开始接触 JavaScript,Codecademy 为初学者提供了非常好的课程。(请注意:本课程并非由 Google 开发,与 Google 并无关联。)

下面是一个名为 DOUBLE 的简单自定义函数,该函数将输入值乘以 2:

function DOUBLE(input) {
  return input * 2;
}

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

创建自定义函数

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

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

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

从 Google Workspace Marketplace获取自定义函数

Google Workspace Marketplace 提供了一些自定义函数作为 Google 表格的插件。要使用或探索这些插件,请执行以下操作:

  1. 在 Google 表格中创建或打开电子表格。
  2. 点击顶部的插件 > 获取插件
  3. Google Workspace Marketplace 打开后,点击右上角的搜索框。
  4. 输入“custom functions”(自定义函数),然后按 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 秒内返回。否则,单元格将显示错误:Internal error executing the custom function.

数据类型

Google 表格以不同的格式存储数据,具体取决于数据的性质。在自定义函数中使用这些值时,Apps 脚本会将它们视为 JavaScript 中的相应数据类型。最常见的情况如下:

  • Google 表格中的时间和日期在 Apps 脚本中会变成 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;
}

高级

使用 Apps 脚本服务

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

与大多数其他类型的 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 表格插件发布。

优化

每次在电子表格中使用自定义函数时,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 的 Array 对象的 map 方法,以递归方式对单元格的二维数组中的每个值调用 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;
}

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