使用受保护的工作表和范围“仅查看"模式 [英] Working with Protected Sheets and Ranges "View Only" mode

问题描述

只想问问用户是否有一种方法可以使用带有 lock 或 unlock 脚本的按钮，即使 Protected Sheets和范围位于"仅查看"模式?使用网络应用程序是否行得通还是仍然不可能?

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?

推荐答案

问题:

如果执行脚本的用户没有对受保护的工作表/范围的编辑访问权限，脚本将无法保护/取消保护工作表或范围.

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().

将脚本部署为网络应用时，您可以将将应用程序设置为:我(即，部署Web应用程序的用户).如果此用户具有对受保护的工作表/范围的编辑访问权限，则即使没有编辑访问权限的用户访问了该应用程序，该Web应用程序也可以取消保护工作表/范围.

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.

例如，假设您有一个电子表格，其中包含名为 Sheet1 的工作表，该工作表受保护并且只能由用户#1进行编辑.在这种情况下，您可以使用以下 doGet 函数取消保护它:

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!");
}

然后，以用户#1登录时，通过以下方式部署Web应用程序:

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

• 点击 Publish>部署为Web应用... .
• 设置将应用程序执行为:我.
• 将有权访问该应用程序的人设置为将包括应该访问此应用程序的其他用户的受众.
• 点击 Deploy .
• 复制当前网络应用的URL:.

• 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:.

最后，使用用户#2(没有对受保护工作表的编辑权限)访问此URL.床单将变得不受保护.

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

您可以编写另一个函数来通过

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);
}

通过 UrlFetch 访问此URL可能需要设置在脚本的清单文件中明确显示:

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"
]

这篇关于使用受保护的工作表和范围“仅查看"模式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！