How do I remove filters if filter already exists and create new filter in Google Scripts-CodePudding

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);
};

CodePudding user response：

The reason for your error may be as follows:

On line 4, you assign the value of the variable filter as the filter for the sheet 'MTD-Assignments'. In line 14, you remove that filter from the sheet. On line 22, you set the active sheet as 'Torian', and then on line 24 you again call filter.remove(). However, even though you've changed the active sheet, this does not change the filter variable to point at the filter for the new active sheet: filter still points at the filter for 'MTD-Assignments'. So line 24 does not remove the filter from the sheet 'Torian'. To do so, you first need to re-assign the value of filter by repeating the line

 filter = spreadsheet.getActiveSheet().getFilter()

after you've set the active sheet to 'Torian' and before you call filter.remove() again.

Something like this:

///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);

And similarly for your other sheets.

CodePudding user response：

Try this:

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);
  }
}

Basically all you need to get your script to work is use loop and apply same filter changes for each sheet in a loop. It is considered best practice with repeating same commands, and also can help you to get rid of unintentional errors like you had, when you mistakenly forgot to update filter variable for your next iteration.

Hopefully this helps.