Unanswered

Power Automate sending e-mail for every row in Excel File when new row is added

(0) Share

Report

Posted on by KMaz

Hey Power Automate Gurus!
I'm building my first flow, and loving the function so far.

The goal of the flow is this:
>>From Form submission, write fields to Excel in Sharepoint (Add Rows in Table)
>>In Excel Table, use VLOOKUP to populate additional columns employee and supervisor name from employee ID
>> List Rows in Table
>>Use the Select Data Operation to convert VLOOKUP fields to Text output
>>Generate an e-mail upon each new Row entry, containing converted VLOOKUP & standard fields in the subject and body.

WhatsApp Image 2021-04-17 at 12.38.55 AM.jpeg

I've got the core function of all the steps working great.

BUT, have run into an issue where the flow is generating an e-mail for every single row in a spreadsheet each time a new row is added.

I think this is caused by the second Apply to Each criteria that is being auto-added. I just don't know how to fix it, because both converted VLOOKUP text and regular excel cell text are needed for these e-mails.

Can anyone offer any tips?

Categories:

Building flows

I have the same question (0)

All responses (5)

Answers (0)

Ed Gonzales 4,531 Most Valuable Professional on at

Like (0)

Report
@KMaz

Hi there. So, you really shouldn't need any loops to do what you want to do. It should be:

Forms Trigger

Get response details

Add row to spreadsheet

Get Row (using Row ID from create row step)

Send email (You can probably use a String() function, if needed, to change the vlookup output to text here).

I think you're getting the second loop because you doing "List Rows", which is many...vs. Get Row, which is one.

Keep us posted.

-Ed

If you liked this reply, please give it a thumbs up! If this reply has answered your question or resolved your challenge, please consider marking it as a Solution. This helps other users find it more easily via search.

Was this reply helpful? Yes No
KMaz 4 on at

Like (0)

Report

@edgonzales Thank you so much for your reply!
The solution you suggest was actually my first choice, and I've been trying so hard to get it to work.
Unfortunately, it keeps erroring out.
I'm using the form completion time as my Key Column and Key Value. Not sure if that's the correct approach.
But I keep getting an error message showing that the system is searching for a form posting 5 hours different than when the form was actually submitted. So I think there's some kind of wacky time difference kicking in.
I would really welcome any suggestions you could offer.

Was this reply helpful? Yes No
Ed Gonzales 4,531 Most Valuable Professional on at

Like (0)

Report

@KMaz

No worries, you are super close. I suspect the issue has to do with your local time being five hours off from Coordinated Universal Time (UTC). I'm not sure which side isn't being converted, but you can use a Convert Time Zone action (or expression) to change one side so it will find a match (hopefully).

Keep us posted.

-Ed

Was this reply helpful? Yes No
KMaz 4 on at

Like (0)

Report

@edgonzales
I have concluded that the Get a Row function in Flows is actually the devil.

So I tried everything I could think of to solve for the date issue, and then decided to change the Key Column and Key Value to ID, because it's simpler, every single form is assigned one, and it should be easier to solve for. Right?

No. Nosir.

When I change to that, not only do I get an error telling me the ID that is absolutely present in the spreadsheet cannot be found, but the test somehow also duplicates that line and writes it again in the spreadsheet.

Thank you SO much for your continued advice and expertise on this. Get a Row is giving me the Flow smackdown! 🤣

Was this reply helpful? Yes No
Ed Gonzales 4,531 Most Valuable Professional on at

Like (0)

Report

@KMaz

You've got the right idea, for sure! In your Get a Row step, where it asks for "Key Column", it wants to know "Which column should I be looking in for the key value (your form response ID)?".

Currently, you've got a dynamic value, which means it will be looking for a column with a name that matches the ID of that particular item.

Instead, your Key Column should be a drop-down of the column headers for the table in Excel, and you would select the one where the ID is located. ("ID")

Something to consider: It is expecting unique values here...something that discerns one row from the next. I'm not certain why the last two rows in your example are duplicated, but that might create an issue in a production environment (meaning: "Which of the two should I use?").

Either way, you're super close...keep at it!

-Ed

If you liked this reply, please give it a thumbs up! If this reply has answered your question or resolved your challenge, please consider marking it as a Solution. This helps other users find it more easily via search.

Was this reply helpful? Yes No