google-sheetsfilteringsubtotal

Google sheets - workaround for filtering with non-edit access / using SUBTOTAL to find filtered values


I am looking for a workaround to a specific problem - I have spreadsheets with pivot tables that are accessible to people with commenter access. I would like to give them an option to filter some of the data - but I cannot extend them the editor access, as that allows to download the whole file and I cannot have that (you can disable this option for people without edit access). This means they cannot edit the filters in the table, but also cannot use slicers (which I was very surprised by, as it doesn't make much sense to me). The only thing they can do is use base filters, but that doesn't exactly affect pivot tables. Not unless I can use the SUBTOTAL function to extract a list of string values they have filtered in some column, and use that as condition for a filter in the pivot table.

So is it possible to use the SUBTOTAL for that? I don't see a way to do it but I might be missing something. I will also take suggestions on how to resolve the underlying issue of filtering with non-edit access.

Edit: I've made an example sheet to show what I mean, sorry about that https://docs.google.com/spreadsheets/d/1ixJwWB41XWevLVNZ77G2UNrawhf5owg4_lG0plhn0S0/edit?usp=sharing

The idea is to be able to filter for weeks like what can be done with the slicer, but by using the filter function in the cell A18


Solution

  • Added one approach using subtotal() for you to test out. It filters the pivot table based on the filter function setup you have in cell_A18:

    =vstack("Helper_",
     map(D2:D,lambda(Σ,if(Σ="",,if(ifna(xmatch(Σ,let(Λ,'Pivot Table 1'!A19:A,filter(Λ,map(Λ,lambda(x,subtotal(103,x))))))),true,)))))
    

    enter image description here