用于掌握宏的 Google Apps 脚本教程

已发表: 2022-03-11

高效的主管知道时间是限制因素……也许没有什么比他们对时间的温柔关爱更能区分高效的主管了。

彼得·德鲁克

时间是我们最宝贵的资源。 我们希望将其花在我们能做的最具影响力和最具价值增值的活动上，不仅因为这些活动通常具有最高的货币价值，而且是为了不断挑战自我并最大限度地提高我们的工作满意度。

有很多方法可以提高您的效率和生产力，以便更好地利用您的时间。 在之前关于 Google 表格的文章中，我详细阐述了在线协作的力量如何成为提高生产力的关键。

在另一篇文章中，我演示了 Python 编程语言如何成为金融专业人士的强大分析和任务自动化工具。

受此启发，我现在想介绍一个 Google Apps 脚本教程。 Google Apps 脚本允许您使用 JavaScript 编写脚本和程序，以自动化、连接和扩展 Google 的 G Suite 中的产品，包括表格、文档、幻灯片、Gmail、云端硬盘等。 学习它需要时间投资，编写脚本也是如此，但是生产力的提高和它带来的额外机会使得它非常值得。

作为第一步，让我们从一个熟悉的概念开始：宏。

在 Google 表格中记录和使用宏

如果您花费大量时间使用 Excel，那么您一定会在某个时候接触到 Excel 的 VBA（Visual Basic for Applications）宏接口。 要么通过自己录制或编写它们，要么通过其他人创建的捎带。

宏是自动化重复和繁琐工作流程的好方法。 VBA 可能不是您花费大量时间学习的语言，但它的美妙之处在于您实际上并不需要为了提高工作效率并创建自己的宏。 您可以简单地记录您想要自动化的工作流程，然后进入代码并进行所需的任何小的更改以使宏更通用。

在某些方面，VBA 是如何向非技术人员介绍编码的一个很好但被遗忘的课程。 您可以记录动作然后填充代码以供以后查看的方式确实是一种比阅读教科书和被动观看教程更实用的学习方式。

Google 表格中提供了与 VBA 相同的录制功能。 这是一个如何使用它的简单示例：

让我们从一些示例数据开始，使用 IMPORTHTML 查询来导入表。 在此示例中，我从 Wikipedia 下载了世界上 15 家最大对冲基金的列表。 不用说，但是，这是一个任意的例子； 目的是让您更多地关注应用程序，而不是主题。

用于导入表的示例数据。

宏录制过程通过以下菜单路径启动：工具 > 宏 > 录制宏。

然后我们遍历我们想要记录的动作（PC 格式）：

  1. 选择第一行
  2. 按 Shift + Ctrl + 向下箭头选择所有内容
  3. Ctrl + C 复制
  4. Shift + F11 创建新工作表
  5. 给工作表一个新名称
  6. 按 Shift + Control + V 粘贴值

完成后，按底部宏窗口上的保存按钮，为其命名和可选的键盘快捷键。

对于可以通过这些相同步骤完全复制的更简单的操作，该过程将在此处结束，您可以立即开始使用您的宏。 但是，在这种情况下，我们需要在代码可用之前进行一些更改。 例如，我们复制到的工作表每次都需要有不同的名称。 让我们看看如何做到这一点。

手动编写 Google Apps 脚本

现在我们将第一次看到 Google Apps Script 的骨架； 在 Google 服务器上运行的编程平台。 这为我们的宏提供了动力，并允许您为应用程序本身创建非常复杂的工作流程，甚至是附加组件。 它不仅可以用来自动化电子表格工作，而且实际上几乎可以用来自动化谷歌 G Suite 中相互关联的任何东西。

Apps Script 的编程语言是JavaScript ，它是最流行的编程语言之一，这意味着有大量资源可供任何想要广泛学习的人使用。 但是，就像使用 VBA 一样，您实际上并不需要：您可以使用相同的 Record 功能，只需执行您希望能够自动重复的步骤。 录制的输出可能看起来很粗糙，很可能与您想要完成的内容不完全匹配，但它会提供一个足够可靠的起点。 现在让我们为刚刚录制的脚本执行此操作。

录制时，请注意不要意外录制您不想在最终录制中捕获的任何其他步骤，但有时很难避免：就像在按下停止之前选择不同的单元格一样简单每次运行脚本时，录制按钮都会被捕获并随后重复。 编辑我们的脚本的第一步是清理它并删除任何此类步骤。 让我们进入文件菜单中的工具 > 脚本编辑器。

脚本编辑器

如果您了解 JavaScript，您会立即认出这一点，并且您可能还会惊讶地看到“var”关键字而不是现代 JavaScript 中的“let”或“const”。 这反映了 Apps Script 中的 JavaScript 版本相当老旧，并且不支持该语言的许多最新功能。 最后，我将为那些想要使用最新语言功能的人介绍一种解决方法。

