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

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

	/**
	* Return the time that the referenced cell or range was last changed.
	* Initial use shows current time.
	*
	* @param {Sheet3!B32} reference Cell or range to monitor.
	* @returns The time the reference was last changed.
	* @customfunction
	*/
	function lastModified( reference ) {
	// From gist.github.com/mogsdad/3bef949fcae5b4411524
	// Surprise - we don't actually care what has changed!
	return( new Date() );
	}

view raw lastModified.gs hosted with ❤ by GitHub

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:

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.

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.

	/**
	* Perform a Network Service Lookup, using StatDNS API.
	*
	* @param {"google.com"} dn A well-formed domain name to resolve.
	* @return {String} Resolved IP address
	* @customfunction
	*/
	function NSLookup(dn) {
	// From gist.github.com/mogsdad/ece531531e0ef5dc355d
	var url = "http://api.statdns.com/%FQDN%/a".replace("%FQDN%",dn);
	var result = UrlFetchApp.fetch(url,{muteHttpExceptions:true});
	var rc = result.getResponseCode();
	var response = JSON.parse(result.getContentText());
	if (rc !== 200) {
	throw new Error( response.message );
	}
	var ip = response.answer[0].rdata;
	return ip;
	}

view raw NSLookup.gs hosted with ❤ by GitHub

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

	/**
	* Calculate the driving distance (in meters) along a route.
	*
	* @param {"london","manchester","liverpool"} route
	* Comma separated ordered list of two or more map
	* waypoints to include in route. First point
	* is 'origin', last is 'destination'.
	*
	* @customfunction
	*/
	function drivingDistance(route) {
	// From gist.github.com/mogsdad/e07d537ff06f444866c5
	// Adapted from developers.google.com/apps-script/quickstart/macros
	// If a range of cells is passed in, 'route' will be a two-dimensional array.
	// Test for an array, and if we have one, collapse it to a single array.
	if (route.constructor === Array) {
	var args = [];
	for (var row=0; row<route.length; row++) {
	for (var col=0; col<route[row].length; col++) {
	// Skip blanks
	if (route[row][col]) args.push(route[row][col]);
	}
	}
	}
	else {
	// No array? Grab the arbitrary arguments passed to the function.
	args = arguments;
	}
	args = args.clean(""); // remove blanks
	// Just one rule to a route - we need a beginning and an end
	if (args.length < 2) throw new Error( "Must have at least 2 waypoints." )
	// Pass our waypoints to getDirections_(). Tricky bit, this.
	var directions = getDirections_.apply(this, args);
	// We have our directions, grab the first route's legs
	var legs = directions.routes[0].legs;
	// Loop through all legs, and sum up distances
	var dist = 0;
	for (var i=0; i<legs.length; i++) {
	dist += legs[i].distance.value;
	}
	// Done - return the value in meters
	return dist;
	}
	/**
	* Use Maps service to get directions for a route consisting of an arbitrary
	* set of waypoints.
	*/
	function getDirections_(route) {
	// From gist.github.com/mogsdad/e07d537ff06f444866c5
	// Just one rule to a route - we need a beginning and an end
	if (arguments.length < 2) throw new Error( "Must have at least 2 waypoints." )
	// Assume first point is origin, last is destination.
	var origin = arguments[0];
	var destination = arguments[arguments.length-1];
	// Build our route; origin + all midpoints + destination
	var directionFinder = Maps.newDirectionFinder();
	directionFinder.setOrigin(origin);
	for ( var i=1; i<arguments.length-1; i++ ) {
	directionFinder.addWaypoint(arguments[i]);
	}
	directionFinder.setDestination(destination);
	// Get our directions from Map service;
	// throw an error if no route can be calculated.
	var directions = directionFinder.getDirections();
	if (directions.routes.length == 0) {
	throw 'Unable to calculate directions between these addresses.';
	}
	return directions;
	}
	// From stackoverflow.com/a/281335/1677912
	Array.prototype.clean = function(deleteValue) {
	for (var i = 0; i < this.length; i++) {
	if (this[i] == deleteValue) {
	this.splice(i, 1);
	i--;
	}
	}
	return this;
	};

view raw drivingDistance.gs hosted with ❤ by GitHub

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.