sharepointmicrosoft-graph-apisharepoint-online

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

Solution

  • 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