Create dynamic Excel file from JSON data using MS Flow

[habib6093]

Hello good people, I hope someone can help me.

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

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

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.

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

[DamoBird365]

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 😉

[habib6093]

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? 

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?

[DamoBird365]

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 · Accepted solution]

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).

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

Step 3: Now 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.

Step 4: Now 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

 
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.

[habib6093]

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