i want to run delete row function if a certain column meets the conditions

Question

i want to delete multiple rows of data on condition if column B in that row is equal to CID parameter. but after I run it, the deleted data is incorrect. Sometimes the deleted data is located one row above or below the specified row in the for row loop here is my code :

  if (e.parameter.func == "DeleteRec") {
    var rg=ss.getSheetByName("MAIN").getDataRange().getValues();   
    var lr= ss.getSheetByName("MAIN").getLastRow();
    for(var row=1;row<=lr;row++){
      var cid=rg[row].toString().split(',');
      if(cid[1]==e.parameter.CID){
        ss.getSheetByName("MAIN").deleteRow(row-1);
        var data=200;
       
      }
    }
       
    return ContentService.createTextOutput(data).setMimeType(ContentService.MimeType.TEXT);
  }

Answer 1

Explanation:

You need to delete the rows from bottom-up or backwards because after you delete one row you change the structure of the spreadsheet file and therefore rg does not match the new structure of the sheet.

• Keep in mind that array indexes in JavaScript start from 0. When variable row is 1 you want to delete row+1 in the sheet, because row=1 is getting the second element of your array.

• Calling getSheetByName in a for loop will increase the performance cost of your script. Instead, define the sheet variable before the loop so you can use it every time you need it.

• Since you are using getDataRange the length of rg is equal to what getLastRow returns. Therefore lr is not needed.

• Not sure why you iteratively assign a fixed value to a variable var data=200 but I kept that part since it is not part of your question.

Solution:

Replace the relevant part with this code:

const sheet = ss.getSheetByName("MAIN");
const rg = sheet.getDataRange().getValues();   
for (var row = rg.length - 1; row>0; row--) {
    var cid=rg[row].toString().split(',');
    if(cid[1]==e.parameter.CID){
      sheet.deleteRow(row+1);
      var data=200;       
    }
}