Custom function output in rows instead of columns

Question

The following script returns the values from the for loop in rows. Is there anyway to return the results into the adjacent columns instead?

function readMessage(msgId){
  var url = "https://api.pipedrive.com/v1/mailbox/mailThreads/";
  var token = "/mailMessages?api_token=token";
  Logger.log("readMessage called with msgId: " + msgId);
  if (msgId){ // make sure there is a msgId in the cell 
    var rows = [] 
    var response = UrlFetchApp.fetch(url+msgId+token); 
    var dataAll = JSON.parse(response.getContentText()); 
    var dataSet = dataAll;

    var rows = [], data;

    for (var i = 0; i < dataSet.data.length; i++) {
      data = dataSet.data[i];

      rows.push([data.body_url]);
    } 

    Logger.log( JSON.stringify(rows,null,2) ); 

    return rows;
  }

Current output is-

   A                  B    C    D
1 =readMessage(msgId)
2 "blah blah"
3 "blah blah"

What I want is-

   A                       B            C          D
1 =readMessage(msgId) "blah blah"  "blah blah"
2 
3

acharlop · Accepted Answer · 2018-04-29 19:34:53Z

2

Google Apps Script data is always in the form of a 2D array. It's helpful to have visualization.

    A   B   C
1 [[A1, B1, C1],
2 [A2, B2, C2]]

in order to return a row of data the function should return

[[A,B,C]] // 3 columns of data in one row

in order to return a column of data you can use either the full 2D array or a single array

[[1],[2],[3]]
OR
[1,2,3]

answered Apr 29, 2018 at 19:34

acharlop

4376 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

David Leal Over a year ago

what about when we need to expand both rows and columns with a custom function? thanks

Chris · Accepted Answer · 2018-02-25 09:17:27Z

0

The issue is the formatting of the data you are returning. Try this rows.push(data.body_url); instead:

for (var i = 0; i < dataSet.data.length; i++) {
    data = dataSet.data[i];

    rows.push(data.body_url); //Brackets [] have been removed to insert data as a row.
}

If you return data like this: [[1],[3],[6]] the result will be a column of values:

1

3

6

If you data has this format: [2,4,8] you get a row of values:

2 | 4 | 6

Source: Custom Function

edited Feb 25, 2018 at 9:17

answered Feb 24, 2018 at 23:09

Chris

2,1071 gold badge15 silver badges26 bronze badges

Collectives™ on Stack Overflow

Custom function output in rows instead of columns

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related