Export SharePoint Multiple Lookup Column Data to Excel Spreadsheet

[alht]

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:

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?

[Sundeep_Malik]

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]

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.

How do I just combine the countries?

Eg:

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

[glfern]

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!

[jerome18]

You will find a solution to extract the information from an array you want to put on a CSV table or HTML table. Follow Tom Riha blog :

https://tomriha.com/export-multiple-person-or-group-column-into-csv-table-in-power-automate/