CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
cjvdg
cjvdg

Reputation: 533

Working with Protected Sheets and Ranges "View Only" mode

Just want to ask if there is a way for a user to use a button with a lock or unlock script even if the permission on Protected Sheets and Ranges is in "View Only" mode? Will using a web app work or it is still impossible?

Upvotes: 0

Views: 1620

Answers (1)

Iamblichus
Iamblichus

Reputation: 19309

Issue:

A script cannot protect/unprotect a sheet or range if the user executing it doesn't have edit access to the protected sheet/range.

Trying to do so this result in a message like:

You are trying to edit a protected cell or object. Please contact the spreadsheet owner to remove protection if you need to edit.

In order to avoid the script throwing this exception, it's useful to first check if the user has edit access, using Protection.canEdit().

Using a web app:

When you deploy a script as a web app, you can set Execute the app as: Me (that is, the user deploying the web app). If this user has edit access to the protected sheet/range, the web app will be able to unprotect the sheet/range, even if it's accessed by a user who doesn't have edit access.

For example, let's say you have a spreadsheet with a sheet named Sheet1, which is protected and can only be edited by user #1. In this case, you could have the following doGet function to unprotect it:

function doGet(e) {
  const sheet = SpreadsheetApp.getActive().getSheetByName("Sheet1");
  const protection = sheet.getProtections(SpreadsheetApp.ProtectionType.SHEET)[0];
  if (protection && protection.canEdit()) {
    protection.remove();
  }
  return ContentService.createTextOutput("Sheet unprotected!");
}

And then, while logged in with user #1, deploy the web app the following way:

  • Click Publish > Deploy as web app....
  • Set Execute the app as: Me.
  • Set Who has access to the app: to an audience that will include the other users who should be able to access this.
  • Click Deploy.
  • Copy Current web app URL:.

Finally, access this URL with user #2 (which doesn't have edit access to the protected sheet). The sheet will become unprotected.

Update:

You could write another function to access this URL programmatically via UrlFetch. See, for example:

function unprotect() {
  const url = "WEB_APP_URL";
  const options = {
    headers: { Authorization: "Bearer " + ScriptApp.getOAuthToken() }
  }
  UrlFetchApp.fetch(url, options);
}

Accessing this URL via UrlFetch might require setting scopes explicitly in the manifest file of the script:

"oauthScopes": [
  "https://www.googleapis.com/auth/script.external_request", 
  "https://www.googleapis.com/auth/drive", 
  "https://www.googleapis.com/auth/spreadsheets"
]

Upvotes: 2

Related Questions