Your First Google Sheets Macro

 

You have probably imagined a Google Spreadsheet function that did exactly what you needed it to without messing around with 17 different functions just to get the desired result. Well, you are in luck! Google Spreadsheets supports custom macros written with Google Apps Script for this very reason. If you are familiar with Excel and VBA, this will come easily to you. If you are new to custom functions altogether, this is the perfect place to start.

Google Sheets macros, also known as “custom functions,” can be really simple, (they don’t even need to take a single argument) or they can be really powerful- getting data from other web services, modifying the spreadsheet beyond the cell, or even beyond the Spreadsheet!

 

 

Intro to Google Spreadsheet Macros

The Google Apps Script language is a slightly modified version of modern Javascript that runs in the Google Apps Script Editor, an “integrated development environment” for Google Apps Script. The syntax and rules are the same so the nice thing about learning Google Apps Script is that you are learning Javascript at the same time! Unlike VBA (Visual Basic) Javascript is used everywhere.

Google Apps Script also offers a few additional features and utilities that allow you to interact with other Google Services, like Spreadsheets, but also Google Drive, Gmail, and even Google Maps! For this tutorial, we will start simple though and get you on your way to writing your own custom macros.

 

Writing Your First Custom Macro

You are probably familiar with the LEN() function that takes some text as an input and returns how many characters are in that text. It is about as simple as it gets for spreadsheet functions, so let’s start with by rewriting it in Google Apps Script.

Start by going to the Google Apps Script Editor by clicking Tools > Script Editor…  and clear the empty myFunction function and start with a blank slate. If you found a different custom macro somewhere on the internet and you just want to try it out, just copy it here! Then save the script by clicking File > Save.

As you can see by the myFunction function, Javascript functions have this basic form:

The LEN() function would then look like this:

That custom macro would work just like the Google Spreadsheet LEN() function (except a bit slower as is often the case with Google Apps Script custom functions).

Note that Javascript variables and function names are often camelCassedLikeThis but it is good form to name Google Apps Script custom macro functions in ALLCAPPS for consistency with other Google Spreadsheet functions.

 

Documenting You Macro Help Text and Autocomplete

When you use Google Spreadsheet built-in functions, you often see helpful text that describes what the function does as you write it. You can replicate this behavior by writing you own custom help text using a specific format. Autocomplete formatting uses a Javascript /** multi-line comment */ to express the function options in the help text.

 

The first line of the comment is the function description. Each line after @param {data type} describes the expected function input. The @return line

This is what the LEN function (or seen here as CUSTOMLEN) help text would look like this:

For more information on documenting functions, see the JSDoc documentation.

 

More Interesting Custom Functions with Google Services

One of the beauties of Google Spreadsheet is that it is cloud-based which makes getting data from the Web so easy. In this example, we use the Google Maps library within Google Apps Script to get the Latitude and Longitude of a location- a place like Yankee Stadium or an address like, “1600 Pennsylvania Avenue NW Washington, DC.”

This macro function is a bit different than before in a few ways: it uses Google Apps Script’s Cache service and Google Map’s API, and it returns a Range instead of a single value.

The Cache service is very handy when your script makes a lot of requests to external data. This can speed up the function execution and prevent the script from making too many requests and/or timing out.

The Google Maps API, like other Google services, is accessible as a single “class” which provides a lot of functions that to get data from the service. (In this case, geocode coordinates).

The return value of this function is different than the first function because it returns a Range of values rather than a single value. This is done by returning what is called a two-dimensional array, or as Google Apps Script documentation describes them. SomeDataType[][]. For example, a Range of cells filled with text would be denoted as String[][].

The input in this function is again, only a single parameter, but there could be several. For instance, a GETDISTANCE function might look like this GETDISTANCE(origin, destination).

 

A Few More Notes and Potential Challenges

  • Handle errors gracefully. Users of your custom macros may accidentally input the wrong type of argument so you should handle these gracefully by type checking and using try catch blocks to catch errors. That way you can help the user understand what they need to change if they make a mistake.
  • Be Fast. Custom macros run on Google Server, not on the user’s computer so try to make function execution fast. If functions take over 30 seconds, they will timeout and leave the user wondering what happened.
  • Watch out for API rate limits. App script functions can run multiple times per sheet very rapidly. If you are calling an API make sure that you account for API rate limits.

 

