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!
- SharePoint error - An exception occurred when trying to issue security token: This implementation is not part of the Windows Platform FIPS validated cryptographic algorithms..
- PowerShell iterate and get all SharePoint sites and sub sites
- SharePoint Online: Editing session has ended Message
- How to Share Microsoft SharePoint Site with Users or Groups
- How to delete SharePoint Online List Item using REST API
- SharePoint List excel import - This table exceeds the maximum number of supported rows
- How to disable SharePoint subsite creation option for owners
- SharePoint 2010 August 2015 Update KB3055049 - Duplicate Document ID issue bug fixed
- SharePoint Server 2016 Preview installation error - This Product Key isn't a valid Microsoft Office 2016 Product Key. Check that you've entered it correctly.
- How to upload file programmatically to SharePoint Document Library using Server Object Model C# .Net
- Access URL for SharePoint Tenant Admin Center (Online Office 365)
- Error when deleting SharePoint Online folder or file
- How to extend retiring SharePoint 2010 Workflows and continue with Office 365
- How to generate client id and secret to register SharePoint App with OAuth
- How to delete SharePoint List Item programmatically using C#.Net
- How to retrieve all SharePoint Online List Items using Rest API
- [Solved] SharePoint Search Internal server error exception
- Create SharePoint Site Collection using PowerShell New-SPSite
- Fix SharePoint PowerShell error - The term Get-SPweb is not recognized as the name of a cmdlet function script file or operable program
- How to enable anonymous public access for SharePoint Online site collection, file, folder without login ?
- SharePoint Server 2016 installation System Hardware requirements
- How to create SharePoint Online List Item using REST API
- Changed AD user display name showing old name in SharePoint
- Recommended size and resolution for SharePoint Online Site logo
- Microsoft 365: How to Turn Off Delve in SharePoint Online for All Users
- How to Create AWS SNS Topic using AWS CLI - AWS
- What is the Max and Minimum Value of int type in Python? - Python
- Add Bookmark macOS Safari - MacOS
- How to Clear Cache for a website (URL) in Safari for Mac - MacOS
- Java Generics explained with simple definition and examples - Java
- Bash Command to Download a File From URL - Bash
- PHP Code for sending Emails - PHP
- Fix: Invalid Gradle JDK configuration found. Could not find the required JavaSDK - Gradle