cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
alht
Frequent Visitor

Export SharePoint Multiple Lookup Column Data to Excel Spreadsheet

Hi All,

 

I am trying to create an excel report based on a SharePoint list which uses a Lookup column with mutliple values.

 

Question: 

How do I get the data from a SharePoint lookup column shown in the Excel spreadsheet. 

 

 

EG:

alht_2-1666616961814.png

 

 

If I do this then I get the following data in my spreadsheet

 

{"@odata.type":"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference","Id":1,"Value":"Denmark"},{"@odata.type":"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference","Id":2,"Value":"United Kingdom"}

 

Do I need to use a JSON Parser?

 

How can I do this?

 

 

3 REPLIES 3
Sundeep_Malik
Community Champion
Community Champion

Hey @alht 

 

Yes you can try json parser.

 

Just add the below data in generate from sample of Parse JSON action.

[{"@odata.type":"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference","Id":1,"Value":"Denmark"},{"@odata.type":"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference","Id":2,"Value":"United Kingdom"}]

 

And in the country field just pass the Value dynamic field you will get from parse JSON.

 

And also make sure in the types of value you give type: [string, null] 

So, if you get a null value in that, it does not give an error.

alht
Frequent Visitor

Thanks for the response! 🙂

That sort of worked!

However rather than creating a comma separated list of countries in a single cell it create a new entry in the excel spreadsheet for each country.

 

alht_0-1666618260083.png

 

How do I just combine the countries?

 

Eg:

 

Megan Brown | MeganB@email.com| Denmark, United Kingdom, USA

glfern
New Member

Hello there - I am trying to do this exact same automation, but am a total beginner. Would you be able to share how you set up each step in your flow so I can base mine off of yours?

 

TIA!

Helpful resources

Announcements
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Community Calls Conversations

Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Users online (1,553)