Google Sheets Script - Loop values in different cells

Question

I come from VBA and Python so still trying to get the hang of javascript in Google Sheets. I am trying to do something pretty simple.

Find last row in column D - Sucessful
Name array in Column D from row 2 thru LastRow - Sucessful
Copy value of cell for row i and paste in column E - Not Successful

I need it to look in data[0][0] and copy the info in column E, then look in data[1][0] and copy info in column E, etc., etc. up to the lastRow. Everything works up till the last line and I get an error saying the "The coordinates or dimensions of the range are invalid."

Here is my code: (FYI: lastrow() is a function that finds last row # in column specified)

function GatherStoreInfo() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ReportSheet = ss.getSheetByName('Report');

  for (var i = 0; i < lastRow('A') + 1; i = i+1) {
    var data = ReportSheet.getRange(2, 4, lastRow('D'), 1).getValues();
    ReportSheet.getRange(i, 2).setValue(data[i][0]); // <- This doesn't work
  }
}

Senor Penguin Senor Penguin · Accepted Answer · 2017-02-24T18:40:39

Ok after messing around with it more I figured it out. I guess I'll post the answer just in case someone stumbles on this.

Basically it was because I was starting i at 0 and then in the range I was telling it to start at row 0 which doesnt exist, so I had to add i + 1 in the range and it worked. Here's the final code:

function GatherStoreInfo() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ReportSheet = ss.getSheetByName('Report');

  for (var i = 0; i < lastRow('A'); i = i+1) {
    var data = ReportSheet.getRange(1, 4, lastRow('D'), 1).getValues();
    SpreadsheetApp.getActiveSheet().getRange(i + 1, 5).setValue(data[i]);
  }
}

Google Sheets Script - Loop values in different cells

1 Answers