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++;
    }
  }
};

Nice little function. I modified the IF statement so that it checks if the target text is included in the cell.

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].includes('bedroom') || 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 if the word on the first IF condition is included in the cell. For example: First IF condition is "bedroom", if cell value is "the bedroom is big" then the row is deleted.
sheet.deleteRow((parseInt(i)+1) - rowsDeleted);
rowsDeleted++;
}
}
};

Thank you !!! This is exactly what I need!!!

is there a way I can delete certain values in one go? like I have 1k+ values in different rows.
For example I want to delete loan_id's (ex. LN042279, LN020573, LN044841 and so on). Thanks

!

Thank you, I had looked at several, and this one worked the best by far, my sheet is around 70,000 rows. Due to the size, it times out and I have to rerun it, but I do that with another script as well. It gets done after enough runs. Not taking my money for primo run times google! lols Thanks again for the script.

Here is a modified version of the original function. This gets a set of values as input and then deletes all rows that contain any of those values in the first column:

function deleteRows() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();
  var ui = SpreadsheetApp.getUi();
  var valuesToDeleteRow = ui.prompt("Please enter the values to delete (separated by commas):").getResponseText().split(","); // get the values to delete

  var rowsDeleted = 0;
  for (var i = 0; i < numRows; i++) {
    var row = values[i];
    for (var j = 0; j < valuesToDeleteRow.length; j++) {
       // This searches all cells in columns A (change to row[1] for columns B and so on) and deletes that row if the cell has one of the given values
      if (row[0] == valuesToDeleteRow[j]) {
        sheet.deleteRow((parseInt(i) + 1) - rowsDeleted);
        rowsDeleted++;
        break;
      }
    }
  }
  SpreadsheetApp.getUi().alert("Deleted " + rowsDeleted + " rows.");
};

@noelaum I think this is what you're looking for, just keep in mind that if you want to delete from column 2 you'll need to change that part in the code.

Thank you, I had looked at several, and this one worked the best by far, my sheet is around 70,000 rows. Due to the size, it times out and I have to rerun it, but I do that with another script as well. It gets done after enough runs. Not taking my money for primo run times google! lols Thanks again for the script.

Was thinking of how to cut down time needed for looping through cells and deleting cells and tried this.
Essentially to put all the cell values not to be deleted into an array "compacted" before clearing the sheet and then
putting back the values kept in the array back onto the sheet using sheet.getRange(1,1,n1,n2) where n1 is the number of rows of cells to be retained and n2 is the number of columns in each row of cells.
For a 70,000 row by 4 column spreadsheet with 1/3 empty rows and 1/3 rows to be deleted, it took 11 seconds to clear.
There was sufficient memory to deal with the operations on the two arrays of 70,000 rows by 4 columns.
Here's the code:

function deleteRows() {
var sheet = SpreadsheetApp.getActiveSheet();
var rows = sheet.getDataRange();
var numRows = rows.getNumRows();
var numCols =rows.getNumColumns() // this was added to original as the number of columns in range may be more than one.
// and this will be needed when transferring data from an array back to a range of cells in
//spreadsheet
var values = rows.getValues();
let compacted = [""]; // create array to store the relevant values in cells that are not to be deleted later.
var k= 0 // this variable is used to index the values in cells not being deleted into the array "compacted"
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 ignore
row if cell is empty or has value 'delete'.
compacted[j]=values[i]; //storing values of relevant data from cells not to be deleted
k++;
}
}
range = sheet.getRange(1,1,numRows,numCols); // This is the spreadsheets original range of values
range.clear(); //this clears the whole spreadsheet quickly
range = sheet.getRange(1,1, k ,numCols); // This prepares the "range" to receive all the values in the array "compacted"
// which contains all the cell values not to be deleted.
range.setValues(compacted);
};

Was thinking of implementing the UI which allow input of more and different values to be deleted, but the above would have to be altered.

For the 70 000 rows of data, and with ui for input of values to be deleted, below is the code :
(I used different logic in this case. No more use of additional array "compacted". Instead, I used splice(i,1) to delete row i from original array "values". The speed comes from manipulating and deleting the array values in memory instead of operations on the cells of the sheets.
After "values" array has been cleaned of rows of unwanted values or empty rows, the sheet is then cleared and populated with the "values".

function DeleteRowsCrit2(){
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1")
var rows = sheet.getDataRange();
var numRows = rows.getNumRows();
var numCols =rows.getNumColumns();
var values = rows.getValues();
// ************************************************************************
//This section is for input of values to be deleted
//let valuesToDeleteRow = ["delete"]; // use this line of code for pre input of to be deleted items.
var ui = SpreadsheetApp.getUi();
var valuesToDeleteRow = ui.prompt("Please enter the values to delete (separated by commas):").getResponseText().split(","); // get the values to delete
valuesToDeleteRow.push(""); // add this to delete empty rows
// ************************************************************************
var n = numRows;
for (var i = 0; i < n; i++) {
for (var j = 0; j < valuesToDeleteRow.length; j++) {
var row = values[i];
if (row[0] == valuesToDeleteRow[j]) {
// This searches all cells in columns A (change to row[1]
// columns B and so on) and deletes row using splice
// if cell is empty or has value 'Class code'.
values.splice(i,1); //remove unwanted values from array
n--; // this is needed as the array values is getting smaller after deduction
i--; // this is needed as deleting current row i will cause next i to move into current i and hence cause loop to miss out the next i before this deletion.
break; // out of the j loop to continue in the i loop
}
}
}
range = sheet.getRange(1,1,numRows,numCols);
range.clear();
k = values.length;
range = sheet.getRange(1,1,k,numCols);
range.setValues(values)
}

Hello, i have a question. Is it possibble to implement function which will listen to deletedRow. And if row deleted just delete till 5th column and leave all data in 6th and next.

let's consider above in the image, let's deleted row=6, in this case F6 data also willl be lost, can i write script in apps script to save do not touch it just delete till column=F, these A6, B6, C6, D6, E6

if you can, can you provide script it self also

thank you

@abdibrokhim ,

Yes, we can delete all rows or rows that contain specific values, up to the column number just before the columns to keep.

We can add this function (but put it before the new deleteRowV2() function in the app script file)
// This function returns only the rightmost column to be deleted (numColslimit)
// after you state which column header of 1st column (numColkeep) on the right to be retained
//
function numColslimit(){
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1")
// To create dialog for user input
var ui = SpreadsheetApp.getUi();
var valueToFind = ui.prompt("Please enter the column header of 1st column to the right you want to prevent delete:").getResponseText();
var data = sheet.getDataRange();
var numCols2= data.getNumColumns();
var values = sheet.getRange(1,1,1,numCols2).getValues(); //get values of the column headers
var numColkeep = 0;
for (var i = 0; i < numCols2; i++) {
if (values[0][i] == valueToFind) {
numColkeep = i+1;
break; // break from i loop
}
}
return numColkeep -1;
}

We will call the function numColslimit() from the function deleteRowV2() (modified from original DeleteRowsCrit2() which is similar but faster than deleteRow()

function deleteRowV2(){
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1")
var rows = sheet.getDataRange();
var numRows = rows.getNumRows();
var numCols = numColslimit(); // this will call the function numColslimit() which will return the position of the right most column
//not to be deleted
// the old var numCols = rows.getNumColumns(); the new numCols will only collect row values from 1st column up to and including the
// numCols column
range = sheet.getRange(1,1,numRows,numCols);
// values now changed to only getting values from a range which include 1st column to the last column numColslimit
//for all rows up to numRows.
var values = range.getValues();
// ************************************************************************
//This section is for input of values to be deleted
//let valuesToDeleteRow = ["delete"]; // use this line of code for pre input of to be deleted items.
var ui = SpreadsheetApp.getUi();
var valuesToDeleteRow = ui.prompt("Please enter the values to delete (separated by commas):").getResponseText().split(","); // get the values to delete
valuesToDeleteRow.push(""); // add this to delete empty rows
// ************************************************************************
var n = numRows;
for (var i = 0; i < n; i++) {
for (var j = 0; j < valuesToDeleteRow.length; j++) {
var row = values[i];
if (row[0] == valuesToDeleteRow[j]) {
// This searches all cells in columns A (change to row[1]
// columns B and so on) and deletes row using splice
// if cell is empty or has value 'Class code'.
values.splice(i,1); //remove unwanted values from array
n--; // this is needed as the array values is getting smaller after deduction
i--; // this is needed as deleting current row i will cause next i to move into current i and hence cause loop to miss out the next i before this deletion.
break; // out of the j loop to continue in the i loop
}
}
}
// range = sheet.getRange(1,1,numRows,numCols); // already defined earlier
range.clear();
k = values.length;
range = sheet.getRange(1,1,k,numCols);
range.setValues(values)
}

o, man thanks lemme try it out)

Hello, I have came upon this one issue where I use the code but it also deletes all of the rows above it. For example, the value that should have the rows deleted is "Unclear". When i run the script, It firstly clears up the cells above and then clears the cells with "Unclear" value. Is there any way to fix it please? Thank you. @lewws66 , much love.

@ragger1,
Could you give me sample data of your file and the script which you copied ?

@lewws66 Thank you so much, I have solved it. Though, I had one more question.
Can I possibly implement the script to search the row and if a specific word was said in it, it would also mark it as clear.
ex:

So in here for example, used this script:

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].includes('Unclear') || row[0] == '') {
sheet.deleteRow((parseInt(i)+1) - rowsDeleted);
rowsDeleted++;
}
}
};

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

