使用 Google 表格的 Apps 脚本的基础知识 3：处理数据

1. 简介

欢迎学习“使用 Google 表格的 Apps 脚本的基础知识”Codelab 播放列表的第三部分。

完成此 Codelab 后，您将了解如何在 Apps 脚本中使用数据操纵、自定义菜单和公共 API 数据检索来改善 Google 表格体验。您将继续使用此播放列表中之前的 Codelab 介绍的 SpreadsheetApp、Spreadsheet、Sheet、Range 类。

学习内容

• 如何从云端硬盘中的个人或共享电子表格导入数据。
• 如何使用 onOpen() 函数创建自定义菜单。
• 如何解析和处理 Google 表格单元格中的字符串数据值。
• 如何从公共 API 来源拉取和处理 JSON 对象数据。

准备工作

这是“使用 Google 表格的 Apps 脚本的基础知识”播放列表中的第三个 Codelab。在开始此 Codelab 之前，请务必完成前面的 Codelab：

1. 宏和自定义函数
2. 电子表格、工作表和范围

所需条件

• 了解此播放列表的前几个 Codelab 中介绍的关于 Apps 脚本的基本主题。
• 基本熟悉 Apps 脚本编辑器
• 基本熟悉 Google 表格
• 能够读取 Google 表格 A1 表示法
• 基本熟悉 JavaScript 及其 String 类

2. 设置

此 Codelab 中的练习需要一个电子表格才能使用。请按照以下步骤创建要在这些练习中使用的电子表格：

1. 在 Google 云端硬盘中创建一个电子表格。您可以通过在云端硬盘界面中选择新建 > Google 表格来执行此操作。此操作会创建并打开您的新电子表格。该文件会保存到您的云端硬盘文件夹中。
2. 点击电子表格标题，将其从“未命名电子表格”更改为“数据操纵和自定义菜单”。您的工作表应如下所示：

3. 如需打开脚本编辑器，请点击扩展 > Apps 脚本。
4. 点击 Apps 脚本项目名称，将其从“未命名项目”更改为“数据操纵和自定义菜单”。点击重命名以保存标题更改。

如果使用空白的电子表格和项目，您就可以开始实验室了。移到下一部分，开始了解自定义菜单。

注意：如果您使用的是 gmail.com 账号，则首次使用脚本时，可能会出现“此应用未进行验证”对话框。Google 使用此消息来警告可能正在使用未知或不可信作者的代码的用户。如果您看到此对话框，您可以继续操作，因为您是脚本作者。按照屏幕上的提示继续为脚本授权：

1. 在未经验证的应用对话框中，点击高级。
2. 点击转到数据操纵和自定义菜单(不安全)。
3. 点击允许。

在整个 Codelab 中，您可能会收到多项权限提示。如需详细了解此过程，请参阅授权 Google 服务。

3. 概览：使用自定义菜单项导入数据

借助 Apps 脚本，您可以定义可出现在 Google 表格中的自定义菜单。您还可以在 Google 文档、Google 幻灯片和 Google 表单中使用自定义菜单。定义自定义菜单项时，您将创建一个文本标签并将其连接到脚本项目中的 Apps 脚本函数。然后，您可以将菜单添加到界面，使其显示在 Google 表格中：

当用户点击自定义菜单项时，系统会执行您与其关联的 Apps 脚本函数。这是运行 Apps 脚本函数的快速方法，无需打开脚本编辑器。它还允许电子表格的其他用户执行您的代码，而无需了解它的工作原理或 Apps 脚本的工作原理。对他们来说，这只是另一个菜单项。

自定义菜单项是在 onOpen() 简单触发器函数中定义的，下一部分将对此进行介绍。

4. onOpen() 函数

Apps 脚本中的简单触发器提供了一种方式运行特定 Apps 脚本代码来响应特定条件或事件。创建触发器时，您可以定义触发触发器的事件，并提供为该事件运行的 Apps 脚本函数。

onOpen() 是一个简单触发器的示例。它们易于设置，您只需编写一个名为 onOpen() 的 Apps 脚本函数，然后在每次打开或重新加载关联的电子表格时，Apps 脚本就会运行它：

/**
 * A special function that runs when the spreadsheet is first
 * opened or reloaded. onOpen() is used to add custom menu
 * items to the spreadsheet.
 */
function onOpen() {
 /* ... */ 
}

主要概念：简单触发器易于设置和使用，但有几个限制。由于存在这些限制，onOpen() 仅用于创建菜单项。

