Delete a row in google spreadsheet base on content

Question

i have this script to delete certain rows, if the selected cell in the selected colum has the metioned content but i don't understand where it fails

    function DeleteRowByKeyword() {

  var value_to_check = Browser.inputBox("Enter the keyword to trigger delete Row","", Browser.Buttons.OK);

  //  prendo quello attivo
  var DATA_SHEET = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var FIRST_COLUMN = Browser.inputBox("Number of Column to look at (eg: for column A enter 1)","", Browser.Buttons.OK);

  ss.toast("removing duplicates...","",-1);

  var dataCopy1 = DATA_SHEET.getDataRange().getValues();
  var deleted_rows = 0;
  var rangeToCopy = '';

  if (dataCopy1.length > 0) {
    var i = 1;

    while (i < DATA_SHEET.getMaxRows() - deleted_rows) {
  if ((dataCopy1[i][FIRST_COLUMN]).search(value_to_check) != -1) {

    ss.deleteRow(i);
    deleted_rows++;
  } 
  i++;
    }
  }


  ss.toast("Done! " + deleted_rows + ' rows removed',"",5);
    }

thanks in advance for any help

Answer 1

You need to ensure that the index for deletion is the correct index. When you delete a row all bellow rows has their indexes changed -1. So try this code:

if (dataCopy1.length > 0) {
var i = 1;

while (i < DATA_SHEET.getMaxRows() - deleted_rows) {
 if ((dataCopy1[i][FIRST_COLUMN]).search(value_to_check) != -1) {

   ss.deleteRow(i - deleted_rows);
  deleted_rows++;
 } 
i++;
}
}

Answer 2

There are a few things to be improved:

• Remember that spreadsheet rows and columns are numbered starting at 1, for all methods in the SpreadsheetApp, while javascript arrays start numbering from 0. You need to adjust between those numeric bases when working with both.

• The String.search() method may be an inappropriate choice here, for two reasons. The .search() will match substrings, so ('Testing').search('i') finds a match; you may want to look for exact matches instead. Also, .search() includes support for regex matching, so users may be surprised to find their input interpreted as regex; .indexOf() may be a better choice.

• To limit operations to rows that contain data, use .getLastRow() instead of .getMaxRows().

• When deleting rows, the size of the spreadsheet dataRange will get smaller; you did take that into account, but because you're looping up to the maximum size, the code is complicated by this requirement. You can simplify things by looping down from the maximum.

• The input of a Column Number is error-prone, so let the user enter a letter; you can convert it to a number.

• You had not defined a value for ss (within this function).

Here's the updated code:

function DeleteRowByKeyword() {

  var value_to_check = Browser.inputBox("Enter the keyword to trigger delete Row", "", Browser.Buttons.OK);

  var matchCol = Browser.inputBox("Column Letter to look at", "", Browser.Buttons.OK);
  var FIRST_COLUMN = (matchCol.toUpperCase().charCodeAt(0) - 'A'.charCodeAt(0) + 1);  // Convert, e.g. "A" -> 1

  //  prendo quello attivo (get active sheet)
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var DATA_SHEET = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  ss.toast("removing duplicates...", "", -1);

  var dataCopy1 = DATA_SHEET.getDataRange().getValues();
  var deleted_rows = 0;

  if (dataCopy1.length > 0) {
    var i = DATA_SHEET.getLastRow();  // start at bottom

    while (i > 0) {
      if (dataCopy1[i-1][FIRST_COLUMN-1] === value_to_check) {

        ss.deleteRow(i);
        deleted_rows++;
      }
      i--;
    }
  }

  ss.toast("Done! " + deleted_rows + ' rows removed', "", 5);
}