电子表格是当今商业世界中最常见的工具之一. 电子表格软件有多种实现方式, 它们之间存在一些差异, 但不管用的是哪一个, 对于那些工作依赖于他们的人, 他们是绝对不可或缺的.

用电子表格开始工作很容易, 但也需要时间来掌握使用它提供的所有强大功能的技能. 当你开始熟悉公式、函数、数据透视表等时.，还有一个级别允许您实现更多自定义扩展. 不同的产品为创建扩展提供了不同的接口. In this article, 我将重点关注谷歌电子表格, 它提供了一个友好的界面来与名为b谷歌Apps Script的文档进行交互. 它是用JavaScript的一个子集编写的，允许与其他b谷歌应用程序和服务集成.

为什么谷歌Sheets的应用程序脚本这么酷?

电子表格的灵活性使其成为企业家和初创公司的绝佳工具. 有了额外的Apps脚本，增强的可能性变得无穷无尽. 我第一次发现它很有用是在七年前. 我和一群编辑一起经营一个网站，我们想要设定和衡量某些目标. 要创建包含来自Analytics的数据的自定义报告并不容易, AdSense, 和Facebook在一个页面上. 根据每日更新的新数据创建报告也很重要. 第一个想法是创建一个电子表格并保存所有的数字, 但要保持更新是很困难的. 经过一番研究，我发现Apps脚本可能是一个解决方案! 它允许您修改文档, 从第三方服务获取数据, 使整个工作流程自动化. 从那时起，我创建了几十个电子表格来处理各种用例.

Real Use Cases

下面，你会发现现实世界的用例，可能会启发你创建自己的用例:

• 常见的、通用的用例是从任何可访问的REST API检索信息. 您可以按需执行此操作，但也可以使用类似cron的机制在设定的间隔内执行此操作.
• 创建一个React Native应用, 我必须让非技术版主可以编辑一小部分配置文件. 一个简单的类似json的文件正是我所需要的, 所以我的解决方案是创建一个电子表格, 创建从特定单元格收集数据的方法, 并更新服务器上的配置.
• 从银行导入账户交易—您可以使用银行API或使用Puppeteer等工具抓取银行页面并将数据导出为JSON, 哪些可以自动导入到电子表格中.
• 快速开发应用程序或仪表板的原型. 它很容易快速创建一个界面，这使它成为准备第一个工作原型和验证想法的好工具.
• 从电子表格到Slack转发谷歌表单响应.
• 通过自动分配创建一个秘密圣诞老人组, emailing reminders, 以及接收包裹的跟踪信息, 通过电子表格让每个人都了解情况.

可能性是无限的. 让我们仔细看看并创建一个示例扩展.

The Project

In this article, 我们将创建一个简单的扩展，它将验证提供的增值税ID号使用 VIES system 由欧盟创建. 谷歌提供了一个基于web的脚本编辑器，它可能对小型扩展很有用, 但也有很多局限性:

取而代之的是，我们将使用谷歌创建的另一个工具 Clasp. 它是一个命令行工具，可以帮助本地开发Apps Script工具. 这样，我们就可以将代码保存在Git存储库中，并与其他开发者协作 Google Docs developers. 它还有助于部署.

Clasp是一个基于Node的命令行工具.js. 首先，我们需要安装它:

npm i @google/clasp -g

要使用该工具，我们需要登录我们的谷歌帐户. 使用Clasp创建的任何项目都将与此谷歌帐户关联. Simply call:

clasp login

然后会打开一个新的浏览器选项卡. 登录后，我们准备开始一个新项目:

mkdir vat-id-validator;
cd vat-id-validator;
创建“VAT ID验证器”

你会被要求输入一个脚本类型，你应该回答 sheets. 你也可能会被要求启用Apps Script API. 在这种情况下，请按照屏幕上的说明操作. 如果成功完成，将为您提供指向文档和脚本编辑器的链接.g.:

