Accept Arbitrary Number of Arguments in Google Scripts Custom Function?

Question

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?

This question is a duplicate of In Apps Script, How to include optional arguments in custom functions.. — Dan Dascalescu
– Dan Dascalescu, Commented Nov 26, 2024 at 17:49

AdamL · Accepted Answer · 2014-10-29 22:38:23Z

9

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

answered Oct 29, 2014 at 22:38

AdamL

24.8k7 gold badges73 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

bumpkin Over a year ago

It still only shows the required parameters when I call the function in google docs. Here's where they describe this mechanism: developers.google.com/apps-script/guides/sheets/…

AdamL Over a year ago

Try for example function numberOfArguments() {return arguments.length;} and call the custom function in a cell with different numbers of arguments.

AdamL Over a year ago

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

Mogsdad · Accepted Answer · 2014-12-07 03:27:54Z

4

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

answered Dec 7, 2014 at 3:27

Mogsdad

46k21 gold badges166 silver badges289 bronze badges

2 Comments

chenware Over a year ago

Strangely if I put in the same JSdoc code into a script I get different results – rather than "london", "manchester", etc I get Object.

GrayedFox Over a year ago

Any idea how to indicate via JSDOC that the custom function accepts an arbitrary amount of arguments? This works in terms of the method being able to work with any amount of args but Google Sheets complains with a error when I pass more than one arg saying "only takes on argument"

Collectives™ on Stack Overflow

Accept Arbitrary Number of Arguments in Google Scripts Custom Function?

2 Answers 2

3 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related