Copy google spreadsheet with all protected ranges permissions

Copy.gs

function Main() {
  var template = DriveApp.getFileById('YOUR_SPREADSHEET_ID');              //template spreadsheet
  var destination = DriveApp.getFolderById('COPY_DESTINATION_FOLDER_ID');  //the directory to copy the file into
  var curDate = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "yyyy-MM-dd");
  var copyName = template.getName() + ' copy ' + curDate;                  //the filename that should be applied to the new copy (e.g. "My spreadsheet copy 2019-08-24")

  copyTemplate(template, copyName, destination);
}

/**
 * Copy google spreadsheet with all protected ranges permissions
 *
 * @param {File} template - Template spreadsheet
 * @param {string} [copyName] - The filename that should be applied to the new copy (Optional)
 * @param {Folder} [destination] - The directory to copy the file into (Optional)
 * @param {boolean} [allowDuplicates] - Allow copying if a file with a given name already exists (Optional)
 * @throws Will throw an error if an error occurs ¯\_(ツ)_/¯
 * @example
 *     //creates a copy of the template with the same name, inside the root directory, ignore the duplicates
 *     var myTemplate = DriveApp.getFileById('MY_TEMPLATE_ID');
 *     copyTemplate(myTemplate);
 * @example
 *     //creates a copy of the template with the same name, inside the defined directory, if no duplicates exist
 *     var myTemplate = DriveApp.getFileById('MY_TEMPLATE_ID');
 *     var copyDestination = DriveApp.getFolderById('DESTINATION_DIRECTORY_ID');
 *     copyTemplate(myTemplate, null, copyDestination, false);
 */
function copyTemplate(template, copyName, destination, allowDuplicates) {
  var newSheet, templateSheet, fileIterator;

  //set defaults
  copyName = copyName || template.getName();
  destination = destination || DriveApp.getRootFolder();
  allowDuplicates = allowDuplicates === false ? false : true;
  
  //if duplicates are disallowed and a file with the given name already exists, stop execution
  if(!allowDuplicates) {
    fileIterator = destination.getFilesByName(copyName);
    
    while (fileIterator.hasNext()) {
      if(fileIterator.next().getMimeType() == MimeType.GOOGLE_SHEETS) {
        throw new Error("File already exists");
      }
    }
  }
  
  templateSheet = SpreadsheetApp.open(template);
  newSheet = SpreadsheetApp.open(template.makeCopy(copyName, destination));
  
  //copy protections from the template to the new spreadsheet
  copyProtections(templateSheet, newSheet);
}

/**
 * Copies protections from the origin to the target
 *
 * @param {Spreadsheet} origin - Origin spreadsheet
 * @param {Spreadsheet} target - Target spreadsheet
 */
function copyProtections(origin, target) {
  var sheets = origin.getSheets();
  
  for(var i = 0; i < sheets.length; i++) {
    var newSheet = target.getSheetByName(sheets[i].getName());
    var sheetProtection = sheets[i].getProtections(SpreadsheetApp.ProtectionType.SHEET)[0];
    var newSheetProtection = newSheet.getProtections(SpreadsheetApp.ProtectionType.SHEET)[0];
    var rangeProtections = sheets[i].getProtections(SpreadsheetApp.ProtectionType.RANGE);
    var newRangeProtections = newSheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);
    
    //Recreate sheet protection from scratch
    if(sheetProtection) {
      var unprotected = sheetProtection.getUnprotectedRanges();
      var newUnprotected = [];
      for (var j = 0; j < unprotected.length; j++) {
        newUnprotected.push(newSheet.getRange(unprotected[j].getA1Notation()));
      }
      cloneProperties(sheetProtection, newSheetProtection);
      newSheetProtection.setUnprotectedRanges(newUnprotected);
    }
    
    //Remove range protections in the new sheet
    for(var j = 0; j < newRangeProtections.length; j++) {
      newRangeProtections[j].remove();
    }
    
    //Recereate range protections from scratch
    for (var j = 0; j < rangeProtections.length; j++) {
      var protection = rangeProtections[j];
      var newProtection;
      
      if(protection.getRangeName()) {
        //if protection is set to the named range
        newProtection = target.getRangeByName(protection.getRangeName()).protect();
        newProtection.setRangeName(protection.getRangeName());
      } else {
        newProtection = newSheet.getRange(protection.getRange().getA1Notation()).protect();
      }
      cloneProperties(protection, newProtection);
    }//end of ranges
  }//end of sheets
}

