Deleting rows in google sheets using Google Apps Script

Question

I encountered the weirdest error while trying to delete rows that match a specific value using Google Apps Script.

Here is my Code:

function myFunction() {
  var doc = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = doc.getSheetByName("file.csv");
  
  var values = sheet.getRange("N2:N").getValues();
  
  var row_del = new Array();
    
  for(var i=0;i<values.length;i++)
  {
    if(values[i] == 'del'){
      row_del.push(i+2); // This line was added for debugging purposes.
     // sheet.deleteRow(i+2) was the line that was in this condition
     // (i+2) is used because row 1 has headers and the range starts from 0.
    }
  }
  
//  Logger.log(row_del);
//  GmailApp.sendEmail("my_email_address", "subject", row_del)
 
  for (var i = 0; i < row_del.length; i++)
  {
    sheet.deleteRow(row_del[i]);
  }
  
}

The code that I have written picks up the row numbers that should be deleted but not all these rows are deleted in my first try. I should execute my script a number of times for these rows to be deleted.

If my code has an error, it should show up and if the logic is wrong, incorrect rows must be deleted. I encounter neither of these scenarios and I should just execute this function multiple times.

Is there something that I'm missing here?

Answer 1

When a row is deleted from a sheet, the rows below it get renumbered even as the script continues to run. If the script subsequently tries to also delete those rows, the result is unpredictable. For this reason, when deleting rows one should proceed from bottom to top. In your case, like so:

for (var i = row_del.length - 1; i>=0; i--) {
  sheet.deleteRow(row_del[i]); 
}