基于单个单元格和数组删除行的Google Sheet脚本

Question

我正在努力编写一个脚本来删除另一个工作表中相应的行。理想情况下，我希望能够运行一个脚本，从工作表1的供应商和工单和工作表2中删除匹配条目。问题是一些工单将与其他供应商匹配。任何帮助都将不胜感激！

一个示例表：https://docs.google.com/spreadsheets/d/1G5vUPF6H1WWlHICDd92SMg44QfPUObil8A2Ocu_usm4/edit?usp=sharing

Answer 1

​

​

请注意，您需要将完成的工作表更新为此格式

const ss = SpreadsheetApp.openById(sheetID);


function deleteRowMain() {
  //data with completed Results
  let ws_1 = ss.getSheetByName("Sheet1");
  let ws_2 = ss.getSheetByName("Sheet2");
  let data_completed = ws_1.getDataRange().getValues();
  let data_pending = ws_2.getDataRange().getValues();

  //nameIndex and job index on completed sheet
  let nameIndex = 2;
  let jobIndex = 3;

  for (let i = 0; i < data_completed.length; i++) {
    let row = data_completed[i];
    deleteRow_(row[nameIndex - 1], row[jobIndex - 1])
  }
}

function deleteRow_(value_1, value_2) {
  //nameIndex and job index on pending sheet
  let nameIndex = 2;
  let jobIndex = 3;

  let ws = ss.getSheetByName("Sheet2");
  let data = ws.getDataRange().getValues();
  for (let i = data.length - 1; i >= 0; i--) {
    let row = data[i];
    if (row[nameIndex - 1] == value_1 && row[jobIndex - 1] == value_2) {
      //delets row if 'Value1' and 'value2' matches
      ws.deleteRow(i + 1);
    }
  }
}

​