如何使未经授权的用户保护电子表格 [英] How to enable not authorized users to protect the spreadsheet

问题描述

在我们的团队中，我们有以下情形:我们正在使用带有一些受保护区域的google电子表格(主要是不能覆盖的公式-只有"admin"(在我们的情况下是我自己)可以编辑它们).其他输入字段不受保护.每个团队成员都可以输入他们的数据.在某个时间点(冲刺结束)，我们的项目经理需要报告电子表格中的数字.为了确保以后编号不会更改，他们希望查看整个工作表.对于下一个冲刺，我们创建一个新的电子表格.我添加了一个应用脚本(下面的代码)，并将其分配给一个按钮，他们可以单击该按钮来锁定工作表(或至少是人们输入数据的重要范围).

In our team we have the following scenario: we're using a google spreadsheet with some protected areas (mainly formulas that must not be overridden - only the "admin", in our case it's me, can edit them). The other input fields are unprotected. Every team member can enter their data. At some point in time (end of the sprint), our project managers need to report the numbers from the spreadsheet. To ensure that the numbers do not get changed afterward, they want to look at the entire sheet. For the next sprint, we create a new spreadsheet. I added an app script (code below) and assigned this to a button that they can click to lock the sheets (or at least the important ranges where people enter their data).

如果我单击按钮，则一切正常.但是，如果这样做，他们将得到一个例外:"例外:您正在尝试编辑受保护的单元格或对象.如果您需要编辑，请与电子表格所有者联系以取消保护."

If I click the button everything works fine. But if they do that, they get an exception: "Exception: You are trying to edit a protected cell or object. Please contact the spreadsheet owner to remove protection if you need to edit."

我尝试了各种选项，设置范围保护，工作表保护，将我删除为编辑器，将它们添加为保护的编辑器等.但是在所有情况下，只要调用了保护上的函数(例如addEditor())，则出现此异常.

I tried various options, setting range protections, sheet protection, removing me as an editor, adding them as an editor to protection, etc. But in all cases, as soon as a function on the protection is called (e.g. addEditor()), this exception appears.

有什么想法使之成为可能，以便基本上每个人都可以单击按钮并锁定整个工作表吗?

Any idea how to make it possible, so that basically everybody can click the button and so lock the entire sheet?

在我的功能下面.这个例子使用了sheet.protect()，但是我也尝试了远程保护，只是为了更容易理解我在说什么;)

Below my function. this example uses the sheet.protect(), but I also tried the ranged protections, just to make it easier to understand what I'm talking about ;)

function lockSheet() {
  var me = Session.getEffectiveUser();
  var as = SpreadsheetApp.getActiveSpreadsheet();
  as.getSheets().map(function(sheet) {
    var protection = sheet.protect().setDescription('The sprint has been closed. No further editing possible.');
    
    // Ensure the current user is an editor before removing others. Otherwise, if the user's edit
    // permission comes from a group, the script throws an exception upon removing the group.
    protection.addEditor(me);
    
    protection.removeEditors(protection.getEditors());
    if (protection.canDomainEdit()) {
      protection.setDomainEdit(false);
    }
  });
  var ui = SpreadsheetApp.getUi();
  var response = ui.alert('Sheet is now locked. The only editor now is: ' + me);
  
}

推荐答案

我相信您的情况和目标如下.

I believe your situation and goal as follows.

• 登录Google帐户的用户通过单击Google Spreadsheet上的按钮来运行脚本.
• 在您的问题中，当用户单击按钮时，错误为 Exception:您正在尝试编辑受保护的单元格或对象.如果需要编辑，请与电子表格所有者联系以删除保护.发生.
• 您想在用户单击按钮时将工作表锁定在Google Spreadsheet中.

我认为在您的情况下，当用户单击按钮时，脚本将由每个用户运行.但是工作表已经被其他用户锁定.由此，发生这种错误.为了避免这种情况，作为一种解决方法，我建议由用户(在本例中是所有者)锁定表.此解决方法的流程如下.

