Working With Google Sheets and Apps Script

Spreadsheets are one of the most common tools of the current business world. There are multiple implementations of spreadsheet software, with some differences among them all, but no matter which one is being used, for those whose work depends on them, they are absolutely indispensable.

It’s easy to start working with a spreadsheet, but it also takes time to master your skills to use all the powerful features it provides. When you start feeling comfortable with formulas, functions, pivot tables, etc., there is still one more level which allows you to achieve even more—custom extensions. Different products provide different interfaces for creating extensions. In this article, I’m going to focus on Google Spreadsheets, which provides a friendly interface to interact with the document called Google Apps Script. It’s written in a subset of JavaScript and allows integrations with other Google apps and services.

Why Is Google Sheets’ Apps Script So Cool?

The flexibility of spreadsheets makes it a fantastic tool for entrepreneurs and startups. With additional Apps scripts, enhancement the possibilities become endless. The first time I found it to be useful was about seven years ago. I was running a website with a group of editors and we wanted to set and measure certain goals. There was no easy way to create a custom report which would contain data from Analytics, AdSense, and Facebook on one page. It was also important to create reports based on fresh data, with daily updates. The first idea was to create a spreadsheet and keep all the numbers there, but it was difficult to keep it updated. After some research, I found that Apps scripts may be a solution! It allows you to modify the document, fetch data from third-party services, and automate the whole workflow. Since then, I’ve created dozens of spreadsheets to handle a variety of use cases.

Real Use Cases

Below, you will find real-world use cases which may inspire you to create your own:

• The common, general, use case is to retrieve information from any accessible REST API. You can do that on demand, but also at set intervals using a cron-like mechanism.
• Creating a React Native app, I had to make a small chunk of a config file editable by non-technical moderators. A simple JSON-like file would be exactly what I needed, so my solution was to create a spreadsheet, create a method to collect data from specific cells, and update the configuration on the server.
• Importing account transactions from your bank—you can use your bank API or scrape the banking page with tools like Puppeteer and export data to JSON, which can be automatically imported to your spreadsheet.
• Quickly develop prototypes of apps or dashboards. It’s easy to quickly create an interface which makes it a great tool to prepare first working prototypes and validate an idea.
• Forward Google Forms responses from a spreadsheet to Slack.
• Create a Secret Santa group by automating assignments, emailing reminders, and tracking information for incoming packages, and keeping everyone in the loop all from a spreadsheet.

The possibilities are endless. Let’s take a closer look and create a sample extension.

The Project

In this article, we are going to create a simple extension which will validate provided VAT ID numbers using the VIES system created by the European Union. Google provides a web-based script editor which may be useful for small extensions, but also has many limitations:

Instead of that, we are going to use another tool created by Google called Clasp. It’s a command line tool which helps with local development of Apps Script tools. This way, we will be able to keep the code in a Git repository and collaborate with other Google Docs developers. It also helps with deployments.

Clasp is a command line tool based on Node.js. First thing, we need to install it with:

npm i @google/clasp -g

To use the tool, we need to log in to our Google account. Any projects created with Clasp will be associated with this Google account. Simply call:

clasp login

And a new browser tab should open. After signing in, we are ready to start a new project:

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

You will be asked for a script type and you should answer sheets. You may also be asked for enabling Apps Script API. In that case, follow the instructions on the screen. If finished successfully, you will be provided with links to the document and script editor, e.g.:

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

Note down both URLs, we are going to need them later. Now you are ready to open the folder in your favorite editor and to start hacking!

Hello World

Let’s start with something simple. Create a new file, call it index.js, and paste the following content:

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

Now let’s push the code:

clasp push

And open the script editor in your browser (you got the link on step creation). Here you can run and test the code. Select Run > Run function > validate.

Protip: Clasp allows you to run the functions directly from CLI. It requires additional setup of the project. You will find more information here.

If everything is ok… nothing will happen! Logs are stored in the Stackdriver Logging tool. You can access it via View > Stackdriver Logging, but it’s more convenient to do that from the console. Simply call:

