写一个谷歌应用程序脚本的想法

2020年10月13日

最近我的一位朋友问了一个简单的程序,将关联来自一对夫妇的在线服务,得出了一些数据。我决定做这对他将是使用谷歌电子表格作为东道主的最佳方式,将代码到电子表格中的脚本区域。我在谷歌应用程序脚本专家,但运动导致了一些意见,我觉得有必要份额。

谷歌表格对于非程序员来说是一个很好的平台

如果我是出于自己的目的而编写这个脚本,我绝不会考虑使用谷歌表。我只是把它写成命令行脚本。但是当你写一个程序的时候,你必须考虑它将如何被部署。在这种情况下,我的朋友虽然是视听技术方面的天才,但不是每天使用命令行的程序员。他还使用windows,我对这个环境已经不再熟悉了。(我听说它的命令行更好了,但门槛低得足以让蠕虫跳过。)然而,像许多计算机用户一样,他一直使用谷歌表格。

电子表格受到了美国专业程序员的高度重视,他们经常发现许多企业依赖于几个Excel电子表格。当我们这样说的时候,我们翻白眼,这样的电子表格往往是一堆复杂的代码,很少的模块化,未经测试,没有版本控制,坐在用户的计算机上没有任何管理。电子表格常常被强制执行不适合它们的任务,经常充当拼凑在一起的数据库和表,它们的关系是一个错综复杂的查询网络。

但是这些电子表格被广泛使用是有原因的。表格隐喻是处理结构化数据的一种简单而有效的方法。通过使用说明性的编程,用户可以在使用公式时立即看到结果。谷歌Sheets使得与其他人共享电子表格变得很容易,使得几个人可以轻松地在一个公共位置存储共享数据。作为一个本能地不喜欢基于技能的障碍的人,我经常感到沮丧的是,软件开发人员没有更多地参与到其他专业人员正在用他们的电子表格做什么。

很多人一样,电子表格让我的朋友来执行这项任务自然环境。谷歌床单允许我创建一个新的菜单,轻松绑定脚本菜单项,这样他就可以轻松运行我的脚本,请参阅该脚本生成的数据,并添加额外的数据直接成片状。我们可以很容易地共享一个表,所以我可以更新脚本或者看数据,如果一个问题出现了。他没有对他的机器上安装任何软件,或保持最新状态。

如果我被公共汽车碾过,他可以很容易地与其他人分享工作表,而其他人可以很容易地编辑和运行工作表。由于代码是JavaScript,它是程序员使用的一种广为人知的语言。

最难的部分是身份验证

脚本中的“业务逻辑”非常简单。将他的Slack频道的成员名单与在Patreon或他自己单独名单上注册的成员进行比较。然后使用比较生成要从Slack中添加或删除的人员列表。它所需要的只是从列表中提取电子邮件,并进行一些集差操作。

困难的部分是获取列表,不是从REST URL获取它们(这也很容易),而是满足授权脚本获取数据的服务。这两个服务都使用OAuth来分类身份验证,但是这个标准并没有表明它是即插即用的练习,即使使用谷歌应用脚本提供的库。

最后,我半避免和完全避免了身份验证。Slack有一个很好的机制,你可以创建一个访问Slack数据的应用程序,给它你需要的授权,它会在它的网站上给你一个简单的访问令牌。对于这个应用程序,我可以将访问令牌放入脚本中。通常这是很糟糕的安全实践,但是在这种情况下,脚本与它下载的数据在同一个电子表格中(而且这些数据不是非常敏感)。这回避了OAuth所呈现的大部分复杂性。

Patreon的数据既难以验证,也更敏感。因此,在这里我对身份验证做了一个总结。Patreon网络应用程序允许用户将数据下载到CSV文件中。所以我让我的朋友这样做,并导入数据到电子表格。

谷歌有机会简化整个身份验证流程。我应该能够在远程服务上调用fetch方法,并让基础设施对身份验证流进行排序,而不需要我自己对它们进行调查和编程。

谷歌的文档是非零的

这是我能说的最好的话了。这里有一个所有类及其方法的列表。通过通读它们,我通常能弄明白一些事情。但除此之外就没有什么了,即使是这样一个简单的任务也会让我陷入更多的尴尬境地。

