如果过滤器已经存在，我该如何删除过滤器，并在谷歌脚本中创建新的过滤器

Question

我正试图创建一个脚本，在我的谷歌工作表中运行11个不同的工作表。该函数本质上只是删除当前过滤器(如果适用(，并创建一个包含更新信息的过滤器。我遇到的问题是，我只在过滤器不等于null的情况下删除过滤器的逻辑似乎不起作用，因为我一直得到一个"0"；例外情况：不能在已经有过滤器的工作表中创建过滤器；然而，我的filter.remove应该已经删除了它。我已经盯着它看了几个小时了，如果能提供一些帮助，我将不胜感激。这是我的一份剧本，有点长，如果可能的话，我最好也缩短一下。

function Filter_All() {
///Set Variables
var spreadsheet = SpreadsheetApp.getActive();
var filter = spreadsheet.getActiveSheet().getFilter();
var criteria = SpreadsheetApp.newFilterCriteria()
var daycount = 0
var MILLIS_PER_DAY = 1000*60*60*24*daycount
var now = new Date();
var filterdate = new Date(now.getTime() - MILLIS_PER_DAY);
///Select "MTD Assignments"
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('MTD-Assignments'), true);
///Remove filter
if (filter !== null) {
filter.remove();
}
///Create Filter
spreadsheet.getActiveSheet().getRange('A:N').createFilter();
criteria = SpreadsheetApp.newFilterCriteria()
.whenDateAfter(filterdate)
.build();
spreadsheet.getActiveSheet().getFilter().setColumnFilterCriteria(5, criteria);
///Select "Torian"
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Torian'), false);
///Remove filter
if (filter !== null) {
filter.remove();
return;
}
///Create Filter
spreadsheet.getActiveSheet().getRange('A:N').createFilter();
criteria = SpreadsheetApp.newFilterCriteria()
.whenDateAfter(filterdate)
.build();
spreadsheet.getActiveSheet().getFilter().setColumnFilterCriteria(5, criteria);
///Select "Luke"
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Luke'), false);
///Remove filter
if (filter !== null) {
filter.remove();
return;
}
///Create Filter
spreadsheet.getActiveSheet().getRange('A:N').createFilter();
criteria = SpreadsheetApp.newFilterCriteria()
.whenDateAfter(filterdate)
.build();
spreadsheet.getActiveSheet().getFilter().setColumnFilterCriteria(5, criteria);
///Select "Charity"
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Charity'), false);
///Remove filter
if (filter !== null) {
filter.remove();
return;
}
///Create Filter
spreadsheet.getActiveSheet().getRange('A:N').createFilter();
criteria = SpreadsheetApp.newFilterCriteria()
.whenDateAfter(filterdate)
.build();
spreadsheet.getActiveSheet().getFilter().setColumnFilterCriteria(5, criteria);
///Select "Tenisha"
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Tenisha'), false);
///Remove filter
if (filter !== null) {
filter.remove();
return;
}
///Create Filter
spreadsheet.getActiveSheet().getRange('A:N').createFilter();
criteria = SpreadsheetApp.newFilterCriteria()
.whenDateAfter(filterdate)
.build();
spreadsheet.getActiveSheet().getFilter().setColumnFilterCriteria(5, criteria);
///Select "Shannon"
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Shannon'), false);
///Remove filter
if (filter !== null) {
filter.remove();
return;
}
///Create Filter
spreadsheet.getActiveSheet().getRange('A:N').createFilter();
criteria = SpreadsheetApp.newFilterCriteria()
.whenDateAfter(filterdate)
.build();
spreadsheet.getActiveSheet().getFilter().setColumnFilterCriteria(5, criteria);
///Select "Siley"
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Siley'), false);
///Remove filter
if (filter !== null) {
filter.remove();
return;
}
///Create Filter
spreadsheet.getActiveSheet().getRange('A:N').createFilter();
criteria = SpreadsheetApp.newFilterCriteria()
.whenDateAfter(filterdate)
.build();
spreadsheet.getActiveSheet().getFilter().setColumnFilterCriteria(5, criteria);
///Select "Macheera"
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Macheera'), false);
///Remove filter
if (filter !== null) {
filter.remove();
return;
}
///Create Filter
spreadsheet.getActiveSheet().getRange('A:N').createFilter();
criteria = SpreadsheetApp.newFilterCriteria()
.whenDateAfter(filterdate)
.build();
spreadsheet.getActiveSheet().getFilter().setColumnFilterCriteria(5, criteria);
///Select "Nia"
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Nia'), false);
///Remove filter
if (filter !== null) {
filter.remove();
return;
}
///Create Filter
spreadsheet.getActiveSheet().getRange('A:N').createFilter();
criteria = SpreadsheetApp.newFilterCriteria()
.whenDateAfter(filterdate)
.build();
spreadsheet.getActiveSheet().getFilter().setColumnFilterCriteria(5, criteria);
///Select "Alicia"
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Alicia'), false);
///Remove filter
if (filter !== null) {
filter.remove();
return;
}
///Create Filter
spreadsheet.getActiveSheet().getRange('A:N').createFilter();
criteria = SpreadsheetApp.newFilterCriteria()
.whenDateAfter(filterdate)
.build();
spreadsheet.getActiveSheet().getFilter().setColumnFilterCriteria(5, criteria);
///Select "Shannon/Johan/Meybeling"
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Shannon/Johan/Meybeling'), false);
///Remove filter
if (filter !== null) {
filter.remove();
return;
}
///Create Filter
spreadsheet.getActiveSheet().getRange('A:N').createFilter();
criteria = SpreadsheetApp.newFilterCriteria()
.whenDateAfter(filterdate)
.build();
spreadsheet.getActiveSheet().getFilter().setColumnFilterCriteria(5, criteria);
};

Answer 1

错误的原因可能如下：

在第4行，将变量filter的值指定为工作表"MTD Assignments"的筛选器。在第14行中，从工作表中删除该过滤器。在第22行，将活动图纸设置为"Torian"，然后在第24行再次调用filter.remove()。但是，即使更改了活动工作表，也不会将filter变量更改为指向新活动工作表的筛选器：filter仍然指向"MTD分配"的筛选器。因此，第24行不会从"Torian"表中移除过滤器。为此，您首先需要通过重复行来重新分配filter的值

filter = spreadsheet.getActiveSheet().getFilter()

在将活动工作表设置为"Torian"之后，再次调用filter.remove()之前。

类似这样的东西：

///Set Variables
var spreadsheet = SpreadsheetApp.getActive();
var filter = spreadsheet.getActiveSheet().getFilter();
var criteria = SpreadsheetApp.newFilterCriteria()
var daycount = 0
var MILLIS_PER_DAY = 1000*60*60*24*daycount
var now = new Date();
var filterdate = new Date(now.getTime() - MILLIS_PER_DAY);
///Select "MTD Assignments"
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('MTD-Assignments'), true);
///Remove filter
if (filter !== null) {
filter.remove();
}
///Create Filter
spreadsheet.getActiveSheet().getRange('A:N').createFilter();
criteria = SpreadsheetApp.newFilterCriteria()
.whenDateAfter(filterdate)
.build();
spreadsheet.getActiveSheet().getFilter().setColumnFilterCriteria(5, criteria);
///Select "Torian"
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Torian'), false);
filter = spreadsheet.getActiveSheet().getFilter();  // This is the line to be added.
///Remove filter
if (filter !== null) {
filter.remove();
return;
}
///Create Filter
spreadsheet.getActiveSheet().getRange('A:N').createFilter();
criteria = SpreadsheetApp.newFilterCriteria()
.whenDateAfter(filterdate)
.build();
spreadsheet.getActiveSheet().getFilter().setColumnFilterCriteria(5, criteria);

