Extract text and dollar amount pairs from email and insert into SharePoint list.

[Nick1]

I receive a daily email from Azure with a breakdown of how much each subscription has spent in the last 24 hours, I want to take the name of the subscription and the dollar amount associated with the subscription from the email and add it to a SharePoint list.

Example email:

-------------------------------------------

From:  Azure
Sent: Thursday, 6 September 2018 11:26 PM
To: Nick
Subject: Daily Spending Report

azure costs
  
Daily Report
This report contains your actual spendings for this month (2018-07) of the enterprise agreement with the enrollment number XXXXXXXX. For further information view the full report.
 
Your actual spendings for this month:
NZD XXXX.XX
Daily Burn Rate at 2018-07-17: NZD XXX.XX 

Daily Burn Rate Overview
The Daily Burn Rate overview gives information about the spendings trend in your subscriptions
100.00 NZD 180.00 NZD 40.00 NZD 20.00 NZD 01 02 03 04 05 06 07 08 09 10 

Your Spending Breakdown
The spending breakdown contains structured information about your current costs for this month (2018-07).
Subscription Actual Spendings
NameOfSubscription1 200.40 NZD
NameOfSubscription2 100 NZD
NameOfSubscription3 20 NZD
 

 We hope you enjoyed receiving this message. If you'd rather not receive future emails from Azure Costs for your enterprise agreement unsubscribe here. - Follow us on Twitter - Visit our Blog
 

My SharePoint list consists of 3 columns, Subscription name, dollar amount associated with subscription and the date of the expense (same as email received date).

Can anyone help explain how best to go about extracting this info from email and inserting it into SP please?

Thanks,
Nick

[v-yamao-msft · Accepted solution]

Hi @Nick1,

If the email body has always the same format, it could be achieved by using a couple of Compose actions.

I have made the following test.

The trigger is When a new email arrives. Then convert the email body from HTML to text.

Add action Compose2 to return the start position of the actual subscription name, input the following code

add(indexOf(body('Html_to_text'),'Subscription Actual Spendings'),29)

Add action Compose3 to get the ending position of the spending:

indexOf(body('Html_to_text'),'We hope you enjoyed receiving this message.')

Add action Compose4 to return all info about subscription name and spending:

substring(body('Html_to_text'),outputs('Compose_2'),sub(outputs('Compose_3'),outputs('Compose_2')))

Add Compose5, split each subscription with the character “NZD”:

split(outputs('Compose_4'),'NZD')

Add Compose6, get the first subscription name and spending then separate the name and spending with the blank:

split(first(outputs('Compose_5')),' ')

Add Compose7(NameOfSubscription1) to return the name of the subscription1:

first(outputs('Compose_6'))

Add Compose8(Subscription1 spending) to return the spending of the subscription1:

first(skip(outputs('Compose_6'),1))

The same with other two subscriptions, please take it for a reference and try it on your side. After getting the subscription name and spending, you could insert the outputs to SharePoint list.

Image for your reference:

Functions reference for WDL:

https://docs.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference 

Best regards,

Mabel Mao

[GabrielStJohn]

Hello, @Nick1!

Thank you for posting on the Flow Community Forum! Have you had an opportunity to apply @v-yamao-msft‘s recommendation to adapt your Flow? If yes, and you find that solution to be satisfactory, please go ahead and click “Accept as Solution” so that this thread will be marked for other users to easily identify!

Thank you for being an active member of the Flow Community!

-Gabriel
Flow Community Manager