Google 表格中的自定义函数

Google 表格提供数百个内置函数，例如 AVERAGE、SUM 和 VLOOKUP。当这些内容无法满足您的需求时，您可以使用 Google 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，而且没有时间学习，请访问插件商店，看看是否已有人构建了您需要的自定义函数。

创建自定义函数

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

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 秒内返回。否则，该单元格将显示错误：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;
}

高级

使用 Google Apps 脚本服务

自定义函数可以调用某些 Google 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 的 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;
}

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