触发器是一个复杂的主题，超出了此 Codelab 的范围。现在，您可以仅将 onOpen() 视为事件处理程序，以便在每次加载电子表格时添加菜单项。

实现

让我们现在来创建自定义菜单。

1. 将脚本项目中的代码替换为以下内容：

/**
 * A special function that runs when the spreadsheet is first
 * opened or reloaded. onOpen() is used to add custom menu
 * items to the spreadsheet.
 */
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Book-list')
    .addItem('Load Book-list', 'loadBookList')
    .addToUi();
}

2. 保存脚本项目。

代码审核

我们来看看此代码，了解其工作原理。在 onOpen() 中，第一行使用 getUi() 方法获取 Ui 对象，该对象表示与该脚本绑定到的活动电子表格的界面。

接下来的三行会创建菜单 (Book-list)，将菜单项 (Load Book-list) 添加到该菜单，然后将菜单添加到电子表格的界面。这分别使用 createMenu(caption)、addItem(caption, functionName) 和 addToUi() 方法完成。

addItem(caption, functionName) 方法会在菜单项标签和 Apps 脚本函数之间创建连接，后者在选中菜单项时运行。在这种情况下，选择 Load Book-list 菜单项会导致 Google 表格尝试运行 loadBookList() 函数（尚不存在）。

结果

现在运行此函数来查看其是否有效：

1. 在 Google 表格中，重新加载电子表格。注意：这通常会关闭显示了脚本编辑器的标签页。
2. 选择工具 > 脚本编辑器来重新打开脚本编辑器。

电子表格重新加载后，新的 Book-list 菜单应显示在菜单栏上：

点击图书信息后，您可以看到生成的菜单：

下一部分将为 loadBookList() 函数创建代码，并介绍了一种与 Apps 脚本中的数据交互的方式：读取其他电子表格。

5. 导入电子表格数据

现在您已经创建了自定义菜单，接下来可以创建能够通过点击菜单项运行的函数。

目前，自定义菜单 Book-list 有一个菜单项：Load Book-list.。当您选择 Load Book-list 菜单项时调用的函数 loadBookList(), 在您的脚本中不存在，因此选择 图书信息 > 加载图书信息会抛出错误：

您可以通过实现 loadBookList() 函数来修复此错误。

实现

您希望新的菜单项使用数据填充电子表格，因此您将实现 loadBookList()，来从另一个电子表格读取图书数据并将其复制到此电子表格中：

1. 将以下代码添加到您的脚本中的 onOpen() 下：

/** 
 * Creates a template book list based on the
 * provided 'codelab-book-list' sheet.
 */
function loadBookList(){
  // Gets the active sheet.
  var sheet = SpreadsheetApp.getActiveSheet();
  
  // Gets a different spreadsheet from Drive using
  // the spreadsheet's ID. 
  var bookSS = SpreadsheetApp.openById(
    "1c0GvbVUDeBmhTpq_A3vJh2xsebtLuwGwpBYqcOBqGvo" 
  );

  // Gets the sheet, data range, and values of the
  // spreadsheet stored in bookSS.
  var bookSheet = bookSS.getSheetByName("codelab-book-list");
  var bookRange = bookSheet.getDataRange();
  var bookListValues = bookRange.getValues();

  // Add those values to the active sheet in the current
  // spreadsheet. This overwrites any values already there.
  sheet.getRange(1, 1, bookRange.getHeight(), bookRange.getWidth()) 
    .setValues(bookListValues);
  
  // Rename the destination sheet and resize the data
  // columns for easier reading.
  sheet.setName("Book-list");
  sheet.autoResizeColumns(1, 3);
}

2. 保存脚本项目。

代码审核

此函数的工作原理是什么？loadBookList() 函数主要使用 Spreadsheet、Sheet 和 Range 类中的方法 - 之前的 Codelab 介绍过这些类。考虑到这些概念，您可以将 loadBookList() 代码细分为以下四个部分：

1：确定目标工作表

第一行使用 SpreadsheetApp.getActiveSheet() 获取对当前工作表对象的引用，并将其存储在变量 sheet 中。这是数据将复制到的工作表。

2：确定源数据

接下来的几行建立 4 个变量，用于引用您要检索的源数据：

