I'm trying to re-build COUNTIFS as a Google Scripts Custom Function and having trouble with one thing: how do I build a function that accepts an arbitrary number of arguments?
If you use COUNTIFS in google sheets, the inputs look like this:
=COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2, ...])
My Google Script can be this:
function COUNTIFS(criteria_range1, criterion1){
// CountIFS code
}
...but how do I get the optional arguments in my function?
To get started, open a Google Sheets spreadsheet and click an empty cell. Type =AND(Argument A, Argument B) and replace each argument with the criteria you want to use. You can use as many arguments as you'd like, but you must have at least one for AND to work. In the example below, we used three arguments.
Strict equality (===): a === b results in true if the value a is equal to value b and their types are also the same.
You can reference the arguments object when the number of arguments passed to a function is variable.
(Example to support AdamL's answer.)
The autocomplete feature for custom functions abuses jsdoc tags somewhat. The "parameter type" which is normally enclosed in braces, e.g. {String}
, is used verbatim in the "Example" section of the function's help, while the parameter name is used in the quick-help.
You can take advantage of this to clarify functions with arbitrary parameters, as shown here.
Code
/**
* Calculate the driving distance 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) {
if (arguments.length < 2) throw new Error( "Must have at least 2 waypoints." )
var origin = arguments[0];
var destination = arguments[arguments.length-1];
...
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With