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

This is awesome! Thank you!

This seems to be exactly what I'm looking for. Sorry, but how do I run it? I did get connected to Scripts on Google Drive, created a new script and copy pasted your code. Not sure what to do next though.

This seems to be exactly what I'm looking for. Sorry, but how do I run it? I did get connected to Scripts on Google Drive, created a new script and copy pasted your code. Not sure what to do next though.

go to google drive, click new>more>google apps script

replace any existing code with the code from here.

you will also need to copy the spreadsheet ID and plug it into the code.

Then just hit run (the little play button) and it will duplicate your google document including permissions.

The copy of the file will be located in the root directory of your google drive.

Got this after hit run:

You do not have permission to access the requested document. (line 3, file "Code")Dismiss

Hi. Thanks for writing this code. I was wondering can this be modified to copy a page within a spread sheet. My spreadsheet has multiple pages with schedules and each page needs the same protection. It is annoying to put in 30 ranges each time.Thanks.

This worked absolutely perfectly. Thank you takvol.

I am not familiar with coding at all, anyone know how to batch copy instead of one at a time?

Also, auto change the filename sequentially?

Thank you!

I get this when running the script. :(

You do not have permission to access the requested document. (line 3, file "Copy")

You do not have permission to access the requested document. (line 3, file "Copy")

@WayneGreen, @17500mph Are you trying to copy your own spreadsheet? Maybe you have multiple accounts and run the script using the wrong account. This error might indicate that the owner of the spreadsheet disabled the option to copy for viewers.
Try creating a new spreadsheet, new script project, run it and see how it works.
@GSheetUser Batch copy and name change are trivial tasks but you have to use a little coding.:

Batch Copy

Copy this code and run batchCopy function. Modify copyName and n variables as needed;
Note that if you try to create many copies you may run out of time quota since Google Apps Script execution time limit is around 6 min. See https://developers.google.com/apps-script/guides/services/quotas#current_limitations

function batchCopy() {
  var n = 5;//number of copies to create
  var template = SpreadsheetApp.openById('template_spreadsheet_id');  //place template spreadsheet ID here
  
  for(var i = 1; i <= n; i++) {
    var copyName = template.getName() + ' copy #' + i;//this will create names like 'My Spreadsheet copy #1'
    copyTemplate(template, copyName);
  }
}

function copyTemplate(template, target) {
  var copy = template.copy(target);
  var sheets = template.getSheets();
  var newSheets = copy.getSheets();
  
  for(var i = 0; i < sheets.length; i++) {
    var sheetProtection = sheets[i].getProtections(SpreadsheetApp.ProtectionType.SHEET)[0];
    var newSheetProtection = newSheets[i].getProtections(SpreadsheetApp.ProtectionType.SHEET)[0];
    var rangeProtections = sheets[i].getProtections(SpreadsheetApp.ProtectionType.RANGE);
    var newRangeProtections = newSheets[i].getProtections(SpreadsheetApp.ProtectionType.RANGE);
    
    if(sheetProtection) {
      if (!sheetProtection.isWarningOnly()) {
        newSheetProtection.addEditors(sheetProtection.getEditors());
        
        if(sheetProtection.canDomainEdit()) {
          newSheetProtection.setDomainEdit(true); //  only if using an Apps domain
        }
      }
    }
    
    for (var j = 0; j < rangeProtections.length; j++) {
      //creating ranges protections
      var protection = rangeProtections[j];
      var newProtection = newRangeProtections[j];
      
      if (!protection.isWarningOnly()) {
        newProtection.addEditors(protection.getEditors());
        
        if(protection.canDomainEdit()) {
          newProtection.setDomainEdit(true); //  only if using an Apps domain
        }
      }
    }
  }
}

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.