I am running the following script for Google Sheets duplication:
function duplicateSheetWithProtections() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheetByName('MASTER'); var sheetName = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'dd/MM'); if (ss.getSheetByName(sheetName)) { console.log(`Same sheet name of "${sheetName}" is existing. Please confirm the sheet name again.`); return; } var sheet2 = sheet.copyTo(ss).setName(sheetName).activate(); var idx = ss.getSheets().findIndex(s => s.getSheetName() == "MASTER") + 2; ss.moveActiveSheet(idx); 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 } } }
from this answer by Tanaike.
How do I add a Timestamp in a specific cell inside the duplicated new sheet that is locked from editors?
I understood that OP's expected situation is as follows.
function duplicateSheetWithProtections() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('MASTER');
var sheetName = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'dd/MM');
if (ss.getSheetByName(sheetName)) {
console.log(`Same sheet name of "${sheetName}" is existing. Please confirm the sheet name again.`);
return;
}
var sheet2 = sheet.copyTo(ss).setName(sheetName).activate();
var idx = ss.getSheets().findIndex(s => s.getSheetName() == "MASTER") + 2;
ss.moveActiveSheet(idx);
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
}
}
}
In this case, when the user "A" runs the script by clicking the button, the script is run as the user "A". By this, the user "A" can edit the cell even when the cell is protected. I understood that this was not the OP's expected result.
In order to achieve this goal, I think that the same method as this thread can be used. But, from Second I have no idea how to write or edit your script.
from OP's comment, I would like to propose a modified script for this situation.
The script is run through the Web Apps because in this case, the script can be run as the owner of Spreadsheet even when the user "A" clicks the button. In order to use this, please do the following flow.
Please do the following flow on the script editor.
The detailed information can be seen in the official document.
Please set this using the new IDE of the script editor.
https://script.google.com/macros/s/###/exec
. This URL is used with Python script.Please copy and paste the following script to the script editor. And, please set the Web Apps URL to const webAppsUrl = "https://script.google.com/macros/s/###/dev";
.
const doGet = _ => duplicateSheetWithProtections(true);
function duplicateSheetWithProtections(e) {
if (!e) {
const webAppsUrl = "https://script.google.com/macros/s/###/dev"; // Please set your Web Apps URL.
UrlFetchApp.fetch(webAppsUrl, { headers: { authorization: "Bearer " + ScriptApp.getOAuthToken() } });
return;
}
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('MASTER');
var sheetName = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'dd/MM');
if (ss.getSheetByName(sheetName)) {
console.log(`Same sheet name of "${sheetName}" is existing. Please confirm the sheet name again.`);
return;
}
var sheet2 = sheet.copyTo(ss).setName(sheetName).activate();
var protect1 = sheet2.getRange("B2").setValue(new Date()).protect(); // Added
protect1.removeEditors(protect1.getEditors()); // Added
var idx = ss.getSheets().findIndex(s => s.getSheetName() == "MASTER") + 2;
ss.moveActiveSheet(idx);
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
}
}
}
// ### Please don't remove the following comment line.
// DriveApp.getFiles(); // This comment line is used for automatically detecting a scope of "https://www.googleapis.com/auth/drive.readonly". This scope is used for requesting Web Apps.
// ###
When you click the button assigned with duplicateSheetWithProtections
, the script is run. And, the script is run by Web Apps. By this, when the date object is put into cell "B2" and the cell "B2" is protected. In this case, the user who is not the owner of the Spreadsheet cannot edit "B2".
As an appendix, my 1st understanding of OP's question is as follows.
When the script is run, OP wanted to put a date object to the cell "B2" of the new sheet. For this, I proposed the following modified script.
function duplicateSheetWithProtections() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('MASTER');
var sheetName = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'dd/MM');
if (ss.getSheetByName(sheetName)) {
console.log(`Same sheet name of "${sheetName}" is existing. Please confirm the sheet name again.`);
return;
}
var sheet2 = sheet.copyTo(ss).setName(sheetName).activate();
var protect1 = sheet2.getRange("B2").setValue(new Date()).protect(); // Added
protect1.removeEditors(protect1.getEditors()); // Added
protect1.setWarningOnly(true); // Added
var idx = ss.getSheets().findIndex(s => s.getSheetName() == "MASTER") + 2;
ss.moveActiveSheet(idx);
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
}
}
}
But, from the discussion in this chat room, I could understand the OP's expected result. So, I proposed the above-modified script using Web Apps.