Search for a string in a sheet and remove it when found

Question

I'm trying to read a cell from sheet INPUT and check to see if it is present in sheet STORAGE (which has only one column). If it is found, it would need to be removed. The script can terminate if the string is found. I have written some code that seems like it would work in theory, but just times out after six minutes when I try using it.

I've tried setting it up so that the loops only iterate once each, but it still seems to be stuck somewhere. Here is my code:

var active_spreadsheet = SpreadsheetApp.getActive();
var sheet_input = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('INPUT');
var sheet_storage = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('STORAGE');
var last_row_counter = 0;

function findAndRemove() {
  last_row_counter = sheet_storage.getLastRow();
  var n = 1;
  while (n <= last_row_counter) {
    if (sheet_input.getRange("B2").getValue() == sheet_storage.getRange(n,1)) {
      sheet_storage.deleteRow(n); 
      n = last_row_counter;
    }   
    n = n++;
  }  

Answer 1

Brian,

See if this runs better:

function findAndRemove() {
var ss = SpreadsheetApp.getActive(),
    valToFind = ss.getSheetByName('INPUT')
        .getRange('B2')
        .getValue(),
    sheetToCheck = ss.getSheetByName('STORAGE'),
    values = sheetToCheck.getRange('A2:A')
        .getValues();
for (var i = 0, l = values.length; i < l; i++) {
     if (values[i][0] == valToFind) {
        sheetToCheck.deleteRow(i + 2);
        break;
    }
}
}

As you mentioned the code will exit after the first time the value is found. I used col A as the column that needs to be searched (change to suit). As you see this code gets all the values of that column in one call, then loops through it to see if a match is found.

Answer 2

Where do I start?!

1. I suggest you store the sheet_input.getRange("B2").getValue() in a variable before running the loop, as this means the script only needs to query the value once, instead of once per row.

2. In javascript n++ already means "increment n by 1" ie: n=n+1;, so the assignment is over the top. Just use: n++;

3. A for loop is better to use in this case as it includes the start number, the end condition and the increment all-in-one; Some info Here

4. The main problem is that when you do your comparison sheet_input.getRange("B2").getValue() == sheet_storage.getRange(n,1) you are comparing a value eg "hello" to a CELL, not its value. So you need sheet_storage.getRange(n,1).getValue() instead.

5. Instead of artificially saying "if a matching value is found, remove the row and then increment the row count to max" to stop the loop, just use the break; clause, which exits the loop.

6. I strongly suggest you provide some kind of prompt to let the user know when the function finished, and if it worked. See info on class Browser.msgBox()

Here's my code:

var active_spreadsheet = SpreadsheetApp.getActive();
var sheet_input = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('INPUT');
var sheet_storage = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('STORAGE');


function findAndRemove() {

  var comapreValue = sheet_input.getRange("B2").getValue();
  var last_row_counter = sheet_storage.getLastRow();
  var sum_of_deleted_rows = 0;

  for (var n = 1; n <= last_row_counter; n++) {
    if (comapreValue == sheet_storage.getRange(n,1).getValue()) {
      sheet_storage.deleteRow(n); 
      sum_of_deleted_rows++;
      // break; //if you would like to stop after just one row is removed, un-comment this line
    }    
  } 
  Browser.msgBox(sum_of_deleted_rows + " row(s) deleted");
}