0

So this is a question that has been asked before, with a slightly different twist: I need to delete rows in a google sheet based on a value contained within a cell in that row.

The issue is previous answers I have found allow me to do this only if the value is exact. I have a LET AGREED phrase which occasionally appears but it comes with other text in the cell as well. I want to delete any rows in which the phrase LET AGREED appears.

Two solutions I found which came very close:

Delete a row in google spreadsheet base on value of cell in row

Delete a row in google spreadsheet base on contents of cell in row

Any help appreciated, I'm not great with Google Scripts!

Shadician
  • 47
  • 2
  • 7

1 Answers1

2

I assume you want this to happen whenever something is edited? The .search() method can be called strings to see if a substring is present. If it is, it returns the index of where it was found. If it is not found, it returns -1.

This is an onEdit trigger, and it will get called the moment anything on your sheet is changed. So the moment the text "LET AGREED" is saved in a cell, the row will disappear.

Also, if you are copy and pasting into this sheet a range of data where the words "LET AGREED" will be present in many rows, it will be able to handle deleting all of those rows.

function onEdit(e) {

    var ss = e.source;
    var sheet = ss.getSheets()[0];
    var range = sheet.getDataRange();
    var values = range.getValues();

    var rows_deleted = 0; 

    for (var i = 0; i < values.length; i++) {
        for (var j = 0; j < values[i].length; j++) {

            var value = values[i][j];

            //row numbers are 1-based, not zero-based like this for-loop, so we add one AND...
            //every time we delete a row, all of the rows move down one, so we will subtract this count
            var row = i + 1 - rows_deleted;

            //if the type is a number, we don't need to look
            if (typeof value === 'string') {

                var result = value.search("LET AGREED");

                //the .search() method returns the index of the substring, or -1 if it is not found
                //we only care if it is found, so test for not -1

                if (result !== -1) {
                    sheet.deleteRow(row);
                    rows_deleted++;
                }
            }  
        }
    }
};
Dan Oswalt
  • 2,201
  • 2
  • 14
  • 24
  • Fantastic, thank you! This is exactly what I was looking for. Really appreciate the comments as well as they help me understand how it works so I can get better at building these sort of things myself :) EDIT: I must be doing something wrong, I can't seem to get the script to work. Is there anything I need to edit within the script to map it to my sheet? Any ideas what I could be doing wrong? – Shadician Feb 19 '15 at 11:27
  • Does your spreadsheet have more than one worksheet in it? The above script will only work on the first sheet, that's what the var sheet = ss.getSheets()[0]; line at the top does. You can grab a different sheet by simply changing the index (zero-based, remember), or you can you ss.getSheetByName("yourSheetName") if they aren't always going to be in that order. Does that help? – Dan Oswalt Feb 19 '15 at 16:41
  • Very cool! The issue with an "onEdit" is that it will attempt to run every time you edit a cell. What is the best way to be able to turn it on or off, or to run it once? – Sherwood Botsford Jan 02 '17 at 22:59
  • Hi, if calling this manually is what you want, you can always just change the name of the function from 'onEdit(e)' to a custom function name, like 'deleteCompletedRows()', and update `var ss = e.source` to be `var ss = SpreadsheetApp.getActiveSheet()`. – Dan Oswalt Jan 03 '17 at 02:29