Power Automate - Send only one email when row added to excel

[StephenCES]

Hi, 

I'm new to PA. I'm building a flow to collect employee wage forms. This is for speaking test examiners who are paid for each speaking test that they do. When they submit, I want them to get an email which lists each date and corresponding number of tests and also, to have a total of speaking tests. 

I have 2 flows.

In first flow, in Microsoft forms, I have a questionnaire where employees state the date they worked and the number of speaking tests that they did. This information is added as a row to Excel document in Sharepoint. The excel document has a column that totals up the number of speaking tests. It is basic, looks like this and runs fine.

In the second flow, I have the Sharepoint excel file feed to email. The idea is that in each row, there will be information from a new employee - this will include their name, their email, dates worked and number of speaking tests. So they will get an email that summarises their claim. It looks like the below.

The problem I'm having is that each time I test it, it sends an email to every row, rather than just to the recently updated row. 

How would I fix this so that it only emails the newly added row?

Many thanks,
Stephen 

[AndrewR1]

Hi @StephenCES I think you can do all of this in the one flow: 

In your first flow you have wrapped this in a apply to each this is not necessary as when the form is submitted it should only pass that submission to the flow and therefore only running it once you can then add the rows of the submission to an excel sheet then email the responder directly in the initial flow.  

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. Remember, you can accept more than one post as a solution.

If the content was useful in other ways, please consider giving it Thumbs Up.

Thanks
Andrew

[StephenCES]

Hi Andrew, 

Thanks for this. The reason I have it running as two flows is that I want to total up the number of speaking tests. The excel document that the row is added to has a formula so that it totals the number of speaking tests inputted into the microsoft form. If I follow your flow, the email doesn't contain the total - it is like the flow is too fast. 

It is really the total that I am trying to get - I can get an email that contains the form responses but I would really like to get the total added up. I tried it using power automate, converting responses to integers with int function and then doing add function but that failed because many of the questions are unanswered (there are 10 possible dates to submit speaking tests for but some examiners might only submit 2). 

Basically, that is the problem I am trying to solve. How to have a total of the number of speaking tests submitted.

[AndrewR1 · Accepted solution]

Hi @StephenCES you could try doing this: 

So I have added a delay for x minuets which may give it time to calculate the totals and then get that row back using the ID? 

If not you could also use run a child flow off the back of this and then pass in the row ID that is generated in this flow then do the same get a row from excel and send and email?

The reason it is sending multiple email is because it is wrapped in a apply to each so for each row you return it will send an email you want to avoid doing this! 

Hope this helps! 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. Remember, you can accept more than one post as a solution.

If the content was useful in other ways, please consider giving it Thumbs Up.

Thanks
Andrew

[StephenCES]

Hi Andrew,

You're a genius! Thanks a million. That worked perfectly. I added the delay (2 minutes) and it's now working like a dream and I can do it in one flow and only the one email is going out. 

Thank you so much. Very much appreciate your help

All the best,

Stephen