Changing Owner of the Sheet irrespective of the duplicator

1
votes

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.

My current code:

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

};
1
google-apps-scriptgoogle-sheets
Can I ask you about your question? In your situation, how do the users run the script? – Tanaike
There is a image in the master sheet assigned to run the above script so when user runs this script a duplicate of master sheet is created – Harsh
Thank you for replying. For example, when the owner who is not the user is run the function of Protect(), is that the result you expect? – Tanaike
Yes, when the user who is not the owner/admin runs the function Protect() then the ranges which are mentioned to be unprotected shall only by allowed to be edited by the user and not the whole sheet. As the script duplicates the master sheet in which only admin/owner can edit other ranges except the ones mentioned in the unprotected ranges. – Harsh
Thank you for replying. From your replying, I proposed a workaround as an answer. Could you please confirm it? If that was not the direction you expect, I apologize. – Tanaike

1 Answers

2
votes

I believe your goal as follows.

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

Modification points:

  • In this case, I thought that to run the script by the owner might be the solution of your issue, when the user runs the script.
  • When the user is run the script, in order to run the script by the owner who is not the user, I would like to propose to use Web Apps.

Usage:

1. Prepare script.

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

};
  • 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.

2. Deploy Web Apps.

  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.
  3. Select "Anyone" for "Who has access to the app:".
    • In this case, the access token is required to request to Web Apps.
  4. Click "Deploy" button as new "Project version".
  5. Automatically open a dialog box of "Authorization required".
    1. Click "Review Permissions".
    2. Select own account.
    3. Click "Advanced" at "This app isn't verified".
    4. Click "Go to ### project name ###(unsafe)"
    5. Click "Allow" button.
  6. Click "OK".

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.

Note:

  • Please use this script with enabling V8.

References: