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

TL;DR

This article explains how the filtering criteria of Google Sheets slicers can be reset using a few lines of Apps Script code. Two different approaches are show. The first method uses the Spreadsheet Service, while the second one draws upon the Google Sheets API using the Advanced Sheets Service. Additionally, some shortcomings and peculiarities of both methods are highlighted.

A tale of (more than) two slicers

Google Sheets provides three different filter tools that are available from the Data menu.

Slicers, the last of these tools to enter the sheet party (August 2019), enables spreadsheet creators to build interactive dashboards that can (notwithstanding some pain points!) mimic to a certain extent those built with dedicated data visualization tools like the excellent Google Data Studio.

In this article we won't be discussing how slicers work (there already are excellent resources, such as this post by Ben Collins), or explaining in detail in which ways they differ from filter and filter views (a well-covered topic in the Docs Editors Help). Instead, we'll look specifically at how a group of slicers can be reset simultaneously to make all rows in the filtered data range they filter visible.

I believe that slicers in Google Sheets are just an inch away from greatness. But please, let me elaborate on this —maybe striking— statement.

Slicers are extremely convenient UI elements that enrich Google Sheets and encourage users to explore data in a more interactive way, but they are however not devoid of some annoying quirks. And I can't help but think that this is the direct consequence of an insufficiently polished, maybe rushed implementation.

Users may encounter issues such as these:

• Modifying the filtered data range on several slicer controls at once is no easy task. If you attempt to do it individually, slicer after slicer, you'll find that it is simply not possible at all.  A rather cumbersome trick has to be pulled to overcome this upsetting situation.
• Sometimes, for unknown reasons, slicer controls default to certain filtering settings, even after being removed and created anew. And setting a specific filter status as default does not solve this problem, either. This issue has been documented here and also here, and I've witnessed this annoying behavior myself in at least a couple of my own spreadsheets.

Oh well, using slicers  is not as smooth and painless as it should!

These, together with how our beloved Apps Script voodoo can 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.

The problem statement, sliced!

Why bulk resetting slicers is a good thing?

Well, the answer is quite ovious, 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 and multiple filtering actions, having to perform a two-digit number of clicks with your mouse to see all your data again is not fun.

For this reason, 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, there will be two of them, 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 plunge into the hows now.

Two Apps Script functions to tame slicers

All my posts, especially the GAS-related ones, usually gradually develop the solution to some problem and only show the meat and bones of the implementation at the end.

This time, for a change 😅, I'll be doing the opposite, your time is valuable.

I would like to introduce two spreadsheet-bound Apps Script functions, which you can find in this GitHub repository.

These functions accomplish the same task, albeit with varying degrees of effectiveness and, above all, a massive difference in execution time. This difference is due to the distinct methods that they use.

🙆‍♂️ Which one should you use?
Long story short: the API one!

Using the Slicer class in the Spreadsheet Service is pretty straightforward, but:

👎 As already mentioned, the process is rather slow, since multiple calls to different methods of the Spreadsheet Service are needed (just watch the GIF above to see how long it takes to reset a mere four slicers).

👎 The width and height of slicers cannot be presently set, to the best of my knowledge. New slicers will hence be of the default size, which can be a serious nuisance.

Please, grab this sample Google Spreadsheet used to capture the animated GIF above, it contains some sample sheets and the codebase used in this article:

👉 Reset slicers demo spreadsheet 👈

Now you are free to take your code and call it a day... or keep reading if you are interested in getting to know more about the inner workings of both functions. Along the way I will be glad to highlight some more of those quirks.

Strategy & code break-down

The Slicer class in the Spreadsheet Service provides the promising setColumnFilterCriteria() method:

According to the documentation, a null value (in the filterCriteria parameter), resets the slicer.

But wait a minute! That sentence is wrong. Partially, at least. Don't believe me? Okay, let's try this:

1️⃣ Create a new Google spreadsheet and write some sample values, starting from column A,  like this:

2️⃣ Create a slicer, set it to filter your data interval A2:B5 using in any column and just unselect some of its values to hide rows accordingly.

3️⃣ Open the Apps Script editor (Extensions → Apps Script) and paste this code snippet:

function resetSlicerNull() {
  const mySlicer = SpreadsheetApp.getActiveSheet().getSlicers()[0];
  mySlicer.setColumnFilterCriteria(mySlicer.getColumnPosition(), null);
  SpreadsheetApp.flush();
}

4️⃣ Run resetSlicerNull().

Can you see any difference in that handy-dandy slicer of yours? No? Neither do I. Try again, if you fancy 😏.

