Last active
April 12, 2024 06:50
-
-
Save takvol/bbd960f98a4521791e24e34762a72be3 to your computer and use it in GitHub Desktop.
Copy google spreadsheet with all protected ranges permissions
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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); | |
} | |
} | |
} |
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
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?