We have a master form in Google Sheets that is copied by team members and shared with their clients to be filled in. We are using personal Google accounts associated with work emails, not G Suite accounts. We've recently had issues with team members' clients editing cells they shouldn't be editing and causing issues in the copies. We want to protect those cells in the copies from being edited as explained here or here. We have set that up in the master and it works as far as clients are concerned.
However, when a user makes a copy of the master, here is the behavior we've observed with respect to permissions:
- The permissions that the owner of the master had are transferred to the user making the copy.
- Any users that can access the folder into which the copy is being made except the owner of the master form are given the permissions they have in that folder.
The problem we're running into is the "except the owner of the master form" bit. If the owner of the copy needs assistance from the owner of the master, the copy's owner would need to grant permission sheet by sheet to the master's owner, which is not practical both because of the number of sheets and because of the typical technical level of our average end users. The master's owner has full edit permission for every folder into which copies are made, so this behavior seems more like a bug than a feature.
It seems like the code in Google Sheets that's handling this transition is first adding those with permissions in the folder (which won't re-add the master's owner) and then switches the master's owner's permissions to the copy's owner, which removes the master's owner completely even if they're on the target folder.
The OP of this question seemed to have faced a similar issue, but the answers there don't speak to how to resolve this buggy behavior.
So when making a copy of a Google Sheets spreadsheet with protected sheets or ranges, is there a way for the owner of the copied spreadsheet to be assigned the same permissions as such owner has in the folder into which the copy was made?