Filter Sheet for users who do not have 'Edit' access

[Jeff King]

I spent a great deal of time getting this code to work; to filter the rows based upon user input.

It worked great, until I tested with a restricted user.  The generated error states that the user must have 'Edit' access to filter the sheet.  

I'd rather not give them edit access, but will to enable this functionality.  

Is it possible to allow this code to operate for those users without changing their permissions?

The 'text' value is from a user input dialog:

    

    var ss = SpreadsheetApp.getActiveSpreadsheet();

    var filterCriteria = {"type":"TEXT_CONTAINS","values":[{"userEnteredValue":text}]}; 

    var filterSettings = {};

    filterSettings.range = {sheetId: ss.getSheetByName("MasterArchive").getSheetId()};

    filterSettings.criteria = {};

    var columnIndex = 0;

    filterSettings['criteria'][columnIndex] = {'condition': filterCriteria}; // Modified

    var request = {setBasicFilter: {filter: filterSettings}};

    Logger.log(JSON.stringify({'requests': [request]}))

    Sheets.Spreadsheets.batchUpdate({'requests': [request]}, ss.getId());

    

    ss.setActiveSheet(ss.getSheetByName("MasterArchive"));

[Jeff King]

New Problem discovered:  When I set the filter using this method, the sheet is filtered with these values for all users.  

This will not work.

Is it possible to set temporary filter views, visible only to each user, via GAS?

[CBM Services]

It is only one spreadsheet being used. So every change is reflected for all.

What you can do is add a script onOpen to reset filters. But all that would do is reset the view. If someone is already in spreadsheet, it will reset their view as well.

---

From: Jeff King
Sent: ‎2019-‎11-‎29 4:28 AM
To: Google Apps Script Community
Subject: [Apps-Script] Re: Filter Sheet for users who do not have 'Edit'access

New Problem discovered:  When I set the filter using this method, the sheet is filtered with these values for all users.  

This will not work.

Is it possible to set temporary filter views, visible only to each user, via GAS?

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/25935a1d-555e-4a2d-a0b9-bac6b23a5c28%40googlegroups.com.

[Jacopo Rumi]

To make it very easy: why don't you give the user edit access at the file level but protect all so that he she can't modify any cell?

Better yet, if you put the file in a Team Drive, you can also prevent accidental deletion.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/d3f9081a-7be6-4941-8275-4093f84b3156%40googlegroups.com.

[Jeff King]

It's good to know those options and techniques.  Thanks for suggesting.

I've determined that even if I do create a work around, this is undesired functionality.  The filter needs to be visible only to user/browser that is setting the filter.  

When these restricted users use the Filter option in a sheet that they cannot edit, they can create these 'Temporary Filters'; and that is the functionality I need to chase down.  

In their browser window, Sheets will apply a black horizontal and vertical bars, then allow you to select a filter for each column, just like it normally does.  It can be saved, and it's visible only to that user session.  

Brilliant.  

I want to call that in GAS.