How do I use an array as argument for my custom function in Google Apps Script in a spreadsheet

Question

I'm trying to create a custom function in my google spreadsheet with the script editor.

I can't seem to allow the user to give the function an array as the argument.

My script works when I hardcode like this:

    var values = SpreadsheetApp.getActiveSheet().getRange("G2:j30").getValues();

What I want to do is pass the array as an argument:

   function arrayToList(chosenArray) {
        ...
        var values = SpreadsheetApp.getActiveSheet().getRange(chosenArray).getValues();
        ...
   }

How do you write your formula to use custom function. Do you use =arrayToList("G2:j30") or =arrayToList(G2:j30) — Waqar Ahmad, Sep 24 '12 at 11:04
Actually I'd like the user to be able to choose the array so that's =arrayToList(G2:j30) — Riku, Sep 24 '12 at 11:40
So in that case, your custom function simply becomes `function arrayToList(chosenArray) { var values = chosenArray; }` — AdamL, Sep 24 '12 at 21:47
This question may help: http://stackoverflow.com/questions/12206399/passing-cell-references-to-spreadsheet-functions — HardScale, Jun 11 '16 at 13:56

score 3 · Answer 1 · answered Jun 17 '19 at 16:28

There are two ways to pass an array as argument to a Google Sheets custom function

By using a range reference, like A1:D5. Usage: =myCustomFunction(A1:D5).
By using the Google Sheets array notation like {1,2;"Yellow","Orange"}. Usage: =myCustomFunction({1,2;"Yellow","Orange"}). Note: If your spreadsheet is set to use comma as decimal separator use / as column separator

References

score 2 · Accepted Answer · edited May 23 '17 at 11:58

2

Now this functionality is not implemented in GAS. There are similar questions in SO (here and here). You can post a feature request to the issue tracker.

edited May 23 '17 at 11:58

Community

1
1

answered Sep 24 '12 at 14:32

megabyte1024

8,482
4
30
44

This answer is obsolete since a while, it's even included on the official docs https://developers.google.com/apps-script/guides/sheets/functions – Rubén Jun 17 '19 at 16:10

score 1 · Answer 3 · answered Sep 24 '12 at 14:40

Riku - Did you try calling your function as arrayToList(G2:j30)? I believe Apps Script will convert that selected range to a comma separated string (going left -> right and then top->down).

It wont be the same as a the Range object, but at least you can get a CSV string representation of the selected range and perhaps that is sufficient?

How do I use an array as argument for my custom function in Google Apps Script in a spreadsheet

3 Answers3

Linked