• bookSS 存储对代码从中读取数据的电子表格的引用。代码通过电子表格 ID 查找电子表格。在本示例中，我们提供了要读取的源电子表格的 ID，并使用 SpreadsheetApp.openById(id) 方法打开该电子表格。
• bookSheet 会在 bookSS 中存储对包含所需数据的工作表的引用。代码通过名称 codelab-book-list 标识要读取的工作表。
• bookRange 将对一系列数据的引用存储在 bookSheet 中。Sheet.getDataRange() 方法返回包含工作表中所有非空单元格的范围。您可以轻松确保获取一个涵盖工作表中所有数据的范围，而不包含空行和空列。
• bookListValues 是一个二维数组，其中包含从 bookRange 中的单元格提取的所有值。Range.getValues() 方法通过从源工作表中读取数据生成此数组。

3：将数据从源复制到目标

下一个代码部分将 bookListValues 数据复制到 sheet，然后重命名工作表：

• Sheet.getRange(row, column, numRows, numColumns) 用于标识 sheet 中数据复制的位置。
• Range.getHeight() 和 Range.getWidth() 方法用于测量数据的大小，并定义相同维度的目标范围。
• Range.setValues(values) 将 bookListValues 的二维数组复制到目标范围，覆盖此处已存在现有数据。

4：设置目标工作表的格式

Sheet.setName(name) 用于将目标工作表名称更改为 Book-list。此函数中的最后一行使用 Sheet.autoResizeColumns(startColumn, numColumns) 来调整目标工作表中的前三列的大小，以便您更轻松地读取新数据。

结果

您可以看到此函数的实际效果。在 Google 表格中，选择图书信息 > 加载图书信息，运行该函数以填充电子表格：

您现在已经有了一个工作表，其中有一个包含书名、作者和 13 位 ISBN 编号的列表。在下一部分中，您将学习如何使用字符串操作和自定义菜单修改和更新此图书信息中的数据。

6. 概览：清理电子表格数据

现在，您的工作表中已有图书信息。每行引用一本特定图书，在单独的列中列出其书名、作者和 ISBN 编号。不过，您可能还会看到该原始数据有一些问题：

1. 对于某些行，标题和作者一起放置在标题列中，由英文逗号或字符串“by”连接。
2. 某些行缺少书名或作者。

在接下来的部分中，您将通过清理数据来解决这些问题。对于第一个问题，您将创建读取标题列的函数，并在每次找到英文逗号或“by”分隔符时都拆分文本，将相应的作者和标题子字符串放在正确的列中。对于第二个问题，您将编写代码来使用外部 API 自动搜索缺失的图书信息，并将该信息添加到工作表中。

7. 添加菜单项

您需要创建三个菜单项来控制要实现的数据清理操作。

实现

让我们更新 onOpen()，添加您需要的额外菜单项。执行以下操作：

1. 在脚本项目中，更新 onOpen() 代码以匹配以下内容：

/**
 * A special function that runs when the spreadsheet is first
 * opened or reloaded. onOpen() is used to add custom menu
 * items to the spreadsheet.
 */
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Book-list')
    .addItem('Load Book-list', 'loadBookList')
    .addSeparator()
    .addItem(
      'Separate title/author at first comma', 'splitAtFirstComma')
    .addItem(
      'Separate title/author at last "by"', 'splitAtLastBy')
    .addSeparator()
    .addItem(
      'Fill in blank titles and author cells', 'fillInTheBlanks')
    .addToUi();
}

2. 保存脚本项目。
3. 在脚本编辑器中，从函数列表中选择 onOpen，然后点击运行。这将运行 onOpen() 来重新构建电子表格菜单，因此您无需重新加载电子表格。

在该新代码中，Menu.addSeparator() 方法在菜单中创建了一个水平分隔线，使相关菜单项组在视觉上保持有序。随后，新的菜单项会添加到下方，并带有标签 Separate title/author at first comma，Separate title/author at last "by" 和 Fill in blank titles and author cells。

注意：菜单项在 Google 表格中显示的顺序与在 onOpen() 代码中添加的顺序相同。

结果

在电子表格中，点击 Book-list 菜单来查看新的菜单项：

点击这些新项会导致错误，因为您尚未实现其相应的函数，所以我们接下来执行该操作。

8. 使用逗号分隔符拆分文本

您导入到电子表格中的数据集有几个单元格，其中作者和标题使用逗号不当分隔到一个单元格中：

将文本字符串拆分为单独的列是常见的电子表格任务。Google 表格提供 SPLIT() 函数，可将字符串划分为列。但是，数据集通常存在无法通过 Google 表格的内置函数轻松解决的问题。在这些情况下，您可以编写 Apps 脚本代码来执行清理和整理数据所需的复杂操作。

要开始清理数据，请先实现一个名为 splitAtFirstComma() 的函数，它会在找到英文逗号时将作者和标题划分为各自的单元格。

