Processing a JSON file being received by email with Power Automate

Introduction

In this tutorial, we will learn how to process a JSON file received via email using Power Automate. This guide will take you through converting base64 encoded email attachments into a string, parsing the string into JSON format, and finally extracting relevant data for insertion into an Excel table. This process is useful for automating data collection from emails and organizing it in spreadsheets.

Step 1: Set Up Email Trigger in Power Automate

1. Create a new flow:

   • Log in to Power Automate.
   • Select "Create" from the left menu.
   • Choose "Automated cloud flow."

2. Select trigger:

   • Search for "When a new email arrives" trigger.
   • Configure the trigger to monitor the appropriate email account and folder.

3. Add conditions (optional):

   • You can set conditions to filter emails based on specific criteria, such as sender or subject line.

Step 2: Get Attachment Content

1. Add action to retrieve attachments:

   • Click on "Add an action" below your email trigger.
   • Search for "Get attachments" and select it.
   • Specify the "Message Id" from the trigger to ensure it retrieves attachments from the correct email.

2. Retrieve attachment content:

   • Add another action, search for "Get attachment content."
   • Use the "Message Id" and the "Attachment Id" from the previous step.

Step 3: Convert Base64 to String

1. Add a Compose action:

   • Click on "Add an action" again.
   • Search for "Compose" and select it.

2. Input expression:

   • In the Compose action, input the following expression to convert base64 content to a string:

     base64ToString(body('Get_attachment_content')?['$content'])
     

Step 4: Parse JSON

1. Add Parse JSON action:

   • Click on "Add an action."
   • Search for "Parse JSON" and select it.

2. Configure the action:

   • In the "Content" field, select the output from the previous Compose action.
   • For the "Schema," you can either provide a schema manually or generate it by using sample data.

Step 5: Extract Data and Insert into Excel

1. Add action for Excel:

   • Click on "Add an action."
   • Search for "Add a row" in Excel and select it.

2. Configure Excel table:

   • Select the Excel file and the specific table where you want to insert the data.
   • Map the fields from the parsed JSON to the corresponding Excel columns.

3. Test your flow:

   • Save the flow and test it by sending an email with a JSON attachment to the monitored account.

Conclusion

By following these steps, you can automate the process of extracting data from JSON files received via email and organizing it into Excel spreadsheets. This method enhances efficiency and reduces manual data entry. Next, consider exploring additional actions in Power Automate to further expand your automation capabilities, such as sending notifications or integrating with other applications.