Passing arguments / parameters from a custom Google Sheets function to a script function

Question

I am stuck on something basic. How to pass arguments to a custom Google Sheets function.

I have a function in the Script application 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 I try to debug a function, I find that there are two arguments: "undefined". I looked through a lot of posts and even copied functions directly from the examples with the same result.

Answer 1

Running a function from the code editor does not return arguments. In this situation, the arguments will always be undefined. This is also true for situations, for example, when using the simple onEdit() trigger. The only way you can get the arguments passed to the function is to enter a valid user-defined function in a cell in the spreadsheet.

If you want to test the code for a user-defined function from the code editor, you must hardcode the argument values.

In addition, Logger.log() will not write anything to the log when a user-defined function is evaluated on a worksheet.

Start with a simple custom function that works and build it.

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

Two-dimensional array:

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

The data from the range is placed in a two-dimensional array. There is ONE external array with internal arrays. Internal arrays have as many elements as the number of columns in a range.

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

The above 2D array is only used for 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 user-defined function will return the value in A1;

 =myCustomFunction(A1:B5) 

The only way to know for sure if your argument was passed to the .gs server function is to return the result.

There are inputs that you cannot use. If you use an input that is not allowed, then (for a quote from the documentation):

If a user-defined function tries to return a value based on one of these unstable built-in functions, "Loading ..." will be displayed indefinitely.

For example, you cannot use NOW () or RAND () as an argument.

Answer 2

be sure to use the exact case as identifiers are case sensitive in Google Script.

T. theDate is not the same as thedate or TheDate

This is a common mistake for those who are not used for case sensitive languages.