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 :

SharePoint O365 Workflow Suspended for External User

Issue :

Recently I was doing a Workflow in SharePoint office 365 site. The workflow was so simple.  Whne a Item is created it Automatically read some values form list and send mail to a Group. It was working fine for SharePoint internal Users.

But when External use fill in  the workflow not started automatically instead giving HTTP 401 Error.

Details: An unhandled exception occurred during the execution of the workflow instance. Exception details: System.ApplicationException: HTTP 401 {"error_description":"The server was unable to process the request due to an internal error. For more information about the error, either turn on IncludeExceptionDetailInFaults 

Then i tried to do the Steps by App Step. I put all the Action under it. Though the Workflow kick stated but it did not found any item giving Item not found error.

Cause :

The cause i found it the list i have created is customized to View/Edit items which Created by the User only  (i.e. by out of box feature ). That's why the App Step could not the find the item. So we need to give the Permission to the Workflow App.

Solution :

I gave permission to the Workflow App by following article.  Create a workflow with elevated permissions by using the SharePoint 2013 Workflow platform

Note - when ever we add any External user we should at lest the user get sync by User Profile service,