DEV Community

SheetError
SheetError

Posted on Originally published at sheeterror.com AI-assisted

You are trying to edit a protected cell or object in Google Sheets: hard block vs Edit anyway

You're an Editor on the spreadsheet. You double-click a cell, or try to type over a formula, and Sheets refuses. The dialog says You are trying to edit a protected cell or object. Please contact the spreadsheet owner to remove protection if you need to edit. The formula is still in the cell. There is no Edit anyway button. That's a hard block, not a warning.

Why it happens

Data → Protect sheets and ranges can put an allow list on one range (or a whole sheet). Restrict who can edit this range → Only you means the owner, and anyone later added to that rule. A file-level Editor is still blocked on that range. Share permission does not override the range rule.

A warning-only rule is a different dialog. It tells you that you're editing a part of the sheet that shouldn't be changed accidentally, and it offers Edit anyway. That click succeeds. If your dialog has that button, you are not on the hard block this article is about.

Reproduce it in a minute

You need two Google accounts. The hard-block dialog does not appear for the owner who created the rule.

  1. On an account you own, open a blank spreadsheet. In A1 type sku, in A2 101. In B1 type double. In B2 enter:
=A2*2
Enter fullscreen mode Exit fullscreen mode

B2 shows 202. You can still edit B2. Nothing is protected yet.

  1. Optional: see the warning dialog first, then remove it. Select B2 → Data → Protect sheets and ranges → range B2 → Set permissions → Show a warning when editing this range → Done. Double-click B2. You can cancel or click Edit anyway. Cancel, then delete that rule in the sidebar so only one rule is in force for the next step.

  2. Share the file with a second Google account as Editor. Select B2 again → Protect sheets and ranges → range B2 → Set permissions → Restrict who can edit this range → Only you → Done.

  3. Open the same URL signed in as the second account. Double-click B2, or select it and type. The dialog is You are trying to edit a protected cell or object. Please contact the spreadsheet owner to remove protection if you need to edit. B2 stays =A2*2. There is no Edit anyway button. Leave the cell unchanged.

Back on the owner account, change A2 to 104. B2 becomes 208 if the formula is intact. The owner is on the allow list, so the owner does not see the block. Protection is per person. It does not freeze the formula's result. Anyone who can view the cell still sees =A2*2 update when A2 changes.

The fix

Leave the second account blocked until you mean to open the range, so you can see the dialog disappear.

On the owner account, open Data → Protect sheets and ranges, select the B2 rule, and Change permissions. Either add the other Google account under Restrict who can edit, or delete the protection. Reload as that account. B2 accepts an edit. The formula is still =A2*2 until someone changes it.

A common setup is the opposite of this recreate: protect the formula column, and leave the input column unprotected so editors can type values. Don't protect A2 and then wonder why the input column is blocked.

Watch out for

  • Commenter is not this dialog. If the account is only a Commenter, the formula bar is read-only on every cell, protected or not. There is no Protect sheets rule to find. Check Share first. This page starts after the account is already an Editor and one range still refuses.
  • If the whole file is read-only, it's a share role, not a range rule. Make the person an Editor, then fix the protection if a single range still blocks them.
  • If the button says Edit anyway, the range is a warning. It has no allow list. Use that option when you want a prompt without a lock. Use Restrict who can edit when the formula must stay. Don't switch the person to Commenter "to stop accidents" — Commenter cannot edit any cell.
  • A whole-sheet rule is the same sentence. If the sheet is protected with an exception range for the inputs, typing in a cell that is not on the exception list shows the same hard-block dialog. The sidebar entry names the sheet, not B2. The sentence on the dialog does not change.
  • Hiding a sheet is not protection. A hidden tab can be unhidden from the tab menu. A protected range stays protected when the tab is visible.
  • Apps Script writes as a user. If that user is not on the allow list, the script fails with the same protected-cell sentence. Re-authorizing the script does not add them to the range.

Originally published on SheetError, a free library of Google Sheets error fixes (I run it).

This article was created with the help of AI.

Top comments (0)