/**
 * Copies protection object basic properties
 *
 * @param {Protection} origin - Protection object of the source
 * @param {Protection} target - Protection object of the target
 */
function cloneProperties(origin, target) {
  target.setDescription(origin.getDescription());
  target.setWarningOnly(origin.isWarningOnly());

  if (!origin.isWarningOnly()) {
    var editors = origin.getEditors();
    var oldEditors = target.getEditors();
 
    for(var i = 0; i < oldEditors.length; i++) {
      target.removeEditor(oldEditors[i]);
    }
    for(var i = 0; i < editors.length; i++) {
      target.addEditor(editors[i]);
    }
    if (origin.canDomainEdit()) {
      target.setDomainEdit(true);
    }
  }
}

Hi there, I think this is so close to what I need. I need to set permissions so that only I can edit a range and have that permission stay even when the new user makes a copy of the sheet. Is that possible?

When I run this and open the sheet as another user, then make a copy as that new user, I see that the protections are there but it's set so that new user account is the only one who can edit. If I succeed, then it would show that only the other account can edit the range. Thanks for any help!

@nmbr-bae

Hi there, I think this is so close to what I need. I need to set permissions so that only I can edit a range and have that permission stay even when the new user makes a copy of the sheet. Is that possible?

I don't see a straightforward solution for this task. If a user can make a copy by himself, then he becomes an owner of that spreadsheet and can edit and change any protections on the sheet.

I'm struggling with an error on line 78, it says Range not found :(
Line 78 is : newUnprotected.push(newSheet.getRange(unprotected[j].getA1Notation())); is my case
I have a spreadsheet with 8 sheets, I don't know what is going on :(

I'm struggling with an error on line 78, it says Range not found :(
Line 78 is : newUnprotected.push(newSheet.getRange(unprotected[j].getA1Notation())); is my case
I have a spreadsheet with 8 sheets, I don't know what is going on :(

@rousseaubenjamin can you please share any example of the spreadsheet with that error so I can check it?

This is exactly what I need, I was starting to code when I decided to look for any existing code. Thank you a lot for sharing!

When I run this code I get an error,
Exception: Unexpected error while getting the method or property getFileById on object DriveApp. (line 2, file "Code")Dismiss

Can you help?

@laksy002

When I run this code I get an error,
Exception: Unexpected error while getting the method or property getFileById on object DriveApp. (line 2, file "Code")Dismiss

Make sure that you pass the correct file id string to DriveApp.getFileById.
If you are sure that parameters are 100% correct you might also add next script scopes to the manifest file View > Show Manifest file:
"oauthScopes": ["https://www.googleapis.com/auth/spreadsheets", "https://www.googleapis.com/auth/drive"]
Some good suggestions are here:
https://stackoverflow.com/questions/60553062/unexpected-error-while-getting-the-method-or-property-getauthorizationinfo-on-ob

Hi takvol and all of you,

This is a request for help.
I dont know how to script. I am not a programmer or anything technical.

I just want to copy the content from a protected google sheet. It has all type of protection: copy protection,edit protection and all that.

The data in the sheet is about links to various sites for blog posting.
There are about 1100 links in the sheet. I just cant type each link manually.

Can anyone of you please help me?

Please take a look at the sheet. Kindly copy the full content in an excel sheet and send it to me by email.

I hope its not a big trouble.

Protected sheet: https://docs.google.com/spreadsheets/d/138GCs_8Tptsz1k5I7VsWNVZc5hQ3VCVZ2K6G2VK31K8/edit#gid=0
Email to send the file: ohioky@gmail.com
(Please send by email or put a link to the sheet here.)

Much obliged for your help.

Thanks in advanced. God Bless You.

@noviceinscript
I think you misunderstand the purpose of this script. It's can be executed only by the owner of the spreadsheet if the owner wants to automate the copying process.

The Copy.gs code works to copy from a spreadsheet with a given set of permissions to a specific folder.

Can anyone suggest a way that the code can be modified to copy a specific google sheet (with its contained sheets and all the designated protections) to another (already existing) google sheet in the same drive?
Thank you!

Does this script keep the editors the same as the original? Also, how can I adapt this to make multiple copies and save to one folder?

To give context, I need to take my original template (which has 4 admin on it that need editing access on all copies) and create a copy of it for 24 teachers, then share each sheet with its corresponding teacher. The teacher should not have editing access to the protected ranges. Any ideas?

Hello Takvol

I am new to this group.
I copied and used your Copy.gs code. All functions are working properly.
But app.script is running very slowly (6-7 minutes). How to solve this?
Sorry to trouble you.