Google Apps Script: Delete Rows Based on a Column’s Cell Value in Google Sheet

Delete rows based on a cell value in Google Sheets with Google Apps Script

Google Apps Script, SpreadsheetApp, Google Sheet, Iteration

A not too uncommon task for Google Sheets users is to delete rows based on a cell’s values in a certain column. Often you don’t want to change the row position of the values so sorting the values and then deleting them in blocks is out.

All you want to do is iterate through the rows in the Sheet searching your selected column for the text or value that will trigger your code to remove the assigned row. Then delete the row.

Rinse and repeat until all rows have been checked.

This task can be automated with Google Apps Script in a number of ways depending on the size of your data set.

Table of Contents

Small Data Sets

The most logical approach to delete rows in a small data set might be like this:

// Deleting Rows Based on Cell Values

//GLOBALS

var SS = SpreadsheetApp.openById("YOUR SHEET ID");
var SHEET = SS.getSheetByName("YOUR SHEET TAB ID");
var RANGE = SHEET.getDataRange();


var DELETE_VAL = "TEXT TRIGGERING THE DELETION";
var COL_TO_SEARCH = A NUMBER; // The column to search for the DELETE_VAL (Zero is first)

  
function deleteEachRow(){
  
  var rangeVals = RANGE.getValues();
  
  //Reverse the 'for' loop.
  for(var i = rangeVals.length-1; i >= 0; i--){
    if(rangeVals[i][COL_TO_SEARCH] === DELETE_VAL){
      
      SHEET.deleteRow(i+1); 
    };
  };
};

// Deleting Rows Based on Cell Values

//GLOBALS

var SS = SpreadsheetApp.openById("YOUR SHEET ID");

var SHEET = SS.getSheetByName("YOUR SHEET TAB ID");

var RANGE = SHEET.getDataRange();

var DELETE_VAL = "TEXT TRIGGERING THE DELETION";

var COL_TO_SEARCH = A NUMBER; // The column to search for the DELETE_VAL (Zero is first)

