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.

Could you describe how you determined that the arguments were undefined? — Brionius
The problem could be your theDate argument. Remove that argument, and use just the range argument. Then do something very simple, like get a value out of the range, and returning it. — Alan Wells
I determined that the arguments were undefined by looking at the items when trying to debug the function. They displayed as "undefined". The answer below describes why - rendering debugging scripts in the editor mostly useless for this rather common development scenario, determining the input arguments and being able to step through a function that passes arguments from a sheet. — Neil Lamka

Alan Wells Alan Wells · Accepted Answer · 2016-01-26T21:23:09

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.

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

4 Answers

Two Dimensional Array: