[FIXED] How to append array data to spreadsheet rows using Google Script?

Issue

I have created a script to retrieve data from REST API. I can view all the array data in logger. How do I add all those data into rows. This is my current function:

function myFunction() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var mainSheet = ss.getSheetByName("test")
  mainSheet.getRange('A1:A3').clear();

  var apiKey = 'test';

  var URL_STRING = "test";

  var url = URL_STRING + "?ApiKey=" + apiKey;

  var response = UrlFetchApp.fetch(url);
  var json = response.getContentText();
  var data = JSON.parse(json);
  var arr = [];
  //Logger.log(data.output.o1);

  for (var i=0;i<data.output.o1.length;i++){
    x=(data.output.o1[i].company_name);
    arr.push(x);
    Logger.log(arr);
  } 
}

This is the sample output for arr:

This is my expected output:

Solution

I believe your goal as follows.

• You want to put the values of arr from row 2 of the column "A" in the sheet "test".

In this case, how about the following modification?

From:

for (var i=0;i<data.output.o1.length;i++){
  x=(data.output.o1[i].company_name);
  arr.push(x);
  Logger.log(arr);
}

To:

for (var i = 0; i < data.output.o1.length; i++) {
  x = (data.output.o1[i].company_name);
  arr.push([x]); // Modified
  Logger.log(arr);
}
mainSheet.getRange(2, 1, arr.length).setValues(arr); // Added

• If you want to append the values to the sheet, please modify mainSheet.getRange(2, 1, arr.length).setValues(arr); as follows.

    mainSheet.getRange(mainSheet.getLastRow() + 1, 1, arr.length).setValues(arr);
  

References:

• getLastRow()
• getRange(row, column, numRows)
• setValues(values)

Answered By - Tanaike

Answer Checked By - Gilberto Lyons (PHPFixing Admin)