Delete all filer criterias but preserve filter

问题:

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.

回答1:

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 :

• Advanced Google Services : https://developers.google.com/apps-script/guides/services/advanced
• Sheets API v4: https://developers.google.com/sheets/api/

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

回答2:

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

• No API
• API