I have a google sheet where many collaborators are there doing data entry. I want that if there is a wrong entry then the sheet will be automatically locked. I write below code in google app script but it not happen.
Note: – There is a trigger in app script that function duplicate will called for every edit in spreadsheet.
function protection() {
let ui = SpreadsheetApp.getUi();
let pin = "0000";
let attempt = "";
while (attempt != pin) {
attempt = ui.prompt("Enter pin to unlock the sheet").getResponseText()
}
ui.alert("Sheet unlocked")
}
function duplicate() {
let sheet = SpreadsheetApp.getActive().getSheetByName("Master");
let range = sheet.getRange("E:E");
let value = range.getValues();
let lastRow = sheet.getLastRow();
for (let i=0;i < lastRow;i++) {
if (value[i][0] === "FALSE") {
protection()
}
}
}
1
Several insights:
- You are missing several semicolons (
;
) at the end of your code lines. Sometimes they are “added” automatically while parsing, but you should not rely on that and ought to always use them. - I suggest you use column number notation instead of the A1 notation; that is, writing
getRange(1, 5, sheet.getLastRow())
instead ofgetRange("E:E")
. - You need not loop through the rows; using the Array.some() method should be faster (Array.some()):
if (value.some(cell => cell[0] == false)) {protection();}
.
- And here comes the important part: what type of data do you have in the E column? If you, a script, or someone else are typing “TRUE” or “FALSE” in those cells, your code should work. However, if the cells are supposed to contain boolean values, there’s the problem:
"FALSE"
is not the same asfalse
. - Even when you get your code to work, your
protection()
function does not effectively protect the sheet. The prompt message will indeed keep popping up until the correct pin is entered, but the non-zero time elapsed between the closing of the previous prompt and the next leaves some time to the user to edit the sheet. I suggest looking into theSheet.protect()
method and the Protection class.
user27425627 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
On Edit Function ()
I used the "On Edit"
function to automatically lock the sheet if an incorrect entry is made. Additionally, I created functions for locking and unlocking the sheet when necessary.
Trigger Setup:
As a reminder, the duplicate
function should be set as a trigger to run on each edit in the spreadsheet.
Go to Apps Script > Triggers.
Set the trigger for Function: duplicate and Event Type: On edit.
Script and Output.
Function that triggers on edit to lock the sheet if “FALSE” is entered in Column E.
function duplicate(e) {
let sheet = e.source.getSheetByName("Master");
if (!sheet) return;
let range = e.range;
if (range.getColumn() === 5) {
let value = range.getValue();
if (value === "FALSE") {
lockSheet(sheet);
protection();
}
}
}
Function to lock the sheet.
function lockSheet(sheet) {
let protection = sheet.protect().setDescription('Sheet locked due to incorrect entry');
let me = Session.getEffectiveUser();
protection.removeEditors(protection.getEditors());
protection.addEditor(me);
protection.setWarningOnly(false);
SpreadsheetApp.getUi().alert("The sheet has been locked due to a wrong entry.");
}
Function to unlock the sheet with a PIN
function protection() {
let ui = SpreadsheetApp.getUi();
let pin = "0000";
let attempt = "";
while (attempt != pin) {
attempt = ui.prompt("Enter PIN to unlock the sheet").getResponseText();
if (attempt === pin) {
let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Master");
let protection = sheet.getProtections(SpreadsheetApp.ProtectionType.SHEET)[0];
if (protection) {
protection.remove();
ui.alert("Sheet unlocked successfully!");
Logger.log("Sheet unlocked successfully.");
}
return;
} else {
ui.alert("Incorrect PIN. Please try again.");
}
}
}
PIN
Unlocked Sheeet
Reference
onEdit(e)