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

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 表格文件的数据。您也可以使用该类执行其他文件级操作，如添加协作者。

f00cc1a9eb606f77.png

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

39dbb10f83e3082.png

简言之，Spreadsheet 类可以处理一系列工作表，并在 Google 云端硬盘中定义 Google 表格文件。Sheet 类则可处理电子表格中的个别工作表。

Range 类

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

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

3. 设置

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

5376f721894b10d9.png

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

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

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

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

4. 访问和修改电子表格

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

这些操作虽然很简单，但它们往往是构成规模更大、更复杂的工作流程的基础。了解如何使用脚本代码自动完成这些任务后，您便能更轻松地学习如何自动执行更复杂的操作。

重命名活动电子表格

假设您想将默认名称“未命名电子表格的副本”更改为能够更好地反映电子表格用途的标题。那么，您可以使用 SpreadsheetApp 和 Spreadsheet 类来执行此操作。

  1. 在脚本编辑器中，将默认的 myFunction() 代码块替换为以下代码：
function renameSpreadsheet() {
  var mySS = SpreadsheetApp.getActiveSpreadsheet();
  mySS.rename("2017 Avocado Prices in Portland, Seattle");
}
  1. 要保存脚本，请点击“保存”保存。
  2. 如需重命名 Apps 脚本项目，请点击未命名项目，输入“牛油果价格”作为新项目名称，然后点击重命名。
  3. 如需运行脚本，请从函数列表中选择 renameSpreadsheet，然后点击运行。
  4. 按照屏幕上的说明对宏进行授权。 如果您看到“此应用未经验证”的消息，请点击高级，然后点击转到“牛油果价格”（不安全）。在下一个屏幕上，点击允许。

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

226c7bc3c2fbf33e.png

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

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

复制活动工作表

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

  1. 将以下新函数添加到脚本项目中已有的 renameSpreadsheet() 函数下：
function duplicateAndOrganizeActiveSheet() {
  var mySS = SpreadsheetApp.getActiveSpreadsheet();
  var duplicateSheet = mySS.duplicateActiveSheet();
}
  1. 保存脚本项目。
  2. 如需运行脚本，请从函数列表中选择 duplicateAndOrganizeActiveSheet，然后点击运行。

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

d24f9f4ae20bf7d4.gif

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

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

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

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

更改工作表名称

重命名工作表就和 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());
}
  1. 保存并运行该函数。

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

91295f42354f62e7.gif

在添加的代码中，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);
}
  1. 保存并运行该函数。

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

2e57c917ab157dad.gif

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

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

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"));
}
  1. 保存并运行该函数。

运行此函数时，系统会创建并激活一个复制的工作表，并设置该工作表的格式。此外，F 列的内容会移动到 C 列：

10ea483aec52457e.gif

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

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

对范围排序

通过 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);
}
  1. 保存并运行该函数。

现在，除了上述格式设置之外，该函数还会使用 C 列中的价格信息对表中的所有数据进行排序：

a6cc9710245fae8d.png

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

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

7. 总结

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

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

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

是 否

您学到的内容

后续步骤

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

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