function deleteEachRow(){

var rangeVals = RANGE.getValues();

//Reverse the 'for' loop.

for(var i = rangeVals.length-1; i >= 0; i--){

if(rangeVals[i][COL_TO_SEARCH] === DELETE_VAL){

SHEET.deleteRow(i+1);

};

In the Globals section, lines 5-7 get the Spreadsheet, Sheet and Range Data. We use the SpreadsheetApp Class to get this information. For your own use, you would change the SS and SHEET variables to suit your project. The RANGE variable is the full range of data

Next, we have the parameters we will use to decide what rows we will delete. The DELETE_VAL variable is the text value in the row you want to delete. When the code sees this, it will delete the row.

The COL_TO_SEARCH variable tells the code which column to search. In your Google Sheet, you will have a letter across the top to determine the columns. We want to change this to a number. In coding, numbers start at zero. So, if your column to search is, say column “D”, then you would count:

0:A, 1:B, 2:C, 3:D

deleteEachRow()

Our deleteEachRow() function first gets the value in our RANGE. This is presented as a 2D array like:

[["cat",1,100],["dog",34,565],["penguin",10,123]]

And would look like this on your Google Sheet:

Next, on line 19 we will reverse the ‘for‘ loop.

Why reverse the ‘for’ loop?

When you delete a row the value locations below it change. Imagine doing this when you are iterating through the rows and columns from the top to the bottom. After you delete the first row and loop through to the next row, your row-to-delete will be positioned one point below where it is meant to be, deleting the wrong row. One solution to this is to reverse the “for” loop.

To do this, the first argument for our loop variable “i” is to start at the end of the array:

var i = rangeVals.length-1;

Then we want to ensure the variable stops when it reaches the zeroeth value:

i >= 0;

🐐You can support me for free by using this Amazon affiliate link in your next tech purchase :Computers & Stuff! 🐐

Finally, we want to take one away from the length of the array each time we iterate through the loop:

i–

Next, we want to check if our DELETE_VAL exists in our COL_TO_CHECK column of each row, line 20. If it does we will use Google Apps Scrips custom deleteRow() method (line 22).

To use deleteRow() we first grab the SHEET we are working in. We then determine what row we want to delete. For us, this will be our iterator “i” plus one.

Now each row with our selected value will be deleted one at a time.

Great, but let’s see how this works with a Google Sheet with say 10,000 rows.

Large Data Sets

An Example

Imagine we have a data set with 10,000 rows. Take a look at the example below:

We want to delete the rows that contain the word Hematite (column 2, starting from zero). Let’s go ahead and time the whole process to see how long it takes for this code to run.

A bad approach

Let’s use the code above again. Take a look at the updated code. I have highlighted the changes:

// Deleting Rows Based on Cell Values

//GLOBALS

var SS = SpreadsheetApp.openById("1xWCiwR2mDEFTKEVeOv9CtgwYiFfTxWA_iazlNaKZW7M");
var SHEET = SS.getSheetByName("Sheet1");
var RANGE = SHEET.getDataRange();


var DELETE_VAL = "Hematite";
var COL_TO_SEARCH = 2; //Zero is first

function main() {
  
  var startTime = new Date().getTime();
  
  var deleteSelectedRows = deleteEachRow();
  
  var runTime = (new Date().getTime() - startTime)/1000;
  Logger.log("Runtime is: "+runTime + " seconds");
};


  
function deleteEachRow(){
  
  var rangeVals = RANGE.getValues();
  
  //Reverse the 'for' loop.
  for(var i = rangeVals.length-1; i >= 0; i--){
    if(rangeVals[i][COL_TO_SEARCH] === DELETE_VAL){
      
      SHEET.deleteRow(i+1); 
    };
  };
};

// Deleting Rows Based on Cell Values

//GLOBALS

var SS = SpreadsheetApp.openById("1xWCiwR2mDEFTKEVeOv9CtgwYiFfTxWA_iazlNaKZW7M");

var SHEET = SS.getSheetByName("Sheet1");

var RANGE = SHEET.getDataRange();

var DELETE_VAL = "Hematite";

var COL_TO_SEARCH = 2; //Zero is first

function main() {

var startTime = new Date().getTime();

var deleteSelectedRows = deleteEachRow();

var runTime = (new Date().getTime() - startTime)/1000;

Logger.log("Runtime is: "+runTime + " seconds");

};

function deleteEachRow(){

var rangeVals = RANGE.getValues();

//Reverse the 'for' loop.

for(var i = rangeVals.length-1; i >= 0; i--){

if(rangeVals[i][COL_TO_SEARCH] === DELETE_VAL){

SHEET.deleteRow(i+1);

};

On lines 5-6 you can see we added our own spreadsheet id and our Google Sheet tab name.

Our DELETE_VAL is now “Hematite” and our search column in column two, which is column C on the sheet.

We have created a new function called main(). We’ll use this to add our timer code and reference the deleteEachRow() function.

Line 15, sets our start time. We then run our deleteEachRow() function, line 17. Finally, we get the time again and subtract it from our start time. This will give us the length of time the code has been running in milliseconds. We will divide those milliseconds by 1,000 to get a more readable time in seconds.

Now, Google Apps Script Gods, I kinda feel bad about this next bit, because this really put wasted processing time on your Google Cloud Servers. A drop in the ocean I am sure, but I still feel icky. Sorry, but it’s for a good cause. Promise.

For your benefit, dear reader, I ran this code 10 times to get an average (I don’t encourage you to do this unless you need to take a nap). The results in seconds are as follows:

Yep that is a run time average of 205.584 seconds to iterate through 10,000 rows and delete out the Hematite rows (approx 6oo rows). In the very true words of one of my readers:

This is taking F.O.R.E.V.E.R.

The problem here is that we are making calls to the Google Sheet to delete a row every time it finds our target value. This is extremely costly for processing.

Okay, using SHEET.deleteRow() is not a good option for large data sets. What are our alternatives?

If you have already looked at my tutorial on how to iterate through Google Sheets the right way, you might have some clues.

A faster approach

Keeping in mind that we probably don’t want to sort the data before we run the code, I can only think of one option that can be executed in a number of ways.

Instead of making calls to the Google Sheet every time we want to delete a row, we:

Use the array rangeVals array to create a new array of variables that don’t have the target, hematite, rows in it.
Clear out ALL the old data in the Google Sheet so that the sheet is empty.
Add in the new array values to the Sheet.

This will maintain the order of the array. Further, instead of making hundreds or thousands of calls to the Google Sheet, we will only be making four calls.

How about we look at two ways of approaching this with code:

remove then set new values

// Deleting Rows Based on Cell Values

//GLOBALS

var SS = SpreadsheetApp.openById("1xWCiwR2mDEFTKEVeOv9CtgwYiFfTxWA_iazlNaKZW7M");
var SHEET = SS.getSheetByName("Sheet1");
var RANGE = SHEET.getDataRange();


var DELETE_VAL = "Hematite";
var COL_TO_SEARCH = 2; //Zero is first

function main() {
  
  var startTime = new Date().getTime();
  
  var deleteSelectedRows = removeThenSetNewVals();
  
  var runTime = (new Date().getTime() - startTime)/1000;
  Logger.log("Runtime is: "+runTime + " seconds");
};

function removeThenSetNewVals(){
  
  var rangeVals = RANGE.getValues();
  
  var newRangeVals = [];

  for(var i = 0; i < rangeVals.length; i++){
    if(rangeVals[i][COL_TO_SEARCH] != DELETE_VAL){
      
      newRangeVals.push(rangeVals[i]);
    };
  };
  
  RANGE.clearContent();
  
  var newRange = SHEET.getRange(1,1,newRangeVals.length, newRangeVals[0].length);
  newRange.setValues(newRangeVals);
};

// Deleting Rows Based on Cell Values

//GLOBALS

var SS = SpreadsheetApp.openById("1xWCiwR2mDEFTKEVeOv9CtgwYiFfTxWA_iazlNaKZW7M");

var SHEET = SS.getSheetByName("Sheet1");

var RANGE = SHEET.getDataRange();

var DELETE_VAL = "Hematite";

var COL_TO_SEARCH = 2; //Zero is first

function main() {

var startTime = new Date().getTime();

var deleteSelectedRows = removeThenSetNewVals();

var runTime = (new Date().getTime() - startTime)/1000;

Logger.log("Runtime is: "+runTime + " seconds");

};

function removeThenSetNewVals(){

var rangeVals = RANGE.getValues();

var newRangeVals = [];

for(var i = 0; i < rangeVals.length; i++){

if(rangeVals[i][COL_TO_SEARCH] != DELETE_VAL){

newRangeVals.push(rangeVals[i]);

};

RANGE.clearContent();

var newRange = SHEET.getRange(1,1,newRangeVals.length, newRangeVals[0].length);

newRange.setValues(newRangeVals);

};

We are going to use a new function here called from out main() function on line 17.

removeThenSetNewVals() first gets all the values from the spreadsheet (line 25).

Next, we create a variable to store our new data that does not have the Hematite rows in them. We call this newRangeVals(), (Line 27).

This time around we don’t need to iterate through the 2d array from the bottom up. We can iterate top down with our ‘for’ loop (Line 29).

This time around we want to collect any row that does not have Hematite in it in our desired column. We use the not equate to comparison if statement for this (line 30).

If the row doesn’t contain Hematite, then we push that row into our newRangeVals() array (line 31).

Next we want to clear all the content from our selected Google Sheet, but keep the formatting (line 36). We do this with the handy clearContent() method. This method is called from the range class. It takes a range of columns and rows, in our case, the entire data range, and clears just the data while maintaining the formatting. Neat, Right?

Now that we have cleared out the data we can add in our new data set minus the row with our dreaded Hematite value. First, we need to get the range equal to the width and height of our new data. We use the SHEET.getRange() method for this. This method takes, the start row, start column, row length and column length.

Finally, we take this new range and setValues of our new data set into our sheet.

Done! And considerably faster too.

Let’s look at an alternative approach.

Filter out the value and set the new value in the sheet

In this approach we create a new array that filters out the Hematite rows by using JavaScript’s “filter” method. Take a look at our new function. Not much has changed.

// Deleting Rows Based on Cell Values

//GLOBALS

var SS = SpreadsheetApp.openById("1xWCiwR2mDEFTKEVeOv9CtgwYiFfTxWA_iazlNaKZW7M");
var SHEET = SS.getSheetByName("Sheet1");
var RANGE = SHEET.getDataRange();


var DELETE_VAL = "Hematite";
var COL_TO_SEARCH = 2; //Zero is first

function main() {
  
  var startTime = new Date().getTime();
  
  var deleteSelectedRows = removeFilterSetVals();
  
  var runTime = (new Date().getTime() - startTime)/1000;
  Logger.log("Runtime is: "+runTime + " seconds");
};

function removeFilterSetVals(){
  var rangeVals = RANGE.getValues();
  
  var filteredRange = rangeVals.filter(function(val){
    
    return val[COL_TO_SEARCH] != DELETE_VAL ;
  });
  
  RANGE.clearContent();
  
  var newRange = SHEET.getRange(1,1,filteredRange.length, filteredRange[0].length);
  newRange.setValues(filteredRange);
};

// Deleting Rows Based on Cell Values

//GLOBALS

var SS = SpreadsheetApp.openById("1xWCiwR2mDEFTKEVeOv9CtgwYiFfTxWA_iazlNaKZW7M");

var SHEET = SS.getSheetByName("Sheet1");

var RANGE = SHEET.getDataRange();

var DELETE_VAL = "Hematite";

var COL_TO_SEARCH = 2; //Zero is first

function main() {

var startTime = new Date().getTime();

var deleteSelectedRows = removeFilterSetVals();

var runTime = (new Date().getTime() - startTime)/1000;

Logger.log("Runtime is: "+runTime + " seconds");

};

function removeFilterSetVals(){

var rangeVals = RANGE.getValues();

var filteredRange = rangeVals.filter(function(val){

return val[COL_TO_SEARCH] != DELETE_VAL ;

});

RANGE.clearContent();

var newRange = SHEET.getRange(1,1,filteredRange.length, filteredRange[0].length);

newRange.setValues(filteredRange);

};

As you can see on lines 26-29, we have removed our ‘for’ loop and added our ‘filter’. The filter takes a function with our parameter ‘val’. The ‘val’ parameter will represent each row of data.

In each row, we want to only look at the column that contains Hematite. Line 28, states that the function should only return the values that don’t contain the target value.

This is all stored in our filteredRange variable as a new 2d array that we can set back in our Google Sheet after we have cleared the content.

Conclusion

So what is the difference in performance. A heap! Check out the test:

Create and Publish Google Workspace Add-ons with Apps Script Course 300px

Eleven to twelve second run time for setting values versus over two hundred seconds to individually delete each row. I’ll take it!

As you can see, there is little difference in time between using the ‘for’ loop approach over the ‘filter’ approach. The test sample wasn’t large enough to determine any significant difference. I it really up to you what you prefer to use.

One criticism of these faster approaches might be the risk of losing all your data if the code fails between the clearContent phase and the setValues phase. However, considering Google Sheets automatic version history, and the extreme unlikelihood that this would occur, I don’t consider it to be much of an issue.

So what do you think? Will you change how you delete rows in big data sets, now? Do you have a better approach? I would love to hear your thoughts in the comments below.

Need help with Google Workspace development?

My team of experts can help you with all of your needs, from custom app development to integrations and security. We have a proven track record of success in helping businesses of all sizes get the most out of Google Workspace.

Schedule a free consultation today to discuss your needs and get started or learn more about our services here.

41 thoughts on “Google Apps Script: Delete Rows Based on a Column’s Cell Value in Google Sheet”

Josh Jones says:

at 10:28 pm

Well dang. Where was this article when I was waiting F.O.R.E.V.E.R. ???

Seriously though, I gave up on my script ever finishing after it timed out at 1800s three times in a row. I eventually moved the whole thing to Excel (I have 32GB ram and 8-core processor) and ran it with VBA. It still took about 5min to complete, but it was significantly easier than trying to use apps script.

Loading...

Reply
Felix says:

at 2:23 pm

What would happen if the number of rows increases due to a scrip that is bringing data nonstop from an API with more unliked rows?

Thanks for sharing your knowledge 😉

Loading...

Reply
1. Yagi says:
  
  at 3:09 pm
  
  Hi Felix,
  
  You would no be able to use an onEdit() trigger here. You would have to use a time trigger.
  
  Loading...
  
  Reply
Sandi says:

at 12:01 pm

Great article!
I Would like to delete rows where the time and date in certain cell is earlier than yesterday noon 🙂
Do you think this is possible with this function?

regards,
Sandi

Loading...

Reply
1. Yagi says:
  
  at 7:04 pm
  
  Thanks!
  You could do this with a time trigger running daily.
  
  Loading...
  
  Reply
James says:

at 11:06 am

Thank you so much for this post, it’s extremely useful!

One question, what if I wanted to keep the rows that contain Hematite and delete all other rows, how would you edit your script to do this?

I currently have this predicament and for many reasons (mainly my incompetance) I can’t get this to work!

Thank you in advance for your help,
James

Loading...

Reply
1. Yagi says:
  
  at 9:11 am
  
  Hi James,
  
  Awesome! I’m glad you found the code useful.
  
  The tutorial code reads on line 28:
  return val[COL_TO_SEARCH] != DELETE_VAL ;
  
  It says, that if the value in the row does not equal (!=) the delete value Hematite, then keep the data. So what operator would you use to ensure that the now ironically named DELETE_VAL remains an all other values stay?
  
  Cheers,
  Yagi
  
  Loading...
  
  Reply
  1. Leonidas says:
    
    at 7:25 pm
    
    Hi Yagi, for some reason I have the same issue that James mentioned. I have tried both ‘==’ and ‘===’ for this case and none of them works, while the != works properly. I would really appreciate it if you could look into this. Thank you in advance!
    
    Loading...
    
    Reply
Michael says:

at 6:55 am

This is exactly what I need, however I have run into a problem. When the script runs it replaces all formulas in the range with values. I would like to remove rows that are older than 45 days, so I need to retain the formula that designates ‘delete’ if value(x)<today(0)-45.

Loading...

Reply
1. Yagi says:
  
  at 3:22 pm
  
  Hi Michael,
  
  Yes, the code in this tutorial will not accommodate the pasting of formulas. It’s a little bit more tricky to retain formulas and complete deleting the rows quickly, but it is not too bad. Have a look at the getFormula method.
  
  However, if you only have a relatively small data set then the “Bad Approach” might be the best option here.
  
  Loading...
  
  Reply
  1. Kurt Svendsen says:
    
    at 8:42 pm
    
    A big shout out to Yagi for the many useful posts…this makes my top ten list! I have a requirement similar to that expressed by Michael. One thought I have is to use this method in conjunction with the ArrayFormula function. I have been finding that my “go to” sheet design increasingly involves the reservation of the first row or two for a combination of hard-coded column headings, Query formulas, and/or ArrayFormulas. It seems to me that if this approach is taken, then this clearContent() method will still work as long as you specify the range to EXCLUDE the reserved first row or two which contain the ArrayFormulas. I’m new to all of this, so this approach might not work or might have some unintended pitfalls. What do you think?
    
    On a related note regarding processing time. I’ve found that if I’m adding a lot (like as many as 50,000) of new rows of data via setValues, that the automatic calculation of these ArrayFormulas (in this sheet or related sheets that might be using the Query function) really slows things down. I have been increasingly resorting to the inclusion of two functions in my script: a “formulas to values” function at the beginning of the script, and then a “values to formulas” function. These functions simply make use of the setValue and setFormula methods (or whatever they are called). There’s probably an easier way…any suggestions?
    
    An example is shown below (I hope I can just paste it below).
    
    function LineItemsValueToFormula() {
    // sets variables for source data
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var shForecastItemsFlipOrder = ss.getSheetByName(“ForecastItemsFlipOrder”);
    var cellForecastItemsFlipOrder = shForecastItemsFlipOrder.getRange(“A1″);
    //* cellForecastItemsFlipOrder.setValue(‘temp freeze’);
    cellForecastItemsFlipOrder.setFormula(‘=query(ForecastItems!A:D,”select C, A”,1)’);
    var shLineItemsWithForecasts = ss.getSheetByName(“LineItemsWithForecasts”);
    var cellLineItemsWithForecasts = shLineItemsWithForecasts.getRange(“A1″);
    //* cellLineItemsWithForecasts.setValue(‘temp freeze’);
    cellLineItemsWithForecasts.setFormula(‘=query(budgetItems!A:J,”select B where H 0”,1)’);
    var shForecastItemsWithForecasts = ss.getSheetByName(“ForecastItemsWithForecasts”);
    var cellForecastItemsWithForecasts = shForecastItemsWithForecasts.getRange(“A1″);
    //* cellForecastItemsWithForecasts.setValue(‘temp freeze’);
    cellForecastItemsWithForecasts.setFormula(‘=query(LineItemsWithForecasts!A:B,”select B”,1)’);
    }
    
    Loading...
    
    Reply
    1. Yagi says:
      
      at 6:00 pm
      
      Hi Kurt,
      
      Thanks for the kind words.
      
      You could possibly ONLY select the range of cols that don’t have formulas and contain ( or not) the value you are going to delete the cell by and run the code.
      
      This might give you some direction with that: https://yagisanatode.com/2018/11/17/google-apps-script-copy-selected-columns-in-one-sheet-and-add-them-to-the-bottom-of-different-selected-columns-in-another/
      
      Loading...
      
      Reply
CRAIG SCHAFFEL says:

at 5:30 pm

Thank you for this. I’ve found it very useful. Quick question. How would we manipulate the script to be able to look for multiple values and delete each row. ie. hematite and quartz
Thanks.

Loading...

Reply
1. Yagi says:
  
  at 3:05 pm
  
  Hi Craig,
  
  If it is just 2 or 3 extra values then you could incorporate it into the IF statement on line 30. By using the OR || operator.
  
  Alternatively, you could use an array of values:
  DELETE_VALS = ["Hematite", "another item", "etc"];
  
  Then on line 30 you could add your loop of DELETE_VALS
  
  for(var val = 0; val = < DELETE_VALS.length; val++){ if(rangeVals[i][COL_TO_SEARCH] != DELETE_VAL[val])( newRangeVals.push(rangeVAls[i]) )
  };
  
  cheers,
  
  Loading...
  
  Reply
MB says:

at 11:07 pm

Thanks for your detailed tutorial, I’ve tried to use your script, but it doesn’t seem to be working when I click on execute… Is there anything special you need to do to make it work ?

Thanks !

Loading...

Reply
1. Yagi says:
  
  at 5:45 pm
  
  Hi MB. Have you updated the Globals (Uppercase variables) for your own sheet?
  
  Loading...
  
  Reply
MB says:

at 9:07 pm

Yeah I had, but I fixed the problem, I just had copy/pasted the code wrong (beginner problems…)
Everything is working perfectly now, thanks again for sharing the script !

Loading...

Reply
Dexter Siglos says:

at 4:31 pm

Hello Yagi,

I’m a beginner and I’m trying to figure out how to delete 3 specific values.

// Deleting Rows Based on Cell Values

//GLOBALS

var SS = SpreadsheetApp.openById(“1HmS3GieR3Ds7If8ufN2bCWg2MFrJudDEOzqKC0fSko0”);
var SHEET = SS.getSheetByName(“January Raw”);
var RANGE = SHEET.getDataRange();

var DELETE_VASL = [“ABC”, “ABCD”, “ABE”]
var COL_TO_SEARCH = 2; //Zero is first

function main() {

var startTime = new Date().getTime();

var deleteSelectedRows = removeFilterSetVals();

var runTime = (new Date().getTime() – startTime)/1000;
Logger.log(“Runtime is: “+runTime + ” seconds”);
};

function removeFilterSetVals(){
var rangeVals = RANGE.getValues();

var filteredRange = rangeVals.filter(function(val){

return val[COL_TO_SEARCH] != DELETE_VAL ;
});
for(var val = 0; val = < DELETE_VALS.length; val++){
if(rangeVals[i][COL_TO_SEARCH] != DELETE_VAL[val])(
newRangeVals.push(rangeVAls[i])
)

};
RANGE.clearContent();

var newRange = SHEET.getRange(1,1,filteredRange.length, filteredRange[0].length);
newRange.setValues(filteredRange);
};

You said on craigs reply that

Alternatively, you could use an array of values:
DELETE_VALS = [“Hematite”, “another item”, “etc”];

Then on line 30 you could add your loop of DELETE_VALS

for(var val = 0; val = < DELETE_VALS.length; val++){
if(rangeVals[i][COL_TO_SEARCH] != DELETE_VAL[val])(
newRangeVals.push(rangeVAls[i])
)

};

cheers,

I have followed it and it didn’t work properly.

Loading...

Reply
1. Yagi says:
  
  at 9:10 am
  
  Hi Dexter,
  
  Take a look a the Remove and Set New Values code.
  
  for(var i = 0; i < rangeVals.length; i++){ if(rangeVals[i][COL_TO_SEARCH] != DELETE_VAL){
  newRangeVals.push(rangeVals[i]); }; 1 2 3 newRangeVals.push(rangeVals[i]); };
  };
  Now that your DELETE_VAL is an array, we can do 2 things:
  1. If there are only a few delete values then you could simply add it to the if statement inside the for loop:
  for(var i = 0; i < rangeVals.length; i++){ var cell = rangeVals[i][COL_TO_SEARCH]; if(cell != DELETE_VAL[0] | cell != DELETE_VAL[1] | cell != DELETE_VAL[1] ){
  newRangeVals.push(rangeVals[i]); }; };
  2. If you have a whole bunch of delete values you are proberly better off with a loop:
  
  for(var i = 0; i < rangeVals.length; i++){ for (var dv; dv < DELETE_VAL; dv++){ if(rangeVals[i][COL_TO_SEARCH] != DELETE_VAL[dv]){
  newRangeVals.push(rangeVals[i]); }; }; }
  
  Have a play around with this and let me know if you have any issues.
  
  Cheers,
  
  Yagi
  
  Loading...
  
  Reply
  1. James says:
    
    at 3:18 pm
    
    Hi Yagi,
    
    Firstly your site has been a great help as I’m starting out with Google Script!
    
    I am having a similar issue as above, I have set the DELETE_VAL up as an array. When I run it though, it pastes the data without removing the values to be deleted, I have tried a number of things but I’m clearly missing something.
    
    would you have any suggestions? Thanks
    
    var DELETE_VAL = [“Day 1”, “Day 2”, “Day 3”];
    var COL_TO_SEARCH = 6; //Zero is first
    
    function main() {
    
    var startTime = new Date().getTime();
    
    var deleteSelectedRows = removeFilterSetVals();
    
    var runTime = (new Date().getTime() – startTime)/1000;
    Logger.log(“Runtime is: “+runTime + ” seconds”);
    };
    
    function removeFilterSetVals(){
    var rangeVals = RANGE.getValues();
    
    var filteredRange = rangeVals.filter(function(val){
    
    return val[COL_TO_SEARCH] != DELETE_VAL ;
    });
    
    for(var i = 0; i < rangeVals.length; i++){
    for (var dv; dv < DELETE_VAL; dv++){
    if(rangeVals[i][COL_TO_SEARCH] != DELETE_VAL[dv]){
    
    newRangeVals.push(rangeVals[i])
    
    };
    };
    }
    
    RANGE.clearContent();
    
    var newRange = SHEET.getRange(1,1,filteredRange.length, filteredRange[0].length);
    newRange.setValues(filteredRange);
    };
    
    Loading...
    
    Reply
Cristian says:

at 2:59 am

Hi Yagi.

I have a script that pulls data from gmail, filtered by label/s of choice,stores to arrays, (one for each column), and than writes to a sheet.
Whenever new (email) messages are added to a label, the script will keep the old data and add just the new rows.

I am trying to add a new function, to delete rows whenever the script checks my inbox and finds that 1 or more (email) messages have been removed from the label.

So what I need is a version of your script that deletes based not a simple string value, but based on comapring 2 arrays: first one being data pulled from gmail, and the second being data already existing in the spreadsheet.

Here are the 2 functions:

//////////////////////////////////////////////////////////////////////////////////////////////////////////////
//DECLARE STORAGE ARRAYS AND EDIT var SEARCH_QUERY = “YOUR DESIRED LABEL”;
//////////////////////////////////////////////////////////////////////////////////////////////////////////////
var SEARCH_QUERY = “test”;
var subjects = [];
var bodys = [];
var dates = [];
var froms = [];
var names = [];
var emailPath = [];
var tos = [];
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];

//////////////////////////////////////////////////////////////////////////////////////////////////////////////
//GETS INFO FROM GMAIL AND STORES IN ARRAYS
//////////////////////////////////////////////////////////////////////////////////////////////////////////////
function getInfo_(q) {
// var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
var messageIdRange = sheet.getRange(2,7,sheet.getLastRow());
var label = GmailApp.getUserLabelByName(q);
var threads = label.getThreads();
for (var i = 0; i < threads.length; i++) {
var msgs = threads[i].getMessages();
for (var j in msgs) {
var message = msgs[j];
var finder = messageIdRange.createTextFinder(message.getId());
Logger.log (finder)
if (finder.findNext() != null) continue;
this.froms.push([message.getFrom().replace(/^.+</, ”).replace(“>”, ”) ]);
this.tos.push([message.getTo().replace(/^.+</, ”).replace(“>”, ”) ]); //Cristian added
this.subjects.push([message.getSubject().replace(/<.?>/g, ‘\n’).replace(/^\s\n/gm, ”).replace(/^\s/gm, ”).replace(/\s\n/gm, ‘\n’)]);
this.bodys.push([message.getPlainBody().replace(/<.?>/g, ”).replace(/^\s\n/gm, ”).replace(/^\s/gm, ”).replace(/\s\n/gm, ‘\n’)]);
this.names.push([message.getFrom().replace(/<.?>/g, ”).replace(/^\s\n/gm, ”).replace(/^\s/gm, ”).replace(/\s\n/gm, ‘\n’)]);
this.dates.push([message.getDate()]);
this.emailPath.push([message.getId()]);
}
}
}

//////////////////////////////////////////////////////////////////////////////////////////////////////////////
//APPENDS ARRAY INFO TO THE SPREADSHEET
//////////////////////////////////////////////////////////////////////////////////////////////////////////////
function appendData_(sheet, array2dFroms, array2dtos, array2dSubjects, array2dBodys, array2dNames, array2dDates, array2dURL) {
sheet.getRange(sheet.getLastRow() + 1, 1, array2dFroms.length, array2dFroms[0].length).setValues(array2dFroms);
sheet.getRange(sheet.getLastRow() + 1 – array2dFroms.length, 2, array2dtos.length, array2dtos[0].length).setValues(array2dtos); //Cristian added
sheet.getRange(sheet.getLastRow() + 1 – array2dFroms.length, 3, array2dSubjects.length, array2dSubjects[0].length).setValues(array2dSubjects);
sheet.getRange(sheet.getLastRow() + 1 – array2dFroms.length, 4, array2dBodys.length, array2dBodys[0].length).setValues(array2dBodys);
sheet.getRange(sheet.getLastRow() + 1 – array2dFroms.length, 5, array2dNames.length, array2dNames[0].length).setValues(array2dNames);
sheet.getRange(sheet.getLastRow() + 1 – array2dFroms.length, 6, array2dDates.length, array2dDates[0].length).setValues(array2dDates);
sheet.getRange(sheet.getLastRow() + 1 – array2dFroms.length, 7, array2dURL.length, array2dURL[0].length).setValues(array2dURL);
}

Loading...

Reply
Pete says:

at 4:04 pm

Hi Yagi
Thank you so much for your detailed input.
I am very new to this environment and i have tried to modify your script (which i have working) to work on the data range at B47:I1200 where col3 is blank. I have my field names on row 47 and the data starts in row 48. Your script correctly takes out all of the data above which i would like to retain. Any suggestions?

Thank you in advance.

Loading...

Reply
Ariel says:

at 12:08 am

what would the script be if I want to do this but based on a ticked checkbox ?

Loading...

Reply
1. Yagi says:
  
  at 6:10 pm
  
  Hi Ariel,
  I’ll give you a hint. Tickboxes are glorified true/false values. Give it a try and if you get stuck, feel free to post your code.
  
  Loading...
  
  Reply
chhaya says:

at 3:16 pm

I want to delete selected row from spreadsheet using apps script..plz suggest me function for that

Loading...

Reply
SKS says:

at 3:46 am

I think another way could be to first create a helper column. It will a function something like
=arrayformula(if( C1:C=”Hematite”, row(C1:C),””))

Now through script the data can be sorted on this column. Then start row and end row for deletion can be found by reading this column after sorting. Then from their rows can be deleted in one go.

I use it and find it OK.

Only thing is that it will need a helper column. I personally try to do as much as possible with formula and go for script only when inevitable. I find this approach workable in most of the circumstances.

Loading...

Reply
Calvin says:

at 8:43 am

Is there a way to lock the sheet during this process, so we don’t clobber any data added after we pull the range values and before we write the new values back?

Loading...

Reply
1. Yagi says:
  
  at 9:16 am
  
  Hi Calvin,
  
  Absolutely, you could use the Google Apps Script Lock Service API. Alternatively, you could use the Protection Class of the SpreadsheetApp API, though your users may have some permissions issues here.
  
  Cheers,
  
  Yagi
  
  Loading...
  
  Reply
Kaladhar says:

at 10:33 pm

Hi Yagi,

Kindly help me out with below code. I am running the code but it is still removing the formulas from column E:H.

I am beginner for coding. I don’t have clarity where to paste getformulas() code in this main code. Column E to H is completely formula driven values. i.e., I had done vlookup from other sheets. Kindly help me with modified code. I will use this for large data sets. Added sample data set at the bottom of this.

//GLOBALS

var SS = SpreadsheetApp.openById(“1nSnKcozc5ONbYN5Zh3icjanncquHWv7z1Qn2b4XyrU4”);
var SHEET = SS.getSheetByName(“Inbound Bags”);
var RANGE = SHEET.getDataRange();

var DELETE_VAL = “INTRANSIT”;
var COL_TO_SEARCH = 4; //Zero is first

function main() {

var startTime = new Date().getTime();

var deleteSelectedRows = removeFilterSetVals();

var runTime = (new Date().getTime() – startTime)/1000;
Logger.log(“Runtime is: “+runTime + ” seconds”);
};

function removeFilterSetVals(){
var rangeVals = RANGE.getValues();

var filteredRange = rangeVals.filter(function(val){

return val[COL_TO_SEARCH] != DELETE_VAL ;

1
2

return val[COL_TO_SEARCH] != DELETE_VAL ;

});

RANGE.clearContent();

var newRange = SHEET.getRange(1,1,filteredRange.length, filteredRange[0].length);
newRange.setValues(filteredRange);
};

Sample Data Set:

Source Trip ID Bag ID Bag Type Current Status Input Date Ageing (Hrs) Ageing (Days)
A A A bag INTRANSIT 3/15/2021 17:55 0 0
S S S bag INTRANSIT 3/15/2021 17:55 0 0
D D D bag CLOSED 3/15/2021 17:55 0 0
F F F bag RECEIVED 3/15/2021 17:55 0 0
G G G bag RECEIVED 3/15/2021 17:55 0 0

Loading...

Reply
1. Yagi says:
  
  at 10:36 pm
  
  Hi Kaladhar,
  
  One approach would be to get the height of the filteredRange (filteredRange.length) and then use something like this in the removeFilterSetVals() function:
  
  let formula = sheet.getRange(your-row-start, your-col-number).getFormula()
  
  //Then once you have your filteredRange value create an array.
  
  let formulaCol = Array.from(new Array(filteredRange.length), x => x = [formula])
  
  Sheet.getRange(your-row-start, your-col-number,filteredRange.length) .setFormulas(formulaCol)
  
  Cheers,
  
  Loading...
  
  Reply
Wesley says:

at 10:01 am

Yagi, this is the almost-perfect solution to my need, which is to drop rows based on the same value present in two distinct columns.

Is there a modification to the script you would suggest to accomplish this?

Thank you – Wesley

Loading...

Reply
Tiff says:

at 1:11 pm

Thanks for the great explanation! Is it relatively easy to modify the search parameters to look through two columns for only one criteria? My M and N columns are checkboxes to see if the shipment in the row is marked either Complete (M) or Canceled (N), and if they are True, ought to be deleted when the cleanup function is run (by drop down “button”), leaving behind the incomplete shipments.
(I skimmed through all the comments again and am now seeing that the last person to post was looking for the exact same thing, with no answer, so maybe it’s not an easy thing? A probably not pretty method (I’m a newbie, so nothing is pretty haha) would be to run it twice and then combine the two filtered ranges.)
Thanks!

Loading...

Reply
1. Tiff says:
  
  at 12:14 am
  
  In case it’s of any use to anyone else, this is my probably-not-super-efficient method of dealing with having one delete criteria (true), but in two different rows, Row M (Completed), and Row N (Canceled). We just need to delete a couple hundred rows once a week, and since my sheet is an upgrade over a yellow legal pad, it’ll work well enough 🙂
  
  function cleanUpDataEntryRows(){
  clearCompletedRows();
  clearCanceledRows();
  }
  
  function clearCompletedRows() {
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var ws=ss.getSheetByName(“Data Entry”);
  
  var startTime = new Date().getTime();
  
  var deleteSelectedRows = removeCompletedFilterSetVals();
  
  var runTime = (new Date().getTime() – startTime)/1000;
  Logger.log(“Runtime is: “+runTime + ” seconds”);
  };
  
  function removeCompletedFilterSetVals(){
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var ws=ss.getSheetByName(“Data Entry”);
  var completedRange= ws.getDataRange();
  var delete_val = true;
  var colToSearch = 12; //Zero is first
  var rangeVals = completedRange.getValues();
  
  var filteredRange = rangeVals.filter(function(val){
  
  return val[colToSearch] != delete_val;
  
  1
  2
  
  return val[colToSearch] != delete_val;
  
  });
  
  completedRange.clearContent();
  
  var newRange = ws.getRange(1,1,filteredRange.length, filteredRange[0].length);
  newRange.setValues(filteredRange);
  };
  
  function clearCanceledRows() {
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var ws=ss.getSheetByName(“Data Entry”);
  
  var startTime = new Date().getTime();
  
  var deleteSelectedRows = removeCanceledFilterSetVals();
  
  var runTime = (new Date().getTime() – startTime)/1000;
  Logger.log(“Runtime is: “+runTime + ” seconds”);
  };
  
  function removeCanceledFilterSetVals(){
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var ws=ss.getSheetByName(“Data Entry”);
  var canceledRange= ws.getDataRange();
  var delete_val = true;
  var colToSearch = 13; //Zero is first
  var rangeVals = canceledRange.getValues();
  
  var filteredRange = rangeVals.filter(function(val){
  
  return val[colToSearch] != delete_val;
  
  1
  2
  
  return val[colToSearch] != delete_val;
  
  });
  
  canceledRange.clearContent();
  
  var newRange = ws.getRange(1,1,filteredRange.length, filteredRange[0].length);
  newRange.setValues(filteredRange);
  };
  
  Loading...
  
  Reply
LeAnne says:

at 6:15 am

Works perfectly. I would like to tweak this script to exclude the first three Nth rows but don’t know where to start. Appreciate anyone’s help.

Loading...

Reply
1. Yagi says:
  
  at 8:25 am
  
  Hi LeAnne,
  You can change the SHEET variable and use the getRange() method instead of getDataRange() and use either A1 notation or row start, col start, row depth, col width notation.
  
  If you want ot dynamically get the last row of your data, you could use the SHEET.getLastRow() method and replace that with your row depth.
  
  Hope this helps.
  
  ~Yagi
  
  Loading...
  
  Reply
Wesley says:

at 8:58 am

Hi All —

I have a similar need to the solution that Tiff posted, with a slight difference that I don’t know how to address.

I have two columns that need to be assessed, with all rows removed when cells in BOTH columns contain the same value.

In my spreadsheet, if the cell value for column N AND column Y is the integer zero (0), that row needs to be removed.

If one column’s cell value is zero while the other is not, that row must be retained.

I just do not know how to change the original or Tiff’s modified script to accomplish this — any guidance is sincerely appreciated!

Loading...

Reply
1. Yagi says:
  
  at 2:04 pm
  
  Hi Wesley,
  
  In the last code snippet in the tutorial. You could change DELETE_VAL to 0 (Line 10). Then change COL_TO_SEARCH to COLS_TO_SEARCH = {n:13, n:24}. (Line 11).
  Next, on line 28 you could update the returned values to return (val[COL_TO_SEARCH.n] != DELETE_VAL && val[COL_TO_SEARCH.y] != DELETE_VAL)
  Here you are only keeping the rows that don’t equal zero in column N and (&&) column Y.
  
  Have a crack at implementing that and if you get stuck. Feel free to share your code. I might not be able to get to it, but one of the readers might.
  
  Cheers,
  
  Yagi
  
  Loading...
  
  Reply

Yagi, thank you, much appreciated. I tweaked your suggestions to ensure that the filter is OR vs. AND (line 28). I need the script to run through multiple tabs, so added a for loop. The top-most function adds the removal function to the sheet UI. Here’s what I wound up with:

// Add UI in spreadsheet to run script

function onOpen() {
const ui = SpreadsheetApp.getUi();
ui.createMenu(‘Clear OI’)
.addItem(‘Clear OI’, ‘removeZeroOI’)
.addToUi();
}

// Yagi’s modified script

function removeZeroOI() {

var tabs = [
‘Tab1’,
‘Tab2’,
‘Tab3’
];

var SS = SpreadsheetApp.openById(“mySheetID”);
for (var i = 0; i < tabs.length; i++) {
var SHEET=SS.getSheetByName(tabs[i]);
var RANGE = SHEET.getDataRange();
var COLS_TO_SEARCH = {n:13, y:24}; // Search columns N and Y
var DELETE_VAL = 0;

  main(); 

  function main() {
    var startTime = new Date().getTime();
    var deleteSelectedRows = removeFilterSetVals();
    var runTime = (new Date().getTime() - startTime) / 1000;
    Logger.log("Runtime is: " + runTime + " seconds");
  };


  function removeFilterSetVals() {
    var rangeVals = RANGE.getValues();
    var filteredRange = rangeVals.filter(function(val) {
      return (val[COLS_TO_SEARCH.n] != DELETE_VAL || val[COLS_TO_SEARCH.y] != DELETE_VAL)
    });

    RANGE.clearContent();
    var newRange = SHEET.getRange(1, 1, filteredRange.length, filteredRange[0]
      .length);
    newRange.setValues(filteredRange);
  };

}

main();

function main() {

var startTime = new Date().getTime();

var deleteSelectedRows = removeFilterSetVals();

var runTime = (new Date().getTime() - startTime) / 1000;

Logger.log("Runtime is: " + runTime + " seconds");

};

function removeFilterSetVals() {

var rangeVals = RANGE.getValues();

var filteredRange = rangeVals.filter(function(val) {

return (val[COLS_TO_SEARCH.n] != DELETE_VAL || val[COLS_TO_SEARCH.y] != DELETE_VAL)

});

RANGE.clearContent();

var newRange = SHEET.getRange(1, 1, filteredRange.length, filteredRange[0]

.length);

newRange.setValues(filteredRange);

};

}

Yagi says:

at 9:23 am

Looks great, Wesley.

Loading...

Reply

Pingback: Is There A Way To Make This Script Run Faster? - Programming Questions And Solutions Blog
Pingback: Is There A Way To Make This Script Run Faster? - Programming Questions And Solutions Blog

Google Apps Script: Delete Rows Based on a Column’s Cell Value in Google Sheet

Small Data Sets

deleteEachRow()

Large Data Sets

An Example

A bad approach

A faster approach

remove then set new values

Filter out the value and set the new value in the sheet

Conclusion

Like this:

Related

41 thoughts on “Google Apps Script: Delete Rows Based on a Column’s Cell Value in Google Sheet”

Leave a ReplyCancel reply

Small Data Sets

deleteEachRow()

Large Data Sets

An Example

A bad approach

A faster approach

remove then set new values

Filter out the value and set the new value in the sheet

Conclusion

Share this:

Like this:

Related

41 thoughts on “Google Apps Script: Delete Rows Based on a Column’s Cell Value in Google Sheet”

Leave a ReplyCancel reply