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."
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 |
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/IDGet user Designation using REST
https://c2c.sharepoint.com/sites/SPDemo/_api/web/lists/getbytitle('PaymentRequests')/items?$select=Title,Approver/JobTitle&$expand=Approver/IDGet user Department using REST
https://c2c.sharepoint.com/sites/SPDemo/_api/web/lists/getbytitle('PaymentRequests')/items?$select=Title,Approver/Department&$expand=Approver/IDComments:
- How to implement this with Angular JS? I cant' figure out.
14 Dec 2020 16:12:42 GMT
- This does not work in case of multiple entries in people field. Any suggestions?
02 Dec 2020 13:19:44 GMT
- I can achieve this by referring to your blog and able to fetch user name and email, but not able to get the profile picture. Can you help plz
24 Nov 2020 17:44:01 GMT
- I passed email in the expand query but not working.
11 Nov 2020 20:20:49 GMT
- Can you share the correct query for getting SharePoint user display name using Graph API? I want to use in an Azure hosted App.
11 Nov 2020 09:11:43 GMT
- I only get an ID, something wrong with the API call but don't understand. Can you share your email? I can share the query with you for checking.
02 Nov 2020 12:12:32 GMT
- Further comments disabled!
- error CAML Query containing special characters
- [Solved] SharePoint Illegal operation attempted on a registry key that has been marked for deletion
- Trigger Flow on selected Listitem from SharePoint view - create button with JSON column formatting
- SharePoint Excel error - The workbook cannot be opened because it contains the following features that are not supported by Excel in the browser
- Send Email with attachment using SharePoint PowerShell, SMTP server
- How to show or hide columns in SharePoint Online List Library from
- SharePoint excel error - A problem occurred while connecting to the server. If the problem continues, contact your administrator.
- How to get SharePoint Online user details from person or group column using REST API
- How to get SharePoint List Item URL using PowerShell
- How to get the SharePoint Tenant Login URL
- Special character & not working with SharePoint REST API
- How to disable SharePoint subsite creation option for owners
- Managed Metadata error - The data returned from the tagging UI was not formatted correctly
- Deploy SharePoint wsp solution package using PowerShell
- How to create classic site in SharePoint Online
- See actual SharePoint error exception modify web.config
- 'Edit Document' Requires a Windows Sharepoint Services-compatible application and Microsoft Internet Explorer 6.0 or higher
- How to enable anonymous public access for SharePoint Online site collection, file, folder without login ?
- SharePoint installation error - Setup is unable to proceed due to the following error This product requires Microsoft .Net Framework 4.5
- How to add animated Gif to SharePoint Online Page
- [Solved] SharePoint Search Internal server error exception
- How to create SharePoint Document Library
- SharePoint - Use Today's Date Time in list view filter and calculated column
- How to redirect SharePoint Site Collection to different URL
- SharePoint error cannot connect to the configuration database
- Types of brackets used in Programming Languages - Codes - 2022
- How to sort a List using Lambda in Python - Python
- Setting Expire Time - EX EXAT PX PXAT and KEEPTTL with Spring Boot + Redis - Java
- JSON Datatypes : Tutorial - Json-Tutorial
- How to disable SharePoint subsite creation option for owners - SharePoint
- [Fix] Microsoft Windows Store error 0x00000005 (Windows 8/10/11) - Windows
- Installing vue.js in Laravel 8 - PHP
- Steps to Delete or Deactivate Instagram Account - HowTos