使用 Google 表格创建 Apps 脚本的基础知识 #2：电子表格、表格和范围

使用 Google 表格的 Apps 脚本的基础知识 2：电子表格、工作表和范围

关于此 Codelab

上次更新时间：4月 4, 2022

Google Workspace Developer Relations 编写

1. 简介

欢迎观看“Google 表格的 Apps 脚本基础知识”Codelab 播放列表的第二部分。上一个 Codelab 重点介绍了脚本编辑器、宏和自定义函数的概念。此 Codelab 会详细介绍电子表格服务，您可以使用它在 Google 表格中读取、写入和处理数据。

学习内容

• 电子表格、工作表和范围在 Apps 脚本中的表示方式。
• 如何使用以下项访问、创建和重命名活动（打开）电子表格：SpreadsheetApp 和 Spreadsheet 类。
• 如何使用 Sheet 类更改工作表的名称和范围的列/行方向。
• 如何使用 Range 类指定、激活、移动一组单元格或一系列数据并对其进行排序。

准备工作

这是“Google 表格的 Apps 脚本基础知识”播放列表中的第二个 Codelab。在开始之前，请务必完成第一个 Codelab：宏和自定义函数。

所需条件

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

下一部分将介绍电子表格服务的核心类。

2. 电子表格服务简介

这四个类构成了电子表格服务的基础：SpreadsheetApp、Spreadsheet、Sheet 和 Range。本部分介绍了这些类及其用途。

SpreadsheetApp 类

在展开电子表格、工作表和范围之前，您应查看其父类：SpreadsheetApp。许多脚本会先调用 SpreadsheetApp 方法，因为它们可以提供您 Google 表格文件的初始访问点。您可以将 SpreadsheetApp 视为电子表格服务的主类。此处未深入探讨 SpreadsheetApp 类。不过，在此 Codelab 后面的内容中，您可以找到示例和练习，以帮助您了解本课。

电子表格、工作表及其类

在表格中，电子表格是 Google 表格文件（存储在 Google 云端硬盘中），其中包含按行和列整理的数据。电子表格有时称为“Google 表格”，这种方式与将文档称为“Google 文档”相同。

您可以使用 Spreadsheet 类来访问和修改 Google 表格文件数据。您也可以使用该类执行其他文件级操作，如添加协作者。

工作表** 表示电子表格的单个页面，有时也称为“标签页”。每个电子表格可以包含一个或多个工作表。您可以使用 Sheet** 类来访问和修改工作表级数据和设置，例如移动行或数据列。

要点：除了名称之外，电子表格还提供该工作表独有的 ID 字符串。电子表格网址中会显示此 ID，格式为：

https://docs.google.com/spreadsheets/d/spreadsheetID/edit#gid=0

电子表格中的每个工作表也都有一个与该 ID 对应的 ID 编号。此 ID 号码是随机生成的，但您也可以在电子表格网址中看到，格式为：

https://docs.google.com/spreadsheets/d/spreadsheetID/edit#gid=sheetID

在此示例中，您可以看到两个 ID：

由于电子表格和工作表的 ID 是唯一的（并且电子表格名称不是唯一的），因此最好尽可能使用 ID 来指定电子表格和工作表。例如，您可以在 Google 云端硬盘中创建多个同名的电子表格。通过使用 SpreadsheetApp.openById(id) 等脚本方法来打开电子表格，您可以避免意外修改错误的文件。

请参阅 Google 表格简介，详细了解 Google 表格中的 ID。

总而言之，Spreadsheet 类可处理一系列工作表，并定义 Google 云端硬盘中的 Google 表格文件。Sheet 类适用于电子表格中的各个工作表。

Range 类

大多数数据操纵操作（例如，读取、写入或格式化单元格数据）都需要定义运算所适用的单元格。您可以使用 Range 类选择工作表中的特定单元格集。此类的实例表示范围，即工作表中一个或多个相邻单元格组成的群组。可以按行号和列号或使用 A1 表示法指定范围。

关键术语：在 Apps 脚本中，活动工作表是当前在 Google 表格界面中打开和可见的表格。同样，活动范围是活动表格中当前突出显示的一个或多个单元格的集合。活动单元格是活动工作表中具有光标焦点的单个突出显示单元格。

许多类方法都会返回所请求类的有效对象。例如，运行 SpreadsheetApp.getActiveSpreadsheet() 会返回用户的有效电子表格或当前电子表格。通过获取有效的电子表格，您可以将操作应用到该电子表格。同样，Sheet.activate() 方法等方法可更改活动对象。

启用相应工作表、范围或单元格相当于在 Google 表格界面中点击该工作表、范围或单元格，因此光标会移动到该位置。

此 Codelab 的其余部分介绍了适用于这些类及其方法的脚本示例。

3. 设置

