How can I return the range spreadsheet in the same form as in the that it is become row by row

Question

I'm a beginner in programming. I do a query to the spreadsheet in cell A2, then I filter data according to request and receive desired range “I1:L16”.Then I return the data to the tablet. Everything is fine back on the tablet,but it is uncomfortable to read, no line breaks! Please tell me how can I return the range "I1:L16" in the same form as in the spreadsheet that it is became row by row .

My script

function doGet(e)
{
  var sheet = SpreadsheetApp.openById("1UBKi7VDo1aJjn6IwGMKvlxy4Jz9d9jVdY79zpOrkQP8");
  sheet.getRange("A2").setValue(e.parameter.p1) ;
  var n= sheet.getRange("I1:L16").getValues();
  return ContentService.createTextOutput(n);
}

Spreadsheet https://docs.google.com/spreadsheets/d/1UBKi7VDo1aJjn6IwGMKvlxy4Jz9d9jVdY79zpOrkQP8/edit?usp=sharing

URL Web applications: sample query https://script.google.com/macros/s/AKfycbyHu7d5djciltlG-micI6O-cQheC6kN7yWKkevm5KwGCJiKROZB/exec?p1=1234567891118

Best regards, Alexander.

Jack Brown Jack Brown · Accepted Answer · 2017-04-03T08:13:09

You can do something like this:

function doGet(e)
{
  var sheet = SpreadsheetApp.openById("1UBKi7VDo1aJjn6IwGMKvlxy4Jz9d9jVdY79zpOrkQP8");
  sheet.getRange("A2").setValue(e.parameter.p1) ;
  var n= sheet.getRange("I1:L16").getValues();
  var retStr =""
  for(var i = 0 ; i< n.length ; i++){ 
   retStr += n[i].join("\t") +"\n" //Insert a tab between each element and newline at each row
  }
  return ContentService.createTextOutput(retStr);
}

Edit: Alternative/Robust option

The better option is to service a Html content, that way you can make use of Html tag to improve presentation of your data. In the below data I am assuming you want to highlight row 6, you do that using css element to make the text red and bold. Code:

function doGet(e)
    {
      var sheet = SpreadsheetApp.openById("1UBKi7VDo1aJjn6IwGMKvlxy4Jz9d9jVdY79zpOrkQP8");
      sheet.getRange("A2").setValue(e.parameter.p1) ;
      var n= sheet.getRange("I1:L16").getValues();
      var retStr ="<style> .tcol { font-weight: bold; color : red; } </style> <table>"
      for(var i = 0 ; i< n.length ; i++){
       if ( i != 5) { //I assuming row 6 is what you want to highlight
        retStr +="<tr><td>"+ n[i].join("</td><td>") +"</td></tr>"
       } else {
        retStr +="<tr><td class = 'tcol'>" + n[i].join("</td><td class = 'tcol'>") +"</td></div></tr>"
       }

      }
      retStr += "</table>"
      return HtmlService.createHtmlOutput(retStr) 
     }

Hope that helps!

How can I return the range spreadsheet in the same form as in the that it is become row by row

1 Answers