I think that in your case, when the users click the button, the script is run by each user. But the sheet has already been locked by other users. By this, such error occurs. In order to avoid this, as a workaround, I would like to propose to lock the sheets by an user (in this case, it's the owner.). The flow of this workaround is as follows.

1. 单击按钮时，脚本将请求Web Apps.在这种情况下，用户的电子邮件地址将发送到Web Apps.
2. 在Web Apps中，工作表由所有者锁定.
3. 锁定完成后，将运行 ui.alert .

用法:

1.准备脚本.

请将以下脚本复制并粘贴到脚本编辑器中并保存.

Usage:

1. Prepare script.

Please copy and paste the following script to the script editor and save it.

const key = "sampleKey";

function doGet(e) {
  if (e.parameter.key == key) {
    var me = e.parameter.me;
    
    // Below script is your script.
    as.getSheets().map(function(sheet) {
      var protection = sheet.protect().setDescription('The sprint has been closed. No further editing possible.');
      
      // Ensure the current user is an editor before removing others. Otherwise, if the user's edit
      // permission comes from a group, the script throws an exception upon removing the group.
      protection.addEditor(me);
      
      protection.removeEditors(protection.getEditors());
      if (protection.canDomainEdit()) {
        protection.setDomainEdit(false);
      }
    });

  } else {
    throw new Error("Wrong key.");
  }
  return ContentService.createTextOutput();
}

function lockSheet() {
  var lock = LockService.getDocumentLock();
  if (lock.tryLock(10000)) {
    try {
      var me = Session.getEffectiveUser();
      var as = SpreadsheetApp.getActiveSpreadsheet();
      var url = "https://script.google.com/macros/s/###/exec";  // Please set the URL of Web Apps.
      url += `?me=${me}&key=${key}`;
      try {
        var res = UrlFetchApp.fetch(url);
        var ui = SpreadsheetApp.getUi();
        var response = ui.alert('Sheet is now locked. The only editor now is: ' + me);
      } catch(er) {
        throw new Error(er.message);
      }
    } catch(e) {
      throw new Error(e);
    } finally {
      lock.releaseLock();
    }
  }
}

2.部署Web应用.

1. 在脚本编辑器上，通过发布"打开对话框.->部署为网络应用".

1. On the script editor, Open a dialog box by "Publish" -> "Deploy as web app".

为将应用程序执行为:" 选择我" .

• 通过这种方式，脚本以所有者身份运行.

为有权访问该应用的用户:" 选择任何人，甚至是匿名的" .

• 在这种情况下，不需要请求访问令牌.我认为我建议使用此设置来测试此变通办法.但是在此脚本中，使用了用于运行脚本的密钥.
• 当然，您也可以使用访问令牌.但是，在这种情况下，当使用访问令牌时，该示例脚本不能直接用作自定义函数.因此，在这个答案中，我建议使用该密钥来运行Web Apps脚本.但是，如果您想使用访问令牌，我认为可以使用PropertiesService来实现.

点击部署"按钮作为新的项目版本".

Click "Deploy" button as new "Project version".

自动打开需要授权"对话框.

Automatically open a dialog box of "Authorization required".

1. 点击查看权限".
2. 选择自己的帐户.
3. 点击高级"在此应用未验证"中.
4. 点击转到###项目名称###(不安全)"
5. 点击允许"按钮.

单击确定".

Click "OK".

复制Web应用程序的URL.就像 https://script.google.com/macros/s/###/exec .

Copy the URL of Web Apps. It's like https://script.google.com/macros/s/###/exec.

• 修改Google Apps脚本后，请重新部署为新版本.这样，修改后的脚本将反映到Web Apps.请注意这一点.

，请将上述脚本的 https://script.google.com/macros/s/###/exec 的URL设置为 url .并请重新部署Web Apps.这样，最新脚本将反映到Web应用程序中.所以请注意这一点.

Please set the URL of https://script.google.com/macros/s/###/exec to url of above script. And please redeploy Web Apps. By this, the latest script is reflected to the Web Apps. So please be careful this.

4.测试此替代方法.

请点击分配给 lockSheet 的按钮.这样，用于锁定工作表的脚本由所有者运行.

4. Test this workaround.

Please click the button assigned with lockSheet. By this, the script for locking sheets are run by the owner.

• 修改Google Apps脚本后，请重新部署为新版本.这样，修改后的脚本将反映到Web Apps.请注意这一点.
• 请在启用V8的情况下使用此脚本.

• Google表格中的自定义功能
• 网络应用
• 利用Google的Web Apps优势Apps脚本

这篇关于如何使未经授权的用户保护电子表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！