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?
Can you post a scrren shot of your Flow please - it will help in trying to assist.
Thank you for the reply. I have attached the flow screenshot here.
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!
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 "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.
The flow works successfully as below:
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
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?
I have been trying alternative approaches for this and have found that if I use compose I get very near the result I want:
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!
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.
Check out the News & Announcements to learn more.
Participate in the Power Virtual Agents Community Challenge
Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.
Check out our new Discover Your Career Path blog post series and get all the details.