复制文件时保持保护范围 [英] Maintaining protected ranges while copying a file

问题描述

我们在Googlesheets上有一个文件模板，该模板具有受保护的范围.我们希望复制此文件(file，进行复制)，并为每个复制此文件的人保留受保护的范围，以便他们只能填充某些特定的单元格.问题是，每当有人制作副本时，他/她都会自动成为副本的所有者.这使得无法保护此副本不受此人的攻击(无论如何，所有者似乎都拥有对电子表格的完全访问权限).有人可以解决此问题吗?

We have a file template on Googlesheets which has got protected ranges. We would like to get this file copied ( file , make a copy) and keep the protected ranges for everyone making a copy of this file so they can only fill some specific cells. The problem is, whenever someone makes a copy, he/ she automatically becomes the owner of the copy. Which makes it impossible to protect the ranges of this copy against this person ( the owner seems to have full access to the spreadsheet no matter what). Would someone have a workaround for this issue ?

推荐答案

• 您想让用户将Google云端硬盘上的电子表格复制到用户的Google云端硬盘.
  • 您的电子表格有几个受保护的范围.
  • 您的电子表格已与用户共享.
  • 用户没有与您共享自己的文件夹.
  • You want to make users copy a Spreadsheet on your Google Drive to user's Google Drive.
    • Your Spreadsheet has several protected ranges.
    • Your Spreadsheet is shared with the user.
    • User doesn't have own folder shared with you.

    如果我的理解正确，那么这个答案如何?

    If my understanding is correct, how about this answer?

    在上述情况下，我认为以下几点是瓶颈.

    In above situation, I think that the following points are the bottleneck.

    • 当您将Google云端硬盘中的电子表格复制到用户的Google云端硬盘时，必须由该用户的帐户来运行.
    • 当用户将电子表格复制到用户的Google云端硬盘时，受保护范围的所有者将成为该用户.因此，用户可以编辑受保护的范围.并且用户不能由用户将其从编辑器中删除.
      • 为了使用户不要编辑受保护的范围，需要更改电子表格的所有者，然后要求从受保护的范围的编辑器中删除用户.
      • When the Spreadsheet in your Google Drive is copied to user's Google Drive, it is required to be run by the user's account.
      • When the Spreadsheet is copied to user's Google Drive by user, the owner of protected ranges becomes the user. So the user can edit the protected ranges. And the user cannot remove the user from the editor by the user.
        • In order to make the user not edit the protected ranges, it is required to change the owner of Spreadsheet, and then, it is required to remove the user from the editor of the protected ranges.
        1. 为了将电子表格的所有者更改为您，它必须由用户帐户运行.
        2. 为了从受保护范围的编辑器中删除用户，它必须由您的帐户(不是用户帐户)运行.

      结果，为了使用脚本实现超出您的目标，该脚本需要由用户和您运行.

      As the result, in order to achieve above your goal using a script, the script is required to be run by the user and you.

      在我的解决方法中，使用2个脚本和2个帐户，可以达到上述目标.该解决方法的重点是使用2个Web Apps.请认为这只是几种解决方法之一.此解决方法的流程如下.

      In my workaround, using 2 scripts and 2 account, it achieves above goal. The point of this workaround is to using 2 Web Apps. Please think of this as just one of several workarounds. The flow of this workaround is as follows.

      在此替代方法中，使用了两个Web应用程序，分别是Web应用程序"A"和Web应用程序"B".

      In this workaround, 2 Web Apps of Web Apps "A" and Web Apps "B" are used.

      1. 用户访问您部署的Web应用程序"A".
         • 在这种情况下，Web Apps是作为用户执行的.
      1. User accesses to Web Apps "A" that you deployed.
         • In this case, Web Apps is executed as the user.
      1. 您的电子表格已从您的Google云端硬盘复制到用户的Google云端硬盘.
      2. 复制的电子表格的所有者是用户.因此，将所有者从用户更改为您.
      3. 访问脚本中的Web应用程序"B".
         • 在这种情况下，Web Apps是按您执行的.
      1. Your Spreadsheet is copied from your Google Drive to user's Google Drive.
      2. Owner of copied Spreadsheet is the user. So change the owner from the user to you.
      3. Access to Web Apps "B" in the script.
         • In this case, Web Apps is executed as you.
      1. 该用户已从受保护范围的编辑器中删除.

      脚本的用法:

      准备工作:

      请创建2个独立脚本类型的项目.

      Usage of scripts:

      Preparation:

      Please create 2 projects of the standalone script type.

      • 一个用于Web应用程序"A".
      • 另一个是针对Web应用程序"B"的.

      首先，请部署Web应用程序"B"，因为Web应用程序"A"的脚本中使用了Web应用程序"B"的URL.请复制以下脚本，并将Web Apps部署为Execute the app as: **Me**和Who has access to the app: **Anyone, even anonymous**.然后，请复制此Web应用程序"B"的URL，并将其粘贴到Web应用程序"A"的脚本的url中.

      At first, please deploy Web Apps "B", because the URL of Web Apps "B" is used at the script of Web Apps "A". Please copy the following script and deploy Web Apps as Execute the app as: **Me** and Who has access to the app: **Anyone, even anonymous**. And please copy the URL of this Web Apps "B" and paste it to url of the script of Web Apps "A".

      function doGet(e) {
        var id = e.parameter.id;
        if (id) {
          var owner = Session.getActiveUser().getEmail();
          var ss = SpreadsheetApp.openById(id);
          var protectedRanges = ss.getProtections(SpreadsheetApp.ProtectionType.RANGE);
          protectedRanges.forEach(function(p) {
            var removes = p.getEditors().filter(function(e) {return e.getEmail() != owner});
            p.removeEditors(removes);
          });
        }
        return ContentService.createTextOutput("Done");
      }
      

      Web应用程序"A"的独立脚本:

      请复制以下脚本并设置srcSpreadsheetId和url的变量.在这种情况下，url是在Web Apps"B"上检索到的URL.然后，请将Web Apps部署为Execute the app as: **User accessing the web app**和Who has access to the app: **Anyone**.并请复制此Web应用程序的URL"A".该URL供用户访问.

      Standalone script for Web Apps "A":

      Please copy the following script and set the variables of srcSpreadsheetId and url. In this case, url is the URL retrieved at Web Apps "B". Then, please deploy Web Apps as Execute the app as: **User accessing the web app** and Who has access to the app: **Anyone**. And please copy the URL of this Web Apps "A". This URL is used for accessing by the user.

      function doGet() {
        var srcSpreadsheetId = "###"; // Please set your source Spreadsheet ID.
        var url = "###"; // Please set the URL of Web Apps B.
      
        var srcSS = SpreadsheetApp.openById(srcSpreadsheetId);
        var dstSS = srcSS.copy(srcSS.getName());
        var file = DriveApp.getFileById(dstSS.getId()).setOwner(srcSS.getOwner().getEmail());
        var res = UrlFetchApp.fetch(url + "?id=" + file.getId(), {muteHttpExceptions: true}); // Here, run the script of Web Apps "B".
        return ContentService.createTextOutput(res.getContentText());
      }
      

      运行:

      当用户使用上述脚本时，请使用户使用自己的浏览器访问Web应用程序"A"的URL.当用户访问Web应用程序"A"时，将显示Google的登录屏幕.通过登录Google，将显示授权屏幕.通过对其进行授权，将运行Web应用程序"A"的脚本，并由Web应用程序"A"运行Web应用程序"B".授权只需要执行一次.

      Run:

      When the user uses above scripts, please make the user access to the URL of Web Apps "A" using own browser. When the user accesses to Web Apps "A", the login screen of Google is displayed. By logging in to Google, the authorization screen is shows. By authorizing it, the script of Web Apps "A" is run, and Web Apps "B" is run by the Web Apps "A". The authorization is required to do only one time.

      当用户浏览器中显示完成"时，用户可以在根文件夹中看到复制的电子表格.

      When "Done" is shown in user's browser, the user can see the copied Spreadsheet at the root folder.

      通过这种方式，您在Google云端硬盘中的电子表格将被复制到用户的Google云端硬盘，并且电子表格的所有者也将被修改为您，然后该用户将从受保护范围的编辑器中删除.因此，用户将无法编辑受保护的范围.

      By this, your Spreadsheet in your Google Drive is copied to the user's Google Drive, and the owner of Spreadsheet is modified to you, and then, the user is removed from the editor of the protected ranges. So the user get to not be able to edit the protected ranges.

      1. 在脚本编辑器上，通过发布"->作为Web应用程序部署"打开一个对话框.
      2. 为执行应用程序为:"选择用户正在访问网络应用程序" 或我" .
      3. 为谁有权访问该应用程序:"选择任何人" 或任何人，甚至是匿名的" .
      4. 单击部署"按钮作为新的项目版本".
      5. 自动打开需要授权"对话框.
      1. On the script editor, Open a dialog box by "Publish" -> "Deploy as web app".
      2. Select "User accessing the web app" or "Me" for "Execute the app as:".
      3. Select "Anyone" or "Anyone, even anonymous" for "Who has access to the app:".
      4. Click "Deploy" button as new "Project version".
      5. Automatically open a dialog box of "Authorization required".
      1. 点击查看权限".
      2. 选择自己的帐户.
      3. 在此应用未验证"中单击高级".
      4. 点击转到###项目名称###(不安全)"
      5. 单击允许"按钮.
      • 复制当前的Web应用程序URL:".
        • 就像https://script.google.com/macros/s/#####/exec.
        • Copy "Current web app URL:".
          • It's like https://script.google.com/macros/s/#####/exec.

          重要提示:

          1. 修改Web应用程序的脚本后，请重新部署Web应用程序为新版本.这样，最新脚本将反映到Web Apps.即使修改了脚本也没有重新部署Web Apps时，不会使用最新的脚本.请注意这一点.
          2. 请确认您要复制用户的电子表格已与用户共享.

          参考文献:

          • 网络应用
          • 利用Google的Web Apps优势Apps脚本
          • 类保护

          References:

          • Web Apps
          • Taking advantage of Web Apps with Google Apps Script
          • Class Protection

          如果我误解了你的问题，而这不是你想要的方向，我深表歉意.

          If I misunderstood your question and this was not the direction you want, I apologize.

          这篇关于复制文件时保持保护范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！