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

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

CodePudding user response：

Modification points:

In your script, from getRange(3,1,sheet.getLastRow()-1,colstoworkon), in this case, it is required to be getRange(3,1,sheet.getLastRow()-2,colstoworkon).
In the case of for (var i in data) {, i is the string type.
When you want to use sheet.getRange(i,4).setValue("done"), it is required to be sheet.getRange(Number(i) 3, 4).setValue("done").
- I thought that this might be the reason of your issue of but the statuses get added to the wrong rows..
In the case of if (data[i][1] && data[i][2]) {, if the value is 0, data[i][1] && data[i][2] is false.

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");
}

Modification points:

Modified script:

References: