编写 Google Apps 脚本的思考

Google 表格是面向非程序员的绝佳平台

如果我为自己的目的编写此脚本，我永远不会考虑使用 Google 表格。我只会把它写成一个命令行脚本。但是，无论何时编写程序，都必须考虑如何部署它。在这种情况下，我的朋友虽然精通视听技术，但他不是每天都使用命令行的程序员。此外，他还使用 Windows，而这并非我熟悉的环境。（我听说它的命令行已经改进了，但门槛低到足以让蠕虫跳过。）但是，与许多计算机用户一样，他经常使用 Google 表格。

电子表格从我们专业程序员那里得到了很多批评，我们经常观察到许多企业依赖于一些 Excel 电子表格。当我们这么说的时候，我们会翻白眼，这些电子表格通常是一团糟的复杂代码，几乎没有模块化，没有经过测试，没有版本控制，位于用户计算机上，没有任何管理。电子表格经常被强迫执行它们不适合的任务，通常充当拼凑的数据库，其关系是错综复杂的 VLOOKUP 的网络。

但这些电子表格被广泛使用是有充分理由的。表格隐喻是一种简单而有效的方式来处理结构化数据。通过使用 说明性编程，用户可以在使用公式时立即看到结果。Google 表格使与他人共享电子表格变得容易，使多个人可以轻松地拥有一个共同的地方来存储共享数据。作为本能地不喜欢基于技能的障碍的人，我一直感到沮丧的是，软件开发人员没有更多地参与其他专业人员在他们的电子表格中所做的事情。

与许多其他人一样，电子表格为我的朋友执行此任务创造了一个自然的环境。Google 表格允许我创建一个新菜单，并将脚本轻松地绑定到菜单项，因此他可以轻松地运行我的脚本，查看脚本生成的数据，并将其他数据直接添加到工作表中。我们可以轻松地共享工作表，以便我可以更新脚本或查看数据，如果出现问题。他无需在其机器上安装任何软件，也无需对其进行更新。

如果我被公共汽车撞了，他可以轻松地与其他人共享工作表，其他人可以轻松地编辑和运行工作表。由于代码是 JavaScript，因此对于程序员来说，它是一种广为人知的语言。

最难的部分是身份验证

脚本中的“业务逻辑”非常容易。将他的 Slack 频道中的成员列表与在 Patreon 或他自己的独立列表中注册的成员进行比较。然后使用比较生成要添加到 Slack 或从 Slack 中删除的人员列表。它只需要从列表中提取电子邮件并执行几个集合差运算。

难的部分是获取列表，不是从 REST URL 获取它们（这也很容易），而是满足脚本被授权获取此数据的服务的需要。两种服务都使用 OAuth 来解决身份验证问题，但该标准并不意味着它是一个即插即用练习，即使使用 Google Apps 脚本提供的库也是如此。

最后，我最终半避免和完全避免了身份验证。Slack 具有一个不错的机制，您可以通过该机制创建应用程序来访问 Slack 数据，授予其所需的授权，它会在其网站上为您提供一个简单的访问令牌。对于此应用程序，我可以将该访问令牌直接放入脚本中。通常，这是一种糟糕的安全做法，但在这种情况下，脚本与它下载的数据位于同一电子表格中（并且该数据并不特别敏感）。这绕过了 OAuth 带来的大部分复杂性。

Patreon 数据既更难进行身份验证，又是更敏感的数据。因此，我在这里绕过了身份验证。Patreon 网页应用程序允许用户将数据下载到 CSV 文件中。因此，我要求我的朋友这样做，并将数据导入电子表格。

Google 在这里有一个真正的机会来简化整个身份验证流程。我应该能够在远程服务上调用 fetch 方法，并让基础设施解决身份验证流程，而无需我自己调查和编程。

Google 的文档并非为零

关于这一点，我能说的最好的事情就是。有一个所有类及其方法的列表。通过阅读它们，我通常可以弄清楚一些事情。但除此之外没有太多，即使像这样简单的任务也让我陷入了比我想象的更多尴尬的境地。

组织电子表格

我很少用电子表格编程（我在 R 中进行一般数据整理和图形绘制），因此我没有足够的经验来对结构良好的电子表格是什么样子有强烈的意见。可能在某个地方有关于如何设计电子表格的建议，但我找不到它（如果有人知道任何好的文章，请告诉我）。

