更改工作表的所有者，而不考虑复制者 [英] Changing Owner of the Sheet irrespective of the duplicator

问题描述

我有一个电子表格，其中包含可以由各种用户使用脚本复制的工作表.问题是，当用户复制工作表时，他将成为默认所有者，并获得编辑该工作表受保护范围的权限.

I have a spreadsheet which contains a sheet that can be duplicated using a script by various users. The problem is when the user duplicates the sheet he becomes the default owner and gets the rights to edit even the protected ranges for that sheet.

我当前的脚本将保护完美地复制到了新的工作表中，但是复制者用户成为了该编辑器的编辑者.

My current script copies the protection to new sheet perfectly but the duplicator user becomes the editor for the same.

请帮助我，如何允许被允许重复工作表但不成为受保护范围的编辑者的用户，或者如何将工作表所有者(而非电子表格)重设回管理员用户的方法.

Please help me in how can a user who is allowed to duplicate the sheet but does not become editor of the protected ranges or a method to reset the owner of the sheet (not the spreadsheet) back to admin user.

我当前的代码:

function Protect() {
  var spreadsheet = SpreadsheetApp.getActive();
  var myValue = SpreadsheetApp.getActiveSheet().getSheetName();
  spreadsheet.duplicateActiveSheet();
  var totalSheets = countSheets(); //script function
  myValue = "DO" + totalSheets;
  SpreadsheetApp.getActiveSpreadsheet().renameActiveSheet(myValue);
  var protection = spreadsheet.getActiveSheet().protect();
  protection.setUnprotectedRanges([spreadsheet.getRange('C2:E5'), spreadsheet.getRange('C6:D7'), spreadsheet.getRange('F5:G6'), spreadsheet.getRange('B9:G18'), spreadsheet.getRange('G7:G8')])
    .removeEditors(['user1.com', 'user2.com', 'user3.com']);
  spreadsheet.getRange('G2').setValue(myValue);
  spreadsheet.getRange('G3').setValue(new Date()).setNumberFormat('dd-MMM-YYYY');
  spreadsheet.getRange('H1:H').clearContent();

};

推荐答案

我相信您的目标如下.

• 当Protect()脚本由不是所有者的用户运行时，您要允许复制工作表，并且不想使用Google Apps脚本将该用户作为编辑者添加到复制的整个工作表中
• 电子表格已与用户共享.

• When the script of Protect() is run by an user who is not the owner, you want to permit to copy the sheet and don't want to add the user as the editor to the copied whole sheet using Google Apps Script.
• The Spreadsheet has already been shared with the users.

• 在这种情况下，我认为当用户运行脚本时，由所有者运行脚本可能是您遇到的问题的解决方案.
• 当用户运行脚本时，为了由不是用户的所有者运行脚本，我建议使用Web Apps.

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

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

function doGet() {
  script();
  return ContentService.createTextOutput();
}

function Protect() {
  const url = ScriptApp.getService().getUrl();
  UrlFetchApp.fetch(url, {headers: {authorization: "Bearer " + ScriptApp.getOAuthToken()}});

// DriveApp.getFiles()  // This is used for automatically detecting the scope of "https://www.googleapis.com/auth/drive.readonly". This scope is used for the access token.
}

// This script is the same with your "Protect".
function script() {
  var spreadsheet = SpreadsheetApp.getActive();
  var myValue = SpreadsheetApp.getActiveSheet().getSheetName();
  spreadsheet.duplicateActiveSheet();
  var totalSheets = countSheets(); //script function
  myValue = "DO" + totalSheets;
  SpreadsheetApp.getActiveSpreadsheet().renameActiveSheet(myValue);
  var protection = spreadsheet.getActiveSheet().protect();
  protection.setUnprotectedRanges([spreadsheet.getRange('C2:E5'), spreadsheet.getRange('C6:D7'), spreadsheet.getRange('F5:G6'), spreadsheet.getRange('B9:G18'), spreadsheet.getRange('G7:G8')])
    .removeEditors(['user1.com', 'user2.com', 'user3.com']);
  spreadsheet.getRange('G2').setValue(myValue);
  spreadsheet.getRange('G3').setValue(new Date()).setNumberFormat('dd-MMM-YYYY');
  spreadsheet.getRange('H1:H').clearContent();

};

• 在此脚本中，不包括countSheets().因为我不确定您提出的问题是否是countSheets().因此，请注意这一点.请准备这个.
• In this script, countSheets() is not included. Because I'm not sure about countSheets() from your question. So please be careful this. Please prepare this.
1. 在脚本编辑器上，通过发布"打开对话框. -> 部署为网络应用".
2. 选择我" 作为将应用程序执行为:" .
   • 通过这种方式，脚本以所有者身份运行.
1. On the script editor, Open a dialog box by "Publish" -> "Deploy as web app".
2. Select "Me" for "Execute the app as:".
   • By this, the script is run as the owner.
• 在这种情况下，需要访问令牌才能请求Web Apps.
1. 点击查看权限".
2. 选择自己的帐户.
3. 点击高级"在此应用未验证"中.
4. 点击转到###项目名称###(不安全)"
5. 点击允许"按钮.
• 点击确定".

3.测试此替代方法.

请单击分配给Protect的按钮.这样，脚本由所有者运行.这样，即使单击用户按钮，脚本的结果也与所有者运行的结果相同.

3. Test this workaround.

Please click the button assigned with Protect. By this, the script is run by the owner. By this, even when the user is clicked the button, the result of script is the same with that run by the owner.

• 请在启用V8的情况下使用此脚本.
• 网络应用
• 利用Google的Web Apps优势Apps脚本
• Web Apps
• Taking advantage of Web Apps with Google Apps Script

这篇关于更改工作表的所有者，而不考虑复制者的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！