Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Google Apps script function to delete rows based on value in 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] == '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++;
}
}
};
@CM-Astrivis
Copy link

CM-Astrivis commented Mar 7, 2020

Cool script, thanks for sharing.

How could I adapt it to delete a row if a value is not found in a variable that outputs an array?

I tried running it like below, but it deletes ALL the rows

if (row[0] != My Variable || row[0] == ''){}

Thanks in advance.

@hristovvd1
Copy link

hristovvd1 commented Sep 23, 2020

Hello,

Thank you for that script but I have a question:

How to make it work for all tabs in a spreadsheet?
It works only for the current one selected.

Thank you in advance!

@davidfurlong
Copy link

davidfurlong commented Oct 5, 2020

Much simpler to work backwards from the end of the array and not use rowsDeleted btw

@tannerjones2
Copy link

tannerjones2 commented Dec 11, 2020

This have been a great help! I am trying to delete a cell based on cell color. How could modify this script to delete a row if the cell is red.

@tramos02
Copy link

tramos02 commented Dec 13, 2020

With Filter method you don't need the for nor rowsDeleted. The whole process is done inside the array and the new array (with the elements deleted when condition is false) is written with setValues().

@vicdecode
Copy link

vicdecode commented Feb 25, 2021

Excellent solution, thanks for sharing!

@makasulap
Copy link

makasulap commented Mar 14, 2021

Working Perfectly..... Thank you for sharing the code.

@CristoferRybner
Copy link

CristoferRybner commented Nov 16, 2021

Hi! Works almost perfect for me, I changed it a bit to delete rows of a table based on the value of a cell (in which I have a SORTN(FILTER(... function), but it deletes two consecutive values, in stead of just one, any ideas?

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[1] == SpreadsheetApp.getActiveSheet().getRange(2, 3).getValue()) { // This searches all cells in columns B and deletes row if cell has the value shown in C2.
      sheet.deleteRow((parseInt(i)+1) - rowsDeleted);
      rowsDeleted++;
    }
  }
};
C2 contains:
=IFERROR(
    SORTN(
        FILTER($B$8:$B$2426,(
            MATCH(B$8:$B$2426, 'Danger zone list'!$C$2:$C))*(($Q$8:$Q$2426/$R$8:$R$2426)>0.05)
                         )
                 ,4,0)
             ,"")

@kevin0101
Copy link

kevin0101 commented Dec 16, 2021

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment