How can I create a cell lock / unlock mechanic in Google Sheets similar to Excel?

NOTE: I know how to protect a google sheet and limit editing permissions to certain users. There are many similar and/or duplicates of that question/answer on StackExchange. I am not looking for that. None I have found are what I'm looking for.

Background: There is a fundamental difference in how Excel protected contents and how google protects contents. For many tasks, Google Sheets is far superior, particularly when security is the most important factor. It uses Google's built in authentication to restrict on a per user basis, and that is great, and the method is much more secure than Excel particularly if you use macros, where you'd often have to store the password in plaintext.

However, I have been unable to duplicate the specific lock/unlock mechanic that Excel has. I am a longtime user of Excel and I liked the ability of excel to protect sheets / cells from accidental editing. This is less about security, and more just preventing mistakes which is one way Excel is currently better.

What I mean is: in Excel, if you protected sheets/cells, then ANYONE, regardless of their user permissions, would be stopped from accidentally editing a cell unless they took action to unlock it. Depending on the settings, unlock may or may not require a password. I often didn't even use a password, I simply wanted to prevent accidentally editing a cell with a fixed formula, even by myself. By requiring someone to first "unprotect" (what I would call "unlock" in this context, with or without password) in order to edit the cells/sheet that required someone THINK and realize they SHOULDN'T be editing it UNLESS they needed to update the formula.

In Google Sheets, if you are a user who has write permissions, there's nothing to stop you from accidentally editing over a cell that shouldn't be edited even if the sheet/cells are protected. This is further exacerbated by the fact that Google Sheets auto saves all changes - and unless the user notices and hits "undo" right away the changes stick. Then, the sheet owner has to figure out what happened and fix it, often long after the fact as the change may not be noticeable until long after it was made. To me this is a huge disadvantage of Sheets.

I want that extra check by requiring an unlock type of action in order to edit. Is there a way to do this in Google Sheets?

NOTE: I would prefer a method WITHOUT using Apps Script, but I'm pretty sure that's not possible, so I'm open to a method using an Apps Script also.


1 Answer 1


An editor of a spreadsheet cannot modify sheets or ranges that have been protected from edits. However, if they are the owner of the spreadsheet, they can always edit any cell directly. The exception is with cells that have been protected as Show a warning when editing this range.

As the owner of the spreadsheet, use Data > Protect sheets and ranges > Set permissions > Sheet > Show a warning when editing this range to avoid unintended edits. Use the Except certain cells option to exclude ranges that require no warning.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.