Expanding fields not working fully on SharePoint Lists

user_1188023 0
Viewed 1681

I am following the documentation for Get metadata for a list.

Querying using either PowerShell or the Graph Explorer fails to fully expand the fields for items in a SharePoint list.

An example of this is a lookup field called Responsible that looks up users in Azure Active Directory (or in SharePoint terms, the column is a Person or Group column, limited to people only).

Once selected via the GUI, it's populated with a display name (although I'd hope for more definitive information to be stored on the back end, like UPN).

When querying the Graph API using the form:

$Uri = "https://graph.microsoft.com/v1.0/sites/$($SPSite.id)/lists/$($ServiceList.id)/items?expand=fields"
$Data = Invoke-RestMethod -Headers @{Authorization = "Bearer $accesstoken"} -Uri $Uri -Method Get -ErrorAction Stop

we get something like this:

@odata.etag               : "REMOVED"
Title                     : Storage Platform
Description               : Central storage platform
ResponsibleLookupId       : 14
Responsible2LookupId      : 13
AccountableLookupId       : 3
Features                  : NFS
AudienceLookupId          : 92
RequestProcess            : {@{LookupId=1; LookupValue=Service Desk}}
Support                   : {@{LookupId=1; LookupValue=Service Desk}}
AvailabilityLookupId      : 1
DependsOn                 : {}
O365GroupLookupId         : 87
LifecycleStageLookupId    : 2
ConsultLookupId           : 88
id                        : 1
ContentType               : Item
Modified                  : 2017-11-17T10:47:07Z
Created                   : 2017-11-17T10:47:07Z
_UIVersionString          : 1.0
Attachments               : False
Edit                      : 
LinkTitleNoMenu           : Storage Platform
LinkTitle                 : Storage Platform
ItemChildCount            : 0
FolderChildCount          : 0
_ComplianceFlags          : 
_ComplianceTag            : 
_ComplianceTagWrittenTime : 
_ComplianceTagUserId      :

You can see that the field ResponsibleLookupId just gives a value of 14 which is not useful. Other fields link to Office 365 Groups, but again return values. As such it's impossible to link any of this data to users/groups and is very limited in value except when looking at it through the portal.

How do we expand this data? Will it be provided by the API call at a later date, or do we have to perform further look ups?

microsoft-graph-api sharepoint-online
1 Answers

By default, Microsoft Graph will return the LookupId for lookup fields. You can ask it to provide the actual value by specifically requesting that field in a $select parameter.

Using the following query will return the displayName rather than the LookupId for Responsible:

...items?expand=fields($select=Responsible)

You can read about how this works in the documentation for FieldValueSet.

As for returning the userPrincipalName, currently you can't control which value it returns (it's either LookupId or displayName).I recommend visiting the UserVoice and adding your suggestion.

user_1055180
Related