CODEWITHSUNDEEP
google-sheetsgoogle-apps-scriptfilterview
joshofsorts
joshofsorts

Reputation: 59

Allow "Create New Filter View" feature on Google Sheet with some columns protected

I am working on a large Google Sheet that has many editors from multiple companies, so I have had to give permissions on each tab on a per column basis. The issue is, because most editors only have editor access to 10-20% of the columns, the "Create New Filter View" feature is not working for those users. I am finding Filter View access to be very finicky in general. Sometimes it works, sometimes it doesn't on a sheet with similar protections. Other times "Create New Filter View" is not an available option, but if you select an existing filter view and click "Duplicate" you can create one. Seems like there are a few weird bugs with Google Sheets and permissions that I can't pin down.

Any ideas on how to use Apps Script to unlock the protections on the "Create New Filter View" option? This could be for all editors or all viewers, either would work in this instance. Thanks!

Upvotes: 1

Views: 3115

Answers (1)

Iamblichus
Iamblichus

Reputation: 19339

Issue:

While a FilterView can be created in a script if the Advanced Sheets Service is enabled, the same issue you're experiencing while creating it in the UI would show up here: a user cannot create a FilterView if one of the columns is protected from this user.

Naturally, this same user cannot run a script to unprotect the corresponding columns either - what worth would a protection be if the users to which it applies could remove it!

Possible workarounds:

A way around this would be to have a script that runs under the authority -see getEffectiveUser()- of a user who can edit those protected columns. In most situations, this is the user that is triggering the script -see getActiveUser()-, but in certain situations, like an installable trigger or a web app that executes as the user who deployed it, that is not the case.

For example, you could install an onEdit trigger with a user who has access to all the columns in the desired FilterView.

Then, this would fire whenever any user edits the spreadsheet, but it would fire under the authority of the user who installed the trigger (and can access the protected columns), so the FilterView could be created.

In order to create the FilterView only for certain edits (for example, when a specific cell is edited and there is a specific edited value), you could check these conditions at the beggining of the onEdit function. And if you needed to pass more information for customization of the filter view (for example, which columns and rows, which sheet, etc.), you could put that information in other cells and retrieve the corresponding values via getValue()/getValues().

For example, It could be something like this:

function fireOnEdit(e) {
  if (e.range.getA1Notation() === "E1" && e.value === "Create FilterView") {
    const ss = e.source;
    const spreadsheetId = ss.getId();
    const sheet = ss.getSheets()[0];
    const resource = {
      requests: {
        addFilterView: {
          filter: {
            range: {
              sheetId: sheet.getSheetId(),
              startRowIndex: 0,
              endRowIndex: 4,
              startColumnIndex: 0,
              endColumnIndex: 3
            }
          }      
        }
      }
    }
    Sheets.Spreadsheets.batchUpdate(resource, spreadsheetId);
  }
}

Upvotes: 2

Related Questions