splitAtFirstComma() 函数应执行以下步骤：

1. 获取表示当前所选单元格的范围。
2. 检查范围内的单元格是否具有英文逗号。
3. 找到英文逗号后，在第一个逗号位置处将字符串拆分为两个（且只有两个）子字符串。为简单起见，您可以假设任何英文逗号表示“[authors], [title]”字符串模式。此外，您还可以假设如果单元格中有多个逗号，可以按字符串中的第一个逗号进行拆分。
4. 将子字符串设置为相应标题和作者单元格的新内容。

实现

要实现这些步骤，您需要使用与之前相同的电子表格服务方法，但还需要使用 JavaScript 来处理字符串数据。请执行以下步骤：

1. 在 Apps 脚本编辑器中，将以下函数添加到脚本项目的末尾：

/**
 * Reformats title and author columns by splitting the title column
 * at the first comma, if present.
 */
function splitAtFirstComma(){
  // Get the active (currently highlighted) range.
  var activeRange = SpreadsheetApp.getActiveRange();
  var titleAuthorRange = activeRange.offset(
    0, 0, activeRange.getHeight(), activeRange.getWidth() + 1);

  // Get the current values of the selected title column cells.
  // This is a 2D array.
  var titleAuthorValues = titleAuthorRange.getValues();

  // Update values where commas are found. Assumes the presence
  // of a comma indicates an "authors, title" pattern.
  for (var row = 0; row < titleAuthorValues.length; row++){
    var indexOfFirstComma =
        titleAuthorValues[row][0].indexOf(", ");

    if(indexOfFirstComma >= 0){
      // Found a comma, so split and update the values in
      // the values array.
      var titlesAndAuthors = titleAuthorValues[row][0];

      // Update the title value in the array.
      titleAuthorValues[row][0] =
        titlesAndAuthors.slice(indexOfFirstComma + 2);

      // Update the author value in the array.
      titleAuthorValues[row][1] =
        titlesAndAuthors.slice(0, indexOfFirstComma);
    }
  }

  // Put the updated values back into the spreadsheet.
  titleAuthorRange.setValues(titleAuthorValues);
}

2. 保存脚本项目。

代码审核

我们来回顾一下包含三个主要部分的新代码：

1：检索突出显示的标题值

前三行建立三个变量，它们引用工作表中的当前数据：

• activeRange 表示在调用 splitAtFirstComma() 函数时用户当前突出显示的范围。为简化此练习，我们可以假设用户仅在突出显示 A 列中的单元格时执行此操作。
• titleAuthorRange 表示一个新范围，它涵盖与 activeRange 相同的单元格，但右侧还包含一列。titleAuthorRange 是使用 Range.offset(rowOffset, columnOffset, numRows, numColumns) 方法创建的。该代码需要此扩展范围，因为它需要一个位置来将找到的任何作者放在标题列中。
• titleAuthorValues 是使用 Range.getValues() 从 titleAuthorRange 中提取的数据的二维数组。

2：检查每个标题，并根据找到的第一个逗号分隔符进行拆分

下一部分将检查 titleAuthorValues 中的值以查找英文逗号。JavaScript For 循环用于检查 titleAuthorValues 的第一列中的所有值。当使用 JavaScript String indexOf() 方法找到英文逗号子字符串 (", ") 时，代码会执行以下操作：

1. 单元格字符串值会复制到 titlesAndAuthors 变量。
2. 英文逗号位置通过 JavaScript String indexOf() 方法确定。
3. 调用 JavaScript String slice() 方法两次，以获取逗号分隔符之前的子字符串以及分隔符之后的子字符串。
4. 子字符串会复制回 titleAuthorValues 二维数组中，并覆盖该位置的现有值。由于我们假设为“[authors], [title]”模式，因此两个子字符串的顺序反转，以便将标题置于第一列中，将作者放入第二列中。

注意：如果代码找不到英文逗号，则它会使行中的数据保持不变。

3：将新值复制回工作表

检查所有标题单元格值后，使用 Range.setValues(values) 方法将更新后的 titleAuthorValues 二维数组复制回电子表格。

要点：该代码可以在找到每个英文逗号时逐一更新每个电子表格单元格，在每次需要更改单元格时调用 Range.setValue(value)。但是，这会导致代码慢得多，因为每个 Range.setValue(value) 调用都需要服务器通信。

更新 Google 表格数据时，最好总是先将值写入二维数组，更新该数组，然后通过单次 Range.setValues(values) 调用将数据推送回工作表。

结果

