0

I'm struggling to find a solution for the following problem:

  • I have a lot of Google Sheets with a lot of columns and I use Filter Views to narrow down the data, by filtering multiple columns.

The problem is when I want to "unfilter" all the applied filters (not delete the filter view): I need to go one by one (column by column) and click the filter icon and "Select all". And I must do this for all the columns... And closing the filter view is not the solution because when I reopen it the columns are still filtered as before, so I need to do it manually. And duplicating filter views is also not a good solution.

I'm looking for a script that would "unfilter" the data in all columns at once, without closing or deleting the filter view. Basically, it needs to mimic my manual "unfiltering" of all columns at once when clicking on a new "Unfilter" option in the Google Sheets menu.

If someone can help I'd be very grateful. And thanks in advance.

CMB
  • 4,950
  • 1
  • 4
  • 16
Nuno Miguel
  • 67
  • 5
  • You can use [getFilter()](https://developers.google.com/apps-script/reference/spreadsheet/range#getFilter()) to fetch each filter per range then remove by using [removeColumnFilterCriteria()](https://developers.google.com/apps-script/reference/spreadsheet/filter#removeColumnFilterCriteria(Integer)). If you need more help, please edit your question and include a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example). – CMB Nov 09 '21 at 19:37
  • @CarlosM I believe OP is using "filter view" and not the filter retrieved by ``getFilter()`` – TheMaster Nov 09 '21 at 19:48
  • Probably possible through sheets api [UpdateFilterViewRequest](https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request#UpdateFilterViewRequest) and changing the hidden values for each column. I have a [deleteFilterViewRequest](https://stackoverflow.com/questions/54632698/mass-delete-filter-views-in-google-sheets/54635599) sample, which may help, if you're trying. – TheMaster Nov 09 '21 at 20:02
  • Thanks for your imput. I've checked a lot of the related thread both in Stackoverflow and elsewhere (including the one @TheMaster mentioned). However, I do not intend to delete filters nor filter views. And I'm talking about filter views (that affect the user viewing the spreadsheet only), not filters (that affect all users). The point here is to avoid having to uncheck all the filters in a (filter view) manually. I need a script that unchecks all the filters in all columns at once. – Nuno Miguel Nov 10 '21 at 07:15
  • I only added that as a sample. You should try writing a script using updateFilterView request. – TheMaster Nov 10 '21 at 13:41

0 Answers0