Export JSON (SharePoint/SQL Data) with Dynamic Columns to Excel

 Hi Team,

    Recently there was a requirement to Export SQL Data into Excel. It was bit tricky. Below are the requirements.

1. Number of Columns is Dynamic so it can be fixed.
2. Number of Rows obviously dynamic.
3. Export should fast enough.

You can solve the requirement by below.

1. Crete a Excel File.
2. Add Excel Table into it.
3. The Add each row into it.

But Excel Table take 2 things Table Range / Columns Name which is not easy to in put since they should be dynamic in our case.

so our solution should be

1. Set the JSON output from SQL/SP into a Array Variable.
2. Then we need the Columns in the JSON. For that
1. Get the length of JSON and we will get the Number of Rows. Suppose we get 100.
2. Get the First Item from the JSON. Which will contain the first item along with Columns in it.
3. Create CSV from the first item. Which will help getting the Columns easily since the First item of the CSV will be our Columns.
4. By taking again First of the CSV we will get all the Columns with Comma separated with is our Columns Name with comma separated. 😊.
5. Then we will Spilt the the Table range with Comma and we will get Column Array with which we will able to know the Number of columns in it Which will help us to get the Table Range.
6. since we know how many number of columns we need so its easy to get the Range. 
7. We will create a Array like 'A,B,C,D,...' as much as possible.
8. the Split it with Comma. Then by the index of Column -1 will be our range.
9. Example - Suppose we get we have 4 columns in out JSON. So the range would A1:D100
10. where D is  4 - 1 = 3 . Index start from 0 with A.

Will update my Video link into the same soon. 

Power Automate Design :