cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

Data from Excel into email!

Hi All,

 

I am setting up a new Flow to take data from a MS Form to save to Excel. Following this the next part of the flow is to take elements of the data from Excel into an Email that is sent after a formula has produced a value.

So in context:

 

1. MS Form is submitted with data

2. The Flow adds this data to an Excel sheet (This works and is a Online Form!)

3. The data is then used in a calulation and I present the figures into a separate table to make collection of data easier. 

4. This value is then added to the email body and sent to the requestor.

 

The issue I have is that when the email is sent it creates an email for every cell in the row so I end up with 44 emails each time!

So the question is how do I setup a flow to take the data from a single cell in Excel and present it back to the email template?

 

Thanks

7 REPLIES 7
Highlighted
Super User III
Super User III

Re: Data from Excel into email!

Can you post a scrren shot of your Flow please - it will help in trying to assist.

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Scott
Highlighted
Anonymous
Not applicable

Re: Data from Excel into email!

Hi,

 

Thank you for the reply. I have attached the flow screenshot here.

 

Flow Screenshot.PNGFlow Screenshot 1.PNG

 

As you can see I take a MS Form, add the results to an Excel spreadsheet (Table 1) and then reuse the results from the Form into the reply email. 

In the Excel I have a formula that works out a value in Table 2. I want to be able to add this value back into the email. However if I add the Excel Get Rows it does provide the values but it then sends an email for every row as it is inside of a "Apply to Many". What I find odd is that whenever I click the Excel data connection it automatically adds a new "Apply to Many" box!

 

Thanks

Highlighted
Community Support
Community Support

Re: Data from Excel into email!

Hi @Anonymous,

 

Could you please share a screenshot of your flow's configuration?

Do you want to include the data of the Excel file into the Body of the email?

Further, where does your Excel file store?

 

I think the "Create HTML table" action could achieve your needs, I assume that the data of your Excel file has been formatted as a table, I have made a test on my side and please take a try with the following workaround:

  • Add a "When a new response is submitted" trigger, specify Form Id.
  • Add a "Apply to each" action, input parameter set to output of the trigger.
  • Within "Apply to each" action, add a "get response details" action, specify Form Id, Response Id field set to Response Id dynamic content of the trigger.

         Add a "Add a row into a table" action, specify Location, Document Library, File and Table. Then add a "List rows present in a table" action, specify Location, Document Library, File and Table.

 

Then add a "Select" action, From set to output of the "List rows present in a table" action. Within Map entry, type three entries, the Key of first entry set to TaskName and corresponding value set to TaskName dynamic content of the "List rows present in a table" action. The key of second entry set to Executor and corresponding value set to Executor dynamic content of the "List rows present in a table" action. The key of third entry set to Due Date and corresponding value set to Due Date dynamic content of the "List rows present in a table" action.

 

 

Add a "Create HTML table" action, From set to output of the "Select" action. Then Add a "Send an email" action, Body field set to output of the "Create HTML table" action, Is HTML field set to Yes.

 

Image reference:26.JPG

 

27.JPG

 

28.JPG

The flow works successfully as below:29.JPG

 

30.JPG

 

 

Best regards,

Kris

 

 

 

 

 

 

 

 

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Anonymous
Not applicable

Re: Data from Excel into email!

Hi Kris,

 

Thank you I will try this out and let you know if it works.

To answer your questions:

 

Do you want to include the data of the Excel file into the Body of the email? - Yes but I want only the value that relates to the MS Form submitted. What do you think is the best way to ensure the value marries the response ID? Do I need to manually add the response ID into the Excel as well?  

Further, where does your Excel file store? - Sharepoint O365

 

Thanks again!

Highlighted
Anonymous
Not applicable

Re: Data from Excel into email!

Hi Kris,

 

This is excellent! 

The email now only sends once and present exactly how I wanted it to - thank you!

My only remaining item - at the moment the HTML table lists all rows in the Excel table. 

I need it to present a single value ideally. Is there anyway I can link the dynamic value to the response ID and this present a single row? 

 

Flow Screenshot 2.PNG 

 

Thanks

Highlighted
Anonymous
Not applicable

Re: Data from Excel into email!

I have been trying alternative approaches for this and have found that if I use compose I get very near the result I want:

 

last(outputs('List_rows_present_in_a_table').body.value)
 

 

Capture.PNG

 

So the question now is how do I remove the data other than "Estimated Cost = 0"?

I cant seem to create a HTML table from the compose results!

 

Thanks 

Highlighted
Anonymous
Not applicable

Re: Data from Excel into email!

Hi Kris,

 

Sorry to push on this but I am really struggling to get the final data out. 

I can get the whole table but I need to simply get a single cell value.

In my latest attempt I have used a compose function so I pull in the data earlier but I am unable to figure out the correct expression to get the last cell of data. 

 

Any advice greatly appriecated. 

 

Thank you!

Helpful resources

Announcements
firstImage

Super User Program Update

Three Super User rank tiers have been launched!

firstImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

firstImage

Join the new Power Virtual Agents Community!

We are excited to announce the launch of Power Virtual Agents Community. Check it out now!

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

Top Solution Authors
Top Kudoed Authors
Users online (11,392)