现在，您可以看到 splitAtFirstComma() 函数的实际效果。请在选择以下内容后选择在第一个逗号处分隔标题/作者菜单项，来尝试运行它...

...一个单元格：

...或多个单元格：

注意：您可以选择电子表格中的任何文本列。使用此函数可以查找英文逗号，并根据您设置的规则拆分文本。但是，如果代码找到逗号分隔符，它将覆盖恰好在原始字符右侧的单元格。

现在，您已经构建了一个 Apps 脚本函数来处理 Google 表格数据。接下来，您将实现第二个拆分器函数。

9. 按“by”分隔符拆分文本

通过查看原始数据，您可能会看到另一个问题。就像一些数据在单个单元格中将标题和作者的格式设置为“[authors], [title]”，其他单元格将作者和标题的格式设置为“[title] by [authors]”：

实现

要解决此问题，可使用上一部分介绍的相同技术，创建一个名为 splitAtLastBy() 的函数。此函数的作用类似于 splitAtFirstComma()，唯一的区别是它搜索的文本模式略有不同。通过执行以下操作来实现此函数：

1. 在 Apps 脚本编辑器中，将以下函数添加到脚本项目的末尾：

/** 
 * Reformats title and author columns by splitting the title column
 * at the last instance of the string " by ", if present.
 */
function splitAtLastBy(){
  // Get the active (currently highlighted) range.
  var activeRange = SpreadsheetApp.getActiveRange();
  var titleAuthorRange = activeRange.offset(
    0, 0, activeRange.getHeight(), activeRange.getWidth() + 1);

  // Get the current values of the selected title column cells.
  // This is a 2D array.
  var titleAuthorValues = titleAuthorRange.getValues();

  // Update values where " by " substrings are found. Assumes
  // the presence of a " by " indicates a "title by authors"
  // pattern.
  for(var row = 0; row < titleAuthorValues.length; row++){
    var indexOfLastBy =
        titleAuthorValues[row][0].lastIndexOf(" by ");
    
    if(indexOfLastBy >= 0){
      // Found a " by ", so split and update the values in
      // the values array.
      var titlesAndAuthors = titleAuthorValues[row][0];
      
      // Update the title value in the array.
      titleAuthorValues[row][0] =
        titlesAndAuthors.slice(0, indexOfLastBy);
      
      // Update the author value in the array.
      titleAuthorValues[row][1] =
        titlesAndAuthors.slice(indexOfLastBy + 4);
    }
  }

  // Put the updated values back into the spreadsheet.
  titleAuthorRange.setValues(titleAuthorValues);
}

2. 保存脚本项目。

代码审核

此代码与 splitAtFirstComma() 之间存在一些关键区别：

1. 子字符串“by”用作字符串分隔符，而不是“,”。
2. 此处使用 JavaScript String.lastIndexOf(substring) 方法，而不是 String.indexOf(substring)。这意味着，如果初始字符串中有多个“by”子字符串，则假定除最后一个 by 之外的所有字符串都是标题的一部分。
3. 拆分字符串后，将第一个子字符串设置为标题，将第二个子字符串设置为作者（该顺序与 splitAtFirstComma() 相反）。

结果

现在，您可以看到 splitAtLastBy() 函数的实际效果。请在选择以下内容后选择在最后一个 "by" 处分隔标题/作者菜单项，来尝试运行它...

...一个单元格：

...或多个单元格：

您已完成 Codelab 的这一部分。现在，您可以使用 Apps 脚本读取和修改工作表中的数据，还可以使用自定义菜单执行其他 Apps 脚本命令。

在下一部分中，您将学习如何使用从公共 API 提取的数据填充空白单元格，进一步改进此数据集。

10. 概览：从公共 API 获取数据

到目前为止，您已优化数据集以解决某些标题和作者格式问题，但数据集仍然缺少一些信息，它们在以下单元格中突出显示：

您不能通过对当前拥有的数据使用字符串操作来获取缺失的数据。相反，您需要从其他来源获取缺失的数据。要实现此目的，您可以从可提供额外数据的外部 API 请求信息。

API 是应用编程接口。这是一个泛指的术语，但基本上就是程序和脚本可调用来请求信息或执行某些操作的一项服务。在本部分中，您将调用一个公开可用的 API 来请求可插入工作表中空白单元格的图书信息。

本部分将介绍如何执行以下操作：

• 从外部 API 来源请求图书数据。
• 从返回的数据中提取标题和作者信息，并将其写入电子表格。

11. 使用 UrlFetch 提取外部数据

