Google Apps script function to delete rows based on value in cell.

find-cell-value-and-delete-row.js

function deleteRows() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();

  var rowsDeleted = 0;
  for (var i = 0; i <= numRows - 1; i++) {
    var row = values[i];
    if (row[0] == 'delete' || row[0] == '') { // This searches all cells in columns A (change to row[1] for columns B and so on) and deletes row if cell is empty or has value 'delete'.
      sheet.deleteRow((parseInt(i)+1) - rowsDeleted);
      rowsDeleted++;
    }
  }
};

Just saw your file.
leftmost top cell is not Cell(1,1) so we need to reference backwards from numRows and numCols.

@lewws66
Oh alright, so what do you recommend? I have been researching for some options for a while and couldn't find any unfortunately.

Your shared file's script need some fine tuning. I get it now..
I made also some errors, forgetting I need to write to the cells instead of to the array "values" which is in memory only.

I note you have formulae in column R.
But once I find Ex in columns J to P, and need to change it to Clear, I will overwrite your formulae.
Did you mean you will change column R's formulae so that it will be set correctly to "Clear" or "Unclear" or do you want the script to do it?

Also will your spreadsheet always start in column C and at line 30? or will it be at A1? or do you expect automatic detection, and will you then always have the Ex and numbers on the column 8 to 14 from the leftmost column?

Did you mean you will change column R's formulae so that it will be set correctly to "Clear" or "Unclear" or do you want the script to do it?
I don't mind, whatever is easier for you.

Also will your spreadsheet always start in column C and at line 30? or will it be at A1? or do you expect automatic detection, and will you then always have the Ex and numbers on the column 8 to 14 from the leftmost column?

I'm intending on implementing the result script and formulas onto a whole another spreadsheet which will always start at Colum F but not at line 30. I'm guessing automatic detection would be better if possible. And yes, EX and numbers will always be from 8 to 14.

Thank you so much once again.

Have sent you a copy of your spreadsheet file with revised script that works for the tables of same size but starting at different positions.
Link here : https://docs.google.com/spreadsheets/d/1AARYUNgdRBE_67bT0bsGbOs1ULoA4I2BXGG8OrLynBo/edit?usp=sharing

I think the main hassle is trying to be familiar with referencing and writing to the spreadsheet cells using getRange().setValue as well as
initially copying all the spreadsheet data into the array "values" so that we can manipulate it (looping through can be using for loops or actually using forEach) in memory much faster, make the necessary conditional checks and then actually updating the cells with the correct values.

function deleteRows() {
var sheet = SpreadsheetApp.getActiveSheet();
var rows = sheet.getDataRange();
var numRows = rows.getNumRows();
var values = rows.getValues();
var numCols = rows.getNumColumns();
var rowsDeleted = 0;
var strow = 0;

for (var i = 0; i < numRows ; i++) {
var row = values[i];
for (var j= numCols - 9; j < numCols - 2; j++) {
if(row[(numCols-2)]==""){
strow = i+1; // maximum strow is the start row of non empty cells
}
if (row.includes("EX")) {
row[numCols-1] ='Clear';
// need to write directly to cell, besides writing to array in memory
sheet.getRange(i+1,numCols).setValue('Clear');
break; // jump out of j loop as one occurrence of EX is enough to have 'Clear' in the last columns
} else {
if(!row[(numCols-2)]=="") {
row[numCols-1] ='Unclear';
// need to write directly to cell, besides writing to array in memory
sheet.getRange(i+1,numCols).setValue('Unclear');
}
} // end of if else
} // end of loop j
if (row[numCols - 2]>9){
row[numCols-1] ='Clear';
// need to write directly to cell, besides writing to array in memory
sheet.getRange(i+1,numCols).setValue('Clear');
}
} // end of loop i

// This loop is to clear the rows which have last column's value as "Unclear" or empty
for (var i = strow; i < numRows ; i++) {
var row = values[i];
if (row[numCols-1] == "Unclear" || row[numCols-1] == "") {
sheet.deleteRow((parseInt(i)+1) - rowsDeleted);
rowsDeleted++;
}
}

}

function onOpen(){
SpreadsheetApp.getUi()
.createMenu('rage')
.addItem('Main', 'deleteRows')
.addToUi();
}

Wow, it really does work. Thank you with that, Though, for some reason, It overrides the sum formula. Maybe its cause of numCols-1, I'm unsure. Could you check that out for me? I simply want it to override the "=IF(T36<10, "Unclear", "Clear")" formula if there is an EX in the row. But i guess it overrides the sum formula just next to it. Could you look into it? Thanks. Other than that, works just fine.

As an example here, I tried to implement it on another sheet and it gave me this result. Can this be solved by any chance man?
I was thinking about if we could adapt the "=IF(T36<10, "Unclear", "Clear")" in the script, so that we'd get a singular result instead of two?

May be you show me your script? and the file.
If you used the one I sent you https://docs.google.com/spreadsheets/d/1AARYUNgdRBE_67bT0bsGbOs1ULoA4I2BXGG8OrLynBo/edit?usp=sharing it should work for any number of rows of data, for the same number of columns, placed anywhere in the spreadsheet.

Ah, never mind man, I think It was me who made a mistake. It seems to be working perfectly fine for now. I really do appreciate your help and wish you an amazing day.

Same. Wishing you a good day!

It might be worth updating your script to explain (in a code comment) that this is method does not necessarily have good performance. Ideally you should delete rows in batches. For more information https://developers.google.com/apps-script/guides/support/best-practices or this example https://stackoverflow.com/questions/73651127/speed-up-row-deletion-on-apps-script

i've been trying to make this condition work for the past hour, and i have used the debugger to see what exactly is stored in each variable (as you can see on the right) and they do match, but the condition is FALSE, and i do not get why.
can someone help? (i have never used google script or javascript before)

This is exactly what I was looking for. Had to finagle it for my own purposes, but I got it to work. I deleted a selection of columns within the target row based on whether the value of a certain cell was less than or equal to a certain number.

const deleteRows = async () => {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const rows = sheet.getDataRange();
  const numRows = rows.getNumRows();
  const values = rows.getValues();

  let rowsCleared = 0; // This should be declared with 'let' to avoid a global variable

  for (let i = 1; i < numRows; i++) {
    // Start from index 1 to skip the header row
    let row = values[i];

    // Check if the value in column E (index 4) is <= 5
    if (row[4] <= 5) {
      // Wrap delete cells in a promise to make it async
      await new Promise((resolve) => {
        // Delete cells from columns A-J in the specific row
        sheet
          .getRange(i + 1, 1, 1, 10)
          .deleteCells(SpreadsheetApp.Dimension.ROWS); // i + 1 to convert index to row number
        resolve();
      });
    }
  }
};