-
-
Save takvol/bbd960f98a4521791e24e34762a72be3 to your computer and use it in GitHub Desktop.
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); | |
} | |
} | |
} |
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.
@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.