当您第一次运行脚本时，它会请求授权，这是有道理的，因为脚本可以修改（并可能删除）您的所有数据。 您很可能会从其他 Google 产品中识别出授权过程。

现在我们可以开始修改代码了。 我们需要做的更改很小，但如果您是第一次这样做，可能仍需要快速搜索 Sheets Apps 脚本文档和/或快速查找 JavaScript 概念，例如处理日期。 JavaScript 是一种如此广泛使用的语言这一事实​​就派上用场了：如果您以直截了当的方式表达您的搜索词，通常可以快速找到您面临的任何问题或功能的解决方案。

这个版本的脚本对原始录制版本所做的更改是，我们现在用今天的日期命名，而不是我们创建的新工作表的硬编码名称。 此外，我们还将末尾的复制路径更改为引用此新工作表。 最后四行还演示了如何执行一些格式化操作，例如更改单元格的值、调整列大小和隐藏网格线。

 function createSnapshot() { var spreadsheet = SpreadsheetApp.getActive(); var date = new Date().toISOString().slice(0,10); var destination = spreadsheet.insertSheet(date); spreadsheet.getRange('HTML!A1:F1').activate(); spreadsheet.getSelection() .getNextDataRange(SpreadsheetApp.Direction.DOWN) .activate(); spreadsheet.getActiveRange() .copyTo(SpreadsheetApp.setActiveSheet(destination) .getRange(1,1), SpreadsheetApp.CopyPasteType.PASTE_VALUES,false); var sheet = spreadsheet.setActiveSheet(destination) sheet.getRange("D1").setValue("AUM $bn") sheet.setHiddenGridlines(true); sheet.getRange("A1:D1").setFontWeight("bold"); sheet.autoResizeColumns(1, 4); };

现在运行脚本将显示新工作表确实以今天的日期命名，并且包含从主工作表复制为值（而不是公式）的信息。

现在可以使用相同的记录过程添加图表可视化。 我用它来创建三个简单的图表。

图表可视化。

为每个清理代码将如下所示：

 function createColumnChart() { var spreadsheet = SpreadsheetApp.getActive(); spreadsheet.getRange('C1:D16').activate(); var sheet = spreadsheet.getActiveSheet(); chart = sheet.newChart() .asColumnChart() .addRange(spreadsheet.getRange('B1:D16')) .setMergeStrategy(Charts.ChartMergeStrategy.MERGE_COLUMNS) .setTransposeRowsAndColumns(false) .setNumHeaders(-1) .setHiddenDimensionStrategy(Charts.ChartHiddenDimensionStrategy.IGNORE_BOTH) .setOption('useFirstColumnAsDomain', true) .setOption('curveType', 'none') .setOption('domainAxis.direction', 1) .setOption('isStacked', 'absolute') .setOption('series.0.color', '#0b5394') .setOption('series.0.labelInLegend', 'AUM $bn') .setPosition(19, 6, 15, 5) .build(); sheet.insertChart(chart); };

同样，如果某些选项看起来令人困惑，请不要担心：这都是自动生成的，您只需要足够了解即可删除不必要的步骤，并且稍后可能会进行一些小调整。

高级 Google Apps 脚本示例：将工作表连接到 Google Drive 和幻灯片

现在一切都开始成形了，但是如果我们想要的实际输出不是电子表格而是演示文稿怎么办？ 如果是这种情况，那么这里的大部分工作可能仍然是手动的，如果我们需要重复执行此操作，我们并没有节省太多时间。

现在让我们探索一下使用电子表格中的示例数据自动创建演示文稿会是什么样子。

这个练习现在变得更加先进，原因有两个：

  1. 除了表格之外，我们还需要熟悉如何使用 Google 幻灯片（和 Google 云端硬盘）。
  2. 在幻灯片中，或在一般 G Suite 应用程序之间工作时，没有可用的“录制宏”功能。 这意味着您需要对 Apps 脚本有足够的了解（并且能够轻松浏览每个 G Suite 产品的文档）才能从头开始编写脚本。

下一个示例旨在提供一些基本构建块来帮助您入门和熟悉。

首先，让我们创建一个模板，稍后我们将使用我们的脚本来填充内容。 这是我放在一起的两张简单的演示幻灯片：

每周报告模板。

接下来，您将需要获取此模板的 ID，因为您必须在脚本中引用它。 潜意识里你会多次看到这个 ID，因为它实际上是你在浏览器的 URL 中看到的随机的字符和数字序列：

https://docs.google.com/presentation/p/this_is_your_presentation_ID /edit#slide=id.p 。

现在我们必须将以下行添加到我们的原始脚本中。 这将再次提示授权，这次是访问您的 Google 云端硬盘。

 function createPresentation() { var template; var template = DriveApp.getFileById(templateId); var copy = template.makeCopy("Weekly report " + date).getId(); var presentation = SlidesApp.openById(copy); }

如果您运行此代码片段，您不会立即看到任何视觉反馈，但如果您查看存储模板的 Google Drive 文件夹，您会发现它的副本确实已创建，并且它具有今天的文件名中的日期。 我们有了一个良好的开端！

现在让我们使用更多的构建块开始用内容填充它，以编程方式而不是手动。 将以下行添加到同一函数中：

 presentation.getSlides()[0] .getPageElements()[0] .asShape() .getText() .setText("Weekly Report " + date);

现在事情变得更有趣了，因为我们已将第一页更改为包含今天的日期。 在幻灯片中，与在表格中一样，您使用对象（由类表示），每个对象都有属性和方法（即附加功能）。 它们按层次结构组织，SpreadsheetsApp、DriveApp 或 SlidesApp 是顶级对象。 在上面的代码片段中，我们需要一步一步地通过这个层次结构来到达我们想要编辑的元素，在这种情况下：文本框中的文本。 实际上，这意味着通过 Presentation、Slide、PageElement 和 Shape 对象，直到我们最终到达要编辑的 TextRange 对象。

跟踪您正在处理的对象类型可能会令人困惑，并且由于尝试将操作应用于错误对象而导致的错误可能难以解决。 不幸的是，脚本编辑器本身的帮助功能和错误消息并不总是在这里提供很多指导，一线希望是这种关注至少会改善您的质量控制实践。

创建演示文稿并更新标题后，现在是时候将我们的新图表之一插入其中了。 牢记对象的层次结构，下面的代码现在应该是有意义的：

 var spreadsheet = SpreadsheetApp.getActive(); var sheet = spreadsheet.getSheetByName(date); var chart = sheet.getCharts()[0]; var position = {left: 25, top: 75}; var size = {width: 480, height: 300}; presentation.getSlides()[1] .insertSheetsChart(chart, position.left, position.top, size.width, size.height);

如果您运行完整的脚本，输出演示文稿应如下所示：

样品展示

希望此示例说明了原理并为您开始自己的实验提供了灵感。 如果您考虑一下，我相信您至少可以找到一些在您的公司中完成的手动工作示例，这些示例确实应该以这种方式实现自动化。 服务于腾出时间思考、分析和应用判断，而不是机械地将数据从一种格式和/或地点转移到另一种格式。 改进开发体验 如前所述，Google Apps 脚本支持的 JavaScript 版本较旧，在线脚本编辑器的功能非常有限。 如果您只是在录制宏或编写几十行代码，您将不会真正注意到。 但是，如果您确实有雄心勃勃的计划来自动化每周或每月报告的各个方面，或者想要构建插件，那么您会很高兴知道有一个命令行工具可以让您使用自己喜欢的开发环境进行开发.

如果您处于这样的熟练程度，那么您可能还想利用 JavaScript 必须提供的最新功能，甚至可能更多，因为使用命令行工具，您也可以在 TypeScript 中进行开发。

使用 Python 进行 Google 表格编程

如果您发现使用 Apps Script 不是您的菜，那么还有其他选择，具体取决于用例。 如果您想做更高级的数字运算、连接 API 或数据库，或者只是更喜欢 Python 编程语言而不是 JavaScript，那么 Google 的 Colaboratory 是一款无价的产品。 它为您提供了一个在 Google 服务器上运行的 Jupyter 笔记本，允许您编写与您的 Google Drive 文件无缝集成的 Python 脚本，并且通过“gspread”库，可以轻松处理您的电子表格数据。

我在一篇关于如何将 Python 用于财务功能的文章中概述了 Python 的许多好处，该文章还作为在商业和财务环境中使用 Python 和 Jupyter 笔记本的温和介绍。 对我来说一个非常重要的好处是，与 Apps Script 不同，Colaboratory 中的 Python 笔记本是交互式的，因此您可以在执行每一行或一小段代码后看到结果（或错误消息）。

自动化让人上瘾

这个 Google Apps 脚本教程展示了通过 Google 的编码语言可以实现的功能。 可能性几乎是无穷无尽的。 但是，如果您没有技术背景，代码示例可能看起来令人望而生畏，并且您可能会想，从学习 Google Apps 脚本中获得的生产力收益可能不足以超过所需时间方面的大量投资学习它。

当然，这取决于许多因素，包括您将来拥有或期望拥有的角色类型。 但是，即使您不希望做与此处显示的示例类似的任何事情，了解什么是可能的以及大致需要多少工作才能实施可以引发有关如何提高公司生产力的想法和想法，因为你的客户，或者你自己。

就我个人而言，我可以证明坐下来按下一个按钮，在一分钟内完成一个小时的繁琐手工工作是一种满足感。 在第 50 次这样做之后，你会很感激一开始花了几个小时把它们拼凑在一起，这最终让你腾出时间去追求更多的增值。 一段时间后，这些可扩展性优势确实会让人上瘾。