I am trying to create a script that runs across 11 different sheets throughout my google sheet. This function essentially will just remove the current filter (if applicable) and create a filter with updated information. The problem I am running into is that my logic to only remove the filter if it does not equal null doesn’t seem to be working because I keep getting an “Exception: you can’t create a filter in a sheet that already has a filter” however my filter.remove
should have already removed it.I have been staring at this for hours, some help would be really appreciated. Here is a copy of my script, its a bit long, preferably I would also like to shorten it if possible.
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);
};