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.

Solution 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.

Solution 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.

Comments

[Neil Lamka]

Interesting ... not the answer I was hoping for but that would explain things. Makes debugging not all that helpful in this common scenario though

[GhostCat]

I do not see how this answers the question. This should be a comment at best!

[tehhowch]

Semicolons are not required. Google sheets uses both semicolons and commas as argument delimiters.

[mrts]

Do you have a reference that proves this or is this just an hypothesis? Comma didn't work for me nor for the original poster, probably because our locale settings - at least in Excel the delimiter is locale-dependent and Google mimics Excel syntax.

[tehhowch]

It is indeed locale dependent, but for example in en-us, the argument {A1;B1} is a 2 row, 1 column array of range references, and {A1, B1} is a 1 row, 2 column array of range references.

[mrts]

Take a look at updated answer -. does that look good to you? If spreadsheet locale uses comma as decimal delimiter, the argument delimiter is semicolon, otherwise it would be impossible to pass decimals into functions. Many thanks for bringing this up by the way :)!

[tehhowch]

The OP's issue is not really related to this answer IMO (they were simply using the editor to test, which cannot provide values of the function arguments), but your edits have altered my votes on your answer

[mrts]

Thanks! It's indeed a bit hard to tell if OP's issue is related to this or not. But I think this adds value to the topic nevertheless.