18

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.

Magne
  • 16,401
  • 10
  • 68
  • 88
Neil Lamka
  • 183
  • 1
  • 1
  • 5
  • Could you describe how you determined that the arguments were undefined? – Brionius Jan 26 '16 at 19:48
  • 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 Jan 26 '16 at 21:04
  • 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 Jan 27 '16 at 22:05

4 Answers4

15

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.

Alan Wells
  • 30,746
  • 15
  • 104
  • 152
  • Interesting ... not the answer I was hoping for but that would explain things. Makes debugging not all that helpful in this common scenario though – Neil Lamka Jan 26 '16 at 22:18
3

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.

mrts
  • 16,697
  • 8
  • 89
  • 72
  • Semicolons are not required. Google sheets uses both semicolons and commas as argument delimiters. – tehhowch Apr 22 '18 at 11:44
  • 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. – mrts Apr 22 '18 at 17:33
  • 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. – tehhowch Apr 22 '18 at 17:44
  • 1
    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 :)! – mrts Apr 22 '18 at 17:52
  • 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 – tehhowch Apr 22 '18 at 17:54
  • 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. – mrts Apr 22 '18 at 17:57
0

It might be the A5:A range, try A5:A1000 or A:A. Mixing a cell address A5 with a column address A probably confuses the API.

Max von Hippel
  • 2,856
  • 3
  • 29
  • 46
Gerry
  • 1
-1

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

ie. theDate is not the same as thedate or TheDate

This is a common pitfall for those not used to case sensitive languages!

ShayneFromOz
  • 1
  • 1