组织电子表格

我不太会用电子表格编程(我用R来做一般的数据分析和绘图),所以我没有足够的经验来对一个结构良好的电子表格是什么样子有强烈的看法。在哪里可能有关于如何设计你的电子表格的建议,但我找不到它(如果有人知道任何好的文bet188足球章,请告诉我)。

鉴于这种缺乏,我还是按照我的直觉去做了。首先,只需极少的操作就可以将数据下载到本地存储。所以我从slack下载数据的脚本只是选择了我想要的字段,并把它们转储到电子表格的一个页面。类似地,我的Patreon数据页面假设从Patreon上传一个简单的CSV文件。构建这两个页面是为了在刷新它们时清除和替换整个页面。第三页仅包含手工维护的异常列表。这三张表都是纯数据表,一张表,第一行有标题,没有公式。比较脚本从这三个数据表中读取数据,执行(简单的)应用程序逻辑,并将这两个列表发送到单独的输出表中。

这就是我在命令行应用程序中使用独立文本文件的方式。它允许用户查看下载的原始数据。我可以运行(和测试)应用程序逻辑,而无需每次下载。我可以用测试数据建立一个表格。在表和代码之间有一个明确的单向数据流。

不要使用附录行来附加行

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

仔细研究这个API,我发现许多电子表格操作都依赖于在电子表格中定义一个范围。我添加了一个新行使用Sheet.appendRow,但如果我定义了一个范围(可以是整个工作表),我可以使用Range.setValues代替。一旦我这样做了,添加行实际上就是瞬间完成的。我在文档或web上的其他地方没有找到任何提示来尝试这一点,这一点很重要,因为缺乏此类文档是人们更广泛地使用这个平台的障碍。

允许API查找查找多个值

如上所述,由于身份验证的复杂性,我最终没有对Patreon数据使用REST接口。但青睐CSV下载还有另一个原因。Patreon API包括一个资源,它可以告诉我一个活动的所有支持者,对于这些人它会给我他们的Patreon ID和他们的名字。但是为了核对松弛列表,我还需要他们的电子邮件。我可以通过获取ID索引的资源来查找。然而,我需要为几百个人这样做,并且我需要为每个人单独获取。

给API设计者的消息是这样的。bet188足球如果您提供了通过ID查找有关资源的信息的能力,那么就支持同时为多个ID提供数据的能力。[1]

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

从电子表格访问数据的方法是使用电子表格的列和行约定(例如单元格“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)}

我可以使用它的代码像这样:

常量映射器=行=>({电子邮件:行[3],名称:行[0],slackId:行[1]})= slackData this.extractData( “原始松弛下载”, “A”,“d”,映射器)

一旦我将它放入数组,计算结果就很容易了,尽管我必须编写一个简单的Array.difference功能,因为我没有访问lodash

开发环境的粗糙是可以理解的

为了编写JavaScript,我只需要在电子表格中选择一个菜单项,然后在一个简单的文本编辑器中输入。虽然不是我习惯的那种舒适的家,但在这里过夜也不错。

如果我要做一些更复杂的事情,我会研究如何设置一个更好的环境。一种可能性是,看看我是否可以使用Emacs的Tramp模式编辑脚本(该模式允许编辑远程文件,就像编辑本地文件一样)。更好的方法是将本地文件与谷歌驱动器同步,这样就可以将源代码保存在git repo中。但对于这样一个大约有150行代码的简单任务,不值得去研究这是否可能。

总结

托管在谷歌电子表格的简单应用程序是一系列简单任务,一个有吸引力的部署平台。它允许用户运行代码,而不必在其计算机上安装的东西,在熟悉的环境中输入数据,并支持轻松共享与同事。这不是我听到很多讨论的平台,但它是一个要记住。特别是,对于任何任务,这将是一个简单的shell脚本,但你的用户是不舒服的控制台窗口和文本文件。


脚注

1:也许有这样的方法,但在我决定用CSV路径之前我没有找到它。

重大修改

2020年10月13日:发表

2020年9月15日:开始起草