📌 By the way, you'll find a Test sheet tab in the already provided demo spreadsheet, as well as some code to test this behavior in Test.gs inside the attached script.

What's going on? Well, for some unfathomable reason...

Not all slicers are created equal!
Apps Script services can neither read nor modify the filtering criteria of slicers that have been inserted manually through the spreadsheet GUI!

This applies to the standard Spreadsheet Service and, the Advanced Sheets Service and even the Sheets API itself (phew!)

Ahem, nothing new 🐞, you see. If you came here for the quirks you may very well be sick of them as of now.

We want to reset *all* slicers, not only those that have been created using Apps Script, therefore we'll have to resort to more complicated methods. Instead of simply trying to adjust their setting to reset them, we'll clone our slicers and remove the original versions.

We shall do it in two different ways.

🐌 The Spreadsheet Service way

Let's dive into Code.gs.

1️⃣  First, all slicers in the current sheet are enumerated and their settings are recorded. Immediately after, all present slicers are removed from the sheet.

Please, note that this latter step is mandatory. Otherwise, any new slicer would shockingly inherit the filtering criteria of its original version, rendering the whole cloning process useless (more on the implications of this later).

A toast box is used to signal the start of the process, which will take some seconds to complete.

This whole function is wrapped inside a try...catch block to handle runtime exceptions gracefully.

/**
 * Resets all slicers in the active sheet using the Apps Script Spreadsheet Service.
 * 
 * Context:
 * 
 * Calling setColumnFilterCriteria(columnPosition, null) or trying to modify
 * in some other way the current filter criteria of a slicer whose criteria
 * has already been set manually (using the GUI) won't have any effect.
 * So, this function clones & deletes all slicers, instead.
 * 
 * ⚠️ Limitation: Size cannot be programmatically set, at this moment.
 * 
 * Pablo Felip (@pfelipm)
 * 
 * @OnlyCurrentDoc
 */
function resetSheetSlicers() {

  try {

    const s = SpreadsheetApp.getActiveSheet();
    const oldSlicers = s.getSlicers();

    SpreadsheetApp.getActive().toast('All slicers in the active sheet will be reset.', '🤖 Reset Slicers says:', -1);

    // 1. Get array of params of all slicers
    const params = oldSlicers.map(oldSlicer => {

      // Get configuration of a single slicer
      return {
        columnPosition: oldSlicer.getColumnPosition(),
        containerInfo: oldSlicer.getContainerInfo(),
        range: oldSlicer.getRange(),
        title: oldSlicer.getTitle(),
        titleHorizontalAlignment: oldSlicer.getTitleHorizontalAlignment(),
        titleTextStyle: oldSlicer.getTitleTextStyle(),
        backgroundColor: oldSlicer.getBackgroundColorObject(),
        pivot: oldSlicer.isAppliedToPivotTables()
      };
    });

    // 2. Remove old slicers, needs to be done BEFORE creating the new ones, otherwise they will (surprise!) inherit filter criteria!
    oldSlicers.forEach(oldSlicer => oldSlicer.remove());

2️⃣ Next, fresh new slicers are created with the insertSlicer() method and all their settings bar column and filtering criteria are adjusted to mimic those of the previous one. These self-explanatory set* methods provided by the Slicer class are used here:

• setTitle()
• setTitleHorizontalAlignment(horizontal)
• setTitleTextStyle()
• setApplyToPivotTables()
• setBackgroundColorObject()

    // 2. Remove old slicers, needs to be done BEFORE creating the new ones, otherwise they will (surprise!) inherit filter criteria!
    oldSlicers.forEach(oldSlicer => oldSlicer.remove());

    // 3. Create new instances of all slicers
    params.forEach(slicerParams => {
      const newSlicer = s.insertSlicer(slicerParams.range, // range is only really needed for the first new slicer
        slicerParams.containerInfo.getAnchorRow(),
        slicerParams.containerInfo.getAnchorColumn(),
        slicerParams.containerInfo.getOffsetX(),
        slicerParams.containerInfo.getOffsetY());

      // Clone settings
      newSlicer.setTitle(slicerParams.title)
        .setTitleHorizontalAlignment(slicerParams.titleHorizontalAlignment)
        .setTitleTextStyle(slicerParams.titleTextStyle)
        .setApplyToPivotTables(slicerParams.pivot);

      // Apply background color only when of a known type (not default), otherwise throws exception
      if (slicerParams.backgroundColor.getColorType() == SpreadsheetApp.ColorType.RGB ||
        slicerParams.backgroundColor.getColorType() == SpreadsheetApp.ColorType.THEME) {
        newSlicer.setBackgroundColorObject(slicerParams.backgroundColor);
      }

Please, bear with me before proceeding to the next stage and let's stop for a while on that (apparently) useless check prior to setting the background color in lines 19-21.

You guessed it, time for some more weirdness!

We can read the background color of a slicer with both the getBackgroundColor() and  getBackgroundColorObject() methods. The first one provides a CSS-like color string, while the second one returns a more comprehensive object of the Color class.

But the devil is in these colorful details:

• The getBackgroundColor() method returns #000000 if the background color has not been set, instead of a null value, as stated in the documentation. And, alas, slicers are usually gray, not black, by default! Therefore, this method does not allow us to tell apart a black slicer from one whose background color has not been set at all (by the way, you may want to star this issue).
• The default background color is of the UNSUPPORTED type, as returned by the getColorType() method.
• The getBackgroundColor() will also return #000000 if any standard (theme) color has been picked for the background (THEME color type).

As a consequence, getBackgroundColorObject() and setBackgroundColorObject() are the preferred methods to clone the background color of the slicer, but we won't go ahead and use the latter safely unless we are sure that the color is of the  RGB or THEME type.

3️⃣ Time now to pull a final trick to initialize the slicer and leave it around, ready for the user to play with, albeit with no filtering applied.

The newFilterCriteria().whenFormulaSatisfied() method of Class FilterCriteriaBuilder is used here to apply an always-true (i.e., all-pass) formula-based filter upon the appropriate column of the data range that the filter controls.

      // Set slicer filtering criteria so that it shows everything
      newSlicer.setColumnFilterCriteria(slicerParams.columnPosition, SpreadsheetApp.newFilterCriteria().whenFormulaSatisfied('=true')) // hack1: set column and filter to show all rows...
        .setColumnFilterCriteria(slicerParams.columnPosition, null);  // hack2: ...then reset filter

    });

And since this slicer has been created programmatically this time, its filtering criteria can be successfully nullified by setColumnFilterCriteria() , as expected.

The rest is pretty straightforward, another toast is spawned to tell the user how things have turned out and eventual runtime errors are caught.

    SpreadsheetApp.getActive().toast('Process completed.', '🤖 Reset Slicers says:');

  } catch (e) {
    SpreadsheetApp.getActive().toast(`⚠️ Oops:\n${e.message}.`, '🤖 Reset Slicers says:', -1);
    console.error(e);
  }

}

