Create Flow email from a specific column list on MS Excel after a specific input from MS Forms

[RADSHAR]

Hello,

I have created a form which involves filling out all the pertinent information and to have an email sent out with that information out to a specific division (which is also identified on the form and is one of the questions).

• I would like to create an automated flow that will populate the emails for the Excel spreadsheet. The spreadsheet is designed in such a way that each division is allocated to one column and below that division column there is a list of 1 - 4 email addresses (only in one cell, separated by a semicolon). Not sure if this is the correct way to do this, so feel free to show me the best setup. 

But after I select that division on the MS Forms, I would like for Power Automate (cloud version) to recognize the division that was selected and populate the emails in the "Send an Email V2" widget. There are over 100-200 divisions, so I tried listing it out the conditions manually for each division before realizing that the Power Automate was getting bogged down. Would appreciate any help. It is a pretty specific request and seems like there is a simpler way to do this that I am unable to determine.

The things I have tried so far:

When New Response is Submitted --> Get Response Details --> List Rows Present in a Table --> Apply to each with Send an Email V2 (x 100 to 200 times).

[Ajinder31]

Hello @RADSHAR , Can you please share sample of how the spreadsheet format looks like? so far from what I understood is we're looking to get email values available under the specific Division column. 

[grantjenkins]

As long as your Excel file has a Table for your data and the Division column contains unique values then you can do the following. Note in my example I'm not using a MS Form but instead a Text Input on my Manual trigger, but same concept.

My Excel file for this example is below. Note that the Table name is DivisionsTable.

And the full flow.

Effectively, the user enters a Division and we use that value in our Get a row action to get the row we want. The Key Column is our Division column and we want the row where Division is equal to our Division we entered.

We can then retrieve all the column data (including the emails) for our Send an email action. Hopefully that makes sense for what you are after.

[RADSHAR]

This worked for the most part. In my case, I put the key value for the Get a Row function as the Division (Lab Unit) Unit dynamic content (MS Forms dynamic content). I am met with the error as shown in the first image.

 

Then I tested this individually (without any of the parsing JSON widgets above Get a Row for file attachment), it failed a couple times before it started working. However, if I try to combine this flow again with email attachment widgets, I am still met with the same issue (despite trying it several times and refreshing my flow). My guess is that I am putting the wrong Key Value? I have tried a few things to separate the two parts of the flow incase it was contributing to the issue but am met with the same error each time.

The Excel workbook is prepared like this.

 

It again seems like a simple fix, but am probably attempting complicated solutions.

[grantjenkins]

It looks like you've set it up correctly. Can you try to replace the dynamic content for Lab Unit and just replace it with the text TEST to see if that works? It might just be that you're output from the Form has some additional formatting applied to it.

What's the actual error message you are getting?

[RADSHAR]

When I switch out and add TEST, the email goes through and says this: Dear Lab ["TEST"], using the same exact formatting. However then that will mean I will have to add 100 - 200 "Get a Row" widgets to create the flow each for a division, defeating the purpose. It worked as a standalone, so I am unsure why it will not work now. 

Everytime I test the flow now, it is unable to find the row, even though it is in the Excel workbook (which worked the first time I created the flow).

"No row was found with Id '["TEST"]'.
clientRequestId: bba14b88-81ae-4d24-b632-80966e7c6386
serviceRequestId: 7d53435f-25df-43c1-bb07-d7843d291de1;8a9d16c5-9bc9-43f7-88fd-eee87909a78a;e45bd757-cf80-4e81-91e5-d7dfbda1238e;f9202012-e118-4a3c-a343-b60b56a430d6"

This is how the Excel workbook is formatted (which again worked the first time but will not work now). could this problem be coming from the Excel or the Get a Row function?