创建新的谷歌表:http://drive.google.com/open?id = 1 do83dksmrvsfpugbaos8tmpdyzehvguwwddwmuep8ta
创建了新的谷歌表附加组件脚本:http://script.google.com/d/1gfjbe875R1VPmxf30zA3DAcKy_4qpK5XHsmwbzDt6JWb24P3p17EYk2s/edit

记下这两个url，我们稍后会用到它们. 现在，您已经准备好在您最喜欢的编辑器中打开该文件夹并开始黑客操作!

Hello World

让我们从简单的开始. 创建一个新文件，命名为 index.js，粘贴以下内容:

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

现在让我们推送代码:

clasp push

然后在浏览器中打开脚本编辑器(在步骤创建中有链接). 在这里，您可以运行和测试代码. Select Run > Run function > validate.

Protip: Clasp允许您直接从CLI运行这些函数. 它需要额外的项目设置. 你会发现更多的信息 here.

如果一切正常，什么都不会发生! 日志保存在Stackdriver日志工具中. You can access it via View > Stackdriver Logging，但在控制台上操作更方便. Simply call:

clasp logs

第一次，Clasp将要求您提供项目ID. 要获得它，请返回脚本编辑器并选择 Resources> Cloud Platform project. 在弹出窗口中，您将找到ID，从 project-id-xxxxxxxxx. 在控制台中复制并粘贴整个ID.

过了一会儿，您将在控制台中找到已记录的“hello world”.

Protip: Both commands clasp push and clasp logs accept the --watch 标志，在开发过程中可能有用.

VAT ID Validator

现在是创建验证代码的时候了. VIES提供了一个公共SOAP API, 哪一个不是最容易从JS工作, but for our needs, it will be fine. Update index.Js的代码如下:

function validate() {
   const ret = makeCall("PL"， "8522604586")
   console.log(ret)
   return ret
}

函数makeCall(国家代码，vatNumber) {

   //为WDSL: http://ec创建SOAP消息.europa.欧盟/ taxation_customs /竞争/ checkVatService.wsdl
   var message = '' +
       '' +
       '  ' +
       '    ' +
       '      ' + countryCode + '' +
       '      ' + vatNumber + '' +
       '    ' +
       '  ' +
       ''

   //使用UrlFetchApp (http://developers.google./ apps-script/reference/ur-fetch/ur-fetchapp)发送POST请求
   var xml = UrlFetchApp.fetch("http://ec.europa.欧盟/ taxation_customs /竞争/服务/ checkVatService”,{
       method: "POST",
       contentType: 'text/xml',
       payload: message
   }).getContentText()

   //响应是XML，可以用XmlService (http://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.欧盟:taxud: vie:服务:checkVat:类型);

   var root = document.getRootElement().getChild("Body", mainNs).getChild(“checkVatResponse checkVatResponseNs);

   //提取有趣的信息
   var isValid = root.checkVatResponseNs getChild(“有效的”).getText()
   var companyName = root.getChild(“名字”,checkVatResponseNs).getText()
   var companyAddress = root.getChild(“地址”,checkVatResponseNs).getText()

   return {
       isValid: isValid,
       公司名称:公司名称,
       companyAddress: companyAddress
   }
}

函数validate调用另一个函数(makeCall)，并根据提供的增值税编号检索公司详情. 现在您可以尝试再次运行验证. 它会要求额外的权限，你应该同意. 当您检查日志时，它将包含一个新条目:

{isValid=true, 公司名称=武士道游戏SPÓŁKA Z OGRANICZONĄ ODPOWIEDZIALNOŚCIĄ, companyAddress=ANDRZEJA MAŁKOWSKIEGO 30
70-304 SZCZECIN}

Protip: Clasp允许你用TypeScript编写代码. 通过这种方式，您可以访问箭头函数、类、类型等等. 你可以找到更多的细节 here.

与电子表格集成

So far, 我们有一段调用外部API的代码, 但它没有和电子表格集成. 是时候改变这种状况了. 其思想是使在文档中选择多个单元格，然后调用验证机制成为可能. 经过验证的增值税编号将以绿色或红色突出显示.

