How can I remove all filters in a Google Spreadsheet?

Question

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.}

Answer 1

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

Answer 2

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.