Mass Delete Filter Views in Google Sheets

Question

I found this old thread, but cannot seem to get any of the methods to work.

Issue: I have a spreadsheet that dozens of people use, and over time there have been >200 Filter View's created. I want to mass delete ALL Filter View's from one sheet in the workbook.

I've tried this method after enabling Google Sheets API. It runs, but alas, all the Filter View's remain.

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);
}

Thanks All

Image of Filter Views:

Image of Filter Views

score 3 · Answer 1 · answered Feb 11 '19 at 17:04

3

Issue:

You're issuing a clear filter request, which only cleares the current filter

Solution:

You must issue a deleteFilterView request instead

Sample Code:

function delFilterViews() {
  var ssId = SpreadsheetApp.getActive().getId();
  Sheets.Spreadsheets.batchUpdate(
    {
      requests: Sheets.Spreadsheets.get(ssId, {
        ranges: 'Sheet1', //Sheet in which filterviews are present
        fields: 'sheets/filterViews/filterViewId',
      }).sheets[0].filterViews.map(function(e) {
        return { deleteFilterView: { filterId: e['filterViewId'] } }; //create a new delete filter view request for each filter view present in sheet1
      }),
    },
    ssId
  );
}

References:

answered Feb 11 '19 at 17:04

TheMaster

45,448
6
62
85

Thank you so much! This worked perfectly and cleared out all of the custom filter views in one go! – Jesse Ericksen Feb 11 '19 at 19:28
This will delete all the filterviews in the sheet. How can I delete only the filters that doesn't have a name set? e.g. - Some regex like `Filter*` ? – Dave Apr 11 '20 at 04:44
@Dave Yes. You'd use [Array.filter](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/filter) and then [`.map`](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/map) on `filterViews` instead of just `.map`. If you're unable to execute after reasonable effort, ask a new question – TheMaster Apr 11 '20 at 05:30

score 1 · Answer 2 · answered Aug 25 '21 at 05:39

I found the code by the Master hard to understand because it's not easy to see what's going on in the array which is being sent to the API for deletion, so I rewrote it and added some extra functionality. Here are 2 functions. One deletes all unnamed filters in one go on the active sheet. The other allows the user to go through and delete named filters one by one using the messagebox prompt.

function delUnusedFilterViews() {

  var ssId = SpreadsheetApp.getActive().getId();
  var sheetName = SpreadsheetApp.getActiveSheet().getName();
  SpreadsheetApp.getActiveSpreadsheet().toast('Removing unnamed Filters from sheet: ' + sheetName);
  var allFilters = Sheets.Spreadsheets.get(ssId).sheets[0].filterViews;
  var filterArr =[];
  for (var i in allFilters) {
    var currFilter = allFilters[i];
    var filterName = currFilter.title;
    var currFilterId = currFilter.filterViewId;
    if (/Filter [0-9]/.test(filterName)) filterArr.push({ deleteFilterView: { filterId: currFilterId } })
  }
   Sheets.Spreadsheets.batchUpdate({
      requests: filterArr
    },
      ssId
    )
  Browser.msgBox("All Done. You need to reload the sheet to see the filters have been deleted");
}

function delFilterViewsOneByOne() {
  var ssId = SpreadsheetApp.getActive().getId();
  var sheetName = SpreadsheetApp.getActiveSheet().getName();
  var allFilters = Sheets.Spreadsheets.get(ssId).sheets[0].filterViews;
  for (var i in allFilters) {
    var currFilter = allFilters[i];
    var filterName = currFilter.title;
    var currFilterId = currFilter.filterViewId;
    var userResponse = Browser.msgBox("Remove filter named '" + filterName + "'?", Browser.Buttons.YES_NO_CANCEL);
    if (userResponse == 'cancel') return;
    if (userResponse == 'no') continue;
    Sheets.Spreadsheets.batchUpdate({
      requests: [{ deleteFilterView: { filterId: currFilterId } }]
    },
      ssId
    )
  }
  Browser.msgBox("All Done. You need to reload the sheet to see the filters have been deleted");
}

Personally I added these functions to the main menu "onOpen" so they can be used on any sheet within the spreadsheet. It's quite useful functionality to have available everywhere at all times.