1

I need to loop through all rows/columns in a sheet and remove rows contain certain words. Ideally, I would search through the sheet using a regular expression, but just finding a string would help get me moving. I'm seeing a lot of posts on Stack Overflow about finding and deleting empty rows, but can't find anything about searching an entire sheet and deleting a row if found.

This is what I have so far:

/* Delete rows */
function deleteRows() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();

  var deleted = 0;  // Counter (don't need if we loop backwards)
  var regExp = new RegExp('word');

  for (var row = 0; row < values.length; row++) {
    var regExpMatch = values[row][1].match(regExp);
    if (regExpMatch.length > 0) {
      sheet.deleteRow(row + 1 - deleted);
      deleted++;
    }
  }
  SpreadsheetApp.flush();
};

However this only searches Column B, and it throws an error "TypeError: Cannot read property 'length' from null" even though "word" exists in Column B in my spreadsheet. And if I do a simpler version like:

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

  var deleted = 0;  // Counter (don't need if we loop backwards)

  for (var row = 0; row < values.length; row++) {
    if (values[row][1].search("WordThatExistsInOneRow")) {
      sheet.deleteRow(row);
      deleted++;
    }
  }
  SpreadsheetApp.flush();
};

It starts deleting every row, even if "WordThatExistsInOneRow" only shows up in 1 row.

eddyparkinson
  • 3,680
  • 4
  • 26
  • 52
Keith
  • 673
  • 3
  • 13
  • 27

1 Answers1

4

You have not wrote your loop logic correctly, you need to loop over each row then each column in that row and then assign the row index for deletion which must be performed in revere order. Try this:

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

  var toDelete = [];

  for (var row = 0; row < values.length; row++) {
    for(var column = 0;column<values[row].length;column++){
      if (values[row][column].indexOf("WordThatExistsInOneRow") > -1){
        toDelete.push(row);
      }
    }
  }

  for(var deleteRow = toDelete.length-1; deleteRow >= 0;deleteRow--){
    sheet.deleteRow(toDelete[deleteRow]+1);
  }

  SpreadsheetApp.flush();
};
Simon Staton
  • 4,345
  • 4
  • 27
  • 49
  • I think that does work! However for that IF statement, I couldn't get a regular expression to work in there. I ended up doing this; do you think it's the best solution for testing a RegEx in there? (in this example I'd be finding all cells that end in a capital 'A'): ``var re = new RegExp('A$','gi'); for (var row = 0; row < values.length; row++) { for(var column = 0;column – Keith Feb 18 '15 at 01:58
  • ^^ sorry I was trying to make that code look nicer but can't figure out how to make new lines/indents in comments – Keith Feb 18 '15 at 02:03
  • You can't in comments sadly and yes that would be a good place for a `regexp.test()` – Simon Staton Feb 18 '15 at 09:15