cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Nick1
Frequent Visitor

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

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

1 ACCEPTED SOLUTION

Accepted Solutions
v-yamao-msft
Community Support
Community Support

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:

1.PNG2.PNG

 

Functions reference for WDL:

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

 

 

Best regards,

Mabel Mao

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-yamao-msft
Community Support
Community Support

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:

1.PNG2.PNG

 

Functions reference for WDL:

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

 

 

Best regards,

Mabel Mao

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

GabrielStJohn
Microsoft
Microsoft

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

- Gabriel
Community Manager
Power Automate | Power Virtual Agents
Super User Program Manager



Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Microsoft Ignite 768x460.png

Find your focus

Explore the latest tools,training sessions,technical expertise, networking and more.

Users online (1,656)