for-loopgoogle-apps-scriptsetvalue

Add a status to each proccesed row in for loop that has an if statement with Google App Script

I've got a for loop in App script that is looking only at rows that have data in two columns. I'd like to set a status on each row that is actually processed, but the statuses get added to the wrong rows. When I add to i it adds to the whole length of the array, so I guess I shouldn't be trying to process each row, what am I doing wrong?

    function auditReport() {
  var sheetname = "Sheet1"; // name of data sheet ex. Form Responses 1
  var colstoworkon = 10; // how many cols are filled with data f.e. by a form
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  ss.setActiveSheet(ss.getSheetByName(sheetname));
  var sheet = ss.getSheetByName(sheetname);
  var data = sheet.getRange(3,1,sheet.getLastRow()-1,colstoworkon).getValues(); // starting with row 2 and column 1 as our upper-left most column, 

  //This makes it loops continuously and checks all not done rows
for (var i in data) {
  
    if(data[i][1] && data[i][2]){//if email or copy are undefined just skip

  var setStatus = sheet.getRange(i,4).setValue("done")


            } // end of if
      } // End of Loop
} //End of email function
Solution

  • Modification points:

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

    Modified script:

    function auditReport() {
  var sheetname = "Sheet1";
  var colstoworkon = 10;
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  ss.setActiveSheet(ss.getSheetByName(sheetname));
  var sheet = ss.getSheetByName(sheetname);
  var data = sheet.getRange(3, 1, sheet.getLastRow() - 2, colstoworkon).getDisplayValues();
  for (var i in data) {
    if (data[i][1] && data[i][2]) {
      var setStatus = sheet.getRange(Number(i) + 3, 4).setValue("done");
    }
  }
}

    Or, your script can be also modified as follows. In this modification, done is put using the range list. By this, the process cost can be reduced a little.

    function auditReport() {
  var sheetname = "Sheet1";
  var colstoworkon = 10;
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  ss.setActiveSheet(ss.getSheetByName(sheetname));
  var sheet = ss.getSheetByName(sheetname);
  var data = sheet.getRange(3, 1, sheet.getLastRow() - 2, colstoworkon).getDisplayValues();
  var ranges = data.map(([,b,c], i) => b && c ? `D${i + 3}` : "").filter(String);
  if (ranges.length == 0) return;
  sheet.getRangeList(ranges).setValue("done");
}

    References: