需要根据一个单元格的条件删除整行 [英] Need to delete an entire row based on one cell's criteria

问题描述

在Google表格中，当该行中的一个单元格包含短语"Delivered"时，我试图删除整行. onEdit函数无法满足我的需要，因为它需要我在工作表生效之前手动对其进行编辑-这不是我想要的.我需要它自动[实时]删除行，因为单词"Delivered"是通过实时网络查询{= index(IMPORTXML}(如果您没猜到的话，是发货跟踪))生成的.

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).

在我意识到这不是我需要的东西之前，这里是我使用过onEdit的公式:

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

该公式旨在根据第9列中的已传递"或"I"一词删除到整个行. onFormSubmit是否可以满足我的需求或onChange?如果是这样，我将如何构造公式?

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?

推荐答案

尝试一下:

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

这篇关于需要根据一个单元格的条件删除整行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！