I have found and adapted a script for google sheets for our timesheeting system that copies a template sheet to create a blank timesheet, whilst maintaining certain protections, to stop people breaking formulas.
In the spreadsheet there are a couple of other sheets, but the main thing is we keep running the script to add new sheets for each person. After exactly 4 additions, the script stops functioning completely, with the error:
Exception: Service Spreadsheets failed while accessing document with id [insert doc ID code here]
Script below:
function duplicateSheetWithProtections() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
sheet = ss.getSheetByName('Template');
sheet2 = sheet.copyTo(ss).setName('BLANK TIMESHEET');
var protections = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);
for (var i = 0; i < protections.length; i++) {
var p = protections[i];
var rangeNotation = p.getRange().getA1Notation();
var p2 = sheet2.getRange(rangeNotation).protect();
p2.setDescription(p.getDescription());
p2.setWarningOnly(p.isWarningOnly());
if (!p.isWarningOnly()) {
p2.removeEditors(p2.getEditors());
p2.addEditors(p.getEditors());
// p2.setDomainEdit(p.canDomainEdit()); // only if using an Apps domain
}
}
sheet2.activate();
}
To clarify, I also have these sheets in the workbook by default (in case sheet count is a factor for some reason:
- Overview
- Overtime report
- Template (Hidden, this is the sheet that gets copied)
- Settings (Hidden, just contains data validation stuff)
So to simply conclude, I have a timesheet template that gets duplicated on a button press per person. I can add 4 sheets to the doc and then the 5th gets the error code listed above.
3