How to lock a checkbox in Google Sheets once it's checked?

Other Questions Google Sheets
1

Hey everyone! I’m working on a Google Sheets project and I’m stuck on something. Is there a way to make a checkbox stay checked once a user clicks it? I want to stop people from unchecking it after they’ve marked it.

I’ve tried looking for formulas or conditional formatting, but I can’t figure it out. Maybe there’s a script or something I can use? Any ideas would be super helpful!

I’m thinking it might look something like this:

function lockCheckbox(event) {
  var range = event.source.getActiveRange();
  if (range.isChecked()) {
    range.protect().setDescription('Locked checkbox');
  }
}

But I’m not sure if that’s the right approach. Has anyone done something like this before? Thanks in advance for any tips!

2

I’ve encountered this issue before in my work. While scripts can work, they can be finicky and might slow down your sheet. An alternative approach I’ve found effective is using data validation. Set up a custom formula for the checkbox cells like =OR(A1=TRUE, ISBLANK(A1)). This allows the cell to be either blank or TRUE, effectively preventing unchecking. It’s simpler to implement and doesn’t require ongoing script maintenance. Just remember to adjust the cell reference for each checkbox. This method has served me well in several projects where we needed to lock in user responses.

3

hey there! i’ve done somethin similar before. you’re on the right track with that script. but instead of protecting the range, try using an onEdit trigger and check if the cell was unchecked. if it was, set it back to checked. that way it’ll stay locked once checked. hope this helps!

4

In a project at my company, I tackled a similar issue by using a combination of a Google Apps Script and protected ranges. I set up an onEdit trigger that checked if a checkbox cell was being unchecked. If it was, the script immediately reverted it back to checked and then applied cell protection. Although there was a slight delay when checking boxes, this method proved more reliable than using data validation alone, especially in a shared environment. Testing thoroughly and keeping user experience in mind were crucial for success.