在继续操作之前，您需要一个包含一些数据的电子表格。我们为您提供了一个电子表格：点击此链接复制数据表，然后点击创建副本。

示例电子表格的副本已放入 Google 云端硬盘文件夹中，并命名为“‘无标题电子表格的副本’”。使用此电子表格完成此 Codelab 的练习。

请注意，您可以通过点击扩展程序 > Apps 脚本从 Google 表格打开脚本编辑器。

首次在脚本编辑器中打开 Apps 脚本项目时，脚本编辑器会为您创建一个脚本项目和一个脚本文件。

下一部分将向您介绍如何使用 Spreadsheet 类改进此电子表格。

4. 访问和修改电子表格

在这一部分中，您将了解如何使用 SpreadsheetApp 和 Spreadsheet 类来访问和修改电子表格。具体而言，练习将向您介绍如何重命名电子表格以及如何在电子表格中创建工作表。

这些操作虽然简单，但往往是规模更大、更复杂的工作流程的一部分。了解如何使用脚本代码自动执行这些任务后，您将更容易学习如何自动执行更复杂的操作。

重命名活动电子表格

假设您想将默认名称“Untitled of 电子表格名称”更改为更能反映电子表格用途的标题。您可以使用 SpreadsheetApp 和 Spreadsheet 类执行此操作。

1. 在脚本编辑器中，将默认的 myFunction() 代码块替换为以下代码：

function renameSpreadsheet() {
  var mySS = SpreadsheetApp.getActiveSpreadsheet();
  mySS.rename("2017 Avocado Prices in Portland, Seattle");
}

2. 要保存脚本，请点击“保存”。
3. 要重命名 Apps 脚本项目，请点击 Untitled project，输入“Avocado price”作为新项目名称，然后点击 Rename。
4. 如需运行脚本，请从函数列表中选择 renameSpreadsheet，然后点击运行。
5. 按照屏幕上的说明授权宏。如果您看到“此应用未经验证”的消息，请点击高级，然后点击转到 Avocado 价格（不安全）。在下一个屏幕上，点击允许。

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

函数执行后，电子表格的文件名应发生变化：

我们来看看您输入的代码。getActiveSpreadsheet() 方法会返回一个表示有效电子表格的对象，即您创建的练习电子表格的副本。此电子表格对象存储在 mySS 变量中。对 mySS 调用 rename(newName) 会将电子表格在 Google 云端硬盘中的名称更改为“2017 Avocado Prices in Portland, Seattle, Seattle”

由于 mySS 变量是对电子表格的引用，因此您可以通过对 mySS 调用 Spreadsheet 方法（而不是反复调用 getActiveSpreadsheet()）来使代码更简洁、更高效。

复制活动工作表

在当前电子表格中，您只有一个工作表。您可以调用 Spreadsheet.duplicateActiveSheet() 方法来复制工作表：

1. 将以下新函数添加到脚本项目中已有的 renameSpreadsheet() 函数下：

function duplicateAndOrganizeActiveSheet() {
  var mySS = SpreadsheetApp.getActiveSpreadsheet();
  var duplicateSheet = mySS.duplicateActiveSheet();
}

2. 保存脚本项目。
3. 如需运行脚本，请从函数列表中选择 duplicateAndOrganizeActiveSheet，然后点击运行。

返回到表格，可以看到新的“Sheet_Original 的副本”工作表标签页已添加到您的电子表格中。

在这个新函数中，duplicateActiveSheet() 方法可创建、激活和返回电子表格中的重复工作表。由此生成的工作表存储在 duplicateSheet 中，但代码尚未对该变量执行任何操作。

在下一部分中，您将使用 Sheet 类重命名副本工作表并设置其格式。

5. 使用 Sheet 类设置工作表的格式

Sheet 类提供了允许脚本读取和更新工作表的方法。在这一部分中，您将了解如何使用 Sheet 类中的方法更改工作表的名称和列宽。

注意**：Spreadsheet** 和 Sheet 类共用多种常见工作表具体方法，例如 Spreadsheet.deleteColumn(columnPosition) 和 Sheet.deleteColumn(columnPosition)。

当您调用 Spreadsheet.deleteColumn(columnPosition) 时，该方法会从电子表格中当前有效的（已打开）工作表中移除列。您可以调用 getActiveSheet()，然后调用 Sheet.deleteColumn(columnPosition) 来获得相同的结果。Spreadsheet 版本只是为您节省时间。

更改工作表名称

只需使用 renameSpreadsheet() 即可对该电子表格进行重命名。只需调用一次方法。

1. 在 Google 表格中，点击 Sheet_Original 工作表以将其激活。
2. 在 Apps 脚本中，修改 duplicateAndOrganizeActiveSheet() 函数以匹配以下内容：

