Google Spreadsheets - Delete remove rows with a matching value in the column

Question

I have a Google Spreadsheet with 3 tabs. The tab names are "Sheet1" "Sheet2" and "Sheet3". I am importing data from a url into "Sheet2". I am trying to run a script only on "Sheet2" tab that removes any rows that equal "5555555555" listed in "Column I" which is labeled as "Main Phone".

How do I do this?

Your question is "to broad" for stack overflow. You need to read the documentation, and learn the basics. — Alan Wells
Read the documentation on Sheet.getRange() and also Range.getValues() — Alan Wells

Alan Wells Alan Wells · Accepted Answer · 2016-01-28T20:23:04

This code gets all the values in column I, then checks the data from every row starting with the last inner array in the outer array of data. You must delete rows from the bottom up.

function removeRows() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheetByName('Sheet2');
  //getRange(start row, start column, number of Rows to get, number of Columns to Get)
  var dataInColumn_I = sh.getRange(1, 9, sh.getLastRow(), 1).getValues(); //Get a two dimensional array
  var thisCellValue,
      numberOfRowsInSheet = dataInColumn_I.length;

  for (var i=numberOfRowsInSheet;i>0;i-=1) {
    thisCellValue = dataInColumn_I[i-1];

    if (thisCellValue === "5555555555") {
      sh.deleteRow(i);
    };
  };
};

Google Spreadsheets - Delete remove rows with a matching value in the column

1 Answers