Our team has run into the issue within one of our collective department initiative tracker and intake system with how to ensure teams cant changes columns and rows that dont belong to them.
Our team has created a initiative tracker system that allows team members to view or add strategic initiatives to our departments current list of efforts. This system also allows us to mark these initiatives as approved or not approved by our executive leadership team. This list then pulls into our Power BI reports to communicate the cumulative portfolio for these initiatives and the categories they fall into.
The issue we are facing is at the moment we would like to limit the ability to edit cells by rows and columns per someone's role in the department/sheet wide (Executive Leadership) or role on an initiative/row (initiative lead). At this time only the executive leadership component can be accomplished by locking the approval box column for executive approval and making them an admin. The issue is this grants them the ability to access all other columns even though we truly only want them to be able to edit one specific column. Unfortunately initiative leads are currently able to edit any initiatives columns outside of the locked columns even though the desire would be for them to only be able to edit the ones in the row they are marked as leads for.
It would be nice to be able to enforce data permission levels and groups for columns and rows similar to other project management platforms. This could be enforced via sheet wide groups or via conditional rules row by row controlled by a certain columns value.
An attempt at a visual example of this is shown below.
In this case the area in red would be locked to everyone outside of a group that is enforced sheet wide (Executive Leadership Group). The area in blue on the other hand could be lock and controlled on a row by row basis (depicted in yellow) where the permission to make changes to the columns for that row is controlled by the user associated to the actor column's (marked in black) row value.
This would allow for additional controls to be in place and allow edit capabilities without just making all individuals who have sensitive data inputs an admin.