How to append array data to spreadsheet rows using Google Script?

Question

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:

Tanaike · Accepted Answer · 2021-05-06 13:24:12Z

1

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:

answered May 6, 2021 at 13:24

Tanaike

203k12 gold badges126 silver badges222 bronze badges

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

7 Comments

Sriram Over a year ago

i got an error saying Error Exception: The number of rows in the data does not match the number of rows in the range. The data has 1 but the range has 2.

Sriram Over a year ago

Btw i tried this method. It can work. for (var i=0;i<data.output.o1.length;i++){ x=(data.output.o1[i].company_name); Logger.log(x); mainSheet.appendRow([x]); }

Sriram Over a year ago

How do i append this in specific row and column?

Tanaike Over a year ago

@Sriram Thank you for replying. I apologize for the inconvenience. In that case, can you provide the sample values of arr after your for loop? And also, can you add your current script for replicating your issue? By this, I would like to confirm your situation. I thought that the error message of The data has 1 but the range has 2. means that you might use the script getRange(2, 1, arr.length, 2) instead of getRange(2, 1, arr.length).

Sriram Over a year ago

thanks for your reply. I have added the sample output for arr

|

Collectives™ on Stack Overflow

How to append array data to spreadsheet rows using Google Script?

1 Answer 1

From:

To:

References:

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

From:

To:

References:

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related