Passing arguments / parameters from a Google sheets custom function to the script function

Question

I'm stuck on a something basic. How do I pass arguments to a Google Sheets custom function.

I have a function in the Apps Script code editor defined as:

function usageByMonth(range,theDate) {
    // do something with the arguments passed
}

In my Google sheet the function is used as

=usageByMonth('Source Meter Readings'!A5:A,B1)

When trying debug the function, I find that the two arguments are "undefined". I've looked at many posts and even copied functions directly from the examples with the same result.

Answer 1

Running a function from the code editor doesn't retrieve the arguments. In that situation, the arguments will always be undefined. This is also true for situations like using the onEdit() simple trigger. The only way you can get the arguments passed to the function, is by entering a valid custom function into the spreadsheet cell.

If you want to test code for a custom function from the code editor, you must "hard code" the argument values.

Also, Logger.log() will not log anything to the log when a custom function is calculated in the sheet.

Start with a simple custom function that works, and build on that.

If you want to pass a range into the code for the custom function, you must understand what a two dimensional array is:

Two Dimensional Array:

[  [inner Array One],  [inner Array Two], [etc]  ]

The data from a range is put into a two dimensional array. There is ONE outer array, with inner arrays. The inner arrays have as many elements as the number of columns in the range.

[  ['single value from first column', 'single value second column']  ]

The above 2D array is for only 1 row with two columns.

So to create a simple custom function as a test, create a function:

function myCustomFunction(argOne) { 
  return argOne[0][0];
};

If the range was A1:B5, the custom function will return the value in A1;

=myCustomFunction(A1:B5)

The only way to know for sure, if your argument was passed into the .gs server function, is if you get a result back.

There are inputs that you can not use. If you use an input that isn't allowed, then (to quote from the documentation):

If a custom function tries to return a value based on one of these volatile built-in function, it will display Loading... indefinitely.

For example, you can't use NOW() or RAND() as an argument.

Answer 2

The function argument delimiter depends on the spreadsheet locale. If spreadsheet locale uses comma as decimal delimiter, the argument delimiter is a semicolon:

=usageByMonth('Source Meter Readings'!A5:A; B1)

Otherwise it would be impossible to pass decimals into functions.