7

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?

bumpkin
  • 3,321
  • 4
  • 25
  • 32

2 Answers2

9

You can reference the arguments object when the number of arguments passed to a function is variable.

AdamL
  • 23,691
  • 6
  • 68
  • 59
  • It still only shows the required parameters when I call the function in google docs. Here's where they describe this mechanism: https://developers.google.com/apps-script/guides/sheets/functions#autocomplete – bumpkin Oct 29 '14 at 22:53
  • Try for example `function numberOfArguments() {return arguments.length;}` and call the custom function in a cell with different numbers of arguments. – AdamL Oct 29 '14 at 23:04
  • Oh, if you're referring to the automcomplete information, there isn't a neat way of specifying optional arguments (however you could include that info in the @param description). – AdamL Oct 29 '14 at 23:07
3

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

screenshot

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];
  ...
Mogsdad
  • 44,709
  • 21
  • 151
  • 275
  • Strangely if I put in the same JSdoc code into a script I get different results – rather than "london", "manchester", etc I get Object. – chenware Aug 05 '18 at 01:32