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
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Top Solution Authors
Top Kudoed Authors
Users online (1,608)