使用Google表格和Apps脚本 _Google

本文概述

为什么Google Sheets的Apps脚本如此酷？
实际用例
该项目
Hello, World
增值税ID验证器
与电子表格集成
附加练习
下一步

电子表格是当前商业世界中最常见的工具之一。电子表格软件有多种实现方式, 它们之间都有一些差异, 但是无论使用哪种方式, 对于那些依赖于它们的工作的人来说, 它们绝对是必不可少的。
“ 电子表格：易学, 难于掌握”
开始使用电子表格很容易, 但是使用它提供的所有强大功能也需要花费一些时间来掌握技能。当你开始对公式, 函数, 数据透视表等感到满意时, 还有一个更高的级别可以使你实现更多的自定义扩展。不同的产品提供用于创建扩展的不同接口。在本文中, 我将重点介绍Google Spreadsheets, 它提供了一个友好的界面来与称为Google Apps Script的文档进行交互。它使用JavaScript的子集编写, 并允许与其他Google应用和服务集成。
为什么Google Sheets的Apps脚本如此酷？电子表格的灵活性使其成为企业家和创业公司的绝佳工具。使用其他Apps脚本, 增强的可能性变得无穷无尽。我第一次发现它有用是大约七年前。我当时正在与一群编辑人员一起运营一个网站, 我们想设定和衡量某些目标。创建自定义报告没有简单的方法, 该报告将在一页上包含来自Google Analytics(分析), AdSense和Facebook的数据。基于新鲜数据并每日更新的报告也很重要。第一个想法是创建一个电子表格并将所有数字保存在其中, 但是很难对其进行更新。经过研究, 我发现Apps脚本可能是一种解决方案！它使你可以修改文档, 从第三方服务获取数据以及自动化整个工作流程。从那时起, 我创建了数十个电子表格来处理各种用例。
实际用例在下面, 你将找到现实中的用例, 这些用例可能会激发你创建自己的用例：

常见的通用用例是从任何可访问的REST API中检索信息。你可以按需进行此操作, 也可以使用类似cron的机制按设置的时间间隔进行。
创建一个React Native应用程序后, 我必须使一小部分配置文件可以由非技术版主进行编辑。我需要一个简单的类似JSON的文件, 因此我的解决方案是创建电子表格, 创建一种从特定单元格收集数据的方法, 并更新服务器上的配置。
从你的银行导入帐户交易-你可以使用银行API或使用Puppeteer之类的工具抓取银行页面, 然后将数据导出到JSON, 然后将其自动导入到电子表格中。
快速开发应用程序或仪表板的原型。快速创建一个界面很容易, 这使它成为准备开始工作的原型和验证想法的好工具。
将Google Forms响应从电子表格转发到Slack。
通过自动执行任务分配, 通过电子邮件发送提醒并跟踪传入软件包的信息来创建Secret Santa组, 并通过电子表格使每个人都处于循环中。

可能性是无止境。让我们仔细看看并创建一个示例扩展。
该项目在本文中, 我们将创建一个简单的扩展程序, 该扩展程序将使用由欧盟创建的VIES系统验证提供的增值税ID号。 Google提供了一个基于Web的脚本编辑器, 该编辑器可能对小型扩展有用, 但也有许多限制：

文章图片
取而代之的是, 我们将使用谷歌创建的另一个名为Clasp的工具。这是一个命令行工具, 可帮助本地开发Apps脚本工具。这样, 我们将能够将代码保存在Git存储库中, 并与其他Google Docs开发人员进行协作。它还有助于部署。
Clasp是基于Node.js的命令行工具。首先, 我们需要使用以下命令进行安装：

npm i @google/clasp -g

要使用该工具, 我们需要登录到我们的Google帐户。使用Clasp创建的所有项目都将与此Google帐户关联。只需致电：

clasp login

并应打开一个新的浏览器选项卡。登录后, 我们准备开始一个新项目：

mkdir vat-id-validator; cd vat-id-validator; clasp create "VAT ID Validator"

系统将要求你提供脚本类型, 并应填写答题纸。可能还会要求你启用Apps Script API。在这种情况下, 请按照屏幕上的说明进行操作。如果成功完成, 将为你提供文档和脚本编辑器的链接, 例如：

