最新消息:雨落星辰是一个专注网站SEO优化、网站SEO诊断、搜索引擎研究、网络营销推广、网站策划运营及站长类的自媒体原创博客

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

programmeradmin3浏览0评论

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
Add a ment  | 

3 Answers 3

Reset to default 3

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

  1. By using a range reference, like A1:D5. Usage: =myCustomFunction(A1:D5).
  2. 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?

与本文相关的文章

发布评论

评论列表(0)

  1. 暂无评论