cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lcc19
New Member

PAD: Convert JSON to Excel

Hi,

 

I have a goal to convert a large JSON (>10k rows) into excel file row+2 columns.

The JSON looks something like this:

{

"data": [

{

 "field1": "Value1",  "field2": "Value2",  "field3": "Value3",

 "field4": "Value4",  "field5": "Value5",  "field6": "Value6"

},

{

 "field1": "Value1",  "field2": "Value2",  "field3": "Value3",

 "field4": "Value4",  "field5": "Value5",  "field6": "Value6"

},

.....

]

 

The excel should be something like this:

Row1 ColA=value1,value2,value3 ColB=value4,value5,value6

Row2 ColA=value1,value2,value3 ColB=value4,value5,value6

....

RowN ColA=value1,value2,value3 ColB=value4,value5,value6

 

Currently, I am doing this:

- convert json into custom object

- loop through custom object array to extract fields 1 - 6 and append into a list variable

- for each ROW in list 

- for each COL in ROW

- write to excel

- end

-end

 

It works for small JSON payload. For large payload, it just takes too long to complete.

Is there simpler way?

1 REPLY 1
GeoffRen
Microsoft
Microsoft

I would recommend using Office Scripts, specifically Office Scripts with Power Automate. This is the 'Run Script' action on the Excel connector. With Office Scripts you can use the 'Run Script' action to execute javascript against a workbook. So you can pass the JSON in as a parameter of type { field1: string, field2: string, field3: string, field4: string, field5: string, field6: string }[] and then loop through it and create your table using code. I can help provide a sample if you aren't familiar with javascript/code. Though this will need to be using a Cloud Flow.

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!

Users online (2,289)