This article explains how Google Sheets slicers can be reset using a few lines of Apps Script code. Two different approaches using (1) the Spreadsheet Service and (2) the Advanced Sheets Service that wraps the Google Sheets API are shown, and some shortcomings and peculiarities of both methods are discussed.
Table of Contents
A tale of (more than) two slicers
Any Google spreadsheet conceals three different filter tools tucked inside its Data menu.
Slicers, the last tool to arrive to the sheetparty (August 2019), empower spreadsheet creators to build interactive dashboards that can (notwithstanding some pain points) mimic somewhat the look & feel of those created with dedicated data visualization tools such as the ubiquitous Google Data Studio.
But, in this article, we are neither discussing how slicers work (there already are excellent resources, such as this post by Ben Collins), nor explaining in detail in which ways they differ from filter and filter views (a well-covered topic in the Docs Editors Help).
I am of the opinion that slicers in Google Sheets are just an inch away from greatness... But let me elaborate on this maybe striking statement.
Slicers are extremely convenient UI elements that enrich spreadsheets and foster user interaction, but they are not devoid of some annoying quirks. And I can't help but think that this is the direct consequence of a not enough polished, maybe rushed, implementation. A couple of issues, to mention but a few:
- When multiple active slicers in a worksheet filter a given group of cells, they stubbornly resist any attempt by the user to change the filtered data range. A rather non-obvious trick has to be used to overcome this.
- Sometimes, for unknown reasons, slicers in some spreadsheets default to certain values upon loading... even after being deleted and recreated! Setting current filters as default does not solve it. This circumstance has been documented here and also here, and I've witnessed this most annoying behaviour myself in at least one spreadsheet, so apparently there is something going on that prevents the slicer experience to be absolutely smooth and painless.
These, together with a particular limitation that also rear its ugly head as soon as we resort to our beloved Apps Script voodoo to manage slicers, make things a little bit more complicated than they should be.
Stay with me for the next few minutes if you are eager to meet the whys and hows.
Problem statement, sliced!
Why bulk resetting sliders is a good thing?
Well, the answer is quite straightforward, in my opinion, but let's put the record straight.
Slicers can be deactivated by simply pushing their Cancel button. This is swift and convenient.
But when a large number of slicers come into play and the dataset demands frequent filtering using all or of many of them, having to perform a two-digit number of clicks with the mouse to see all your data is not the funniest of things.
Consequently, we pursue a way to reset all slicers in the current sheet of a Google spreadsheet at the flick of a single button.
🎯 More specifically, we'll build an Apps Script function (well, they will be two, in the end) capable of performing this task when invoked from a menu item or triggered by clicking on an image or drawing shown inside the sheet.
Good, we already know the whys. Let's head into the hows now.
Two Apps Script functions to tame slicers
My posts, especially the GAS related ones, usually develop the solution to some problem or situation gradually and only show the meat and bones of the code at the end.
This time I'll do just the opposite, for a change 😅.
I would like to introduce two spreadsheet-bound Apps Script functions that you can find in this GitHub repository.
These two functions essentially accomplish the same task, albeit with varying degrees of effectiveness and, above all, a massive difference in execution time due to the distinct methods that they use.
|Code.gs||Uses the the more common Spreadsheet Service.|
|Code_API.gs||Uses the super-duper Sheets API (v4) through its Advanced Sheets Service GAS wrapper.|
🙆♂️ Which one should you use? Long story short: the API one.
You can also grab the sample Google Spreadsheet used to capture the above animated GIF here:
Well, if this is the only reason you came here for, that's it, you can take your functions and call it a day. But if you are eager to know more about their actual implementation, bear with me and keep reading!
Strategy & code break-down
The Spreadsheet Service way 🦆
The way of the API 🐯
Final thoughts and next steps