Now you get the basic pattern for creating a Google Spreadsheet custom macro function. You can also use Google Apps Script functions with buttons or Google Apps Script custom menus. Let your imagination go wild and automate all the things! If you want to continue to learn how to get the most out of Google Apps Script, sign up for the Google Apps Scripting email newsletter!

 

January 6, 2018

Posted In: Custom Functions, Google Spreadsheets

2 Comments

Working with URLs in Google Apps Script and Google Sheets

 

There is something so fitting about working with URLs in Google Apps Script and Google Spreadsheets. The cloud-based software works in the browser where, indeed, there are a lot of URLs to work with. Google Apps Script’s doPost, doGet, and UrlFetchApp make HTTP requests to URLs easy. Google Spreadsheets has similar functionality with IMPORTXML, IMPORTHTMLIMPORTFEED, and IMPORTDATA. If you work in web analytics, digital marketing, or especially SEO, chances are, you’ve stared at a big spreadsheet of URLs trying to figure out how to parse, check, or call the URLs quickly and easily. Luckily, there is a beautiful Javascript library that makes working with URLs just as quick and easy as you want it to be.

This post will show how to import the URI.js javascript library from a third-party source (in this case GitHub) and use it in Google Spreadsheet custom functions. The demo should also help you use the library if you are working with a script that involves constructing REST API URLs.

 

Introducing URI.js

URI.js is a very comprehensive javascript library for working with and manipulating URLs. It allows you to get specific parts of the URL, change them, and not have to worry if you’ve mashed up the original URL into a malformed mess. It even lets helps you determine if a URL qualifies as a URL or IP address. If you don’t know how handy this is, I’ve got some spreadsheets you can work on!

To get a javascript library from GitHub to Google Apps Script, you can use RawGit to serve the library’s source file from GitHub. Then use the eval() command to make the library and its methods accessible to the Google Apps Script environment.

The GitHub URL for URI.js is https://github.com/medialize/URI.js/blob/gh-pages/src/URI.js and when you enter that into RawGit, you get the usable source file at https://cdn.rawgit.com/medialize/URI.js/gh-pages/src/URI.js.

Now to get the URI object and all of its handy methods just call eval like so:

Then the URI object is in Google Apps Script’s global scope. To use it just do something like URI.methodName(). To see all the methods in the library check out the API reference.

 

URL Custom Functions for Google Apps Script

Here are a few wrapper functions to take advantage of the URI.js in Google Apps Script. There are other functionalities not included here so don’t be afraid to build on this! Just remember, you must use eval() (above) in order to load the library.

The output of the logs for the function testURI should look like this:

I hope this helps you clean up the internet. Leave a comment if you have any questions!

December 29, 2017

Posted In: Custom Functions, Google Spreadsheets

Leave a Comment

Zip Codes to City, Region & Geocode in Google Sheets

ZIP codes are a very useful and common way to gather location data. But zip codes alone are often less useful than the city, state, latitude and longitude that correspond to the zip code. City and state/region, allow you to group the data with a pivot table while

City and state/region, allow you to group and aggregate numerical data with pivot tables, while latitude and longitude offer sensible ways to determine areas and distance.

Here is a simple custom function that provides easy access to the zippopotom.us free ZIP code converter API.

 

ZIP Code Custom Function

ZIPLOC( zip code [, country abbrv] )

To translate ZIP codes to geographic info, just supply the ZIPLOC function with a ZIP code. The corresponding two-letter country code is optional but necessary for countries other than the US.

For example, if you want to get geographic data about the 1000 ZIP code of Bulgaria, the function would look like this:

ZIPLOC( 1000, BG)

The function will return the following horizontal array of data:

Country, Region, City, Latitude, Longitude

Bulgaria, София (столица) / Sofija (stolica), София / Sofija, 42.6833, 23.3167

 

Google Apps Script Code

The code makes use of the UrlFetchApp function to make a call to the zippopotam.us RESTful APi and parses the requested JSON data and converts it to an array. To see what the JSON data looks like, check out this URL:

http://api.zippopotam.us/bg/1000

To use the code, just copy and paste it into the Google Apps Script Code editor and click Save. The ZIPLOC function will be available on the corresponding Google Spreadsheet.

 

 

March 8, 2017

Posted In: Custom Functions, Google Spreadsheets

Leave a Comment