I have a sheet with a table. When me or one of my editors changes the data of the cell in column F (so that it is not blank), I need the row to become protected for everybody except me (owner).
I have seen several similar questions here, but no one gives a working script... I will appreciate any help.
I believe your current situation and your goal as follows.
In this case, I would like to propose to run the script with the installable OnEdit trigger. The sample script is as follows.
Please copy and paste the following script to the script editor of Spreadsheet and please install the OnEdit trigger to the function of myFunction
.
function myFunction(e) {
const sheetName = "Sheet1"; // Please set your sheet name.
const range = e.range;
const sheet = range.getSheet();
const value = range.getValue();
const row = range.getRow();
if (sheet.getSheetName() != sheetName || range.getColumn() != 6 || row == 1 || value == "") return;
const p = sheet.getRange(`A${row}:F${row}`).protect();
const owner = Session.getActiveUser().getEmail();
p.getEditors().forEach(f => {
const email = f.getEmail();
if (email != owner) p.removeEditor(email);
});
}
About your following 2nd question in comment,
thank you very much!! It works, and I know now something about triggers - a new thing for me. Is it possible to extend this function for 3 sheets with the same structure (within one spreadsheet)? Sheet1, Sheet2,Sheet 3...
When you want to use above script for the specific sheets like "Sheet1", "Sheet2", "Sheet3", how about the following sample script?
function myFunction(e) {
const sheetNames = ["Sheet1", "Sheet2", "Sheet3"]; // Please set the sheet names you want to run the script.
const range = e.range;
const sheet = range.getSheet();
const value = range.getValue();
const row = range.getRow();
if (!sheetNames.includes(sheet.getSheetName()) || range.getColumn() != 6 || row == 1 || value == "") return;
const p = sheet.getRange(`A${row}:F${row}`).protect();
const owner = Session.getActiveUser().getEmail();
p.getEditors().forEach(f => {
const email = f.getEmail();
if (email != owner) p.removeEditor(email);
});
}
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments