For function google sheets loop

Felipe Agliati 0
Viewed 26

I'm having trouble with a looping functions. I'm not getting the results in all the cells. I'm just getting it in the last row where there is data. Here is what ai have

function myfunction() {
      var ss = SpreadsheetApp;
      var getSheet = ss.getActiveSpreadsheet();
      var sheetData = getSheet.getSheetByName("Sheet3")
      var endRow = sheetData.getLastRow();
      for (var i=2; i <= endRow ; i++);
      if (sheetData.getRange(i,2).getValue() == "test") {
        sheetData.getRange(i,4).setValue("alert1");
      } else{
        sheetData.getRange(i,4).setValue("alert2");
      }
      }

Here is an screenshot of the sheet if it helps :)

google-sheets google-apps-script
1 Answers

Modification points:

  • In your script, I think that your for loop doesn't loop by ; of for (var i = 2; i <= endRow; i++);. When you want to loop, please modify for (var i = 2; i <= endRow; i++); to for (var i = 2; i <= endRow; i++).
  • When getValue and setValue are used in a loop, I think that the process cost will become high. Ref

When these points are reflected in your script, it becomes as follows.

Modified script:

function myfunction() {
  var getSheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheetData = getSheet.getSheetByName("Sheet3")
  var range = sheetData.getRange("B2:B" + sheetData.getLastRow());
  var values = range.getValues().map(([b]) => [b == "test" ? "alert1" : "alert2"]);
  range.offset(0, 2).setValues(values);
}

References:

Tanaike0
Related