6

I want to write a script that removes all filters in a spreadsheet once a day.

I haven't been able to find any good documentation on how to handle filtering in Google Apps Script.

I'd also be open to doing this using the Python API if it was possible that way.

pnuts
  • 58,317
  • 11
  • 87
  • 139
JStew
  • 437
  • 3
  • 7
  • 19

9 Answers9

26

There is a new super-easy possibility which I discovered by using the record macro function:

spreadsheet.getActiveSheet().getFilter().remove();

NB the "easy way" described before is not working anymore since moveTo now moves also the filters

Davide Carlo Cazzaniga
  • 261
  • 3
  • 2
  • 1
    you can also do this per sheet. ``` var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheetByName("ToEmail1"); sheet.getFilter().remove(); ``` – Francisco Cortes Jan 13 '20 at 14:38
9

This answer is outdated, please see @Davide's answer

It IS possible, with a little "cheating" :)

(Updated answer: there is a new Google service that allows it, see number 2)

1 - Easy way

Well, I managed to do it this way:

var row = 1 //the row with filter 
var rowBefore = row 

//insert a row before the filters
Sheet.insertRowBefore(row);
row++; 

//move the filter row to the new row (this will move only content)
var Line = Sheet.getRange(row + ":" + row); 
Line.moveTo(Sheet.getRange(rowBefore + ":" + rowBefore)); 

//delete the old row, which contains the filters - this clears the filters      
Sheet.deleteRow(row); 

//if the row was frozen before deletion, freeze the new row    
Sheet.setFrozenRows(rowBefore);

2 - Using the Sheets service:

(Copied from https://issuetracker.google.com/issues/36753410, comment #172)

function clearFilter() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var ssId = ss.getId();
    var sheetId = ss.getActiveSheet().getSheetId();
    var requests = [{
        "clearBasicFilter": {
        "sheetId": sheetId
        }
    }];
    Sheets.Spreadsheets.batchUpdate({'requests': requests}, ssId);
}

(Copied from @AndreLung's comment below)

Go to Resources, then "Advanced Google Services", locate "Google Sheets API and enable. also, go to console.cloud.google.com/apis/dashboard and enable "Sheets API"

3 - Hard way: (kept it here because I wrote it before thinking a little more)

1 - Remove first line (or line where filter buttons are)

Sheet.deleteRow(1);

2 - Insert it again :)

Sheet.insertRowBefore(1);

3 - Set its headers

Sheet.getRange("A1").setValue("Hi there");
Sheet.getRange("B1").setValue("Here I am Again with no filter");

//alternatively
var LineVals = Sheet.getRange("1:1").getValues(); 
LineVals[0][0] = "Hi there";
LineVals[0][1] = "Here I am again";
LineVals[0][2] = "With no filters";
Sheet.getRange("1:1").setValues(LineVals);
//getValues is meant to keep the array exactly the size of the row

4 - Set the line color again:

//Cell color                                   
Sheet.getRange("1:1").setBackground('#ff3300');

5 - Set font styles:

 //I didn't test these ones....
 Sheet.getRange("1:1").setFontColor....
 Sheet.getRange("1:1").setFontFamily....     
 Sheet.getRange("1:1").setFontSize....
 //Actually there are a lot of font options available....

6 - If it was frozen before, freeze it again:

Sheet.setFrozenRows(1);

7 - And finally, if they had NamedRanges, consider naming the entire column instead of a single cell, that will preserve your names unharmed.

Community
  • 1
  • 1
Daniel Möller
  • 84,878
  • 18
  • 192
  • 214
  • 1
    A little help for option 2: go to Resources, then "Advanced Google Services", locate "Google Sheets API and enable. also, go to https://console.cloud.google.com/apis/dashboard and enable "Sheets API" – AndreLung Jun 29 '18 at 14:23
  • Even easier now with ".getFilter()" and ".remove()". https://developers.google.com/apps-script/reference/spreadsheet/filter – Mario Nov 22 '22 at 04:13
2

Edit: Since I wrote this answer way back in 2014, The tracked issue in the link has since been resolved with new methods to manipulate filters. My answer is now obsolete, but I keep getting downvotes for it so.... thanks. I'm aware, but leaving the initial answer for posterity.

