How to automatically scrape data from incoming emails and put them into a SharePoint list

Step by step flow that activates when an email comes in and scrapes the data into a SharePoint list

This flow converts an incoming email into data and places the data logically into a Microsoft List.
Prerequisites are:
  • MS list already setup with data columns setup

  • Outlook shared email box, not a personal mailbox.

  • Emails are not random and have a solid data structure to them.

Objective

Note the detailed structure of the email. It is an outputted email generated from another process.
Heavily redacted, i know, but you get the idea.

What the incoming email (source) looks like

Not a long flow, but flows don't need to be long to be good.
The gist is that when an email comes in, the automatic trigger kicks off the flow. It converts the email to data and then places the data logically into the MS list.

Overall flow

Create an automatic trigger using the "When a new email arrives (V3) which activates when an email arrives.
  1. Mailbox - Select the mailbox where the emails will be sent to.
  2. Folder - Default would be Inbox, but you might set this differently if you have multiple flows going concurrently.

Trigger - When a new email arrives (V3)

Here is where we need to convert the HTML email message, strip out objects etc, and ground it all down to raw text.

Html to text

triggerOutputs()?['body/body']
This first Compose component is to help with separating the money amount so that it's captured perfectly. This is especially difficult because there are brackets in my email which confuses the system.

Compose - Definitions

float(replace(trim(first(split(last(split(string(outputs('Html_to_text')?['body']), 'EUR ')), '(Equivalent)'))), ',', ''))
Rather than having a separate Compose for each piece of data extracted and pulled, we opt instead to have a "compose sandwich" which gets all the data in one go. There is a mixture of extraction logics here but here are the most common.

Compose - Sandwich

if(contains(outputs('Html_to_text')?['body'], 'Engagement:'), trim(first(split(last(split(outputs('Html_to_text')?['body'], 'Engagement:')), decodeUriComponent('%0A')))), '')
trim(first(split(last(split(outputs('Html_to_text')?['body'], 'Confidentiality:')), decodeUriComponent('%0A'))))
if(contains(outputs('Html_to_text')?['body'], 'Total'), replace(replace(replace(replace(replace(replace(replace(replace(first(split(last(split(outputs('Html_to_text')?['body'], if(contains(outputs('Html_to_text')?['body'], 'Total (package aggregate):'), 'Total (package aggregate):', 'Total:'))), decodeUriComponent('%0A'))), ':', ''), '(Equivalent)', ''), 'EUR', ''), 'USD', ''), ',', ''), '(package aggregate)', ''), ' ', ''), decodeUriComponent('%C2%A0'), ''), null)
Then you need to set up a Create Item and point it to your MS list.
Name the URL and the List name. The List should come up in the dropdown, otherwise you can write an expression to point to it.

Then you will need to open up the parameters and fill in each of the columns that you want to automatically populate.

Not all are necessary, and you only need to populate data that you are after. I use the sender and date for testing purposes as you will likely test the hell out of this and run into issues.

Typical expression is below, but obviously will differ depending on the data obtained. Note that its a straightforward call to the Compose sandwich.

Create Item

outputs('Compose_1')?['confidentiality']
Like most flows, you're going to run into several bugs and problems with the flow. If in doubt:
  • Open in code view to see what inputs/outputs are being collected
  • Look at raw inputs and outputs when given the error message. You'll be able to see if the data is being captured or if it is empty or just wrong.
  • Use another Compose and run the flow. The Compose will show you what's captured.

Troubleshooting