不考虑复印机而更改工作表的所有者 [英] 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. 在脚本编辑器上，通过发布"打开一个对话框->部署为 Web 应用程序".
2. 选择我"作为执行应用程序:".
   • 由此，脚本以所有者身份运行.
• 在这种情况下，需要访问令牌才能向 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 的网络应用应用脚本

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