clasp logs

For the first time, Clasp will ask you for a project ID. To get it, go back to the script editor and select Resources> Cloud Platform project. In the popup, you will find the ID, starting from project-id-xxxxxxxxx. Copy and paste the whole ID in the console.

After a while, you will find your logged “hello world” in the console.

Protip: Both commands clasp push and clasp logs accept the --watch flag, which may be useful during development.

VAT ID Validator

It’s time to create the code of our validation. VIES provides a public SOAP API, which is not the easiest to work from JS, but for our needs, it will be fine. Update index.js with the following code:

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
   }
}

Function validate calls another one (makeCall) which connects with VIES and retrieves company details based on the provided VAT ID. Now you can try to run validate again. It will ask for additional permissions and you should agree. When you check logs, it will contain a new entry:

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

Protip: Clasp allows you to write the code in TypeScript. This way you are getting access to arrow functions, classes, typings, and much more. You can find more details here.

Integration with Spreadsheet

So far, we have a piece of code which calls an external API, but it’s not integrated with the spreadsheet. It’s time to change that. The idea is to make it possible to select multiple cells in the document and then call the validation mechanism. Validated VAT numbers will highlight on green or red.

Exchange the validate function in index.js with the following code:

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 = 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");
           }
       }
   }
}

You may notice a new function onOpen has been added. It will create a new menu entry. Open the spreadsheet document (you got the link after calling clasp create). When it fully loads, you should get a new position in the menu called VAT ID Validator with a single option, Validate. Click on it and Google will ask you to authorize additional permissions. After you agree, the script will run but nothing will happen. It’s because we need VAT IDs to validate. Here are a few to play with:

• PL8522604586
• IE6388047V
• NL813981487B01
• IT7863930017
• DE123456789

Simply paste them to the spreadsheet, select all five cells, and click on Validate once again. Voilà! The first three are valid and should be highlighted in green. The other two will be red, as those are not valid VAT ID numbers.

Additional Exercises

The above may be a complete extension, but if you are looking for more practice, you can try to add more validators. For example, you can validate if selected cells contain a valid US Taxpayer Identification Number or credit card number. You can take two approaches:

• Find an API to handle validation, just like we did above for VAT ID. That will be more accurate for TIN where you need to check not only the data format but also validate if the number is officially registered.
• Write your own logic directly in the Apps scripts. For credit card validation, you can follow specific rules and most of them can be validated with the Luhn algorithm.

This is only an introduction and the above example is quite simple, but keep in mind that the business logic in your Apps scripts can be much more advanced. You can use custom JS libraries, which will help you to achieve more complicated tasks. One of them I often use is Moment.js—working with dates on Vanilla JS is a horrifying experience.

Another thing you can do, if your script solves a more general problem, is deployment of your Apps script as an add-on. This will allow you to share the script with other users. You can even choose between sharing the add-on with your organization only or make it public for everyone. It will appear in the add-ons library and will be ready to use in any spreadsheet.

The deployment process isn’t complicated and the first step is to prepare the code for testing. You can find more information about that here. If you decide you are ready to go you can publish your add-on. Take note, if you choose to publish your code publicly, it will be reviewed by the Google team to make sure it complies with standards for app publications.

Next Steps

That’s all you need to know to start hacking. The possibilities are limitless. Apps scripts work not only with Google Spreadsheets but also with Docs and Slides.

The next time you need to find a way to automate some processes or workflows, keep in mind it may be easily achieved with Apps scripts. In this post, we discussed only the tip of the iceberg.

You can inspire yourself or find a ready solution browsing the rich add-ons library.

Here are a two links which will help you start your journey with scripting your spreadsheets:

• The Apps Script Reference for Sheets
• All the code from this article can be found on GitHub: https://github.com/wojciech-skowronek/apps-scripts-vat-id-validator

Also, since Apps Script is a subset of JavaScript, you may consider reading The 10 Most Common Mistakes JavaScript Developers Make from fellow Toptaler Ryan J. Peterson.