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


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 and when you enter that into RawGit, you get the usable source file at

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

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

Copy Multiple Google Sheets with Template Variables

Business and education administration often involves an overwhelming amount of repetitive tasks. We work in cycles of days, weeks, months, and years with multiple students, clients, employees, or team members. This adds up to multiples of multiple tasks. Luckily, automating repetitive and work-intensive tasks is just one of the many things Google Apps Script was made for.

In this Google Apps Script example, let’s automate all but the individualized aspects of working with Google Spreadsheets. We will create a template sheet and make multiple copies of that sheet with individualized variables for each sheet.

With a click of a button, this script will copy multiple versions of a sheet for each time period, each project, or each person.


How to Use the Script

Using the script is just as easy as using any other. Just copy the code below and paste it into the Google Apps Script code editor. For a working example, make a copy of this demo sheet.

  • Save the script
  • Run the onOpen() function or refresh the spreadsheet to instantiate the drop down menu
  • Select Create template variables sheet if the Template Variables Sheet does not already exist. 

Google Spreadsheet template copy

  • Fill in your template variables. The template variable names can any combination of letters, numbers, and underscores (_) enclosed with curly braces  eg: {{1st_place}}

Google Spreadsheets template sheet copy

  • To add more template variables, click Add Template Variable. To remove template variables, just delete that template variable’s column.
  • Before you make copies, ensure that the template variables sheets are completely filled out, including the {{sheet_name}} variable (the name of the tab). Else the variables will be left blank. Also, make sure that all cells outside of the template variable names columns are left empty.
  • To create copies, click the Create copies on the Sheet drop-down menu. This will create a new copy of the template sheet with unique variables for each row of template variable definitions.


How The Script Works

There are three parts to this script: the menu, the template variables sheet, and the sheet copier.

The menu follows a common and obvious pattern; it is built by the onOpen function. The onOpen function is called every time the spreadsheet is opened so whenever you open the spreadsheet, the menu is ready to use. Learn more about menus in Google Apps Script.

To make it as easy as possible for the user to avoid error, there is a function called createTemplateVarsSheet() that adds a sheet labeled “Template Variables” and adds an example header row and an example variable row. This function is called whenever the user clicks Create template variables sheet on the menu.

With the same intent to avoid any user error, the function addTemplateVariable() adds a new template variable column to the template variable sheet. This is can be done manually quite easily, but this function, called when the user clicks Add template variable, walks the user through the process and keeps the same consistent formatting or the Template Variables sheet.

The real value of this script comes in the templateCopier() function. This function iterates through each row of the Template Variables range and creates a copy of the template sheet with the values for each row.

The magic happens in a call to the replaceTags() function. This function is called in every cell of the copied template sheet and uses a regular expression to find and replace a template tag with its corresponding value in for the copy of the template.

Once every cell in every copied sheet has been updated the script is done and just like that, there are a number of new replica sheets.

Here is the script:


Google Spreadsheet Copier

I hope you find it useful for automating your work in Google Spreadsheets. If you think it could be improved to serve your purpose, let me know in the comments.

March 7, 2017

Posted In: Google Spreadsheets

Leave a Comment