Well, after coping with a bunch of eccentricities courtesy of the Spreadsheet service, eventually we have sort of fulfilled our goal.

Let's go for whatever the Sheets API has in store for us!

🐯 The way of the API

We'll be using the Sheets API through its Apps Script advanced service so, first things first, switch it on before proceeding.

The Sheets object provided by the Advanced Sheets Service will grant us some handy superpowers to sort this out.

1️⃣ First, we browse the Spreadsheet object (resource) returned by the spreadsheets.get  method to gather all data needed to make perfect clones of the slicers in the active sheet.

Spreadsheet   →   sheets: []   →   slicers: []

As before, filtering criteria won't be returned for manually created slicers, but width and height will!

/**
 * Resets all slicers in the active sheet using the Sheets API/advanced service.
 *
 * Context:
 * 
 *    - filterCriteria is not updated when adjustments have been applied through the GUI.
 *    - Neither the Spreadsheet Service nor the Sheets v4 API/advanced service can access
 *      or modify the current filtering criteria of a slicer whose criteria has already been
 *      set manually (using the GUI).
 *    - The plain SpreadSheet Service can not access the width and height properties of a slicer.
 * 
 * So, this function clones & deletes all slicers using instead the Sheets V4 API via its advanced service,
 * which turns out to be *much* faster than using the SpreadSheet Service and can adjust the width and height
 * of any slicer.
 *  
 * Pablo Felip (@pfelipm)
 * 
 * @OnlyCurrentDoc
 */
