如何有效地删除与另一个工作表上的标准匹配的行?

Question

我需要从一个大型(115244行)数据集中删除一个例外/排除列表。排除的总数为1133。

我有下面一段代码:

function removeExclusions() {
const ss = SpreadsheetApp.getActive(); 

const exclusionSheet = ss.getSheetByName("Exclusion List");
const excludedAccounts = exclusionSheet
.getRange("C2:C" + exclusionSheet.getLastRow())
.getValues()
.reduce(
(o, [c]) =>
Object.assign(o, {
[c]: true
}),
{}
);
Logger.log("Total accounts to remove: " + excludedAccounts); 

const dataSheet = ss.getSheetByName("Data Sheet");
const dataSheetMatches = dataSheet
.getRange("A2:A" + dataSheet.getLastRow())
.getValues()
.reduce((ar, [a], i) => {
if (excludedAccounts[a]) ar.push(i + 2);
return ar;
}, [])
.reverse();
Logger.log("Accounts left to remove: " + dataSheetMatches.length); 

dataSheetMatches.forEach((r) => {
dataSheet.deleteRow(r);
Logger.log("Row:" + r + " has been deleted");
});
}

然而，由于数据集的大小/帐户数/行数，这需要运行-它需要一个永恒，并达到Google对Apps Script运行时的超时。

我需要一种更有效的方法来做以下事情:检查"排除列表"表(C列)，然后删除"数据表"中的该行。

可以清除A-O行。列p - s标题行(1)包含一些我需要保留的公式。

有什么建议吗?

编辑所以我修改了我的代码如下:

function removeExclusions() {
const ss = SpreadsheetApp.getActive();
const exclusionSheet = ss.getSheetByName("Exclusion List");
const exclusionRange = exclusionSheet.getRange("C2:C");
const exclusionVals = exclusionRange.getDisplayValues();
const dataSheet = ss.getSheetByName("Data Sheet");
const dataSheetRange = dataSheet.getRange("A2:O");
let dataSheetVals = dataSheetRange.getValues();
dataSheetVals = dataSheetVals.filter((data) => {
return !exclusionVals.includes(data[0]);
});
Logger.log(dataSheetVals);
}

但是，它仍然显示我希望它排除的行…

Answer 1

它花了很长时间，达到了谷歌对应用程序的超时时间脚本运行时。

原因是

dataSheet.deleteRow(r);

即使你在20行表上尝试，它也很慢，你几乎可以看到一行一行被删除。

除此之外，100K+行有时对Google Sheets来说太多了，响应时间变慢了。

可以使用的策略是

1. 从"数据表"中提取所有数据;使用getValues()将其转换为变量。(如果可能，排除公式列)
2. 拉下"排除名单";就像你在代码
中所做的那样
3. 在"数据表"上使用.filtervalues数组通过删除不需要的行创建新数组
4. 明确"数据表";值(如果可能，排除公式列)
5. 使用setValues()将简化后的数据写入"数据表"。

指出

1. 这种方法也需要很长时间。它比deleteRow快得多，但是对100K+行和15-20列的getValues和setValues需要一些时间。执行时间的粗略估计是在"数据表"上手动选择所有/复制/粘贴测试。看看你还要等多久。

2. 如果某人/进程正在添加/删除/移动/编辑"数据表";在函数运行的同时，可能会丢失数据。如果你可以通过使用一些每天一次的夜间触发器来运行你的代码来防止这种情况，你应该没问题。

3. 一般来说，你的代码可能会中断，你可以在开始时复制一个工作表(作为备份)，然后如果一切都没有错误地执行，删除最后的重复工作表。

4. 一般来说，你应该考虑将你的数据从Google Sheets转移到一些可以处理大量记录的平台上，我在这种情况下使用BigQuery。

编辑:对修改后的代码的回应

假设所比较的值是字符串:

function somethingLikeThis() {
const ss = SpreadsheetApp.getActive();
// Load "Exclusion List" column C to array of strings
const exclusionSheet = ss.getSheetByName("Exclusion List");
const lastRowExclusionSheet = exclusionSheet.getLastRow();
const exlusionList = exclusionSheet
.getRange(2, 3, lastRowExclusionSheet - 2 + 1) // Data starts at row 2, column is C
.getValues()
.map(row => row[0].toString()) // Convert array of arrays to array of strings
.filter(el => el.length > 0); // Remove empty rows if any
// Read values from "Data Sheet"
const dataSheet = ss.getSheetByName("Data Sheet");
const lastRowDataSheet = dataSheet.getLastRow();
const oldDataRange = dataSheet
.getRange(2, 1, lastRowDataSheet - 2 + 1, 15) // Data starts at row 2, columns A-O
const oldDataValues = oldDataRange.getValues();
// Clear "Data Sheet"
oldDataRange.clearContent();
// Keep rows where column A value is not on the "Exclusion List"
const newDataValues = oldDataValues
.filter(row => exlusionList.indexOf(row[0].toString()) < 0);
// Write reduced rows to "Data Sheet"
if (newDataValues.length > 0) {
dataSheet.getRange(2, 1, newDataValues.length, newDataValues[0].length)
.setValues(newDataValues);
}
}

代码没有经过测试，我没有实际的表格。尝试使用getLastRow()，范围为"C2:C"可以选择工作表末尾的空行。