Lock google sheets for data entry after a wrong entry

Question

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()
  }
}  
}

Answer 1

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)