You can try adding a Google Apps Script to capture when a cell is edited and add a timestamp to a different cell. Here's a previous answer that is similar:
Google Spreadsheet Timestamp?
function onEdit() {
var s = SpreadsheetApp.getActiveSheet();
if( s.getName() == "Sheet1" ) { //checks that we're on the correct sheet
var r = s.getActiveCell();
if( r.getColumn() == 13 ) { //checks the column
var nextCell = r.offset(0, 1);
if( nextCell.getValue() === '' ) //is empty?
var time = new Date();
time = Utilities.formatDate(time, "GMT", "HH:mm:ss");
nextCell.setValue(time);
};
};
}
Scenario 1: template is a protected sheet with unprotected ranges
In the script below, I duplicate the sheet, get its protection of Sheet type, then protect the new sheet in the same way: same description, same type. If the protection is not just a warning, then remove all editors, and add those allowed for the original sheet. Finally, loop over unprotected ranges, remapping each of them (via getA1Notation
) to the new sheet, and unprotect those.
function duplicateProtectedSheet() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
sheet = ss.getSheetByName("Sheet1");
sheet2 = sheet.copyTo(ss).setName("My Copy");
var p = sheet.getProtections(SpreadsheetApp.ProtectionType.SHEET)[0];
var p2 = sheet2.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
}
var ranges = p.getUnprotectedRanges();
var newRanges = [];
for (var i = 0; i < ranges.length; i++) {
newRanges.push(sheet2.getRange(ranges[i].getA1Notation()));
}
p2.setUnprotectedRanges(newRanges);
}
Scenario 2: template is a sheet with protected ranges
Using sheet.getProtections
method, you can get the array of protections on a given sheet, and loop over them, creating their analogs on the target sheet. This is somewhat annoying because there seems to be no method to simply clone a protection to another range. (One can change the range of protection, but that would move it to the new range, instead of copying.)
So, in the function below I do the following:
- Get the A1 notation of each protected range with
p.getRange().getA1Notation();
- Protect the corresponding range of the target sheet with
p2 = sheet2.getRange(rangeNotation).protect();
- Set the properties of new protection
p2
according to the properties of original protection p
.
function duplicateSheetWithProtections() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
sheet = ss.getSheetByName('Template');
sheet2 = sheet.copyTo(ss).setName('My Copy');
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
}
}
}
It's also possible to have protected ranges within a protected sheet, in which case you would need to combine the two functions (do everything that each of them does, except of course you'll be duplicating the sheet only once.)
Best Answer
try
if (SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName() == "FAQ")