1

Scenario is simple: You as a teamleader managing team attendance in Google Sheet document. Every person should have the ability to edit rows that are only for him. So the person shouldn't be able to edit cells of colleague, but can still enter some data for himself.

Options that Google Sheet provide in the UI are restrictions to:

  1. Protect whole sheet
  2. Protect whole sheet with exception
  3. Protect selected range on the sheet

All of those options provides ability to add/edit permission for this Restriction (this is not clear for first time usage). By setting the user himself in here actually cause:

  • Not apply restriction to him (so the user can still edit sheet)
  • User is able to edit restriction (this is kinda expected)

Question is if there is some way to prevent multiple users from entering multiple cells on same sheet, but he restriction will be applied per user?

Solution I'm looking for can be either from developer or classic user perspective.

I expected something like:

  1. Lock whole sheet
  2. Set permission for user A to edit range A1:B2
  3. Set permission for user B to edit range B2:C3

I've visited How to protect ranges per specific users in google sheet? , but as stated above, it only adds editors, where the restriction is not applied to them.

Improve this question
5
  • I have to apologize for my poor English skill. Can I ask you about your question? 1. What meaning of list of Lock whole list? 2. I cannot understand about I've visited How to protect ranges per specific users in google sheet? , but as stated above, it only adds editors, where the restriction is not applied to them.. Can I ask you about the detail of it? 3. Can I ask you about the difference between the protection of ranges and restriction you expect? Commented Nov 23, 2020 at 23:50
  • 1. Sorry, I have it in different language then EN, so I'm using synonyms to explain the mechanic. It was meant to "protect the list". Right-click on list name, select "Protect sheet" 2. I've tried to enter manually editors for this restriction/protection of list. But once I've did that, the user was able to edit whole list (not only the range that was excluded from protection/restriction) + he was able to edit this restriction. 3. I expect to be able to protect the list with some range exception (same as is now), but additionaly also select user to which this restriction applies. Commented Nov 24, 2020 at 8:20
  • Can you please specify what you mean by list? Do you want to protect a range or a sheet? CAn you show your code and maybe a screenshot of your sheet - to understand the structure of your sheet and the error in your code. Commented Nov 24, 2020 at 9:09
  • @ziganotschka sorry once again, language barrier, in CZ it is "list" where in english it is "sheet". Usually they translate same. Edited question. Which means I want to protect whole sheet expect for same range. Commented Nov 24, 2020 at 10:32
  • Thank you for replying. Now I noticed that your issue had been resolved. I'm glad for it. Commented Nov 24, 2020 at 23:02

2 Answers 2

Reset to default
2

When you lock the whole sheet, unfortunately it is not possible to unlock subranges of this sheet

The reason is that a locking of a sheet has a higher access priority than unlocking of a range.

As a consequence, you will need to use a workaround of splitting your sheet in different ranges and define different permissions for each range.

In your case:

  • Range 1: A1:A2 - only user A has access
  • Range 2: B1 - only user A has access
  • Range 3: B2 - user A and user B have access
  • Range 4: C2 - only user B has access
  • Range 5: B3:C3 - only user B has access

The rest of the sheet also needs to be split up in ranges, from which ALL editors need to be removed.

Obviously splitting up of ranges like this is very tedious, however what helps to automatise the process a bit, is using RangeLists.

I can recomend you use the RangeListApp library developped by @Tanaike.

UPDATE

There is a method called setUnprotectedRanges(ranges) which can overwrite the sheet protection.

Sample:

var protection = sheet.protect().setDescription('Sample protected sheet'); var unprotected = sheet.getRange('B2:C5'); protection.setUnprotectedRanges([unprotected]);

Mind that this method lifts the protection entirely, so you would still need to set a separate protection for the given range to make sure that only 1 user can edit this range.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Decided to change my previous comment: How can You prevent user A to enter values to range A1:B2 and then prevent user B to enter values in B2:C3? I've not found this functionality anywhere.
Just like done in the solution you linked: protection1.removeEditors(protection1.getEditors()); protection1.addEditor('[email protected]'); and protection2.removeEditors(protection2.getEditors()); protection2.addEditor('[email protected]');
I'm really surprised it works, I've been trying to play with that for few hours and the user always had option to edit. But I understood principle and it makes sense/works. Thanks!
Btw. I became now aware of the fact that you can use the method setUnprotectedRanges(ranges). I believe that this should useful to you.
0

You only need to use the owner as restriction layer. Then you can set permissions to multiple users for certain ranges, for exampe:

  • Row 1: Owner and user 1 but not user 2 and 3
  • Row 2: Owner and user 2 but not user 1 and 3
  • Row 3: Owner and user 3 but not user 1 and 2

For all other ranges set the permission to the owner only.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.