CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetscustom-function
Robert Millard
Robert Millard

Reputation: 33

Custom function output in rows instead of columns

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

Upvotes: 2

Views: 2035

Answers (2)

acharlop
acharlop

Reputation: 407

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]

Upvotes: 2

Chris
Chris

Reputation: 2107

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

Upvotes: 0

Related Questions