I am trying to fetch a data from SharePoint list to excel. Lookup column with multiple choice (person and group field) is not getting populated in the excel. Can anyone help me with this. Below is my REST endpoint uri being used

<site name>/_api/web/lists/getbytitle('listname')/items?$expand=Owner&$select=Owner/Title



There are lots of reasons mentioned in below article by which your query will not work & will not fetch the data from SharePoint List columns.

One of the example given in below article is

Well if you know SharePoint lists, you know you can change field display names, and many other things in List Settings. In the screen shot above, the “Affiliation” columns are columns which are lookups to the Accounts list. I renamed the column name in the Customers list to be “Affiliate”. This is an important point to remember. The “entity” you are referencing here is the “Affiliation” entity, not the “Accounts” entity. If I call the Account Lookup field “Affiliate” and link by Id, then project the Name and AccountId columns I have to use the following to get the AffiliationId back.

Kindly check below article for OData $expand Query Option.

Using the $expand OData Query Option in SharePoint 2013

Alternate option to get SharePoint list data to excel

You can use Power Query to fetch SharePoint Online List data to Excel.

With this tool, a lot of different sources can be extracted into Excel with just a few clicks. This tip will show you how you can read a SharePoint list with Power Query and how you can extract its contents into Excel.

Kindly check below article for further details.

Extracting a SharePoint List with Power Query

How to Use Power Query for Excel to Query a SharePoint 2013 List

  • power query option looks good but there is no option for fetching a data from SharePoint List as shown in above blog entry – Rahul Dayal Jun 21 '16 at 7:13
  • Do you have this installed? microsoft.com/en-us/download/details.aspx?id=39379 – Hardik Jun 21 '16 at 7:15
  • yes.. same version – Rahul Dayal Jun 21 '16 at 7:15
  • I have added one link... Kindly check it how to use Power query. – Hardik Jun 21 '16 at 7:19
  • Did you tried it? Is it working for you? – Hardik Jun 21 '16 at 8:58

Try this endpoint URL to get the Multi Choice Lookup field data:

var url = "/_api/Web/Lists/GetByTitle('Product Type')/items?$select=Owner/Title&$expand=Owner/ID";

To retrieve the data of each values of multiple field use below code,

var items = data.d.results;
var owners;
for (var i = 0; i < items.length; i++) {
    owners = items[i].Owner.results;
    for (var j = 0; j < owners.length; j++) {
  • below answer doesn't help for me, as i want to fetch data from sharepoint list to excel 2013. – Rahul Dayal Jun 21 '16 at 6:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.