其他床单也是如此。

Answer 2

试试这个：

function Filter_All() {
// set variables
var spreadsheet = SpreadsheetApp.getActive();
var daycount = 0
var MILLIS_PER_DAY = 1000*60*60*24*daycount
var now = new Date();
var filterdate = new Date(now.getTime() - MILLIS_PER_DAY);
var filterCriteria = SpreadsheetApp.newFilterCriteria()
.whenDateAfter(filterdate)
.build();
// reset filter for each sheet in loop
var sheetNamesWithFilter = ['MTD-Assignments', 'Torian']; // TODO fill with your sheet names
for (var i = 0; i < sheetNamesWithFilter.length; i++)
{
var sheetName = sheetNamesWithFilter[i];
var sheet = spreadsheet.getSheetByName(sheetName);
// remove filter
var filter = sheet.getFilter();
if (filter !== null)
{
filter.remove();
}
// create filter
sheet.getRange('A:N').createFilter().setColumnFilterCriteria(5, filterCriteria);
}
}

基本上，让脚本工作所需的就是使用循环，并对循环中的每张工作表应用相同的过滤器更改。它被认为是重复相同命令的最佳实践，也可以帮助您消除无意的错误，就像您错误地忘记为下一次迭代更新filter变量时一样。

希望这能有所帮助。