google apps script - Changing Owner of the Sheet irrespective of the duplicator

Question

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

};

Answer 1

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:

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