I've been wrestling with the best way to do this for awhile. Requirements:
1) I have a single spreadsheet with existing data (one row per person) that needs to be updated regularly.
2) There are 4 different types of users: Group A, Group B, Group C, and Admin.
3) Column C in each row identifies if it is data for a Group A person, Group B, or Group C.
4) Group A people should only be able to update Group A data, Group B only Group B data, etc.
5) Only the Admin should be able to add/delete rows (i.e. add or delete people from groups).
6) Admin should be able to update any row.
I looked into using "Allow users to edit ranges", but the problem is the data is pretty dynamic (rows are added/deleted regularly), so it seemed I would be constantly redefining the ranges.
I also looked into "Protect Sheet" for the admin piece. The problem is even though I only select "Insert rows" and "Delete rows" from the "Protect Sheet" menu, it seems to lock down every cell where I can't input data.
A possible (although clunky IMO) solution would be to test for the contents of Column C for the respective row every time somebody tries to change a cell. Then, based on that, prompt for a password. For example, I'm trying to enter data on a Group A row. I would be prompted for a password, and would need to enter "Group A password" into the dialog box for that cell to be unlocked. I would think a more elegant solution would be to ask for a password when opening the spreadsheet. Based upon the password (4 options: Groups A-C & Admin), grant the appropriate level of access.
Any ideas on how to do this? I'm open to any solution.
Thanks for your time.
1) I have a single spreadsheet with existing data (one row per person) that needs to be updated regularly.
2) There are 4 different types of users: Group A, Group B, Group C, and Admin.
3) Column C in each row identifies if it is data for a Group A person, Group B, or Group C.
4) Group A people should only be able to update Group A data, Group B only Group B data, etc.
5) Only the Admin should be able to add/delete rows (i.e. add or delete people from groups).
6) Admin should be able to update any row.
I looked into using "Allow users to edit ranges", but the problem is the data is pretty dynamic (rows are added/deleted regularly), so it seemed I would be constantly redefining the ranges.
I also looked into "Protect Sheet" for the admin piece. The problem is even though I only select "Insert rows" and "Delete rows" from the "Protect Sheet" menu, it seems to lock down every cell where I can't input data.
A possible (although clunky IMO) solution would be to test for the contents of Column C for the respective row every time somebody tries to change a cell. Then, based on that, prompt for a password. For example, I'm trying to enter data on a Group A row. I would be prompted for a password, and would need to enter "Group A password" into the dialog box for that cell to be unlocked. I would think a more elegant solution would be to ask for a password when opening the spreadsheet. Based upon the password (4 options: Groups A-C & Admin), grant the appropriate level of access.
Any ideas on how to do this? I'm open to any solution.
Thanks for your time.