在深入研究可直接用于电子表格的代码之前，您可使用公共 Open Library API 专门为请求图书信息创建一个辅助函数，了解如何使用外部 API。

注意：Open Library 是一个互联网归档，它旨在为每个图书出版物创建一个页面。如果您有兴趣，您可以在其官方网站上详细了解其 API 文档。

我们的辅助函数 fetchBookData_(ISBN) 将图书的 13 位 ISBN 编号作为参数，并会返回该图书的相关数据。它连接到 Open Library API 并从中检索信息，然后解析返回的 JSON 对象。

实现

通过执行以下操作来实现此辅助函数：

1. 在 Apps 脚本编辑器中，将以下代码添加到脚本的末尾：

/**
 * Helper function to retrieve book data from the Open Library
 * public API.
 *
 * @param {number} ISBN - The ISBN number of the book to find.
 * @return {object} The book's data, in JSON format.
 */
function fetchBookData_(ISBN){
  // Connect to the public API.
  var url = "https://openlibrary.org/api/books?bibkeys=ISBN:"
      + ISBN + "&jscmd=details&format=json";
  var response = UrlFetchApp.fetch(
      url, {'muteHttpExceptions': true});
  
  // Make request to API and get response before this point.
  var json = response.getContentText();
  var bookData = JSON.parse(json); 
  
  // Return only the data we're interested in.
  return bookData['ISBN:' + ISBN];
}

2. 保存脚本项目。

注意：在 Apps 脚本中，以 _（下划线）结尾的函数名称被视为“不公开”。其他脚本在库中存在时无法调用这些函数，在服务器-客户端通信期间，也不能通过客户端调用这些函数。不过，这些都是高级主题，因此如果您知道只有当前脚本会使用某个函数，则最佳做法是以 _ 结尾。

代码审核

此代码分为两个主要部分：

1：API 请求

在前两行中，fetchBookData_(ISBN) 使用 API 的网址端点和 Apps 脚本的 URL Fetch 服务连接到公共 Open Library API。

url 变量只是一个网址字符串，例如网址。它指向 Open Library 服务器上的某个位置。它还包含三个参数（bibkeys、jscmd 和 format），用于告知 Open Library 服务器您请求的信息以及如何构建响应。在这种情况下，您需要提供图书的 ISBN 编号，并请求以 JSON 格式返回详细信息。

注意：每个公共 API 都定义自己的格式以发出请求和返回结果。要让代码与 API 有效通信，您必须遵循代码定义的格式。对于 Open Library，Open Library Books API 文档介绍了请求和响应格式。使用的响应格式为 JSON（JavaScript 对象表示法），这只是数据整理的一种特定方式。JSON 类似于 HTML 或 XML，但更便于人类阅读。

构建网址字符串后，代码会向该位置发送请求并接收响应。您可以通过 UrlFetchApp.fetch(url, params) 方法实现这一目的。它会向您提供的外部网址发送信息请求，并将生成的响应存储在 response 变量中。除了网址之外，代码还会将可选参数 muteHttpExceptions 设置为 true。此设置意味着如果请求导致 API 错误，您的代码不会停止，而是返回错误响应。

该请求会返回存储在 response 变量中的 HTTPResponse 对象。HTTP 响应包括响应代码、HTTP 标头和主要响应内容。此处的相关信息是主要的 JSON 内容，因此代码必须提取它，然后解析 JSON 以查找和返回所需的信息。

2：解析 API 响应并返回感兴趣的信息

在最后三行代码中，HTTPResponse.getContentText() 方法以字符串形式返回响应的主要内容。此字符串采用 JSON 格式，但 Open Library API 定义了确切的内容和格式。JSON.parse(jsonString) 方法可将 JSON 字符串转换为 JavaScript 对象，以便轻松提取数据的不同部分。最后，该函数会返回与图书的 ISBN 编号相对应的数据。

注意：如果您认为自己需要熟悉 JSON，请转到有关使用 JSON 或 JSON 类参考的 JavaScript 文档。

结果

现在您已实现 fetchBookData_(ISBN)，其代码中的其他函数可找到任何具有 ISBN 编号的图书的信息。您将使用此函数来帮助填写电子表格中的单元格。

12. 将 API 数据写入电子表格

现在，您可以实现一个 fillInTheBlanks() 函数来执行以下操作：

1. 识别有效数据范围内缺失的标题和作者数据。
2. 使用 fetchBookData_(ISBN) 辅助方法调用 Open Library API，以检索特定图书缺失的数据。
3. 更新相应单元格中缺失的标题或作者值。

