cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
habib6093
Frequent Visitor

Create dynamic Excel file from JSON data using MS Flow

Hello good people, I hope someone can help me.

I have an application developed with PowerApp where users can select multiple data attributes (columns).

habib6093_2-1625593231150.png

 

Upon clicking on the "Submit" button, the app should generate an Excel file with the data of selected attributes.

habib6093_1-1625593148858.png

I created an MS flow that makes an HTTP request and gets an array of objects. I need to generate an excel file according to the response.

habib6093_3-1625593555475.png

 



Please help me to convert the JSON body to an Excel file every time the flow is triggered.
Thanks in advance.

1 ACCEPTED SOLUTION

Accepted Solutions
habib6093
Frequent Visitor

After a lot of trial and error, I was able to solve the problem. At first, I created an Excel file as a template and customized it (set default table design, cell behaviour, font style according to my requirements). After that, I stored the template (excel file) in a SharePoint folder. Now in MS flow, I used the stored excel file to generate the output excel file. The steps are explained below.

Step 1: Select the template excel file to get the file contents(Customizations).
habib6093_0-1631560137966.png

 

 
Step 2: Create an output excel file with the template file content.

Step 3Now create a table inside the newly created output excel file and set A1:A1 as table range. Afterwards, provide comma-separated column names in the Column Names section. In my case, I prepared a string earlier with the attributes I needed as columns.

 

habib6093_1-1631560138493.png

 

 

Step 4Now insert objects into the table of the output file. Data will be assigned into respective columns according to the attribute name. In my case, I had an HTTP response like the following.

 

HTTP response

habib6093_2-1631560136805.png

 

 

habib6093_3-1631560139174.png

 

 
Step 5: Your excel file is ready with the data.

 

I don't think my requirement is uncommon. I hope someone will find my approach useful.

View solution in original post

5 REPLIES 5
DamoBird365
Super User
Super User

Hi @habib6093 

 

If you're looking to create an excel file, you either need a template excel file with a table (that you create a copy of) or you use Office Scripts.

 

You can see how to populate an excel file with an Excel script here https://youtu.be/Q7GLQnvJJF0

 

There's some example code here for you to try too https://techcommunity.microsoft.com/t5/microsoft-365-pnp-blog/excel-scripts-and-cloud-flows-data-man...

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien


P.S. take a look at my new blog here and like & subscribe to my YouTube Channel thanks 😉

 

 

HI @DamoBird365

Thank you so much for the resource you provided, but I'm still wondering how to insert data into the columns when the total column count can increase and decrease based on user input as users can select which attributes they want to see in the Excel file. For example, users want to see some fruit production in every city. In that case, each user might want to see different types of fruits. The only information I will get is a JSON response from an HTTP call. Is there any way I can generate the columns dynamically and put data accordingly from that JSON response? 

habib6093_2-1624192396244.png


habib6093_0-1624195008134.png

 

habib6093_3-1624194152503.png

 


Also, I'm thinking of a different approach, creating the Excel file in the backend. In that case, Is it possible to save a file in SharePoint if I return the file as an HTTP response?

 

 

 

 

Hi @habib6093 

 

You can create an anonymous sharing link if your tenant has been enabled for this.  See the following https://www.damobird365.com/expose-sharepoint-data-to-the-web-with-http-api

 

In terms of dynamic columns, an office script could deal with that scenario.

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien


P.S. take a look at my new blog here and like & subscribe to my YouTube Channel thanks 😉

habib6093
Frequent Visitor

After a lot of trial and error, I was able to solve the problem. At first, I created an Excel file as a template and customized it (set default table design, cell behaviour, font style according to my requirements). After that, I stored the template (excel file) in a SharePoint folder. Now in MS flow, I used the stored excel file to generate the output excel file. The steps are explained below.

Step 1: Select the template excel file to get the file contents(Customizations).
habib6093_0-1631560137966.png

 

 
Step 2: Create an output excel file with the template file content.

Step 3Now create a table inside the newly created output excel file and set A1:A1 as table range. Afterwards, provide comma-separated column names in the Column Names section. In my case, I prepared a string earlier with the attributes I needed as columns.

 

habib6093_1-1631560138493.png

 

 

Step 4Now insert objects into the table of the output file. Data will be assigned into respective columns according to the attribute name. In my case, I had an HTTP response like the following.

 

HTTP response

habib6093_2-1631560136805.png

 

 

habib6093_3-1631560139174.png

 

 
Step 5: Your excel file is ready with the data.

 

I don't think my requirement is uncommon. I hope someone will find my approach useful.

View solution in original post

habib6093
Frequent Visitor

I have modified my answer with the steps. Hope it helps.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Users online (3,292)