How to get lookup columns data from SharePoint Lists with Graph API

Question

Hi,

I'm using Graph API through Python script to recover data from Online SharePoint Lists.

Here is the URL :

"https://graph.microsoft.com/v1.0/sites/{SITE_ID}/lists/{list_id}/items?$expand=fields&$select=id,fields,createdDateTime,lastModifiedDateTime&$top=5000"

My issue is that this doesn't provide the lookup columns. All the text, numeric, date fields are retrieved but all columns referencing another list are not in the result (I could work with just the ID).

I tried to add the name of the column in the select:

"https://graph.microsoft.com/v1.0/sites/absyss92.sharepoint.com,6207e219-b65c-47e9-b30b-d80438dce575,81746d01-6547-4a95-80df-6a46e4b111c0/lists/89fa0c6f-b14d-4ce3-831a-ff166aa5c510/items?$expand=fields&$select=id,fields,createdDateTime,commercial,lastModifiedDateTime&$top=5000"

But it gives me the error:

"Parsing OData Select and Expand failed: Could not find a property named 'commercial' on type 'microsoft.graph.listItem'."

Any idea on how to adapt the URL to get this data ?

Answer 1

Hello Charles-Antoine Dolbeau,

Thank you for reaching out to Microsoft!!

For the lookup columns in SharePoint, they are stored as references to other lists, and their values are not directly included in the fields property of list items. Instead, you need to use the LookupId property to retrieve the ID of the referenced Item.

Below steps might help you resolve the issue:

• Update your query to include the lookup column's internal name followed by lookupId. For example

 https://graph.microsoft.com/v1.0/sites/{SITE_ID}/lists/{list_id}/items?$expand=fields&$select=id,fields,createdDateTime,lastModifiedDateTime,fields/commercialLookupId&$top=5000

• The fields/commercialLookupId will give you the ID of the referenced item in the look up column.
• Ensure that you are using correct internal name for the look up column. You can find this in the SharePoint list settings under "Column settings".
• Ensure that the lookup column is indexed, matched the internal name and it should be accessible via Graph API.

Reference Link: https://learn.microsoft.com/en-us/answers/questions/916552/how-to-expand-lookup-field-when-listing-items

Hope this helps.

If the answer is helpful, please click Accept Answer and kindly upvote it. If you have any further questions about this answer, please click Comment.