Script in google sheet to clear, but not remove filter

Question

I am completely new in writing scripts for google sheets, so I was hoping some of you could help/guide me a little bit.

So Ideally, I want a script to clear (not remove) ALL filters in my sheet. This is, however, complicated for me to do (If some of you have such a script, I would LOVE to see it :) )

Instead, I made this one (Used recorder):

function Clear_Filter() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('A5').activate();
  spreadsheet.getActiveSheet().getFilter().removeColumnFilterCriteria(1);
  spreadsheet.getRange('B5').activate();
  spreadsheet.getActiveSheet().getFilter().removeColumnFilterCriteria(2);
  spreadsheet.getRange('C5').activate();
  spreadsheet.getActiveSheet().getFilter().removeColumnFilterCriteria(3);
  spreadsheet.getRange('G5').activate();
  spreadsheet.getActiveSheet().getFilter().removeColumnFilterCriteria(7);
  spreadsheet.getRange('J5').activate();
  spreadsheet.getActiveSheet().getFilter().removeColumnFilterCriteria(10);
  spreadsheet.getRange('M5').activate();
  spreadsheet.getActiveSheet().getFilter().removeColumnFilterCriteria(13);
};

So my filter is set in Row 5. First I made the above for all columns (I had 20), but the problem is, that the code is very slow :( So now I am using the columns, that I use the most, when filtering, but the code is still slow. Well the worst thing is, that the code is running one column at a time (which we see in the code), and when the code is finish, I end up in the last column.

Can I do something? I dont want my sheet window keep turning right, when I run the code, and then end up in column M.

I will appreciate any help! Thanks

Answer 1

Here is mine. The function does not remove filters. Instead, it clears them as requested.

function clearFilter(sheet) {
  sheet = SpreadsheetApp.getActiveSheet(); //for testing purpose only
  var filter = sheet.getFilter();
  if (filter !== null) {  // tests if there is a filter applied
    var range = filter.getRange(); // prevents exception in case the filter is not applied to all columns
    var firstColumn = range.getColumn();
    var lastColumn = range.getLastColumn();
    for (var i = firstColumn; i < lastColumn; i++) {
      filter.removeColumnFilterCriteria(i);
    }
  Logger.log('All filters cleared')
  }
  else {Logger.log('There is no filter')}
}

Answer 2

Reset filters criterea + sort by first column (as default state). And add this action to main menu.

/** @OnlyCurrentDoc */

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [
    {name: "Reset filters", functionName: "ResetFilters"},
  ];
  ss.addMenu("Custom actions", menuEntries); // add to main menu
}

function ResetFilters() {
  var spreadsheet = SpreadsheetApp.getActive();
  var lastColumn = spreadsheet.getActiveSheet().getLastColumn();
  var filter = spreadsheet.getActiveSheet().getFilter();
  var criteria = SpreadsheetApp.newFilterCriteria().build(); 
  for (var i = 1; i <= lastColumn; i++) {
    filter.setColumnFilterCriteria(i, criteria);
  }
  filter.sort(1, true); // remove this line for disable setting of sorting order
};

Answer 3

To clear all

`function turnOffFilter(sheet) {
        for (var index = 1; index < sheet.getLastColumn(); index++) {
          if (sheet.getFilter().getColumnFilterCriteria(index)) {
            sheet.getFilter().removeColumnFilterCriteria(index);
          }
        }
    }`

Answer 4

It seems that the answers (e.g. proposed by Birmin) work fine but the script is painfully slow. I find it much faster to reapply the filter:

function clearFilter(sheet) {
  sheet = SpreadsheetApp.getActiveSheet(); //for testing purpose only
  var filter = sheet.getFilter();
  if (filter !== null) {  // tests if there is a filter applied
    var range = filter.getRange();
    filter.remove();
    range.createFilter();
  Logger.log('All filters cleared')
  }
  else {Logger.log('There is no filter')}
}

Answer 5

I, have you tried :

function Clear_Filter() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getFilter().remove();
}