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

1. 简介

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

学习内容

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

准备工作

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

所需条件

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

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

2. 电子表格服务简介

这四个类构成了电子表格服务的基础:SpreadsheetAppSpreadsheetSheetRange。本部分介绍了这些类及其用途。

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.访问和修改电子表格

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

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

重命名活动电子表格

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

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

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

226c7bc3c2fbf33e.png

我们来看看您输入的代码。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();
}
  1. 保存脚本项目。
  2. 如需运行脚本,请从函数列表中选择 duplicateAndOrganizeActiveSheet,然后点击运行

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

d24f9f4ae20bf7d4.gif

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

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

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

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) 方法会冻结给定行数(在本例中为 2 行),以便在读者向下滚动电子表格时保持标题行可见。

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

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。