Why, Google Slicers, why?
January 16, 2023 8:32 PM
A slicer I've added to a workbook to help provide a more user-friendly path to filtering a pivot table refuses to allow anyone but me or the owner to use the slicer. This confuses me, given the language provided by Google on Slicers' Visibility and Permissions (see below). Does anyone know why this might be happening and/or if it's something that can be overcome?
Astonishingly, with an hour or two of searching, I haven't been able to find any help with this on the internet at large, nor at Stack Overflow and Google Sheets subreddits in particular. There's a vague idea I get from this post that users won't be able to use the slicer unless they have edit permissions to filter the source data? I'm not a power-power user, but I don't think I have ever asked The Internet an Excel or Google Sheets question and come up as blank as this. Slicers seem to have other glitchy issues - changing column widths, not staying where you place them - and this is the first time I've really proposed them as a feature for a client, so I am as green as it is possible to be. Thanks for any insight you may have!
Google's language on Slicers:
Visibility: Anyone with access to the spreadsheet can see and adjust filters on a slicer. When you apply a filter on a slicer, changes are only visible to you, unless you set them as default.
Reuse: Filters applied on a slicer can’t be saved for reuse, unless you set them as default. When you set filter selections as default, they'll be applied for everyone that has access to the spreadsheet.
Permissions: Everyone with access to the spreadsheet can see slicers, but you need edit access to add or remove a new slicer.
Astonishingly, with an hour or two of searching, I haven't been able to find any help with this on the internet at large, nor at Stack Overflow and Google Sheets subreddits in particular. There's a vague idea I get from this post that users won't be able to use the slicer unless they have edit permissions to filter the source data? I'm not a power-power user, but I don't think I have ever asked The Internet an Excel or Google Sheets question and come up as blank as this. Slicers seem to have other glitchy issues - changing column widths, not staying where you place them - and this is the first time I've really proposed them as a feature for a client, so I am as green as it is possible to be. Thanks for any insight you may have!
Google's language on Slicers:
Visibility: Anyone with access to the spreadsheet can see and adjust filters on a slicer. When you apply a filter on a slicer, changes are only visible to you, unless you set them as default.
Reuse: Filters applied on a slicer can’t be saved for reuse, unless you set them as default. When you set filter selections as default, they'll be applied for everyone that has access to the spreadsheet.
Permissions: Everyone with access to the spreadsheet can see slicers, but you need edit access to add or remove a new slicer.
Not sure if this is helpful, but are you using Sheets on a company's G Suite? Not your personal computer? My organization tinkers with our Google products and breaks things constantly. There are lots of things I can do w/Google applications on my home machine that just do not work on my company laptop. I was pulling my hair out until I realized what was going on.
posted by Stoof at 2:40 PM on January 17, 2023
posted by Stoof at 2:40 PM on January 17, 2023
Thanks for the solidarity, all! I wrote this late last night, and did *not* include as many details as I should: apologies.
I'm the designer of the tool, but it's owned by the client. Stoof, they do not have a company G Suite instance. The client team has *dangerously minimal* spreadsheet skills - there have been major problems in the last couple of weeks because they can't remember what filters they've applied, and then things blow up and they get angry.
They are adamant that they need to be able to make changes on the fly to one key category displayed in the pivot. Given what I've read about slicers, I thought that they might be both user-friendly and less risky than the client editing the filters on the pivot table each time they need to make a change.
Both in the link you've shared, MollyRealized, and in other writeups/tutorials/discussions I've read, slicers are promoted as valuable for dashboards. This gives me the strong feeling that they're *supposed* to work for users without edit permissions to pivot table/chart tabs or source data - people who build dashboards don't want the big boss clicking into and potentially messing with meticulously-designed pivot tables and charts, right?
The tab I'm adding the slicer to has a pivot table, and the whole tab is protected so it can only be edited by the owner (the client's account) and me. (The source data headers and formula columns that drive info displayed in the pivot table are also protected so that they can only be edited by the owner and me - rows in which data are entered are open for anyone with editing permissions. This allows users to add data and see them show up in the pivot table in real time.) The users who would need the slicer have Edit access, so it's not a matter of View or Comment-only permissions for the workbook as a whole.
flug, the message you're getting is what my colleagues are getting. Editing protections *around* the slicer itself doesn't change things. (I didn't know the trick of opening in incognito! A good tip I will use going forward. )
The thing that is most confusing about all of this is the seeming lack of info online about how permissions on the slicer tab or source data tabs (for pivots/charts) affect user ability to *use* slicers. For example, searching for various iterations of slicer Google Sheets "exact error message text" gets me nowhere, just general articles about protections.
posted by rrrrrrrrrt at 6:01 PM on January 17, 2023
I'm the designer of the tool, but it's owned by the client. Stoof, they do not have a company G Suite instance. The client team has *dangerously minimal* spreadsheet skills - there have been major problems in the last couple of weeks because they can't remember what filters they've applied, and then things blow up and they get angry.
They are adamant that they need to be able to make changes on the fly to one key category displayed in the pivot. Given what I've read about slicers, I thought that they might be both user-friendly and less risky than the client editing the filters on the pivot table each time they need to make a change.
Both in the link you've shared, MollyRealized, and in other writeups/tutorials/discussions I've read, slicers are promoted as valuable for dashboards. This gives me the strong feeling that they're *supposed* to work for users without edit permissions to pivot table/chart tabs or source data - people who build dashboards don't want the big boss clicking into and potentially messing with meticulously-designed pivot tables and charts, right?
The tab I'm adding the slicer to has a pivot table, and the whole tab is protected so it can only be edited by the owner (the client's account) and me. (The source data headers and formula columns that drive info displayed in the pivot table are also protected so that they can only be edited by the owner and me - rows in which data are entered are open for anyone with editing permissions. This allows users to add data and see them show up in the pivot table in real time.) The users who would need the slicer have Edit access, so it's not a matter of View or Comment-only permissions for the workbook as a whole.
flug, the message you're getting is what my colleagues are getting. Editing protections *around* the slicer itself doesn't change things. (I didn't know the trick of opening in incognito! A good tip I will use going forward. )
The thing that is most confusing about all of this is the seeming lack of info online about how permissions on the slicer tab or source data tabs (for pivots/charts) affect user ability to *use* slicers. For example, searching for various iterations of slicer Google Sheets "exact error message text" gets me nowhere, just general articles about protections.
posted by rrrrrrrrrt at 6:01 PM on January 17, 2023
« Older Gardening Books for the Complete Idiot | How do I remove this white stuff on the leaves of... Newer »
This thread is closed to new comments.
When I open an incognito page in my browser and open that link (ie, viewing the spreadsheet but not logged in to google at all) I can both see the default results of the slicer I set up under my account (all movies with the characters "ant" in the title) and edit the slicer as I like.
Just for example, I can change the filter to "bat" and see "Batman vs Superman," "Batman: The Killing Joke," "Battle of the Year," etc.
My guess is a bit like MollyRealized, that maybe your or client's browser has some popup or other similar restrictions, or some kind of ad blocking.
Or maybe your data has some more complex permissions set up other than the basic/normal sheet view/edit type permissions you see when you share a sheet. Just for example, if I go into Data/Protect Sheets & Ranges and protect the column with the Slicer on it, so that only I can edit that column, then suddenly the slicer says "There was a problem. You were trying to edit a protected cell or object."
posted by flug at 7:29 AM on January 17, 2023