Google Spreadsheets Script To Delete Any Rows Where A String Is Found

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 s

Solution 1:

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();
};