admin管理员组文章数量:1316667
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();
...
}
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();
...
}
Share
Improve this question
edited Jun 17, 2019 at 16:14
Wicket
38.5k9 gold badges78 silver badges193 bronze badges
asked Sep 24, 2012 at 10:47
RikuRiku
2,2632 gold badges17 silver badges20 bronze badges
4
- How do you write your formula to use custom function. Do you use =arrayToList("G2:j30") or =arrayToList(G2:j30) – Waqar Ahmad Commented Sep 24, 2012 at 11:04
- Actually I'd like the user to be able to choose the array so that's =arrayToList(G2:j30) – Riku Commented Sep 24, 2012 at 11:40
-
So in that case, your custom function simply bees
function arrayToList(chosenArray) { var values = chosenArray; }
– AdamL Commented Sep 24, 2012 at 21:47 - This question may help: stackoverflow./questions/12206399/… – HardScale Commented Jun 11, 2016 at 13:56
3 Answers
Reset to default 3There 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 ma as decimal separator use/
as column separator
References
- https://developers.google./apps-script/guides/sheets/functions
- Using arrays in Google Sheets
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.
Riku - Did you try calling your function as arrayToList(G2:j30)? I believe Apps Script will convert that selected range to a ma 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?
本文标签:
版权声明:本文标题:javascript - How do I use an array as argument for my custom function in Google Apps Script in a spreadsheet - Stack Overflow 内容由网友自发贡献,该文观点仅代表作者本人, 转载请联系作者并注明出处:http://www.betaflare.com/web/1741997339a2410310.html, 本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容,一经查实,本站将立刻删除。
发表评论