This post will explore how to convert JSON to Microsoft Excel using Encodian Flowr’s Convert JSON to Excel action. This action converts JSON and produces a Microsoft Excel file.
This example scenario involves an HR manager receiving a monthly email with an attached JSON file containing employee performance data collected from various systems. Once the email arrives, a Power Automate flow is automatically triggered, which converts the JSON file to an Excel file and uploads it to an appropriate SharePoint folder for reporting and analysis.
The monthly email is structured as follows:
The attached JSON file contains the following example data (this data has been randomly generated, and these personas do not work at Encodian):
The Power Automate flow is a cloud flow triggered by the ‘When a new email arrives (V3)‘ trigger action:
The ‘Subject Filter‘ input looks for the subject text ‘Employee Performance Data‘, as the monthly emails always have this subject line. The email must also contain an attachment.
Next, we need to loop through each attachment picked up by the trigger action. When using the Outlook connector and Power Automate, all images and files in an email will be included as attachments, including email signatures! You can filter out content like email signatures to actual email attachments using the ‘Attachments Is Inline‘ dynamic content field because any actual attachments are not inline.
As we can see in the image above, this is achieved using a condition that looks at whether ‘Attachments Is Inline‘ is equal to false. If true, we can continue with the next steps down the yes branch.
The first action is the Encodian ‘Convert JSON to Excel‘ action. You can provide a few different input options.
For the ‘Output Filename‘, the current month and year are being used to make the file unique to the current month:
utcNow('MM')
utcNow('yyyy')
There are two different JSON data input options with this action:
As our data is saved in a JSON file, we are using the ‘File Content’ input. To add the file content of the email attachment, you need to convert it to binary using the following expression:
base64ToBinary(items('Apply_to_each')?['contentBytes'])
You will get an error if you don’t use the base64ToBinary expression.
As this action will produce an Excel file as its output, you have some file options:
Now that we have our Excel file, we need to save it.
In SharePoint, I have a document folder called ‘Employee Performance‘. Within this folder are subfolders for each year. I need to save the Excel file in the correct year it was created.
To do this, we can compose the folder path dynamically in a ‘Compose’ action:
/Shared Documents/Employee Performance/@{utcNow('yyyy')}
This is the resulting Excel file after the flow has been triggered:
Now that the data is in Excel, it is in a more readable format, and it is easier to create reports and share them with managers.
Check out Sophie’s companion video on YouTube 👇
Search 150+ Actions to see how Flowr can save you time
Sign up for your free 30-day trial; no cards, catches, or contracts.
No job is too big or too small for our Professional Services team!
Technical Evangelist