Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.2k views
in Technique[技术] by (71.8m points)

google apps script - 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.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...