Need to delete an entire row based on one cell's criteria

Question

In Google Sheets, I am trying to delete an entire row when one cell in that row contains the phrase "Delivered." The onEdit function won't work for my needs because it requires me to manually make an edit to the sheet before it works - which is not what I want. I need it to delete rows automatically [in real time] because the word "Delivered" is being generated from a live web query {=index(IMPORTXML} (shipment tracking if you haven't guessed).

Here is the formula I had using onEdit before I realized it wasn't what I needed:

function onEdit(event) { 
  var s = SpreadsheetApp.getActiveSpreadsheet();
  var r = SpreadsheetApp.getActiveRange();

// getColumn with "Delivered" is currently set to column 9 or I.

  if(r.getColumn() == 9 &&  r.getValue() == "Delivered") {
    var row = r.getRow();
    s.deleteRow(row);
  }
}

The formula is designed to delete to an entire row based on the word "Delivered" in column 9, or "I". Would onFormSubmit work for my needs, or onChange? If so, how would I structure the formula?

My two cents: If your business logic is to remove completed entries (?), then the updating process itself should do it, if possible. — Elaskanator, Jul 31 '19 at 20:25
When you say "live web query", what are you using to record this in the sheet? — ross, Jul 31 '19 at 20:28
Since you're using a formula to update the data, you're probably best off setting up a function to loop through the whole sheet on a time based trigger. — ross, Jul 31 '19 at 20:44

score 1 · Accepted Answer · answered Jul 31 '19 at 23:00

Try this:

function createTimeBasedTriggerIfYouDontAlreadyHaveOne() {
  if(!isTrigger(deleteDeliveredRows)) {
     ScriptApp.newTrigger(deleteDeliveredRows).timeBased().everyDays(1).atHour(0).create();
  }
}

function deleteDeliveredRows() {
  var ss=SpreadsheetApp.openById('ssid')
  var sh=ss.getSheetByName('sheetname');
  var rowStart=2;//assume 1 row for headers
  var rg=sh.getRange(rowStart,1,sh.getLastRow()-rowStart+1,sh.getLastColumn());
  var vA=rg.getValues();
  var d=0;
  for(var i=0;i<vA.length;i++) {
    if(vA[i][8]=="Delivered") {
      sh.deleteRow(i + rowStart - d++);
    }
  }
}

function isTrigger(funcName){
  var r=false;
  if(funcName){
    var allTriggers=ScriptApp.getProjectTriggers();
    for(var i=0;i<allTriggers.length;i++){
      if(funcName==allTriggers[i].getHandlerFunction()){
        r=true;
        break;
      }
    }
  }
  return r;
}

Cooper, This script works perfectly! I had to add quotations around "deleteDeliveredRows" in the first function above in order for the time trigger to fire properly. Thanks for your help everyone. — Drake, Aug 01 '19 at 18:39

Need to delete an entire row based on one cell's criteria

1 Answers1

Linked