实现

通过执行以下操作来实现此新函数：

1. 在 Apps 脚本编辑器中，将以下代码添加到脚本项目的末尾：

/**
 * Fills in missing title and author data using Open Library API
 * calls.
 */ 
function fillInTheBlanks(){
  // Constants that identify the index of the title, author,
  // and ISBN columns (in the 2D bookValues array below). 
  var TITLE_COLUMN = 0;
  var AUTHOR_COLUMN = 1;
  var ISBN_COLUMN = 2;

  // Get the existing book information in the active sheet. The data
  // is placed into a 2D array.
  var dataRange = SpreadsheetApp.getActiveSpreadsheet()
    .getDataRange();
  var bookValues = dataRange.getValues();

  // Examine each row of the data (excluding the header row).
  // If an ISBN is present, and a title or author is missing,
  // use the fetchBookData_(isbn) method to retrieve the
  // missing data from the Open Library API. Fill in the
  // missing titles or authors when they're found.
  for(var row = 1; row < bookValues.length; row++){   
    var isbn = bookValues[row][ISBN_COLUMN];
    var title = bookValues[row][TITLE_COLUMN];
    var author = bookValues[row][AUTHOR_COLUMN];
   
    if(isbn != "" && (title === "" || author === "") ){
      // Only call the API if you have an ISBN number and
      // either the title or author is missing.
      var bookData = fetchBookData_(isbn);

      // Sometimes the API doesn't return the information needed.
      // In those cases, don't attempt to update the row.
      if (!bookData || !bookData.details) {
        continue;
      }

      // The API might not return a title, so only fill it in
      // if the response has one and if the title is blank in
      // the sheet.
      if(title === "" && bookData.details.title){
        bookValues[row][TITLE_COLUMN] = bookData.details.title; 
      }

      // The API might not return an author name, so only fill it in
      // if the response has one and if the author is blank in
      // the sheet.
      if(author === "" && bookData.details.authors
          && bookData.details.authors[0].name){
        bookValues[row][AUTHOR_COLUMN] =
          bookData.details.authors[0].name; 
      }
    }
  }
  
  // Insert the updated book data values into the spreadsheet.
  dataRange.setValues(bookValues);
}

2. 保存脚本项目。

代码审核

此代码分为三个部分：

1：读取现有图书信息

函数的前三行定义了常量，目的是提高代码的可读性。接下来的两行中，bookValues 变量用于维护工作表的图书信息的本地副本。代码将从 bookValues 中读取信息，使用 API 填写缺失的信息，并将这些值写回电子表格。

注意：此安排与 splitAtFirstComma() 和 splitAtLastBy() 不同。在这些函数中，代码仅检查当前突出显示的标题及其对应的行。而在这里，您使用 Spreadsheet.getDataRange() 检查整个工作表。

2：使用辅助函数提取缺失的信息

代码会循环遍历 bookValues 中的每一行，以查找缺失的标题或作者。为了减少 API 调用数量并提高效率，代码仅在满足以下条件时调用 API：

1. 该行的 ISBN 列具有值。
2. 该行的标题或作者单元格为空。

如果满足条件，则代码将使用您之前实现的 fetchBookData_(isbn) 辅助函数调用 API，并将结果存储在 bookData 变量中。它现在应包含您要插入工作表的缺失信息。

剩下唯一的任务是向我们的电子表格添加 bookData 信息。但是需要注意。遗憾的是，Open API Book API 等公共 API 有时不包含您请求的信息，或者偶尔会出现一些其他问题，导致信息无法提供。如果您假设每个 API 请求都会成功，则您的代码将不够可靠，无法处理意外错误。

为了确保您的代码能够处理 API 错误，代码必须先检查 API 响应是否有效，然后才能尝试使用它。代码具有 bookData 后，会执行一个简单的检查，来验证 bookData 和 bookData.details 是否存在，然后再尝试从中读取信息。如果缺少其中任何一个，则表示 API 没有您需要的数据。在这种情况下，continue 命令会指示代码跳过该行 - 您无法填写缺失的单元格，但至少您的脚本不会崩溃。

3：将更新后的信息写回工作表

代码的最后一部分有类似的检查，用于验证 API 返回的标题和作者信息。仅当原始标题或作者单元格为空，且 API 返回您可放置在此处的值时，代码才会更新 bookValues 数组。

检查工作表中的所有行后，循环便会退出。最后一步是使用 Range.setValues(values) 将现已更新的 bookValues 数组写回电子表格。

结果

现在，您可以清理图书数据了。执行以下操作：

