Fetch lookup column data in excel using ODATA Feed from SharePoint List

Question

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

Thanks

Answer 1

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

Answer 2

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++) {
        alert(owners[j].Title);
    }
}