Is it possible to protect a range of cells when a checkbox is checked in Google Sheets?-CodePudding

Sorry for the complicated and possibly confusing title. I am currently developing a spreadsheet on Google Sheets for my co-workers. I'm working in a train transportation company. I made a checklist which includes items like 'Air Conditioner' or 'Doors', 'Lights', etc. After they finish checking the Done or Not Done boxes, I want them to check another box such as 'I checked the tools mentioned above carefully and confirm that the boxes that I checked are real', like a signature on a formal paper. When the person checks this box, all the upper boxes will be protected. I had these spreadsheet physically, and I want to do these to save from papers. I already have a script like:

function onEdit(e){
   let protection = e.range.protect();
   protection.removeEditors(protection.getEditors());
   if (protection.canDomainEdit()) {
     protection.setDomainEdit(false);
   }
}

But I know that this script locks every cell after edited. In order to avoid any risks (such as accidentally checking the 'Done' box instead on 'Not Done'). I think that this idea will be helpful for the train drivers since they don't have computers and enough time while they're monitoring and leading the train at the same time. Thank you so much for all your helps!

EDIT: Thanks to @doubleunary and @TheLegend's comment, I created a sample spreadsheet with no script linked to it: The Table

CodePudding user response：

Use an installable "on edit" trigger, like this:

/**
* Protects the whole sheet when the checkbox in cell B7 is ticked.
* The account that installed the trigger will retain edit rights.
*
* Runs on an installable trigger each time the user hand edits the
* spreadsheet. To manually create an installable "on edit" trigger, see:
* https://developers.google.com/apps-script/guides/triggers/installable
*
* @param {Object} e The "on edit" event object.
*/
function onEditTrigger(e) {
  if (!e) {
    throw new Error(
      'Please do not run the onEditTrigger(e) function in the script editor window. '
        'It runs automatically when you hand edit the spreadsheet.'
    );
  }
  if (e.value !== 'TRUE'
    || e.range.columnStart !== 2 // column B
    || e.range.rowStart !== 7) {
    return;
  }
  const me = Session.getEffectiveUser();
  const protection = e.range.getSheet().protect();
  protection.addEditor(me);
  protection.removeEditors(protection.getEditors());
  if (protection.canDomainEdit()) {
    protection.setDomainEdit(false);
  }
}