I don't believe it's possible to manipulate the filters in Google Sheets programmatically. You can find an open issue for this here. Star it to register your interest, but in the meantime I don't believe there's a solution.

HDCerberus
  • 2,113
  • 4
  • 20
  • 36
1

Daniel, thank you very very much :-)

This is my implementation:

function delFVws_test(){

    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sh = ss.getActiveSheet();

    delFVws(sh, 1); 
}

function delFVws(sh, row){

    var rowp = row;
    sh.insertRowBefore(row++);   
    sh.getRange(row + ':' + row).copyTo(sh.getRange(rowp + ':' + rowp));
    sh.deleteRow(row); 
}

Preserves frozen lines and using copyTo instead of moveTo ensures not to move the filters from line to line.

jpp
  • 194
  • 1
  • 7
  • By the way, this removes filters **and** stored _filter views_ as well (my original goal) – jpp Sep 15 '16 at 14:48
1

I know it's an old thread, but for those that ran into this while searching google for an answer, the following code will remove all filters 

    SpreadsheetApp.getActiveSheet().getFilter().remove();

Or to check if the sheet has a filter

if (!SpreadsheetApp.getActiveSheet().getFilter()) {  //returns null if fitler does not exist
    <what to do if there is no filter>
}
else {
    <what to do if there is a filter>
}
nirazul
  • 3,928
  • 4
  • 26
  • 46
Kevin
  • 21
  • 1
  • 2
    Any difference or distinction you want to make between this other answer? https://stackoverflow.com/a/50574261/9337071 – tehhowch Jul 31 '18 at 16:05
0

Code based filter

This adds a filter using code, but it is not the same one that you add with a menu. You could add your own filter menu, that way you have control. Google App Script / Spreadsheet - How to get filter criteria?

Menu based filter

As @HDCerberus says, there does not look to be a way to remove the "menu based filter".

Python API

Python API is fine grain, but has very few features, so there is no way to even hide a row never mind handle filters.

Community
  • 1
  • 1
eddyparkinson
  • 3,680
  • 4
  • 26
  • 52
0

This is how I implemented Daniel's code

Changed moveTo to copyTo and used the options - Contents only. Also removed frozen rows if any and added the shtRows.setFrozenRows(1) after my code was executed.

var shtRows = mySS.getSheetByName("Rows");
var row = 1 //the row with filter 
var rowBefore = row;
shtRows.setFrozenRows(0); // remove frozen
shtRows.insertRowBefore(row); //inserts a line before the filter
row++;
var Line = shtRows.getRange(row + ":" + row); //gets the filter line
Line.copyTo(shtRows.getRange(rowBefore + ":" + rowBefore), {contentsOnly:true}); //move to new line
shtRows.deleteRow(row); //deletes the filter line - this clears the filter

// a lot of code executing... and then ...
shtRows.setFrozenRows(rowBefore); //if row was frozen before, freeze it again
Meir Gabay
  • 2,870
  • 1
  • 24
  • 34
0
<!-- language: lang-js -->
// 4. Clear any filters currently applied to this first sheet
// https://developers.google.com/apps-script/reference/spreadsheet/sheet#getFilter()
// https://developers.google.com/apps-script/reference/spreadsheet/filter#remove()
var currentFilter = sheet.getFilter();
if ( currentFilter !== null ) {
    console.log( 'a filter currently exists, clearing...' );
    sheet.getFilter().remove();
}
Patrick Lewis
  • 83
  • 6
0

why not using a removeColumnFilterCriteria Method, lets say we have 2 columns... the code should by something like :

function _clearFilter(sheet,headers) {
  for (var i in headers){
    var filter = sheet.getFilter().removeColumnFilterCriteria(Number(i)+1);
  }
}

function clearFilter() {
  var NUM_COLUMNS = 2;
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getActiveSheet();
  var headers = sheet.getRange(1, 1,1,NUM_COLUMNS).getValues(); //or in better way to get the header columns
  _clearFilter(sheet,headers[0]);
}
shlomoglik
  • 21
  • 1
  • 1
  • 4