Skip to content

Instantly share code, notes, and snippets.

@takvol
Last active April 12, 2024 06:50
Show Gist options
  • Save takvol/bbd960f98a4521791e24e34762a72be3 to your computer and use it in GitHub Desktop.
Save takvol/bbd960f98a4521791e24e34762a72be3 to your computer and use it in GitHub Desktop.
Copy google spreadsheet with all protected ranges permissions
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);
}
}
}
@drleff
Copy link

drleff commented Sep 10, 2021

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!

@kflora2015
Copy link

kflora2015 commented Aug 15, 2022

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?

@Tabigat1
Copy link

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment