Did you know? (Custom Functions in Google Apps Script)

In Google Apps Script, Custom Functions are special. They have limitations that other script functions don’t, but to compensate, they have been given awesome powers! Ok, that’s a bit strong – let’s say they have unique capabilities that aren’t immediately obvious.

You can learn most of what you need to know to write your own Custom Functions by reading Google’s documentation. But did you know

  • You can craft effective auto-completion help with comments in your Custom Functions?
  • Your Custom Functions can present customized error messages for users?
  • Custom Functions can support an arbitrary number of parameters?

Today’s blog might have some surprises for you, if you thought you knew everything about writing Custom Functions.

Code Comments and Auto-completionPicture a monitor ringed with sticky notes

One of the powers, er… features of Custom Functions is that they can be made to appear completely integrated into Sheets, as if they were built-in. Users access and utilize built-in functions without noticing that they get help along the way – it’s just an expected part of spreadsheet software. But the absence of that help used to hold custom functions back, before the @customfunction documentation tag arrived with “New Sheets”.

Sticky notes are not an effective form of documentation!

Let’s look at a simple Custom Function, lastModified(), from “Function for last modified date of cell”.

The function is preceded by a comment block with special formatting following a convention used for jsdoc (JavaScript Documentation). Because of that, the Google Apps Script interpreter makes the function available to the spreadsheet as a Custom Function, and sets up auto-completion help for it using the information we’ve provided in the comments. Here’s what you’ll see in Google Sheets if you start entering a formula using lastModified:

auto-completion help

The auto-complete feature for custom functions abuses jsdoc tags somewhat, to present the information in the help pop-up.

  • The parameter name from the function declaration is used in the quick-help, that first line of the pop-up that shows lastModified(reference).
  • In standard jsdoc comments, the @param tag includes a “parameter type” enclosed in braces, e.g. {String}. However, in custom functions that is used verbatim in the “Example” section of the function’s help. We’ve used that in this sample function to provide an example range Sheet3!B32.
  • The “Summary” is provided by the non-tagged comment lines, which will be concatenated together. If you want to force newlines, that can be done. See “How to force newlines in Google Apps jsdoc descriptions” for examples.
  • Each defined parameter is listed; we have just reference in this example. In this segment, the parameter names and descriptions are taken from the @param tags.
  • The @returns tag is ignored.
  • The @customfunction tag is unique to Google Sheets Custom Functions, and must be included to allow a function to be accessed as a spreadsheet function.

Customized error messages

When you encounter an error with a built-in spreadsheet function in a formula, you will see #VALUE! in the formula’s cell, and when you hover over the cell a pop-up will provide more details on the error.

Dashboard   Google Sheets

You can provide this same behavior from your Custom Functions, by throwing errors. In this example function from “Nslookup or dig in Google App Script”, note the use of throw to present details of an HTTP error to the user.

Arbitrary parameter lists

Some built-in spreadsheet functions can be used with varying numbers of parameters. One example is COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2, …]), which takes one or more sets of ranges and criteria.

You can do the same in your own custom functions, taking advantage of a JavaScript trick. Every JavaScript function has a built-in automatic object, arguments, which is much like an array. (If you have named parameters, you’ll also find those parameters as values in arguments.)

In “Looking for distance between multiple points in a googlesheets script”, this technique is used to allow drivingDistance() to accept either an array of waypoints (one argument) or a list of waypoints (arbitrary arguments) to express a driving route. If the named parameter route isn’t an array, the expected list of waypoints is collected by the statement args = arguments;.

This example also demonstrates the other techniques discussed in this article. If no valid route is provided, a custom error message is provided to the user. And as shown in “Accept Arbitrary Number of Arguments in Google Scripts Custom Function”, the comments include jsdoc tags to provide helpful auto-complete information.

gYyqM[1]

Advertisements

One thought on “Did you know? (Custom Functions in Google Apps Script)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s