Google Sheets Custom Function Tutorial

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 Functions

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 functions.

Writing Your First Custom Function

You are probably familiar with the LEN() function that takes some text as 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 function 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:

function functionName ( argument1, argument2, argumentN ) {

  // two backslashes is the start of a comment

  some function logic
 
  return returnValue

}

The LEN() function would then look like this:

function LEN(text){
  
  // the textLength variable is assigned to
  // the count of characters in the value of `text`
  // when it is turned into a Javascript String

  var textLength = String(text).length

  // then the function returns the variable textLength

  return textLength
}

That custom function 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 Your Function 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:

/**
 * Length of a string
 *
 * @param {"A few words"} text The number of characters in text 
 * @return {number}
 * @customfunction
 */
function CUSTOMLEN(text){
  var textLength = String(text).length
  return textLength
}

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.”

/**
 * Get the latitude and longitude of a location
 *
 * @param {"Yankee Stadium"} location The number of characters in text 
 * @return {array}
 * @customfunction
 */
function GEOCODE(location){
  var cache = CacheService.getScriptCache();
    var cached = cache.get(location);
    if (cached != null) {
      return [cached.split(',')];
    }
  // Gets the geographic coordinates the input location
  var response = Maps.newGeocoder().geocode(location);
  var result = response.results[0];
  var ssResult =  [ [result.geometry.location.lat, result.geometry.location.lng] ]
  // Cache for 25 minutes
  cache.put(location, ssResult, 1500);
  return ssResult
}

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!