Created new Google Sheet: https://drive.google.com/open?id=1Do83dksmRvsFPuGbaOS8tMPdyZEHvGuWWdDwMuEp8tA Created new Google Sheets Add-on script: https://script.google.com/d/1gfjbe875R1VPmxf30zA3DAcKy_4qpK5XHsmwbzDt6JWb24P3p17EYk2s/edit

记下这两个URL, 稍后我们将需要它们。现在你可以在自己喜欢的编辑器中打开文件夹并开始黑客入侵！
Hello, World 让我们从简单的事情开始。创建一个新文件, 将其命名为index.js, 并粘贴以下内容：

function validate() { console.log("hello world") }

现在让我们推送代码：

clasp push

并在浏览器中打开脚本编辑器(你已获得步骤创建的链接)。你可以在此处运行和测试代码。选择运行> 运行功能> 验证。
Protip：Clasp允许你直接从CLI运行功能。它需要项目的其他设置。你将在此处找到更多信息。
如果一切正常… 什么都不会发生！日志存储在Stackdriver Logging工具中。你可以通过” 查看” > ” Stackdriver Logging” (视图> Stackdriver Logging)进行访问, 但是从控制台进行操作更为方便。只需致电：

clasp logs

第一次, Clasp会要求你提供项目ID。要获取它, 请返回脚本编辑器, 然后选择资源> Cloud Platform项目。在弹出窗口中, 你将找到ID, 从project-id-xxxxxxxxx开始。将整个ID复制并粘贴到控制台中。
一段时间后, 你将在控制台中找到已记录的” hello world” 。
提示：clasp push和clasp log这两个命令都接受– watch标志, 这在开发过程中可能很有用。
增值税ID验证器是时候创建我们的验证代码了。 VIES提供了一个公共的SOAP API, 这不是从JS上最容易使用的方法, 但是对于我们的需求, 这将很好。使用以下代码更新index.js：