1. 如果您尚未清理逗号问题，请在工作表中突出显示 A2:A15 范围，然后选择图书信息 > 在第一个逗号处分隔标题/作者。
2. 如果您尚未清理“by”问题，请在工作表中突出显示 A2:A15 范围，然后选择图书信息 > 在最后一个“by”处分隔标题/作者。
3. 如需填写所有剩余单元格，请选择图书信息 > 填写空白标题和作者单元格：

13. 总结

恭喜您完成此 Codelab。您已学习如何创建自定义菜单来激活 Apps 脚本代码的不同部分。您还了解了如何使用 Apps 脚本服务和公共 API 将数据导入 Google 表格。这是电子表格处理中的一项常见操作，并且您可使用 Apps 脚本您从各种来源导入数据。最后，您还了解了如何使用 Apps 脚本服务和 JavaScript 来读取、处理和插入电子表格数据。

您觉得此 Codelab 对您有帮助吗？

是 否

您学到的内容

• 如何从 Google 电子表格导入数据。
• 如何在 onOpen() 函数中创建自定义菜单。
• 如何解析和处理字符串数据值。
• 如何使用 URL Fetch 服务调用公共 API。
• 如何解析从公共 API 来源检索到的 JSON 对象数据。

主要概念：

1. onOpen() 是用于创建菜单项的简单触发器。简单触发器具有您必须遵循的各种限制。
2. 读取或写入电子表格单元格数据时，最佳做法是使用 Range.getValues() 或 Range.setValues(values) 一次读取或写入整个范围。请勿单独读取或设置每个单元格，因为会拖慢速度。
3. 以 _（下划线）结尾的 Apps 脚本函数名称被视为“不公开”。其他脚本在库中存在时无法调用这些函数，也不能通过服务器-客户端通信期间由客户端调用这些函数。如果您知道某个函数只会被当前脚本使用，则最佳做法是函数名称以 _ 结尾。
4. 使用外部 API 返回的数据时，请先验证数据，然后再尝试使用它。如果 API 返回意外结果，则会减少代码中的错误。

关键术语：

• 活动（状态）：表示指定的电子表格、工作表、范围或单元格是电子表格用户当前正在查看或突出显示的文件。
• 活动单元格：活动工作表中通过光标聚焦突出显示的单元格。
• 活动范围：活动工作表中当前突出显示的一个或多个单元格。
• API：应用编程接口是一种服务，程序或脚本可以调用该服务来请求信息或执行某些操作。
• 授权：用户授予权限，以允许 Apps 脚本访问相关 Google 服务中的用户数据或代表用户执行操作的过程。
• 容器绑定脚本：绑定到并基于 Google Workspace 文档（例如 Google 表格或 Google 文档）创建的任何脚本。
• 辅助函数：通常由另一个函数调用的函数，用于完成一个小型子任务。当需要在不同位置多次完成子任务时，可使用辅助函数。
• JSON：用于定义文本中的数据对象及其特征的文件格式。
• onOpen()：在脚本中定义时，这是一个简单的触发器，只要文件打开或重新加载就会触发。
• 不公开函数：不能作为库的一部分或者由客户端通过服务器-客户端通信调用的函数。Apps 脚本不公开函数的名称以 _ 结尾。
• 范围**：工作表中的一个或多个相邻单元格。Range** 类可让您读取和更新工作表中的范围。
• 脚本编辑器：Apps 脚本的浏览器内代码编辑器。
• 简单触发器：Apps 脚本中触发器的子类型，具有预留的触发器函数名称和某些限制。
• 工作表**：电子表格的单页。有时用于指代 Google 云端硬盘中的 Google 表格文件。Sheet** 类可让您访问和修改工作表。
• SpreadsheetApp：此类用作电子表格服务的父类，为读取或操作 Google 表格数据的代码提供起点。
• 电子表格**：位于 Google 云端硬盘中的 Google 表格文件。Spreadsheet** 类可让您访问和修改电子表格。
• 电子表格服务：一种 Apps 脚本服务，允许脚本创建、访问和修改 Google 表格文件。
• 触发器：与 Apps 脚本函数关联的事件。当事件（例如正在打开电子表格）发生时，触发器将“触发”并自动执行关联的 Apps 脚本函数。
• URL Fetch 服务：Apps 脚本服务，允许脚本连接到网址端点以发出请求并接收响应。

后续步骤

此播放列表中的下一个 Codelab 将更详细地介绍如何设置电子表格中数据的格式。

可在数据格式设置处找到下一个 Codelab。