Google Sheets v4 API explorer.

Bulk reset slicers in a Google Spreadsheet, the Apps Script way

TL;DR

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.

Data menu in Google Sheets showing three different filtering tools.
Filters, filter views and... slicers!

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.

Four slicers in a spreadsheet.
Slicers can filter rows of data by values or a rich set of conditions (not by color, though, unlike filters and filter views).

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

Features of filters, filter views and slicers compared.
Distinctive features of filters, filter views and slicers (source: 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.

Animation that shows how the cancel button resets an slicer.
Is that me or aren't there enough of those UI visual cues to suggest that the Cancel button will reset the slider?

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.

A spreadsheet with seven slicers.
A worksheet with a lot of slicers, as seen in this post about using Google Sheets to manage the ICT inventory of a school.

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.

Reset buttons for our slicers.
Two nice reset buttons to kill the filters applied by those four slicers.

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.

GitHub repo card.
https://github.com/pfelipm/resetslicers

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.

FileFunctionMethod
Code.gs
resetSheetSlicers()
Uses the the more common Spreadsheet Service.
Code_API.gs
resetSheetSlicersApi()
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.

Animation showing the use of the reset buttons.
Demo of both slicer-resetting GAS functions.

You can also grab the sample Google Spreadsheet used to capture the above animated GIF here:

👉 Reset slicers demo spreadsheet 👈

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

 

 

 


 

Comentarios