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
 WHAT’S NEXT AT MICROSOFT IGNITE 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Register for a Free Workshop.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Top Solution Authors
Users online (1,628)