1

I want to clear all filter rules, but leave the filter itself.

Is there a direct and fast way to do it?

The code I've found is:

var filter = sheet.getFilter();

It gets the filter object, and the number of options I have with filter# is limited.

Note: filter.remove() removes this filter, but I need to preserve it.

Max Makhrov
  • 17,309
  • 5
  • 55
  • 81

3 Answers3

3

Option #1. Slow [~16 sec], no API needed

I've tried this code:

function deleteFilterCriterias(sheet)
{

 var filter = sheet.getFilter(); 
  if (!(filter)) { return -1; }
  
  var rangeF = filter.getRange();
  var cols = rangeF.getWidth();
  var col = rangeF.getColumn();
  
  for (var i = col; i <= cols; i++)
  {  
    // remove filter criteria for each column
    filter.removeColumnFilterCriteria(i)    
  }  
  return 0;  
}

But it seems clunky to me.

Option #2. Fast [~0.3 sec], need Sheets API

After the answer by @Tanaike, I've tried the Sheets API.

My code to reset filter on one sheet is:

function deleteFilterCriterias2(sheet)
{
  
  var ssId = sheet.getParent().getId();
  
  var range = sheet.getFilter().getRange();  

  var rowStart = range.getRow() - 1;
  var colStart = range.getColumn() - 1;
  
  // settings to reset filter
  var filterSettings = {
    "range": {
      "sheetId": sheet.getSheetId(),
      "startRowIndex": rowStart,
      "endRowIndex": range.getHeight() + rowStart,
      "startColumnIndex": colStart + colStart,
      "endColumnIndex": range.getWidth()
    }
  }; 

  var requests = [{
    "setBasicFilter": {
      "filter": filterSettings
    }
  }];
  
  // api request
  Sheets.Spreadsheets.batchUpdate({'requests': requests}, ssId);
    
}

This one is much faster.

References

Community
  • 1
  • 1
Max Makhrov
  • 17,309
  • 5
  • 55
  • 81
2

You want to remove only the criteria of basic filters while the basic filter is not removed . If my understanding is correct, how about using Sheets API? The sample script is as follows. When you use this script, please enable Sheets API at Advanced Google Services and API console.

Enable Sheets API v4 at Advanced Google Services

  • On script editor
    • Resources -> Advanced Google Services
    • Turn on Google Sheets API v4

Enable Sheets API v4 at API console

  • On script editor
    • Resources -> Cloud Platform project
    • View API console
    • At Getting started, click Enable APIs and get credentials like keys.
    • At left side, click Library.
    • At Search for APIs & services, input "sheets". And click Google Sheets API.
    • Click Enable button.
    • If API has already been enabled, please don't turn off.

If now you are opening the script editor with the script for using Sheets API, you can enable Sheets API for the project by accessing this URL https://console.cloud.google.com/apis/library/sheets.googleapis.com/

Sample script :

var id = SpreadsheetApp.getActiveSpreadsheet().getId();
var filters = Sheets.Spreadsheets.get(id, {fields: "sheets/basicFilter"});
var resource = {requests: filters.sheets.filter(function(e){return Object.keys(e).length}).map(function(e){return {setBasicFilter: {filter: {range: e.basicFilter.range}}}})};
Sheets.Spreadsheets.batchUpdate(resource, id);

Note :

  • This sample removes only the criteria by not giving the parameters for the criteria, while the basic filter is not removed.
  • In this sample, the criteria of all sheets in the spreadsheet are removed. So if you want to remove the criteria for the specific sheet, please modify this sample.

References :

If this was not what you want, I'm sorry.

Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • 1
    Hi, @Tanaike, Thank you! I've tried to modify the code to use it with a single sheet. You may see it in my answer. It works much faster, but I wonder if there's a shorter version of the code for such a simple task. I think it could possible with only one API call, your original code uses Sheets# twice. – Max Makhrov May 31 '18 at 07:09
  • @Max Makhrov Thank you for adding more information and investigation. Your modified script is nice for reducing the process cost. I think that when it is a library, it will be useful for users. – Tanaike May 31 '18 at 07:40
1

Max's answer is good. However, a faster option without API is this -- simply remove the filter, and then add it back without any criteria:

  var sheet = SpreadsheetApp.getActiveSheet();
  var currentFilter = sheet.getFilter();
  var filterRange = currentFilter.getRange();
  currentFilter.remove();

  // add filter back
  filterRange.createFilter();
Daniel Hunter
  • 11
  • 1