Google Sheets: Prevent the unchecking of a checkbox after it has been checked

Viewed 1334

If there a conditional statement or formula to Prevent the unchecking of a checkbox after it has been checked by a user?

2 Answers

Solution:

You can take advantage of the Class Protection to protect the cell if the checkbox is checked; value becomes TRUE.

function onEdit(e) {

  const row = e.range.getRow();
  const col = e.range.getColumn();
  const as = e.source.getActiveSheet();

  if(as.getName() == "Sheet1" && col == 2 && row > 1 && e.range.getValue()===true) {  
     
   const protection = as.getRange(row,col).protect();
    
  // Ensure the current user is an editor before removing others. Otherwise, if the user's edit
  // permission comes from a group, the script throws an exception upon removing the group.
   const me = Session.getEffectiveUser();
   protection.addEditor(me);
   protection.removeEditors(protection.getEditors());
   if (protection.canDomainEdit()) {
  protection.setDomainEdit(false);
  }
    
  }
}
  • Feel free to modify "Sheet1" and col == 2 (2 corresponds to column B).
  • To enable this code snippet, copy & paste it to the script editor (Tools => Script Editor) and click on save.

Example:

result

Protect the cell or the sheet after the checkbox has been checked. This could be done manually i.e. right click the cell then select protect to open the sidebar to set the protection settings, or automatically by using Google Apps Script.

Resources

Related