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.
Three Super User rank tiers have been launched!
Features releasing from October 2020 through March 2021
We are excited to announce the launch of Power Virtual Agents Community. Check it out now!
We've updated and improved the layout and uploading format of the Power Automate Cookbook!
Fill out a quick form to claim your user group badge now!