How to get SharePoint Online user details from person or group column using REST API


REST API's are great way to query data from SharePoint Lists, Document libraries, Sites.

But if you refer to a Person (Person or Group) column using REST, you may encounter an exception "The query to field 'Approver' is not valid. The $select query string must specify the target fields and the $expand query string must contains Approver (Microsoft.SharePoint.SPException), where Approver is a person type field."

SharePoint REST API error - person column.PNG
SharePoint REST API error - person column

How to get SharePoint user details using REST ?

You can retrieve SharePoint user (Person or Group) details in REST call by using $expand OData query parameter. You cannot select all details together and need to specify which property to retrieve (say FirstName).

The same applies even for Lookup columns in SharePoint. This approach will work for all SharePoint versions.

Example

Consider you have a custom SharePoint List "Payment Requests" with a person column "Approver".
We want to fetch the Title and Approver First Name.

Incorrect Query - https://c2c.sharepoint.com/sites/SPDemo/_api/web/lists/getbytitle('PaymentRequests')/items?$select=Title,Approver

Correct Query - https://c2c.sharepoint.com/sites/SPDemo/_api/web/lists/getbytitle('PaymentRequests')/items?$select=Title,Approver/FirstName&$expand=Approver/ID

Note that we are expanding the lookup using Approver/ID (Column/ID) and selecting required value as Approver/FirstName (Column/Property).


Similarly we can query and fetch other properties/attributes from the user profile for a given people/lookup column.
Here is a list of attributes which can be queried, replace and use with your $select query as Column/Property.

⚡️ Important - Use exact name with capitalization.

AboutMe
Account
ContentType
ContentTypeID
Created
Deleted
Department
EMail
FirstName
Hidden
IsSiteAdmin
JobTitle
LastName
MobileNumber
Modified
Name
Picture
SIPAddress


Some sample queries,

Get user Email address using REST

https://c2c.sharepoint.com/sites/SPDemo/_api/web/lists/getbytitle('PaymentRequests')/items?$select=Title,Approver/EMail&$expand=Approver/ID

Get user Designation using REST

https://c2c.sharepoint.com/sites/SPDemo/_api/web/lists/getbytitle('PaymentRequests')/items?$select=Title,Approver/JobTitle&$expand=Approver/ID

Get user Department using REST

https://c2c.sharepoint.com/sites/SPDemo/_api/web/lists/getbytitle('PaymentRequests')/items?$select=Title,Approver/Department&$expand=Approver/ID