交换索引中的validate函数.Js的代码如下:

function onOpen() {
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var menuEntries = [{name: "Validate"， functionName: "Validate"}]
   ss.addMenu("VAT ID Validator"， menuEntries);
}

function validate() {
   //获取当前选择
   var range = SpreadsheetApp.getActiveSpreadsheet().getSelection().getActiveRange();

   //遍历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 = cell.getValue();
           //检查单元格值是否具有VAT ID格式
           var parsed = currentValue.匹配(/ ([a - z] {2}) (\ w +) /);
           //如果不是增值税ID，跳过它
           if(!parsed){
               continue
           }

           // make call to VIES
           var validated = makeCall(解析[1]，解析[2])

           // Add colors
           if(validated.isValid){
               //作为补充，我们可以添加一个带有从VIES检索到的公司名称和地址的注释
               cell.setNote(validated.companyName +“\n\n”+已验证.companyAddress)
               cell.setBackgroundColor(“# dfffdb”);
           } else {
               cell.setBackgroundColor(“# e6b8af”);
           }
       }
   }
}

您可能会注意到一个新函数 onOpen has been added. 它将创建一个新的菜单项. 打开电子表格文档(调用后得到链接) clasp create). 当它完全加载时，你应该在菜单中得到一个新的位置 VAT ID Validator with a single option, Validate. 单击它，谷歌将要求您授权额外的权限. 在您同意之后，脚本将运行，但不会发生任何事情. 这是因为我们需要增值税id来验证. 这里有几个可以试试:

• PL8522604586
• IE6388047V
• NL813981487B01
• IT7863930017
• DE123456789

只需将它们粘贴到电子表格中，选择所有五个单元格，然后单击即可 Validate once again. Voilà! 前三个是有效的，应该用绿色突出显示. 另外两个将是红色的，因为它们不是有效的增值税ID号.

Additional Exercises

以上可能是一个完整的扩展, 但是如果你想要更多的练习, 您可以尝试添加更多的验证器. For example, 您可以验证所选单元格是否包含有效的美国纳税人识别号或信用卡号. 你可以采取两种方法:

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

这只是一个介绍，上面的例子很简单, 但请记住，应用程序脚本中的业务逻辑可以更高级. 您可以使用自定义JS库，这将帮助您完成更复杂的任务. 我经常使用的一个词是Moment.在Vanilla JS上处理日期是一种可怕的经历.

你还可以做一件事, 如果您的脚本解决了更一般的问题, 部署你的app脚本是一个附加组件吗. 这将允许您与其他用户共享脚本. 您甚至可以选择仅与您的组织共享该附加组件，还是将其公开给所有人. 它将出现在附加组件库中，并准备在任何电子表格中使用.

部署过程并不复杂，第一步是为测试准备代码. 你可以找到更多的信息 here. 如果你决定要走，你可以走 publish your add-on. Take note, 如果您选择公开发布您的代码, 谷歌团队将对其进行审查，以确保其符合应用程序发布的标准.

Next Steps

这就是你开始黑客所需要知道的. 可能性是无限的. 应用程序脚本不仅可以与谷歌电子表格一起工作，还可以与文档和幻灯片一起工作.

下次您需要找到一种方法来自动化一些过程或工作流, 请记住，它可以很容易地实现与应用程序脚本. 在这篇文章中，我们只讨论了冰山一角.

你可以通过浏览富人网站来启发自己，或者找到现成的解决方案 add-ons library.

这里有两个链接，可以帮助你开始编写电子表格脚本的旅程:

• 应用程序脚本参考表
• 本文中的所有代码都可以在GitHub上找到: http://github.com/wojciech-skowronek/apps-scripts-vat-id-validator

此外，由于Apps Script是JavaScript的一个子集，您可以考虑阅读 JavaScript开发者最常犯的10个错误 来自同为Toptaler的Ryan J. Peterson.