26

I want to write a custom function which has some mandatory arguments but can also accept a few optional arguments. I couldn't find any documentation on this. Does anyone know? Is it similar to Javascript?

Rubén
  • 34,714
  • 9
  • 70
  • 166
Jarod Meng
  • 281
  • 1
  • 3
  • 4

2 Answers2

29

Custom functions don't have a concept of required and optional fields, but you can emulate that behavior using logic like this:

function foo(arg1, opt_arg2) {
  if (arg1 == null) {
    throw 'arg1 required';
  }
  return 'foo';
}

It's convention to use the prefix "opt_" for optional parameters, but it's not required.

Eric Koleda
  • 12,420
  • 1
  • 33
  • 51
  • 3
    better coding convention: `if( null == arg1 )` as this can't be mistaken for an assignment when you accidentally leave off one =. `if( null = arg1 )` will throw an error which you immediately fix. `if( arg1 = null )` will give you confusing results until you find the missing = – Dev Null Sep 06 '19 at 14:45
1

Yes, it is JavaScript (with a limited support for JsDoc too), so you can have an optional parameter with a default value:

/**
 * This is myFunction.
 * @param {number} arg1 - Some number.
 * @param {string} arg2 - Some string.
 * @param {number} arg3 - [OPTIONAL] Additional numbers or ranges to add to value1.
 * @customFunction
**/
function myFunction(arg1, arg2, arg3=false) {
  return arg3;
}

And then you could call it in your spreadsheet using either:

=myFunction(1, "somestring", true)

Or without the optional parameter:

=myFunction(1, "somestring")

Since JavaScript dynamically handles the parameter list.

Magne
  • 16,401
  • 10
  • 68
  • 88
  • For documenting optional parameters properly, see: https://stackoverflow.com/questions/61711660/what-amount-of-jsdoc-is-supported-in-google-sheets-custom-functions – Magne May 10 '20 at 12:13