CODEWITHSUNDEEP
sharepointmicrosoft-graph-apisharepoint-online
SteveCl
SteveCl

Reputation: 4569

Using Graph API to query SharePoint list items and expand user field

I'm trying to query for some SP list items, all is working fine except I can't seem to expand a custom column of type Person.

I can see the createdBy and lastModifiedBy expanded and even includes the AAD user id, which is great and also leads me to think what I want is possible!

But mine is a custom column.

I'm running this and can only seem to get the SP user list id and the user's display name...neither of which are much use.

/items?expand=fields(select=UserLookupId,User)

Ideally I'd like to get the AAD user id as per createdBy and modifiedBy field, but the email would suffice.
Otherwise the only way I can see is to query the User Information List (using the UserLookupId) to get the email?

Thanks

Upvotes: 0

Views: 7090

Answers (2)

Ryan Byrd
Ryan Byrd

Reputation: 1

This was my experience modifying the Build Angular single-page apps with Microsoft Graph. In the examples below, I changed my id's out with the default text.

Here is The Finished Project on thier github

In Graph Explorer, this worked. You can verify it at the Microsoft Graph Explorer.

https://graph.microsoft.com/v1.0/sites/{site-id}/lists/{list-id}/items?expand=fields($select=id,Title)

In the app/graph.service.ts in the app, this did not work. Even though you would expect it to based on the graph explorer.

.api('/sites/{site-id}/lists/{list-id}/items?fields($select=id,Title)')

Changing the app/graph.service.ts api call worked.

.api('/sites/{site-id}/lists/{list-id}/items?')
.expand('fields($select=id,Title)')

The result looked like this:

fields: {
    @odata.etag: ""d6f5b6ea-9f90-452d-98ba-e838f58d3359,1"", 
    Title: "IT SPECIALIST (MID)", 
    id: "20"
}

Here's an example site id: 

some.sharepoint.com,9dk062b-2e54-4e4f-b71a-cdb74f42cc44,c6cf6b0a-cc7c-41fd-a76a-ef8f97e8a22f

Here's an example list id.

8eg8c29a-5555-4cfc-bfa4-0e907488f781

The end url won't have any {} in it.

Upvotes: -1

Vadim Gremyachev
Vadim Gremyachev

Reputation: 59318

This appears to be correct assumption:

Otherwise the only way I can see is to query the User Information List (using the UserLookupId) to get the email?

for non-system user fields, it is indeed a way to go, but there are some distinctions whether user field is multi-valued or single-valued. If Approvers is a multi-valued user field, then the following query:

https://graph.microsoft.com/v1.0/sites/{site-id}/lists/{list-id}/items?$expand=fields($select=Approvers)

returns email and id properties along with displayName property for user field value.

While for single-valued user field only id (available via {userfield}LookupId property) and displayName properties could be requested via items endpoint, for example:

https://graph.microsoft.com/v1.0/sites/{site-id}/lists/{list-id}/items?$expand=fields($select=Approver,ApproverLookupId)

So, indeed User Information List needs to be utilized to request additional user properties, for example:

https://graph.microsoft.com/v1.0/sites/root/lists('User Information List')/items/{item-id}/?$expand=fields($select=Email)

where item-id corresponds to user field lookup id

Upvotes: 4

Related Questions