挑战循环通过数组以删除 Google Apps 脚本中的行

Question

以下函数根据特定值/项目删除Google工作表中的行，我希望将其修改为删除一系列项目。

原始功能

//GLOBALS


var spreadSheet = SpreadsheetApp.openById("SHEET ID"); 


var DELETE_VAL = ["Item_to_delete 1", "Item_to_delete 2", "Item_to_delete 3"]
var COL_TO_SEARCH = 5; // The column to search for the DELETE_VAL (Zero is first)


function deleteEachRow(sheetName){
var SHEET = spreadSheet.getSheetByName(sheetName);
var RANGE = SHEET.getDataRange();
var rangeVals = RANGE.getValues();

//Reverse the 'for' loop.
for(var i = rangeVals.length-1; i >= 0; i--){
if(rangeVals[i][COL_TO_SEARCH] === DELETE_VAL){

SHEET.deleteRow(i+1); 
};
};
};
// Invoke deleteEachRow() for each sheet you want to delete the rows
["SHEET 1", "SHEET 2", "SHEET N"].forEach((sheetName) => deleteEachRow(sheetName))

我尝试传递一个贯穿每个项目的 for 循环，但它抛出了一个错误：

//GLOBALS

var spreadSheet = SpreadsheetApp.openById("SHEET ID"); 


var DELETE_VAL = ["Item_to_delete 1", "Item_to_delete 2", "Item_to_delete 3"]
var COL_TO_SEARCH = 7; // The column to search for the DELETE_VAL (Zero is first)


function deleteEachRow(sheetName){
var SHEET = spreadSheet.getSheetByName(sheetName);
var RANGE = SHEET.getDataRange();
var rangeVals = RANGE.getValues();

//Reverse the 'for' loop.
for(var i = rangeVals.length-1; i >= 0; i--){
for(var i = DELETE_VAL.length; i >=0; i++){
if(rangeVals[i][COL_TO_SEARCH] === DELETE_VAL[i]){

SHEET.deleteRow(i+1); 
};
};
};
};
// Invoke deleteEachRow() for each sheet you want to delete the rows
["SHEET 1", "SHEET 2", "SHEET N"].forEach((sheetName) => deleteEachRow(sheetName));

类型错误： 无法读取未定义的属性"7">

这意味着它不再读取 rangeVals 变量。

我也试过

.
.
.
// Invoke deleteEachRow() for each sheet you want to delete the rows
for(var i = DELETE_VAL.length; i >=0; i++){
["SHEET 1", "SHEET 2", "SHEET N"].forEach((sheetName) => deleteEachRow(sheetName));
};

但这只是无限期地执行，直到超时。

我知道我肯定有一些逻辑缺陷，但我快到了，请帮忙。 我对.js/.gs的了解很少。

我又试过了。

function deleteEachRow(sheetName){
var SHEET = spreadSheet.getSheetByName(sheetName);
var RANGE = SHEET.getDataRange();
var rangeVals = RANGE.getValues();

//Reverse the 'for' loop.
for(var i = rangeVals.length-1; i >= 0; i--){
for(var j = DELETE_VAL.length; j >=0; j--){
if(rangeVals[i][COL_TO_SEARCH] === DELETE_VAL[j]){

SHEET.deleteRow(j+1); 
};
};
};
};

Answer 1

问题：

• 如果有两个嵌套的for循环，请确保两个计数器变量不同(i)。
• j用作DELETE_VAL的索引。这与要删除的工作表行不对应 (SHEET.deleteRow(j+1);)。这应该i(SHEET.deleteRow(i+1);)。
• 此外，我建议不要使用内部for循环，以检查数组DELETE_VAL是否包含相应的单元格值。这样，就没有必要声明j，这是造成这种混乱的原因。

代码片段：

function deleteEachRow(sheetName){
var SHEET = spreadSheet.getSheetByName(sheetName);
var RANGE = SHEET.getDataRange();
var rangeVals = RANGE.getValues();
for(var i = rangeVals.length-1; i >= 0; i--){
if (DELETE_VAL.includes(rangeVals[i][COL_TO_SEARCH])) {
SHEET.deleteRow(i+1);
};
};
};