cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
ScottShearer
Super User III
Super User III

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
Anonymous
Not applicable

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

v-xida-msft
Community Support
Community Support

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.
Anonymous
Not applicable

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!

Anonymous
Not applicable

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

Anonymous
Not applicable

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 

Anonymous
Not applicable

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
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

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.

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors
Users online (2,785)