0

I have a Google Sheet. In this sheet, I want to run a custom function to do a complex calculation. This calculation will be based on values from other cells in other sheet tabs. For that reason, I want to be able to pass in a number of variables that is likely to change in the future.

I noticed the SUM function allows for optional parameters. The optional values are "repeatable". This is the behavior I want to reproduce in my custom function, repeatable/optional parameters. How does the SUM function allow for repeatable/optional parameters? How can I do the same in a custom function?

Thank you.

TheMaster
  • 45,448
  • 6
  • 62
  • 85
Dev
  • 921
  • 4
  • 14
  • 31
  • I'd pass the repeatable parameters in one array, in this use case (especially if you are passing other variables as well). Not sure how to enforce overloading google apps script functions, I think it might not be possible but my research on the topic was back before V8. – J. G. Dec 30 '21 at 15:16
  • Does this answer your question? [In Apps Script, How to include optional arguments in custom functions](https://stackoverflow.com/questions/10843768/in-apps-script-how-to-include-optional-arguments-in-custom-functions) – Kos Dec 30 '21 at 15:35
  • You can pass parameters in an array and then test their length. – Mike Steelson Dec 30 '21 at 16:41

1 Answers1

1

You can use function rest parameter syntax:

function COMPLEXSUM(...args) {
  let sum = 0  

  args.forEach(x => {
    sum += x
  })
  
  return sum
}

or if you want some parameters to be required and additionally some optional ones:

function COMPLEXSUM(param1, param2, ...args) {
  let sum = param1 + param2
  
  return args.length === 0 ? sum : sum + args.reduce((pre, curr) => {
    return pre + curr;
  })
}
Rafa Guillermo
  • 14,474
  • 3
  • 18
  • 54