function duplicateAndOrganizeActiveSheet() {
  var mySS = SpreadsheetApp.getActiveSpreadsheet();
  var duplicateSheet = mySS.duplicateActiveSheet();

  // Change the name of the new sheet.
  duplicateSheet.setName("Sheet_" + duplicateSheet.getSheetId());
}

3. 保存并运行该函数。

在 Google 表格中，当您运行函数时，系统会创建并创建一个工作表副本：

在添加的代码中，setName(name) 方法会更改 duplicateSheet 的名称，并使用 getSheetID() 获取工作表的唯一 ID 号。+ 运算符将工作表 ID 串联到了 "Sheet_" 字符串的末尾。

修改工作表的列和行

您还可以使用 Sheet 类设置工作表格式。例如，我们可以更新 duplicateAndOrganizeActiveSheet() 函数，使其也会调整重复工作表的列大小，并添加冻结行：

1. 在 Google 表格中，点击 Sheet_Original 工作表以将其激活。
2. 在 Apps 脚本中，修改 duplicateAndOrganizeActiveSheet() 函数以匹配以下内容：

function duplicateAndOrganizeActiveSheet() {
  var mySS = SpreadsheetApp.getActiveSpreadsheet();
  var duplicateSheet = mySS.duplicateActiveSheet();

  // Change the name of the new sheet.
  duplicateSheet.setName("Sheet_" + duplicateSheet.getSheetId());

  // Format the new sheet.
  duplicateSheet.autoResizeColumns(1, 5);
  duplicateSheet.setFrozenRows(2);
}

3. 保存并运行该函数。

在 Google 表格中，系统会创建、复制、激活副本工作表并设置其格式：

您添加的代码使用 autoResizeColumns(startColumn, numColumns) 调整工作表的列大小，以方便阅读。setFrozenRows(rows) 方法会冻结给定行数（在本例中为 2 行），以便在读者向下滚动电子表格时保持标题行可见。

注意：在 Apps 脚本中使用数组时，您需要遵循列表以索引 0 开头的数组格式。但是，在处理行和列时，索引会从 1 开始。此逻辑与传统的电子表格格式一致，即您在 Google 表格中的第 1 行和 A 列开始。

在使用与列和行相关的方法（例如 autoResizeColumns(startColumn, numColumns)）时，请注意这一点。在本练习中，您需要输入 1（而不是 0）作为参数来引用 duplicateSheet 的列 A。

在下一部分中，您将了解范围和基本数据操纵。

6. 使用 Range 类重新排列数据

Range 类及其方法提供电子表格服务中的大部分数据操作和格式设置选项。

本部分将介绍对范围的基本数据操作。这些练习着重于如何利用 Apps 脚本中的范围，同时此播放列表中的其他 Codelab 更深入地探讨数据操纵和数据格式。

移动范围

您可以使用类方法和 A1 表示法（在电子表格中标识特定单元格集的简写形式）激活和移动数据范围。如果您需要重新熟悉一下，可以参阅 A1 表示法说明。

请更新 duplicateAndOrganizeActiveSheet() 方法，使其也移动一些数据：

1. 在 Google 表格中，点击 Sheet_Original 工作表以将其激活。
2. 在 Apps 脚本中，修改 duplicateAndOrganizeActiveSheet() 函数以匹配以下内容：

function duplicateAndOrganizeActiveSheet() {
  var mySS = SpreadsheetApp.getActiveSpreadsheet();
  var duplicateSheet = mySS.duplicateActiveSheet();

  // Change the name of the new sheet.
  duplicateSheet.setName("Sheet_" + duplicateSheet.getSheetId());

  // Format the new sheet.
  duplicateSheet.autoResizeColumns(1, 5);
  duplicateSheet.setFrozenRows(2);

  // Move column F to column C.
  var myRange = duplicateSheet.getRange("F2:F");
  myRange.moveTo(duplicateSheet.getRange("C2"));
}

3. 保存并运行该函数。

当您运行此函数时，系统会创建副本、激活副本以及设置其格式。F 列的内容会移动到 C 列：

新代码使用 getRange(a1Notation) 方法标识要移动的数据范围。通过输入 A1 表示法“F2:F”作为方法的参数，您可以指定 F 列（不包括 F1）。如果指定范围存在，getRange(a1Notation) 方法会返回其 Range 实例。代码将实例存储在 myRange 变量中，以方便使用。

找到范围后，moveTo(target) 方法将接受 myRange 的内容（包括值和格式），并移动它们。目的地（C 列）使用 A1 表示法“C2”进行指定。这是单个单元格，而不是列。移动数据时，您无需使尺寸与目标和目标范围相匹配。Apps 脚本只是对齐每个单元格的第一个单元格。

