Forum Discussion
SharePoint Online column level permission
We would like to set the column level permission in our SharePoint online site list. But i aware there is no option to set the column level permission for the list.
Is there any way (using Flow or Powerapps) to achieve this?
11 Replies
- Turn on Content Types and hide the field in the content type, then disable quick edit.
You can also create a PowerApp form and set the field to read only or add a condition that allows users in a second list to edit the field. This is example code you would add to the Display field for the datacard, where the second list is named List Admins and the field you use is EmailAddress. It gets the current user email and compares it to the list: If(User().Email in 'ListAdmins'.'EmailAddress',DisplayMode.Edit,DisplayMode.View)
Display = If(User().Email in 'ListAdmins'.'EmailAddress',DisplayMode.Edit,DisplayMode.View)
I realize this is an old thread but if it helps others in the future, the only way to do this is to setup separate lists, one which has the column and the other which does not. Then use Power Automate or Event Receivers to synchronize information between the two lists. This is the only true way of "mimicking" column level permissions.
HTH!I found one workaround using modern sharepoint column formatting. I was able to use the following code to only show column values to the listed users in the display parameter:
{"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "div",
"children": [{"elmType": "span",
"txtContent": "@currentField",
"style": {
"display": "=if(@me=='email address removed for privacy reasons' || me=='email address removed for privacy reasons' , '', 'none')"}}]}I also had to add conditional formulas to each column I wanted to restrict so that they do not show in the new item/edit item forms. The formula is similar to the display parameter above:
=if(@me=='email address removed for privacy reasons' || me=='email address removed for privacy reasons' , true, false)
Now for the caveats!
1. In my case, most of the columns I am hiding are choice columns. If a person who is restricted happens to open the list in a Quick Edit view (grid view), they could actually manipulate the hidden values.
In Quick Edit mode (also in the new item/edit item form if you do not hide using a conditional formula), when they click on that column, it shows a collapsed and blank dropdown but when they hover over the dropdown, it pops up a tooltip with that hidden value and it allows the user to select it. The value is not visible in the field after they select it but it has in fact been saved as an updated value. I simply disabled Quick Edit mode to resolve this issue.
2. You will want to disable the ability for the user to create personal list views. If not, they could create their own Quick Edit view and affect the restricted fields.
3. I have yet to find a way to create a centralized list of users instead of hard coding it every time in every column formatting field as shown. There has to be a better way! If someone has a better solution, please let me know!
Hi Laronw
Great approach but the one other issue with this solution will be that values from the hidden columns will also show up in Search and related APIs. You'll want to be careful of that as well.
Thank you
Jeyganesh As you know the answer there is no way you can apply column level security but you can write some business logic which will prevent happening the same.
I followed the approach you could try so :
1] Disable quick edit option
2] Give users only edit access
3] Create two columns Status and Status_BKP
4] (Need to handled by your choice business logic handler Remote EH, Flow or Workflow ) Copy the Status Data to Status_BKP if only users who are intended users make the change.
5] If untended users try to modify then send them notification with appropriate reason
6] Control the at the form level but depends which version you are using modern or classic
I hope this gives you some pointer to do some workarounds.
5]If any unintended user try to
- If you provide more information, we might be able to help here...by the way, the more you can achieve is to "simulate" column level permission since this is, as you wel say, a feature not present in SharePoint
In our document library properties we have one column called "Status". This particular column value should be change for the particular people, other people can view only.
Jeyganesh, did you find a solution?
Just thinking out loud to myself here:
What if you had two tables with different permissions?- Table-1 and Table-2 would be nearly identical. However, Table-2 would have a Status column.
- Users would fill in the fields in Table-1 where they are given Contribute rights.
- Workflow copies new row from Table-1 to Table-2 then deletes item from Table-1.
- Managers change status in Table-2 and users only have read access to this table.
Give users a link to the Table-1 form to add content.
Give users a link to the Table-2 list-views to see content.
I also had a thought about lookup fields grabbing calculated column rows from another table and using Javascript to filter on a matching ID. The above seems way simpler.
