Google Sheets JScript Row match and delete if-从表A中取行，如果与表B、表

Question

我的数据导入(表A(包含上次已知事件的所有信息。它只在另一个事件发生时更改它们。因此，如果事件发生在两天前，则它仍在我的数据导入(表A(中，直到该事件发生更改。我需要将我的数据导入(工作表A(与工作表B、工作表C和工作表D中的相同行进行检查。如果行已经存在，则删除该行。

到目前为止，我有：

function testFilter() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheetA = ss.getSheetByName("SheetA");
var sheetB = ss.getSheetByName("SheetB");
var sheetC = ss.getSheetByName("SheetC");
var sheetD = ss.getSheetByName("SheetD");
var sheetE = ss.getSheetByName("SheetE");
var sheetF = ss.getSheetByName("SheetF");
var valuesA = sheetA.getRange('A2:AC400').getValues();
var valuesB = sheetB.getRange('A2:AC400').getValues();
var valuesC = sheetC.getRange('A2:AC400').getValues();
var valuesD = sheetD.getRange('A2:AC400').getValues();
var valuesE = sheetE.getRange('A2:AC400').getValues();
var valuesF = sheetF.getRange('A2:AC400').getValues();
for (var rowa in valuesA)
{
var deleteRowa = false;
for (var rowb in valuesB)
{
if(valuesA[rowa].join() == valuesB[rowb].join())
deleteRowa = true;
}
if(deleteRowa == true){
sheetA.deleteRow(rowa)
}
SpreadsheetApp.flush();
}
for (var rowa in valuesA)
{
var deleteRowa = false;
for (var rowc in valuesC)
{
if(valuesA[rowa].join() == valuesC[rowc].join())
deleteRowa = true;
}
if(deleteRowa == true){
sheetA.deleteRow(rowa)
}
SpreadsheetApp.flush();
}
for (var rowa in valuesA)
{
var deleteRowa = false;
for (var rowd in valuesD)
{
if(valuesA[rowa].join() == valuesD[rowd].join())
deleteRowa = true;
}
if(deleteRowa == true){
sheetA.deleteRow(rowa)
}
SpreadsheetApp.flush();
}
for (var rowa in valuesA)
{
var deleteRowa = false;
for (var rowe in valuesE)
{
if(valuesA[rowa].join() == valuesE[rowe].join())
deleteRowa = true;
}
if(deleteRowa == true){
sheetA.deleteRow(rowa)
}
SpreadsheetApp.flush();
}
for (var rowa in valuesA)
{
var deleteRowa = false;
for (var rowf in valuesF)
{
if(valuesA[rowa].join() == valuesF[rowf].join())
deleteRowa = true;
}
if(deleteRowa == true){
sheetA.deleteRow(rowa)
}
SpreadsheetApp.flush();
}
}

这会在第一次尝试过滤时出现异常：越界错误。deleterows((函数是否适用于挑选特定的行？还是我必须将它放入一个新列表，然后从原始工作表中删除所有行，然后插入列表？

Answer 1

删除SheetA中的匹配行

function testFilter() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const shts = ["SheetB", "SheetC", "SheetD", "SheetE", "SheetF"];
const A1rg = "A2:AC400";
let uA = [];//building a unique flat array of row join
ss.getSheets().filter(sh => ~shts.indexOf(sh.getName())).forEach(sh => {
sh.getRange(A1rg).getValues().forEach(r => {
let j = r.join("");
if (!~uA.indexOf(j)) {
uA.push(j);
}
});
});
let d = 0;
const shA = ss.getSheetByName("SheetA");
shA.getRange(A1rg).getValues().forEach((r, i) => {
if (~uA.indexOf(r.join(""))) {
shA.deleteRow(i + 2 - d++);
}
});
}

这个版本对我来说运行得更快

function testFilter() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const shts = ["Sheet1", "Sheet2", "SheetD", "SheetE", "SheetF"];
const A1rg = "A2:AC400";
let uA = [];
ss.getSheets().filter(sh => ~shts.indexOf(sh.getName())).forEach(sh => {
sh.getRange(A1rg).getValues().forEach(r => {
let j = r.join("");
if (!~uA.indexOf(j)) {
uA.push(j);
}
});
});
let d = 0;
const shA = ss.getSheetByName("Sheet0");
shA.getRange(2, 1, shA.getLastRow() - 1, shA.getLastColumn()).getValues().forEach((r, i) => {
if (~uA.indexOf(r.join(""))) {
shA.deleteRow(i + 2 - d++);
}
});
}

Answer 2

这里的算法与Cooper的算法大致相同，但我希望它更可读。

function myFunction() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var names = ['SheetA', 'SheetB', 'SheetC', 'SheetD', 'SheetE' ]; // etc
const get_array_from_sheet = sheet_name => 
ss.getSheetByName(sheet_name).getRange('A2:AC400').getValues()
.map(x => x.join('')).filter(String);
var [sheetA, ...sheets_etc] = names.map(n => get_array_from_sheet(n));
sheets_etc = sheets_etc.flat();
var rows_to_delete = sheetA.filter(x => sheets_etc.includes(x))
.map(x => sheetA.indexOf(x) + 2);
rows_to_delete.reverse().forEach(x => ss.getSheetByName('SheetA').deleteRow(x));  
}