function validate() { const ret = makeCall("PL", "8522604586") console.log(ret) return ret }function makeCall(countryCode, vatNumber) {// Create SOAP message for WDSL: http://ec.europa.eu/taxation_customs/vies/checkVatService.wsdl var message = '< ?xml version="1.0" encoding="UTF-8"?> ' + '< SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns1="urn:ec.europa.eu:taxud:vies:services:checkVat:types"> ' + '< SOAP-ENV:Body> ' + '< ns1:checkVat> ' + '< ns1:countryCode> ' + countryCode + '< /ns1:countryCode> ' + '< ns1:vatNumber> ' + vatNumber + '< /ns1:vatNumber> ' + '< /ns1:checkVat> ' + '< /SOAP-ENV:Body> ' + '< /SOAP-ENV:Envelope> '// Use UrlFetchApp (https://developers.google.com/apps-script/reference/url-fetch/url-fetch-app) to send POST request var xml = UrlFetchApp.fetch("http://ec.europa.eu/taxation_customs/vies/services/checkVatService", { method: "POST", contentType: 'text/xml', payload: message }).getContentText()// the response is XML, which can be parsed with XmlService (https://developers.google.com/apps-script/reference/xml-service/) var document = XmlService.parse(xml); var mainNs = XmlService.getNamespace('http://schemas.xmlsoap.org/soap/envelope/'); var checkVatResponseNs = XmlService.getNamespace('urn:ec.europa.eu:taxud:vies:services:checkVat:types'); var root = document.getRootElement().getChild("Body", mainNs).getChild("checkVatResponse", checkVatResponseNs); // Extract interesting information var isValid = root.getChild("valid", checkVatResponseNs).getText() var companyName = root.getChild("name", checkVatResponseNs).getText() var companyAddress = root.getChild("address", checkVatResponseNs).getText()return { isValid: isValid, companyName: companyName, companyAddress: companyAddress } }

函数validate调用另一个(makeCall), 它与VIES连接并根据提供的增值税ID检索公司详细信息。现在, 你可以尝试再次运行验证。它将要求其他权限, 你应该同意。当你检查日志时, 它将包含一个新条目：

{isValid=true, companyName=BUSHIDO GAMES SPó?KA Z OGRANICZON? ODPOWIEDZIALNO?CI?, companyAddress=ANDRZEJA MA?KOWSKIEGO 30 M1 70-304 SZCZECIN}

提示：Clasp允许你使用TypeScript编写代码。这样, 你就可以访问箭头功能, 类, 类型等等。你可以在此处找到更多详细信息。
与电子表格集成到目前为止, 我们有一段代码可以调用外部API, 但尚未与电子表格集成。现在该改变它了。这样做的想法是可以选择文档中的多个单元格, 然后调用验证机制。已验证的增值税号将以绿色或红色突出显示。
用以下代码交换index.js中的validate函数：

function onOpen() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var menuEntries = [{name: "Validate", functionName: "validate"}] ss.addMenu("VAT ID Validator", menuEntries); }function validate() { // Take current selection var range = SpreadsheetApp.getActiveSpreadsheet().getSelection().getActiveRange(); // Iterate through range var numRows = range.getNumRows(); var numCols = range.getNumColumns(); for (var i = 1; i < = numRows; i++) { for (var j = 1; j < = numCols; j++) { var cell = range.getCell(i, j); var currentValue = http://www.srcmini.com/cell.getValue(); // check if the cell value has VAT ID format var parsed = currentValue.match(/([A-Z]{2})(/w+)/); // if it's not VAT ID, skip it if(!parsed){ continue }// make call to VIES var validated = makeCall(parsed[1], parsed[2])// Add colors if(validated.isValid){ // as addition we can add a note with company name and address retrieved from VIES cell.setNote(validated.companyName + "\n\n" + validated.companyAddress) cell.setBackgroundColor("#dfffdb"); } else { cell.setBackgroundColor("#e6b8af"); } } } }

你可能会注意到已添加了新功能onOpen。它将创建一个新的菜单项。打开电子表格文档(在调用clasp create之后获得链接)。加载满后, 你应该在菜单中获得一个名为VAT ID Validator的新职位, 并且其中有一个选项Validate。点击它, Google会要求你授权其他权限。在你同意后, 脚本将运行, 但是什么也不会发生。这是因为我们需要增值税ID进行验证。这里有一些可以玩的东西：

PL8522604586
IE6388047V
NL813981487B01
IT7863930017
DE123456789

只需将它们粘贴到电子表格中, 选择所有五个单元格, 然后再次单击Validate。瞧！前三个有效, 应以绿色突出显示。其他两个将为红色, 因为它们不是有效的增??值税ID号。
附加练习以上可能是一个完整的扩展, 但是如果你正在寻找更多实践, 可以尝试添加更多验证器。例如, 你可以验证所选单元格是否包含有效的美国纳税人识别号或信用卡号。你可以采取两种方法：

就像上面对VAT ID所做的那样, 找到一个用于处理验证的API。对于TIN, 这将更为准确, 因为你不仅需要检查数据格式, 还需要验证号码是否已正式注册。
直接在Apps脚本中编写自己的逻辑。对于信用卡验证, 你可以遵循特定规则, 并且大多数规则都可以使用Luhn算法进行验证。

这只是一个介绍, 上面的示例非常简单, 但是请记住, 你的Apps脚本中的业务逻辑可以更加高级。你可以使用自定义JS库, 这将帮助你完成更复杂的任务。我经常使用的其中之一是Moment.js, 在Vanilla JS上处理日期是一种令人恐惧的体验。
如果你的脚本解决了更普遍的问题, 则你可以做的另一件事是将Apps脚本作为附加组件进行部署。这将允许你与其他用户共享脚本。你甚至可以选择只与你的组织共享加载项, 还是向所有人公开。它会出现在加载项库中, 并且可以在任何电子表格中使用。
部署过程并不复杂, 第一步是准备要测试的代码。你可以在此处找到有关此信息的更多信息。如果你确定可以使用, 则可以发布你的加载项。请注意, 如果你选择公开发布代码, 则Google团队将对其进行审核, 以确保其符合应用发布标准。
下一步这就是你开始黑客所需要了解的一切。可能性是无限的。 Apps脚本不仅适用于Google Spreadsheets, 而且适用于文档和幻灯片。
下次你需要找到一种使某些流程或工作流自动化的方法时, 请记住, 使用Apps脚本可以轻松实现。在本文中, 我们仅讨论了冰山一角。
你可以激发自己的灵感, 也可以通过浏览丰富的加载项库找到一个现成的解决方案。
这里有两个链接, 可帮助你开始编写电子表格脚本：