显示/隐藏行的谷歌脚本非常慢(并且崩溃)

Question

我有一个脚本，通过隐藏和显示适当的行来过滤基于单元格(A1(的工作表。它正在工作，但由于工作表每年都变大，因此它已经运行得非常慢，因为它逐行隐藏/取消隐藏。我想以某种方式批处理这些并仅执行一次隐藏/取消隐藏功能。不幸的是，我没有运气这样做。以下是适用于两张工作表的代码，但速度太慢(太慢，有时会崩溃(：

function chooseYearAdj() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("ADJUSTMENTS");
var yrCell = sheet.getRange("A1").getValue()
//  var lastRow = sheet.getLastRow(); extra unnecessary var?
var allRange = sheet.getRange("A3:A")
var i=1
if (yrCell == "ALL" || yrCell == "all"){
//      var rowAll = sheet.getRange("A:A"); extra unnecessary var?
sheet.unhideRow(allRange);
}
else
{
sheet.hideRow(allRange); // hides all rows below header
while ( sheet.getRange("A"+i).getValue() != "") { // i <= last interesting row
{
var year = sheet.getRange("R"+i).getValue();  // Checks the R column
if (year == yrCell) {         // year == chosen year
var rowHide = sheet.getRange("A"+i);
sheet.unhideRow(rowHide);
}
i=i+1
}
}
}
}
function chooseYearTS() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("TEAM SALES");
var yrCell = sheet.getRange("A1").getValue()
//  var lastRow = sheet.getLastRow(); extra unnecessary var?
var rowRange = sheet.getRange("A:A")
var i=3
if (yrCell == "ALL" || yrCell == "all"){
sheet.unhideRow(rowRange); //shows all rows
}
else
{
sheet.unhideRow(rowRange); // shows all rows below header
while ( sheet.getRange("A"+i).getValue() != "") { // i <= last interesting row
{
var year = sheet.getRange("CC"+i).getValue();  // Checks the R column
if (year != yrCell && year != "") {         // year is neither blank nor the chosen year.
var rowHide = sheet.getRange("A"+i);
sheet.hideRow(rowHide);
}
i=i+1
}
}
}
}

其中第二个甚至比第一个慢得多。我尝试在 while 循环中创建范围，然后隐藏/显示整个范围，但这并没有改变任何东西。我承认，这是代码可能存在语法错误，因为这对我来说是一次新的尝试：

function chooseYearAlt() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("ADJUSTMENTS");
var yrCell = sheet.getRange("A1").getValue()
//  var lastRow = sheet.getLastRow(); extra unnecessary var?
var allRange = sheet.getRange("A3:A")
var i=1
var toHide = sheet.getRange()
if (yrCell == "ALL" || yrCell == "all"){
//      var rowAll = sheet.getRange("A:A"); extra unnecessary var?
sheet.unhideRow(allRange);
}
else
{
sheet.hideRow(allRange); // hides all rows below header
while ( sheet.getRange("A"+i).getValue() != "") { // i <= last interesting row
{
var year = sheet.getRange("R"+i).getValue();  // Checks the R column
if (year == yrCell) {         // year == chosen year
toHide = toHide + sheet.getRange("A"+i);

//         var rowHide = sheet.getRange("A"+i);
//         sheet.unhideRow(rowHide);
}
i=i+1
}
sheet.unhideRow(toHide);
}
}
}

我在这里遇到的问题是，无法保证年份列将保持有序，因此我不能只找到具有正确年份的第一行，然后在它之后隐藏正确数量的行，因为有一天，它们最终可能会更加混合。

关于如何加快速度的任何想法？

Answer 1

这是您选择的YearAlt函数的更快替代方案：

function chooseYearAlt() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("ADJUSTMENTS");
var yrCell = sheet.getRange("A1").getValue();
var allRange = sheet.getRange("A3:A");
sheet.unhideRow(allRange);
if (yrCell != "ALL" && yrCell != "all"){
var lastRow = sheet.getLastRow();
var year
for (var i = 3;i<lastRow;i++) {
year = sheet.getRange(i, 18).getValue(); // The 18 means column R
if (year != yrCell) {sheet.hideRow(sheet.getRange(i, 1));}
}
}
}

但我的建议是创建一个新工作表并在 A1 中使用此公式：

=IF(OR(ADJUSTMENTS!A1="ALL",ADJUSTMENTS!A1="all",ISBLANK(ADJUSTMENTS!A1)),QUERY(ADJUSTMENTS!A:Z,"SELECT *"),QUERY(ADJUSTMENTS!A:Z,"SELECT * WHERE R = "&ADJUSTMENTS!A1&""))

您可以对团队销售表使用相同的逻辑。

PS：如果您使用该公式，请确保在两个调整中都替换"Z"！A：Z 与工作表的最后一列调整，以便它带来整个工作表。