关于此 Codelab
1. 欢迎使用 Apps 脚本
什么是 Apps 脚本？
Apps 脚本是一款快速应用开发平台，可助您自动执行、自定义和扩展 Google Workspace 体验。借助 Apps 脚本，您可以简化 Google Workspace 中繁琐或复杂的工作，从而节省时间和精力。
Apps 脚本功能包括以下内容：
- Apps 脚本的内置服务可助您通过脚本读取、更新和操作 Google Workspace 应用数据。
- 您可以使用 Apps 脚本的浏览器内代码编辑器创建脚本，而无需安装和运行代码开发软件。
- 您可以为 Google Workspace 编辑器设计界面，以便从这些编辑器直接使用菜单项、对话框和边栏激活脚本。
此“使用 Google 表格的 Apps 脚本基础知识”Codelab 播放列表介绍了 Apps 脚本的基础知识以及如何使用它来改善 Google 表格体验。此 Codelab 侧重于介绍 Apps 脚本的基础知识。
电子表格服务
您可以使用 Apps 脚本来扩展 Google 表格，以节省时间和精力。Apps 脚本提供电子表格服务，可让脚本与您的 Google 表格文件及其包含的数据进行交互。您可以使用此服务自动执行以下常见电子表格任务：
- 创建或修改电子表格。
- 读取和更新单元格数据、公式和格式设置。
- 创建自定义按钮和菜单。
- 导入和导出其他 Google 应用或第三方来源中的数据。
- 共享和控制对电子表格的访问权限。
学习内容
此播放列表包含开始使用 Apps 脚本和 Google 表格所需的所有主题：
此播放列表中的 Codelab 应按顺序阅读，因此请从此教程开始，按顺序完成，以获得最佳学习体验。
转到下一部分，详细了解此 Codelab 的内容。
2. 简介
欢迎使用此播放列表的第一个 Codelab。在此 Codelab 中，您将学习使用 Google 表格的 Apps 脚本的基本用法。具体来说，此 Codelab 侧重于两个主要概念：宏和自定义函数。
宏是 Google 表格中记录的一系列操作。记录后，您可以激活宏，以便稍后使用菜单项或快捷键重复这些操作。您可以在 Google 表格和 Apps 脚本代码编辑器中创建和更新自己的宏。
在 Apps 脚本代码编辑器中，您还可以创建自定义函数。与 Google 表格提供的内置函数（例如 SUM 或 AVERAGE）类似，您可以使用 Apps 脚本编写自己的自定义函数以执行简单、小众的操作（例如转换或字符串串联）。创建好后，您可以像内置函数一样在 Google 表格中调用这些函数。自定义函数也可用于您编写的单元公式，您可以根据需要将其与其他函数组合使用。
阅读以下内容，了解此 Codelab 涉及的概念和要求。
学习内容
- 如何为 Google 表格创建脚本。
- 如何浏览 Apps 脚本编辑器。
- 如何创建和更新宏。
- 如何创建第一个 Google 表格自定义函数。
您需要满足的条件
- 基本熟悉 JavaScript
- 基本熟悉 Google 表格
- 能够读取 Google 表格 A1 表示法
您已完成简介。转到下一部分以开始开始使用宏。
3. 在 Google 表格中创建宏
通常，在处理电子表格时，您可能会进入重复操作循环（复制单元格值、设置格式、创建公式等），这可能会变得繁琐并导致错误。为了自动执行重复操作，Google 表格提供了宏。借助宏，您可以在表格中“记录”一系列操作。使用记录的宏，您可以通过简单的热键在电子表格的其他位置重复执行相同的操作。
在本部分中，您将学习如何在 Google 表格中构建宏。在下一部分中，您将了解如何使用 Apps 脚本构建宏。
准备工作
在继续操作之前，您需要一个包含一些数据的电子表格。我们为您提供了一个电子表格：点击此链接复制数据表，然后点击创建副本。
供您使用的样本电子表格副本位于您的 Google 云端硬盘文件夹中，并命名为“十大最高票房电影副本（2018 年）”。
创建宏
现在您已经具有待使用的电子表格，可以在 Google 表格中记录宏了。在本例中，您将创建一个宏来设置数据的标题行格式。您只需执行以下操作即可：
- 点击单元格 A1 可将光标置于行中。这是标题行。
- 在菜单中，依次选择扩展程序 > 宏 > 录制宏。
开始记录后，Google 表格会记住您在电子表格中执行的每项操作：突出显示单元格、添加数据、切换到不同的工作表、设置格式等。然后，这些操作将成为“脚本”，您通过保存宏并在稍后将其激活便可重复该脚本。
- 在宏对话框中，选择相对引用。
- 选择第 1 行.
- 将最上一行的填充颜色从白色重新设置为暗洋红 3。
- 将最上一行的文本颜色从黑色更改为白色。
- 要加粗文本，请按 Ctrl+B（在 macOS 上按 Cmd+B）。
- 要冻结最上一行，请选择查看 > 冻结 > 1 行。
- 在宏对话框中点击保存。随即会显示一个新对话框，要求您为宏命名。输入名称“Header”，然后点击保存。
您使用 Google 表格的界面创建了一个宏，专门用于设置标题格式。
激活您的宏
您可以按照以下说明在 Google 表格中应用新宏：
- 要创建工作表，请点击“添加工作表”
。
- 在新表格中，向 A1:C2 添加一些文本。请随意遵循以下示例输入：
- 突出显示第一行。
- 要将该宏应用于所选区域，请点击扩展程序、宏和标题。
- 按照屏幕上的说明对宏进行授权。
- 重复第 4 步，再次运行宏（授权会停止首次执行）。
恭喜，您已经学会了如何在 Google 表格中应用宏。您的电子表格应如下所示：
借助宏，您可以高效地创建电子表格；在本 Codelab 的下一部分中，您将学习如何使宏变得更加强大。此处的要点是：在记录宏时，您真正要执行的操作是编写 Apps 脚本代码。在后台，Google 会构建与宏操作匹配的代码。在下一部分，您将学习如何直接使用 Apps 脚本的浏览器内编辑器修改代码。
4. 脚本编辑器中的宏
创建宏时，Google 表格会将操作保存为 Apps 脚本函数。激活宏后，Google 表格将调用 Apps 脚本函数以按照相同顺序应用这些操作。
脚本编辑器
现在您已经创建了一个宏，可以查看其代码。要查看宏脚本，请点击扩展程序 > Apps 脚本，以打开 Apps 脚本的浏览器代码编辑器。
脚本编辑器允许使用 Apps 脚本编写代码，并在 Google 服务器上运行这些脚本。
macro.gs 的分析
查看当前脚本。Google 表格在您记录 Header 宏时创建了 macros.gs 脚本文件，并使用名为 Header 的相应 Apps 脚本函数填充该文件。激活 Header 宏后，表格将运行此函数。
查看下图，熟悉 Apps 脚本中的宏函数的结构。如果您以不同顺序记录这些步骤，或者在记录时点击电子表格，代码可能看起来略有不同。
第一行是注释，它可影响授权：
/** @OnlyCurrentDoc */
大多数脚本会先要求用户提供一些权限，然后才能运行。这些权限控制用户允许脚本执行的操作。如果脚本项目中存在 @OnlyCurrentDoc 注释，则 Apps 脚本仅请求访问和更新当前电子表格的权限。如果没有此注释，Apps 脚本将请求访问和更新用户的所有电子表格的权限。最佳做法是在仅处理单个文件时添加此注释。宏录制器会自动为您添加此注释。
要了解 Apps 脚本如何表示宏的说明，您可以查看以下函数：
function Header(){
var spreadsheet = SpreadsheetApp.getActive();
var sheet = spreadsheet.getActiveSheet();
sheet.getRange(
spreadsheet.getCurrentCell().getRow(),
1, 1, sheet.getMaxColumns()).activate();
spreadsheet.getActiveRangeList().setBackground('#4c1130')
.setFontColor('#ffffff')
.setFontWeight('bold');
spreadsheet.getActiveSheet().setFrozenRows(1);
};
此代码在您激活 Header 宏时运行。在 function 之后，标签 Header() 定义了函数的名称及其参数。请注意，Header() 不需要参数，因为 Apps 脚本中的宏函数不需要输入。Apps 脚本中的大括号始终会括上函数的正文。
此播放列表后面的 Codelab 介绍了创建宏所涉及的类和概念。现在，您可以浏览以下代码说明，大致了解其组件以及各组件在构建宏过程中担当的角色。考虑第一行：
var spreadsheet = SpreadsheetApp.getActive();
此处，getActive() 会返回一个表示 Google 表格中当前活动电子表格文件的对象，并将其设置为新变量 spreadsheet。
var sheet = spreadsheet.getActiveSheet();
sheet.getRange(
spreadsheet.getCurrentCell().getRow(),
1, 1, sheet.getMaxColumns()).activate();
这些行对应于点击第一行将其突出显示的操作。这称为激活。第一行将当前工作表存储在变量 sheet 中，而第二行使用 getRange() 方法获取整行，然后调用 activate() 以将其激活。第一行使用特定的行号和列号指定。spreadsheet.getCurrentCell().getRow() 调用返回当前行的数量，而 sheet.getMaxColumns() 返回工作表中的列数上限。
spreadsheet.getActiveRangeList().setBackground('#4c1130')
.setFontColor('#ffffff')
.setFontWeight('bold');
这段代码会变得更复杂。为了使用 spreadsheet 高效调用方法，代码将三个方法堆叠到 getActiveRangeList() 上，以防止代码多次冗余调用此 spreadsheet 方法。随着使用 Apps 脚本编写的代码越来越多，您将更加熟悉在一个类（也称为方法链）上调用多个方法的惯例。现在，您可以阅读以下内容，了解代码块中每种方法的简要说明：
getActiveRangeList()返回spreadsheet中的当前活跃RangeList。在本例中，它只是上一代码行激活的第一行。setBackground(color)和setFontColor(color)方法都会更改活跃范围内单元的颜色属性。setFontWeight(fontWeight)可调整活跃范围内单元格中字体的粗细。
最后，最后一行代码冻结宏的第一行：
spreadsheet.getActiveSheet().setFrozenRows(1);
这是您在录制宏时生成的脚本。您不必担心上述任何不熟悉的术语或方法。该说明旨在帮助您思考 Apps 脚本在典型宏函数中重点展示的一些概念，以及未来的 Codelab 深入探讨的主题。
下一部分将重点操控 Header() 函数的代码，以展示如何使用脚本编辑器进一步对宏进行个性化设置。
使用 Apps 脚本自定义宏
Apps 脚本编辑器会显示您之前在 Google 表格中创建的宏。通过调整函数体的内容，您可以进一步自定义宏的说明，以采取不同或额外的操作。以下练习演示了使用脚本编辑器操纵宏的各种方法。
更改受影响的单元格
假设您想要修改宏，使其只影响第一行的前 10 列，而不是影响整行。您可以删除该宏，然后重新记录。但是，通过使用 Apps 脚本编辑器，您可以直接进行更改。一种操作方法是：
- 在脚本编辑器中，将
sheet.getMaxColumns()替换为10。此修改会更改宏在电子表格中影响的单元格范围。
/** @OnlyCurrentDoc */
function Header(){
var spreadsheet = SpreadsheetApp.getActive();
var sheet = spreadsheet.getActiveSheet();
sheet.getRange(
spreadsheet.getCurrentCell().getRow(),
1, 1, 10).activate();
/* sheet.getMaxColumns() replaced with 10.*/
spreadsheet.getActiveRangeList().setBackground('#4c1130')
.setFontColor('#ffffff')
.setFontWeight('bold');
spreadsheet.getActiveSheet().setFrozenRows(1);
}
- 要保存脚本，请点击“保存”
。 - 要重命名项目，请输入“宏和自定义函数”作为新项目名称，然后点击重命名。
- 要创建工作表，请在 Google 表格中点击“添加工作表”。
- 在脚本编辑器中，从函数列表中选择
Header，然后点击运行。
在新表格中，您应该会看到以下结果：
通过修改活跃或目标范围，您的宏现在仅影响第一行的一部分。许多 Apps 脚本方法使用范围或 A1 表示法作为参数来指定要操作的单元格。
接下来，让我们了解如何自定义宏颜色。
更改宏的颜色
为了帮助您设计 Google 表格中宏或其他元素的配色方案，Apps 脚本可以修改某个范围的填充颜色或文本颜色。浏览以下说明，了解如何自定义宏的颜色。
以下说明重点介绍更改宏的背景颜色：
- 在 Google 表格中，切换回包含原始数据的工作表（工作表 1）。
- 点击第一行将其突出显示。
- 在脚本编辑器中，将背景颜色
#4c1130替换为#afeeee。这些值使用十六进制三元组表示法表示不同的颜色。
/** @OnlyCurrentDoc */
function Header(){
var spreadsheet = SpreadsheetApp.getActive();
var sheet = spreadsheet.getActiveSheet();
sheet.getRange(
spreadsheet.getCurrentCell().getRow(),
1, 1, 10).activate();
spreadsheet.getActiveRangeList().setBackground('#afeeee')
/* #4c1130 replaced with #afeeee.*/
.setFontColor('#ffffff')
.setFontWeight('bold');
spreadsheet.getActiveSheet().setFrozenRows(1);
}
- 要保存脚本，请点击“保存”。
- 从函数列表中，选择
Header并点击运行。
在 Google 表格中，第一行中前 10 列的背景填充颜色会重新变为自定义绿松石色：
通过将 setBackground(color) 的参数中的十六进制颜色表示法从 #4c1130（暗洋红 3）转换为 #afeeee（灰绿松石色，Google 表格的默认颜色菜单中未提供此选项），您可以更改宏的背景颜色的颜色属性。
现在，您已经修改了宏设置的背景颜色。如果您还想更改文本颜色，请更改第二个颜色代码。
- 在 Google 表格中，点击第一行以确保其仍然突出显示。
- 在脚本编辑器中，将字体颜色
#ffffff替换为#191970。这会导致宏将字体颜色设置为海军蓝。
/** @OnlyCurrentDoc */
function Header(){
var spreadsheet = SpreadsheetApp.getActive();
var sheet = spreadsheet.getActiveSheet();
sheet.getRange(
spreadsheet.getCurrentCell().getRow(),
1, 1, 10).activate();
spreadsheet.getActiveRangeList().setBackground('#afeeee')
.setFontColor('#191970')/* #ffffff replaced with #191970.*/
.setFontWeight('bold');
spreadsheet.getActiveSheet().setFrozenRows(1);
}
- 要保存脚本，请点击“保存”。
- 从函数列表中，选择
Header并点击运行。
返回到表格，查看标题行的文本颜色现在是否为海军蓝。
现在，您已经了解宏实际上就是记录为 Apps 脚本代码的 Google 表格操作。在下一部分中，您将了解 Apps 脚本可以帮助您使用 Google 表格的另一种方式：自定义函数。
5. 编写您的第一个脚本：自定义函数
与大多数电子表格应用一样，Google 表格还有几个内置公式函数（例如 =SUM()），可用于快速计算电子表格数据。自定义函数仅仅是您使用 Apps 脚本指定的函数。定义自定义函数后，您可以像内置函数一样，在电子表格中的任意位置使用该函数。
本部分介绍如何在 Apps 脚本中创建一个执行货币换算的自定义函数。
创建脚本文件
使用“宏”部分中的同一电子表格和脚本项目，按照以下说明了解如何创建新脚本（该脚本最终可用于创建您的第一个自定义函数）：
- 如需创建 Apps 脚本文件，请返回脚本编辑器。
- 在文件旁边，点击“添加文件”
> 脚本。 - 将新的脚本文件命名为
customFunctions，然后按 Enter 键。（Apps 脚本会自动将.gs扩展名附加到脚本文件名中。）
名为 customFunctions.gs 的新标签页会显示在编辑器中。
现在您已为自定义函数专门创建了脚本，可以在其中填充代码。
美元兑瑞士法郎
假设您要修改“2018 年十大最高票房电影”的数据，不仅要用美元，还要用瑞士法郎显示全球票房金额。使用自定义函数，您可以轻松实现这一目的。以下练习演示了如何创建自定义函数，以数学方法将美元金额换算为法郎金额。
在编写第一个自定义函数之前，请修改数据集以允许该函数演示正确的输出。具体操作步骤如下：
- 在 Google 表格中，右键点击H 列。
- 在随即显示的菜单中，点击向右插入 1 列。
- 为单元格 I1 中的“Worldwide_Total（瑞士法郎）”列添加标签。
现在，您的列中可以存储换算自定义函数的结果。接下来，您可以使用脚本编辑器创建第一个自定义函数。
- 在
customFunctions.gs中，将myFunction()的代码替换为以下代码：
/**
* Converts US dollars to Swiss francs.
*
* @param {number} dollars The total number of dollars.
* @return {number} swissFrancs The converted total of Swiss francs.
* @customfunction
*/
function USDTOCHF(dollars){
var swissFrancs = dollars * .99;
return swissFrancs;
}
此代码可将美元换算为瑞士法郎。请尝试按以下说明操作，并了解如何在表格中运行自定义函数。
- 要保存脚本，请点击“保存”。
- 在 Google 表格中，选择单元格 I2。
- 在函数栏中，输入
=USDTOCHF(H2)。
要将公式应用于列中的其余单元格，请执行以下操作：
- 将光标移动到单元格 I2 的右下角，然后选择蓝色小框（光标指向蓝色框时，应转换为 ）。
- 向下拖动蓝色框以突出显示范围 I3:I11。
I 列现在列出了 H 列中美元金额的瑞士法郎换算金额。
恭喜，您已经创建了第一个自定义函数。下一部分介绍包含 USDTOCHF() 的代码。
USDTOCHF() 的分析
初始注释详细说明了代码的用途：
/**
* Converts US dollars to Swiss francs.
*
* @param {number} dollars The total number of dollars.
* @return {number} swissFrancs The provided value in Swiss francs.
* @customfunction
*/
此类注释块常用于编程，以解释函数的功能。
在该注释中，您可以标识两个部分：函数说明（将美元兑换为法郎）和描述函数参数和返回类型的注释。
借助注释，Apps 脚本可利用 JSDoc 帮助您记录并创建代码的自动补全提示。下面介绍 USDTOCHF() 中使用的每个注释如何帮助您开发 Apps 脚本：
@param：您可以使用@param注释来描述传递给函数的每个参数。@return：您可以使用@return注释来描述函数返回的内容。@customfunction：您应始终在任何自定义函数的文档注释中添加@customfunction。此注释会通知 Google 表格自动补全自定义函数，就像您在单元格中输入函数名称时，Google 表格自动补全内置函数一样。
请注意，自动补全弹出式窗口中显示的文本与您在注释块中设置的说明文本完全匹配。请确保您创建的说明编写正确完整，从而使自定义函数更易于使用。
接下来，重点介绍函数 USDTOCHF() 中的代码：
function USDTOCHF(dollars){
var swissFrancs = dollars * .99;
return swissFrancs;
}
如前所述，USDTOCHF() 获取数字变量 dollars，再乘以固定汇率，然后在数字变量 swissFrancs 中返回换算为瑞士法郎的值。输入参数是将自定义函数添加到单元格时指定的单元格中包含的值。在本示例中，输入的美元金额来自 H 列。输出值 swissFrancs 放置在函数的单元中（在此示例中为 I 列）。
自定义函数可以使用数字或字符串值，如下一部分所示。
连接字符串前缀
假设您希望函数 USDTOCHF() 的数字输出包含瑞士法郎前缀 CHF。您可以使用 Apps 脚本通过串联运算符 (+) 执行此操作,，如以下说明所示：
- 在脚本编辑器中，更新
@return注释以返回字符串而不是数字。 - 将
return swissFrancs更改为return 'CHF' + swissFrancs。
+ 运算符将字符串 CHF 附加到 swissFrancs 中包含的值前面。现在，您的代码应如下所示：
/**
* Converts US dollars to Swiss francs.
*
* @param {number} dollars The total number of dollars.
* @return {string} swissFrancs The provided value in Swiss francs.
* @customfunction
*/
function USDTOCHF(dollars){
var swissFrancs = dollars * .99;
return 'CHF' + swissFrancs;
}
- 要保存脚本，请点击“保存”。
瑞士法郎字符串现在添加 I 列的值作为前缀：
您的自定义函数现在不仅可以将美元换算为瑞士法郎，还输出了带字符串前缀的货币。
高级：提取外部数据
虽然此示例非常适合初学基本自定义函数，但它假定美元的汇率是固定的。假设您想使用当前汇率，以便每次重新加载工作表时，都会重新计算该值以提供当前换算结果？为此，您需要通过某种方式了解当前汇率。虽然 Google 表格并未提供此信息。但幸运的是，您可以使用 Apps 脚本获取此信息。
您可以使用如下代码获取当前瑞士法郎兑美元的汇率：
function USDTOCHF(dollars){
// Gets a cache that is common to all users of the script.
var cache = CacheService.getScriptCache();
// Accesses the memory location (rates.CHF) of the script cache.
var rate = cache.get('rates.CHF');
// If a cache miss occurs, the program fetches the current
// CHF rate from an API and stores the rate in the cache
// for later convenience.
if (!rate) {
var response =
UrlFetchApp.fetch('https://api.exchangeratesapi.io/latest?base=USD');
var result = JSON.parse(response.getContentText());
rate = result.rates.CHF;
cache.put('rates.CHF', rate);
}
// Converts dollars to CHF according to the latest rate.
var swissFrancs = dollars * rate;
// Returns the CHF value.
return 'CHF' + swissFrancs;
}
此代码使用第三方汇率 API 从金融信息服务器中提取当前汇率。这是使用 UrlFetchApp 和 CacheService 等 Apps 脚本服务实现的。虽然这些高级概念超出了此特定 Codelab 的范围，但您可以开始了解 Apps 脚本用途灵活广泛，可自动执行 Google 表格中的任务。
自定义函数指南
恭喜完成自定义函数的练习。在项目中使用自定义函数时，请务必了解它们的特定限制。以下列表总结了Google 表格中的自定义函数指南中详细说明的限制：
- 不要创建需要用户授权的自定义函数。请改为构建自定义函数，以执行比较简单的任务，例如样本数据计算、文本修改等。请转到使用 Apps 脚本服务。
- 自定义函数的名称不能与另一个内置函数相同，名称也不要用下划线结尾。请查看命名准则。
- 不要将变量参数传递给自定义函数。您只能将确定（固定）值以参数的形式传递给自定义函数。传递变量参数（例如
=RAND()的结果）将破坏自定义函数。请参阅参数指南。 - 不要创建超过 30 秒才能完成的函数。如果时间长了，就会发生错误，因此请保持函数代码简单且范围有限。最好使自定义函数中的计算过程尽可能简单。查看返回值准则。
现在，您可以使用脚本编辑器来处理宏并创建自定义函数以改进电子表格。在下一部分中，您可以查看您学到的知识，以及提高脚本技能的后续操作。
6. 总结
您已经完成了使用 Google 表格的 Apps 脚本的基础知识的第一个 Codelab。通过创建和修改 Google 表格的宏和自定义函数，您学习了基本的 Apps 脚本概念。您可以在下一个 Codelab 中进一步拓展您的 Apps 脚本知识。
您觉得此 Codelab 对您有帮助吗？
您学到的内容
- 基本 Apps 脚本概念。
- 如何浏览脚本编辑器。
- 如何创建和更新 Google 表格宏。
- 如何为 Google 表格创建自定义函数。
后续步骤
此播放列表中的下一个 Codelab 介绍了 Apps 脚本电子表格服务的核心类和术语。借助此服务，您可以使用 Apps 脚本精确控制 Google 表格中数据的值和呈现方式。
在电子表格、工作表和范围中查找下一个 Codelab。