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

Question

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.

Answer 1

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.