Sending email based on a new Excel row created by a Forms submission

[LBM]

Good afternoon,

I'm trying to create a flow using a submission from Forms (Quiz) as the trigger.

After the submission, a new row is created on my excel table (which is on my SharePoint). 

Using a few IF-Formulas, I calculate how many points each answer gets (if answer correct 1, otherwise 0)

In another column, I add those points (Column Score) and using Conditioning Formatting on a further column (112), the result is shown (if greater than or equal 7, show passed, otherwise failed).

After this, an e-mail is sent to me with information from both the Form submission (name, date, etc.) and the new excel row (score and result).

My problem is, in the e-mail the information coming from excel is not being shown.

I've tried using a delay from 15 seconds to 5 minutes, but it didn't work.

Anyone has an idea what the problem might be?

I appreciate your help and sorry if the question is too basic.

Attached a picture of the flow and of the e-mail.

Thank you very much!

[tom_riha]

Hello @LBM ,

my guess is that Power Automate doesn't know the calculated value when you create the row, that the action steps go like this:

1. Create a row
2. Update values in the row
3. Apply the formulas on the row

And Power Automate gives you as the output only the step 2, with the data that are directly updated in the row. It doesn't wait for all the calculations/formulas in the Excel table.

Before you use the IF based column in the email, you should load the full row again from the Excel file with the 'Get a row' action. Or you could calculate the values directly in Power Automate, it depends how complicated it is.

[LBM]

Dear @tom_riha 

Thanks for your answer! It already looks better.

I've added the Get a row step, but I'm probably using it wrong.

I used as the key value the word "failed" from the column 112, but what happens now is I do get excel information on my e-mail, but from the first guy on the list with failed on the row and not from the newest row (mixed with the Forms information from the newest submission).

Do you have an idea how I can correct this?

Thanks a lot!

Further info: columns 1, 2,...10 are the answers. Colums 12, 22,...92, 11 are the points each participant gets for the answer they gave. "Score" is the sum from 12, 22.., 11. Column 112 is the conditional formatting based on "Score".

Pics attached

[tom_riha · Accepted solution]

The 'Key Column' is the column Power Automate should check and the 'Key Value' is the value it should look for in that column. Once it finds the value in the column, it'll take it as the row you're looking for. That means you must use some column that has unique value for each row.

I guess that would be the 'Name' column in your case. Use the 'Name' as the 'Key Column' and the 'Name' from the row you just created as the 'Key Value'. That should give you the last row (unless you've got table with multiple users with the same name, then you'd need to look for another column that contains unique values).

[LBM]

[EDIT: I've added an ID-Column on my excel sheet and used it as the Key Column and the ID from the Forms submission as Key Value and it worked as a charm, THANKS!]

Hi @tom_riha 

Now I'm getting a 404 error, which is funny because even though the description says a row with the name "Luke" couldn't be found, they found the name "Luke".

Any idea why is that so?

Thanks a lot for your help and patience. 

Output: