Looping through Google Sheet

1
votes

I am having a hard time with Ranges and figuring out why I need to add i+1 in getRange to get the correct cell in my loop. I'm not sure if its because I increment before the variable or if it has something to do with the initial value?

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Sheet1");
var range = sheet.getDataRange();
var values = range.getValues();`

for (var i = 3; i < 30; ++i) {
  var row = values[i];
  var msgSent = row[3];
  sheet.getRange(i + 1, 4).setValue("ALERTED");

Any help is appreciated!

1
for-loopgoogle-apps-scriptgoogle-sheets

1 Answers

2
votes

.getRange() just uses integer based referencing while the 2D array of values needs to be referenced with a zero-based approach.

The 1st row (array) in values is values[0], but .getRange() uses .getRange(1, 1) to reference the 1st row, 1st column of the sheet. The 1st column value in the 1st row would be values[0][0].

Now if you're looping through 30 or so rows and want to set the values all at once, which is much better than setting them one-by-one, it'll look some thing like this...

var s = ss.getSheetByName('Sheet1');
var newValues = [];
for (var i = 3; i < 30; ++i) {
  var row = values[i];
  var msgSent = row[3];
  newValues.push(["ALERTED"]);
}
s.getRange(4, 4).offset(0, 0, newValues.length).setValues(newValues);