In here, I'm looking to give the "Clear" mark right next to the sum of the row if the person has a day of EX in their row. Could please help me with that man? Thank you. Let me know if you need more explanation and then i can send the sheet.

@ragger1,
See if I got it right. "Ex" can appear in column 8 to 14.
So in row 1, the last column shouldn't be "clear" but should be "Unclear"?
And row 5 to 7 last column should be "clear"?

@lewws66
You partially did. Let me send the link anyways.
https://docs.google.com/spreadsheets/d/1Gr9zqUmK34F6ofPCcA_RhBgFM2P7BJTo1vLujZmbxOY/edit#gid=0

In row 1, it already has 10 at the sum so it is good to be clear.
But for Person 5's row, there is 3 EX's, therefore I want it to say "Clear" at the end of it.(Not that it has 3, EX's, 1 EX should be also enough). But it says Unclear since the formula works like it. =IF(Q34<10, "Unclear", "Clear")

Except for that, the code is fine, I want any sum that is below sam to be Unclear and any above it Clear.
Thank you for responding.

(let line 1 be your statement function deleteRows() { , and empty lines not counted, line reference is to original )
Some comment on your script :

To label the last column "Clear" or "Unclear", you need to add the following to your script above
Between line 5 and 6 insert the following :

var numCols = rows.getNumColumns();

After line 8 (which is var row = values[i];) , and before line 9
You'll need a for loop (j loop below) to go through the columns 8 to 3rd last column in value[i]
to check for "Ex" and then to fill last column as "Clear" if there is "Ex".
You are checking the value of cell (i+1, j+1) (remembering array indexes start from 0)
Here's the script :

for (var j= 7; i <= numCols - 3; i++) {
if (row[j].includes('Ex')) {
row[numCols-1] ='Clear';
break; // jump out of j loop as one occurrence of Ex is enough to have 'Clear' in the last columns
} else {
row[numCols-1] ='Unclear';
} // end of if else
} // end of loop j

For Line 9 (which is : if (row[0].includes('Unclear') || row[0] == '') )
You defined row as : var row = values[i];
So if i = 1, then row[0] is actually values[1][0] I think, which from your sheet is the value in cell on row 2 column 1, i.e. has the value "Person 2"
Since the column you are checking on is the last column, so this might work better :

if (row[numCols-1].includes('Unclear') || row[numCols-1] == '')

I did not test the scripts suggested above since I don't have your spreadsheet. Kindly check if it works with your sheet.

Regards,
Lew W.S.

Just saw your reply that if the sum of 2nd last column >= 10 then the last column can also be "Clear".
Rewriting this part

After line 8 (which is var row = values[i];) , and before line 9
You'll need a for loop (j loop below) to go through the columns 8 to 3rd last column in value[i]
to check for "Ex" and then to fill last column as "Clear" if there is "Ex".
You are checking the value of cell (i+1, j+1) (remembering array indexes start from 0)
Here's the script :

for (var j= 7; i <= numCols - 3; i++) {
if (row[j].includes('Ex')) {
row[numCols-1] ='Clear';
break; // jump out of j loop as one occurrence of Ex is enough to have 'Clear' in the last columns
} else {
row[numCols-1] ='Unclear';
} // end of if else
} // end of loop j

// Add this last check, if sum of columns 8 to 14 is 10 or more, then last column is "Clear"
if (parseInt(row[numCols - 2])>9) { // not sure if I need to use parseInt() for the value of row[numCols-1]
row[numCols-1] ='Clear';
}

Hope this works for you.

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)