Protected Sheet with Only Certain Cells EditableNot Working as Expected

0
votes

I have a sheet which is protected, but with the option 'except certain cells' which is e.g. cell B2.

Behind B2 there is a validation rule allowing only 'yes' or 'no'.

The goal is to enable certain users (e.g. user1) to ONLY edit the content of cell B2 (= selecting 'yes' or 'no')

In order to enable User1 to open the sheet I assign User1 to be a Viewer or an Editor.

BUT if assigned as an Editor then User1 can change any cell in the sheet which I dont want.

Hence, since I want to enable User1 to only edit cell B2 I have tried to assign User1 as a Viewer of the sheet and then change the 'range editing permission' under 'except certain cells', but this does not solve my problem since User1 is not able to edit B2.

What am I doing wrong? Is what I am trying to do not possible with GSheets? Again: the aim is to allow certain users (User1) to only edit certain cells (B2)

1
google-apps-scriptgoogle-sheets
Hi, I posted an answer. I believe you wanted to do this on the UI, but since I saw you tagged [google-apps-script], I'm unsure of that. Could you please clarify that? – Iamblichus

1 Answers

0
votes

Issue:

  • You can to protect a sheet (excepting cell B2) from a certain user (which should be an Editor, in order to be able to edit B2).

Solution:

  • While logged into an account from which the sheet should not be protected, click Data > Protected sheets and ranges.
  • On the Protected sheets & ranges sidebar, (1) click Sheet, (2) on the dropdown, select the sheet to be protected, (3) check Except certain cells, (4) write B2, and (5) click Set permissions.
  • On the option Restrict who can edit this range, click Custom.
  • Uncheck the editors who should be able to edit B2 but not the rest of the sheet, and click Done.

Reference: