Is it possible to set permission in Google Sheet for selected cells per user?

0
votes

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.

1
google-sheetsgoogle-sheets-api
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? – Tanaike
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. – Tatranskymedved
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. – ziganotschka
@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. – Tatranskymedved
Thank you for replying. Now I noticed that your issue had been resolved. I'm glad for it. – Tanaike

1 Answers

1
votes

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.