鉴于这种缺乏，我采用了我的通常本能。第一个本能是将数据下载到本地存储，进行最少的处理。因此，我的从 Slack 下载数据的脚本只选择了我想要字段，并将它们转储到电子表格的一个页面中。类似地，我的 Patreon 数据页面假设从 Patreon 简单上传 CSV 文件。这两个页面都是为了在刷新时清除和替换整个页面而构建的。第三页只包含手动维护的异常列表。所有这三个工作表都是纯数据工作表，一个表格，标题在第 1 行，没有公式。比较脚本从这三个数据工作表中读取，执行（简单）应用程序逻辑，并将两个列表输出到单独的输出工作表中。

这非常类似于我在命令行应用程序中使用单独的文本文件的方式。它允许用户查看原始下载数据。我可以运行（和测试）应用程序逻辑，而无需每次都下载。我可以设置一个带有测试数据的表格。工作表和代码之间有一个清晰的单向数据流。

不要使用 appendRow 添加行

我第一次运行从 Slack 下载代码时，感到很沮丧，因为它运行得非常慢。可能只有大约一千行，但它们以每秒大约一行的速度添加到电子表格中。我们可以忍受，但这并不好。我确信一定有更快的方法来做到这一点。

在 API 中搜索时，我发现许多电子表格操作依赖于在电子表格中定义一个范围。我使用 Sheet.appendRow 添加了一行，但如果我定义了一个范围（可以是整个工作表），我可以使用 Range.setValues 来代替。一旦我这样做，添加行就变得非常快。我在文档或网络上的其他地方没有找到任何提示来尝试这样做，这很重要，因为这种文档的缺乏是人们更广泛地使用此平台的障碍。

允许 API 查找查找多个值

如上所述，我最终没有使用 Patreon 数据的 REST 接口，因为身份验证很复杂。但还有另一个理由支持 CSV 下载。Patreon API 包含一个资源，它可以告诉我所有支持某个活动的参与者，对于这些人，它会提供他们的 Patreon ID 和姓名。但为了与 Slack 列表交叉检查，我还需要他们的电子邮件。我可以通过获取按 ID 索引的资源来查找它。但是，我需要对数百人执行此操作，并且我需要对每个人进行单独的 GET 请求。

对 API 设计者的信息是：如果您提供了按 ID 查找有关资源的信息的能力，请支持一次提供多个 ID 的数据的能力。 [1]

我喜欢将应用程序逻辑与电子表格 I/O 分开

访问电子表格中数据的途径是使用电子表格的列和行约定（例如，单元格“B22”或范围“A2:E412”）。这对许多脚本任务来说是有意义的，因为程序员正在考虑如何操作电子表格中的单元格。

我倾向于以不同的方式思考，更喜欢将我的数据以基本的 JavaScript 数据结构的形式呈现，特别是因为我随后可以在它们上使用 JavaScript 的 集合管道 运算符。

鉴于此，这是一个我编写的用于从工作表中提取数据并将其作为 JavaScript 对象数组返回的便捷函数。

extractData(sheetName, firstCol, lastCol, mapper) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName)
  const numRows = sheet.getLastRow()
  const range = `${firstCol}1:${lastCol}${numRows}`
  return sheet.getRange(range).getValues().map(mapper)
}

然后，我可以像这样使用它：

const mapper = row => ({email: row[3], name: row[0], slackId: row[1]})
slackData = this.extractData("raw-slack-download", "A", "D", mapper)

一旦我将它放入数组中，弄清楚结果就很容易了，尽管我不得不编写一个简单的 Array.difference 函数，因为我无法访问 lodash。

开发环境可以理解地很粗糙

为了编写 JavaScript，我只是在电子表格中选择一个菜单项，然后在粗糙的文本编辑器中输入。这不是我习惯的舒适的家，但对于一夜住宿来说还不错。

如果我要做一些更复杂的事情，我会调查建立一个更好的环境。一种可能性是看看是否可以使用 Emacs 的强大 Tramp 模式（它允许像本地文件一样编辑远程文件）来编辑脚本。更好的方法是将本地文件与 Google 云端硬盘同步，允许我将源代码保存在 git 存储库中。但对于像这样简单的任务，大约 150 行代码，不值得研究这是否可行。

总结

在 Google 电子表格上托管一个简单的应用程序对于各种简单的任务来说是一个有吸引力的部署平台。它允许用户运行代码，而无需在其机器上安装任何东西，可以在熟悉的环境中输入数据，并支持与同事轻松共享。我很少听到关于这个平台的讨论，但它是一个需要牢记的平台。特别是对于任何原本是简单的 shell 脚本的任务，但您的用户不习惯使用控制台窗口和文本文件。