注意：在编写自己的脚本时，请务必对脚本进行全面测试，以确保数据最终出现在预期的位置。更改单元格值时，Apps 脚本会覆盖现有数据值。如果不小心，您可以覆盖您想保留的数据。在此示例中，C 列为空，但如果其中存在数据，则 moveTo(target) 方法会将其移除。

对范围排序

借助 Range 类，您可以读取、更新和整理单元格组。例如，您可以使用 Range.sort(sortSpecObj) 方法对数据范围进行排序：

1. 在 Google 表格中，点击 Sheet_Original 工作表以将其激活。
2. 在 Apps 脚本中，修改 duplicateAndOrganizeActiveSheet() 函数以匹配以下内容：

function duplicateAndOrganizeActiveSheet() {
  var mySS = SpreadsheetApp.getActiveSpreadsheet();
  var duplicateSheet = mySS.duplicateActiveSheet();

  // Change the name of the new sheet.
  duplicateSheet.setName("Sheet_" + duplicateSheet.getSheetId());

  // Format the new sheet.
  duplicateSheet.autoResizeColumns(1, 5);
  duplicateSheet.setFrozenRows(2);

  // Move column F to column C.
  var myRange = duplicateSheet.getRange("F2:F");
  myRange.moveTo(duplicateSheet.getRange("C2"));

  // Sort all the data using column C (Price information).
  myRange = duplicateSheet.getRange("A3:D55");
  myRange.sort(3);
}

3. 保存并运行该函数。

现在，除了我们之前的格式之外，此函数还会使用 C 列中的价格信息对表格中的所有数据进行排序：

新代码将使用 getRange(a1Notation) 指定包含 A3:D55（除列标题外的整个表格）的新范围。然后，代码会调用 sort(sortSpecObj) 方法来对表格进行排序。此处，sortSpecObj 参数是要排序的列号。该方法会对范围进行排序，因此指定的列值会从最低到最高（升序值）。sort(sortSpecObj) 方法可以执行更复杂的排序要求，但您在此处并不需要这些要求。您可以在方法参考文档中查看调用排序范围的所有不同方式。

恭喜，您已成功完成了此 Codelab 中的所有练习。下一部分将回顾此 Codelab 的要点，并预览此播放列表中的下一个 Codelab。

7. 总结

您已学完此 Codelab。您现在可以在 Apps 脚本中使用和定义 电子表格服务的基本类和术语。

接下来可继续进入下一个 Codelab。

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

是否

您学到的内容

• 电子表格、工作表和范围在 Apps 脚本中的表示方式。
• SpreadsheetApp、Spreadsheet、Sheet 和 Range 类的一些基本用法。

主要概念：

1. 在 Apps 脚本中，所有数组都遵循以下规范：列表从索引 0 开始。但是，行和列会从 1 开始编入索引。
2. 在 Apps 脚本中，当前打开的工作表，突出显示的范围，单元格则被视为活动状态。许多 Apps 脚本方法可以访问或修改活跃元素。
3. Spreadsheet 和 Sheet 类共用一些特定于工作表的方法。Spreadsheet 类中的重复方法只是为了方便用户使用，因为它们通常在被调用时以当前工作表为目标。
4. 每个电子表格和工作表都有一个唯一 ID，您可以在浏览器网址中找到该 ID。电子表格和工作表类方法会使用 ID。您可以访问 Google 表格简介，详细了解 ID 和网址。

关键术语：

• 活动（状态）：表示指定的电子表格、工作表、范围或单元格是电子表格用户当前正在查看或突出显示的文件。
• 活动单元格：活动工作表中通过光标聚焦突出显示的单元格。
• 活动范围：活动工作表中当前突出显示的一个或多个单元格。
• 授权：用户授予权限，以允许 Apps 脚本访问相关 Google 服务中的用户数据或代表用户执行操作的过程。
• 容器绑定脚本：绑定到并基于 Google Workspace 文档（例如 Google 表格或 Google 文档）创建的任何脚本。
• 范围：** 工作表中一个或多个相邻单元格组成的组。Range** 类可让您读取和更新工作表中的范围。
• 脚本编辑器：Apps 脚本的浏览器内代码编辑器。
• 工作表**：电子表格（类似于 Microsoft Excel 的工作簿）的一页。有时用于指代 Google 云端硬盘中的 Google 表格文件。Sheet** 类可让您访问和修改工作表。
• SpreadsheetApp：此类用作电子表格服务的父类，为读取或操作 Google 表格数据的代码提供起点。
• 电子表格**：位于 Google 云端硬盘中的 Google 表格文件。借助 Spreadsheet** 类，您可以访问和修改电子表格。
• 电子表格服务：一种 Apps 脚本服务，允许脚本创建、访问和修改 Google 表格文件。

后续步骤

此播放列表中的下一个 Codelab 将更详细地介绍如何读取、写入和修改电子表格中的数据。

在处理数据中可找到下一个 Codelab。