I was reading through Scott’s recent post -> https://www.hanselman.com/blog/ConvertingAnExcelWorksheetIntoAJSONDocumentWithCAndNETCoreAndExcelDataReader.aspx and I was thinking that this could be done in a codeless way using Microsoft Flow. So, I tried to do this in Flow, and it was really easy to do this – under 15mins, as all the plumbing is already there. This is my Flow.
The Flow is triggered by a HTTP GET and reads from an Excel file stored in OneDrive.
The output is already a JSON, but not a valid one probably one, as it has spaces and special characters in the key names, which map to the column names of the table.
So, we can simply re-shape the data using Select, and dump the JSON response.
Now, when I call my trigger URL, the Flow executes and returns me the JSON.
With Flow add-on for Excel, you could run the same Flow from Excel itself, if you want to.
Hope you find this useful.