function resetSheetSlicersApi() {
    
  try {

    const ssId = SpreadsheetApp.getActive().getId();
    const sId = SpreadsheetApp.getActiveSheet().getSheetId();

    // 1. Get config information about all slicers in the active sheet
    const oldSlicers = Sheets.Spreadsheets.get(ssId).sheets.find(sheet => sheet.properties.sheetId == sId).slicers;

2️⃣ Next, slicers are removed. This is accomplished just using the standard service.

A flush call is required. Otherwise, new slicers will keep old filtering criteria (who said we wouldn’t face some extra random weirdness?). 

  // 2. Removes all slicers in the active sheet
    SpreadsheetApp.getActiveSheet().getSlicers().forEach(slicer => slicer.remove());
    SpreadsheetApp.flush(); // Mandatory, otherwise new settings do not apply

3️⃣ Finally, cloned slicers are spawned all at once thanks to the spreadsheets.batchUpdate method.

Batch operations are extremely efficient. They are also available in some other Google APIs, and my recommendation is that you should use them whenever possible! No need this time to toast a message informing the user that the process has started since it should be blazing ⚡ fast.

The cloning procedure is quite effortless:

 // 3. Prepare a Sheets V4 API batch request to create new instances of all slicers
    const newSlicersRequest = [];
    oldSlicers.forEach(newSlicer => {
      newSlicer.slicerId = undefined; // new slicer
      newSlicer.spec.filterCriteria = undefined; // only needed when filterCriteria has been set programmatically, otherwise this field is ignored, anyway
      newSlicersRequest.push({ addSlicer: { slicer: newSlicer } })

    });

    // Create new slicers!
    Sheets.Spreadsheets.batchUpdate({ requests: newSlicersRequest }, ssId);
  
  } catch (e) {
    SpreadsheetApp.getActive().toast(`⚠️ Oops:\n${e.message}.`, '🤖 Reset Slicers says:', -1);
    console.error(e);
  }
}

We have already obtained the complete data structures that characterize our slicers. All we have to do now is unset the slicerId property —a new, unique ID will be assigned by the API— and the spec.filterCriteria object —the slicer will be rendered inactive— with both undefined values.

☝ You may be aware that, sometimes, finding out how to use certain APIs through the wrappers provided by their Apps Script advanced services can be frustrating, especially when it comes to ascertaining the way parameters are expected to be passed. In those situations, the JavaScript samples provided by the interactive Try this API feature, which can be found in the reference documentation, usually come in handy.

The different APIs of Google services may seem unwelcoming at first, but spending some time learning to use them effectively is not only extremely rewarding but often also mandatory to get the best possible results.

Final thoughts and next steps

Apparently, simple things such as our little, beloved slicers often hide unexpected complexities.

In this article, we've developed two Apps Script functions that reset the filtering criteria of all the slicers in a given sheet of a Google Spreadsheet. The first one uses the standard Spreadsheet Service, while the second taps into the Advanced Sheets Service. Along the process, we have uncovered some limitations and peculiarities in the Apps Script framework that I'd like to summarize:

1. Slicers that have been created using the Google Sheets user interface can't be fully managed programmatically.
2. It appears that the Spreadsheet Service has no obvious way of adjusting the dimensions of a slicer.
3. One of the available methods to get the background color of a slicer does not seem to work consistently.

Coding around these constraints has led us to implement a slicer-cloning strategy that gets the job done. And in doing so, we've realized that the Advanced Sheets Service is better suited for the task, as it is way faster than the standard Spreadsheet Service and, at the same time, is also capable of getting perfect replicas of our slicers that preserve custom width and height values.

What next? Well, I can think of several improvements to our current codebase.

To start with, I wonder whether there is a way to distinguish slicers that have been spawned by code from those created manually. If so, we could optimize the whole process, as the former ones could be reset just nullifying their filtering criteria.

Furthermore, replacing slicers is risky, more so if you ponder the fact that we are compelled to remove the original slicers before creating their new instances. If something goes wrong along the process, we could be left in the middle of a spreadsheet mess.

The first of our functions catches exceptions but does nothing meaningful beyond showing an eventual error message, while the second one performs no error handling at all. More specifically, our second, API-based function could use the response body that is returned by the call to the spreadsheets.batchUpdate method, and the AddSlicerResponse objects inside it, to recover better from a partially failed execution.

Fortunately, all actions performed by both Apps Script functions can be swiftly rolled back using several undo (CTRL + Z) commands in the edit menu, but I can't help feeling this is rather sub-optimal.

I will be thrilled to hear about these or some other improvements you come up with!

On my side, now I've yet another feature to add to my HdC+ Sheets add-on, that is crying for a new release (lots of other things in the pipeline!).

PS: I've to say 🙏 thanks (once again) to Google Developer Expert Martin Hawksey for his generosity providing valuable comments on the the draft of this article. Martin, I hope that eventually I'll learn to use proper English in my writings.

Credits: Header image uses a photo by Tom Grünbauer on Unsplash.