Forms Submission to Excel to Multiple Emails (CC) from indexed data

[kevinkhkim]

Hello,

I am looking for a solution where Microsoft Form is generated from an excel with key values. When an individual submits a form, I put formulas in the Excel table to index additional email addresses in the table, so I can use them to CC the multiple emails when automatically responding back to the submitter. 

The Excel table updates upon the submission, but I am having trouble getting the latest submission information to get the associated email addresses to send.

The steps I have are:

1. When a new response is submitted
2. Get response detailed
3. Get a row
4. Send an email

What steps am I missing to make this work? 

Thank you in advance.

[DamoBird365]

Hi @kevinkhkim 

If I understand you correctly you have another column in the table that is dynamic - the email?  Your form populates the excel table directly, ie. no flow involved here.  But then you wish to email them with the response and dynamic value (email). You might have to use get rows with a filter on "ID" to retrieve the row relevant to the response details?  ie ID eq 'ID from response details'

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien

P.S. take a look at my new blog here and like & subscribe to my YouTube Channel thanks 😉

[kevinkhkim]

@DamoBird365 Thank you for your help! Yes, that is correct that the Form automatically updates the Excel that is updated dynamically with the responses along with formulas to add additional contacts based on Submitter names. I used Get a Row action with ID, but now I'm having a challenge savin the flow with the error message below, so I can't test:

Flow save failed with code 'InvalidTemplate' and message 'The template validation failed: 'The repetition action(s) 'Apply_to_each' referenced by 'inputs' in action 'Send_an_email_(V2)_2' are not defined in the template.'.'.

[kevinkhkim]

Correction - The workflow worked and I am able to retrieve details and send accordingly! I still have the error message which does not prevent me from running the flow but how do I get rid of it without impacting the flow?

[DamoBird365 · Accepted solution]

Can you screenshot or rebuild the action Send_an_email_(V2)_2. Sounds like you have an invalid reference in there.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien

P.S. take a look at my new blog here and like & subscribe to my YouTube Channel thanks 😉

[kevinkhkim]

Recreating the Send an Email flow did the trick. Thank you so much!

[kevinkhkim]

I'm not sure why the flow is spotty. The Excel is working to dynamically update email addresses, but CC'ing emails from dynamic content is not working - it worked the first time but has not copied emails over after. Thoughts? @DamoBird365 

[DamoBird365]

Hi @kevinkhkim 

You might want to check your expression in cc. Maybe by replacing the other action, you’ve stopped that from working. Just a guess. 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien

P.S. take a look at my new blog here and like & subscribe to my YouTube Channel thanks 😉

[kevinkhkim]

What I think is happening is that

1. Send an email is executing before Excel is fully updated with formulated values to pull the data from the source

2. I need the Excel completely update, then get the row to send an email

Is there a function I can put either before or after "Get a Row" to make sure data is secured for Send an Email to pull the data to send?

[kevinkhkim]

The issue is that the Send an Email is not reading the email addresses from Dynamic Contents and I'm not sure how to make it read them.

[DamoBird365]

Hi @kevinkhkim 

You need to share an image of that action and the history of that action, as it is really hard to visualise what you are seeing with the description you have provided.

Damien

[kevinkhkim]

@DamoBird365 Please see below and let me know if you need further clarification. Thank you for your help!

I have the Form response feeding into Excel and I am getting a row from there. I put delay in case it can support with Excel to finish running the formulas to refresh before "Send an email" can pull the details from the row.

Where I am challenged is the CC part which grabs the email from the Excel as Dynamic Contents. I put semicolons as directed, but did not work. When I actually put the email addresses, then it works.

[DamoBird365]

Hi @kevinkhkim 

The dynamic email you are using is from the original form submission?  Did you not say that the email came from Excel?  Is it a column from the result of get a row?

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien

P.S. take a look at my new blog here and like & subscribe to my YouTube Channel thanks 😉

[kevinkhkim]

@DamoBird365 That's correct that as Forms are submitted, Email and Name populate in Excel and the green highlighted columns are formulated based on the submitter's "Email" column. The dynamic email I am using for the To is from the original submission. CC is derived from new calculated columns in Excel.

[DamoBird365]

Hi @kevinkhkim 

My bad, it is the cc that is causing problems, not the to.  Have you tried looking at the history output of the get a row?  Do you see the emails in there?  If not, your 10 second delay maybe needs to be above the get a row action.  It is possible for you to also try office scripts if your get a row action is not able to read the dynamic values from the table.

Check the history and see if you can see those emails first.

Damien

[kevinkhkim]

@DamoBird365 Moving the Delay above Get a Row did not work. Now that reviewing history, it doesn't look like the values are there for appropriate attributes... 😞 I'm not sure what triggers it to work only sometimes...

[kevinkhkim]

I found out from another post that Power Automate cannot read formulated cells from Excel.

[DamoBird365]

Hi @kevinkhkim 

Sorry about that one.  I have tried it on my environment and it works, albeit I, like you, am having mixed results.  I have tried writing a basic Office Script to do the same, it works too, albeit it needs to be thrashed to see if it falls over.  

Here's the sample script

function main(workbook: ExcelScript.Workbook, 
	rowguid: string ) 
{
	let selectedSheet = workbook.getActiveWorksheet();

	let findme = selectedSheet.getRange("A1").find(rowguid, { completeMatch: false, matchCase: false, searchDirection: ExcelScript.SearchDirection.forward });

let row = findme.getRowIndex()+1; //row index starts from 0
let cell = 'E'+row; //my email is in colum E
let email = selectedSheet.getRange(cell).getValue() //get email value

	return(email) //return email to Action
}

and the result is based on a lookup expression.

Here's the result:

Another option for you, if your email lookup isn't huge, you can define an array in your flow with the email addresses.  Then you can take the field you are looking up from excel and perform a filter on the array in your flow.  Something a bit like https://www.damobird365.com/if-elseif-else-in-power-automate-simplified/

Although if you define the list of emails as a table in excel, you could equally do get rows and perform a filter on that instead.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien

P.S. take a look at my new blog here and like & subscribe to my YouTube Channel thanks 😉

[kevinkhkim]

@DamoBird365 I'm sorry, I'm not quite following... 

[DamoBird365]

hi @kevinkhkim 

Which bit do you need explaining and I’ll try to help.

Damien