SharePoint provides Datasheet view to display the list as a spreadsheet and enables users to bulk edit contents. Thanks to Column/View Permission which can set a column as hidden or read-only, preventing users from accessing or editing the data stored in specific columns, this feature also works in Datasheet View. Users will not see or edit those columns in Datasheet view.
In this article, we will show how column permission works in datasheet view.
This is what the list looks like in the Datasheet View for user Cynthia Carey. Actually, there is a “Monthly Salary” column in the list, but it is hidden from her under any circumstances
Create a list named as Staff Info from the custom list template with the following columns and staff information on a SharePoint site.
Download the trial version of Column/View Permission, and install it to your SharePoint server.
Navigate to the Staff List prepared above, click List, then click Column/View Permission in Settings group, and then click Manage Column Permission.
In the Column Permission Settings page, click Advanced Settings on the top right corner.
In Advanced Settings page, make sure that Allow modification of read-only column option is not selected. (the Allow modification of read-only column option is not selected by default.)
Note that when Allow modification of read-only column option is disabled, users can still edit the read-only columns in datasheet view, but all changes will not be saved in SharePoint.
Click OK to save the settings
In the Column Permission Settings page, click Add Permission Part to create a new permission part (we call a permission configuration as a permission part), specify a unique name for it.
In Include Users section, input user Cynthia Carey in the Select Users/Groups part as following.
Of course, you can input any SharePoint users, SharePoint groups, Active Directory groups, or users represented by Person or Group fields (such as Created By, Modified By and Assign To) or anonymous users.
In Permission Settings section, click Add Column Permission button.
When Add Column Permission dialog appears, set Work Email as Read-only in the “Edit Item Form”. And set Monthly Salary as hidden in the “New Item Form”, “Edit Item Form” and “View Item Form” as shown below.
The settings we configured above makes users we selected in Include Users section (Cynthia Carey) can’t access the column Monthly Salary whenever she creates a new item, or views/edits the existing items in the list. The Monthly Salary column will be hidden from her no matter what. And she can only read the Work Email column when she tries to edit any existing item in the list.
In the Add Column Permission dialog, click OK to save the column permission settings.
Then, click OK to save all settings for this permission part.
Log in to SharePoint as user Cynthia Carey, navigate to the Staff Info list. Click List, then click Quick Edit.
When the Quick Edit mode is on, Cynthia Carey cannot see Monthly Salary column in datasheet view as following.
In datasheet view, try to make change to Work Email column, for example delete the Work Email for item 101, and click Stop to quit Quick Edit mode.
The changes will not be saved, because the Work Email column is set as read-only for Cynthia Carey.