PAD: Convert JSON to Excel

[lcc19]

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?

[GeoffRen]

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.