Tip #1457: Format JSON for human consumption

It’s not a secret that these days, data float around in automation using the JSON format. Submit a form, get JSON back. It is not human- (or any other carbon-based life form-) friendly.

{ "First Name": "Nancy", "Last Name": "Davolio", 
"Age": 42, "Influencer": true, "Customer": 
"Balderdash Primary School",  "CustomerPONo": 
"PO100042810",  "SalesOrderNo": "sy039978" }

My fellow Power Automate Gymnast, Amey “CI-J” Holden, asked if a code-only connector can be created to help produce nicely formatted emails containing form submission results. And it occurred to me that the JSON Extractor CoC can be used to accomplish the task in three steps:

This image is a screenshot showing a fragment of a Microsoft Power Automate flow. The flow depicts three main steps in the process of converting JSON data into an HTML table which can be inserted into an email. Sample Data: The first step shows an input box with JSON data. The JSON object contains keys such as "First Name", "Last Name", "Age", "Influencer", "Customer", "CustomerPONo.", and "SalesOrderNo.", with corresponding values like "Nancy", "DaVolio", 42, true, "Balderdash Primary School", "PO100042810", and "sy039978". Extract JSON: The second step is to extract JSON using regular expressions. Here, a user would input regex expressions to match and replace property names from the JSON data. However, this part of the flow is not filled out in the screenshot, indicating that the user will need to specify the regex patterns for the extraction process. Select and Create HTML Table: The final displayed step involves selecting specific properties from the JSON data (step not fully visible) and creating an HTML table from the selected data. This is the output that would likely be used to insert the structured data into an email format. The image does not show the actual output or the complete flow, which would likely involve additional steps not captured in the screenshot.
  1. Call Extract JSON action without a regex or replacement. It will return an array of all property names.
  2. Transform the properties array into a name/value array using Select action.
    • Name: item()
    • Value: variables('Sample_data')[item()]
  3. Create HTML table.

This is what comes out on the other end; it looks good enough to go into that important email!

NameValue
First NameNancy
Last NameDavolio
Age42
InfluencerTrue
CustomerBalderdash Primary School
CustomerPONoPO100042810
SalesOrderNosy039978

Note: it only unfurls single-value properties. If a property is an array or an object, it will keep that property “as is”.