Answered

Power Automate - Automatic Population of Email Responses onto Excel Spreadsheet

(0) Share

Report

Posted on by Sim123 Microsoft Employee

Hello,

I currently send an email using Power Automate. I was wondering if there's a way I can get the email responses from this flow automatically inputted into the Responses column of my Excel Spreadsheet. Is there a flow which will allow that the email response to my Flow email triggers the automatic population of the excel column based on who sent the email and that only the body of the message is captured in the column. If email exchange continues, then the respective cell keeps getting populated with added content.

Thanks!

Categories:

General topics

All responses (8)

Answers (1)

Sort by

GeoffRen Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Can you post an example scenario, your question isn't very clear. So can you post an example of what you want your Excel to look like at each step?
Sim123 Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hello,
Apologies for the lack of clarity - I was hoping that the responses to my Power Automate email could automatically be populated into the excel spreadsheet I use to send out emails. For instance, currently I have to manually update the comments column in the excel spreadsheet based on what the person responds. This gets very tedious as I get a lot of responses due to a mass email being sent. Hence, I was hoping if there's a way to automate the process within Power Automate that automatically populates the comments column based on the email response.
GeoffRen Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

More clarifying questions:
1. Do you want this information stored in a table?
2. Does the email sender already exist in the spreadsheet?
3. So if you get multiple responses from the same person you want to just keep adding more responses to the comments column?
Sim123 Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

1. Yes, under the column labelled 'Comments' in my spreadsheet which I am currently manually updating.
2. Yes, I use the sheet to send out emails to requestors via my Flow.
3. Correct! Or their latest correspondence if that's more attainable.
Thank you!
GeoffRen Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Using the Outlook When a New Email Arrives (v3) trigger you can filter for emails you receive based on a subject. That's one way you can get the responses to your email.

To add it to an Excel table you can do this either using the standard ExcelOnline connector actions or using Office Scripts (specifically Office Scripts with Power Automate) (the Run Script action in the ExcelOnline connector).

If using the ExcelOnline connector actions you're going to have to:
1. Get the existing information in the column from the row
2. Append the new information taken from the Outlook trigger to this existing information
3. Write the newly created information from ^ back to the row

If using Office Scripts, you just need to write a script that does the above. If you know javascript this is probably the less finicky option since you can test your script instantly without having to run a Flow. I can also help provide a script that probably works for you here.
Sim123 Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

I would appreciate it a lot if you could provide me with a script or provide some guidance over a Teams call depending upon your convenience and availability of course. If not, perhaps a link to some tutorial which you find will be most useful in this scenario
Verified answer

GeoffRen Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
This should do that. You're going to have to modify the hardcoded sheet, table, and table column name strings to suit your scenario. In Power Automate you can pass in the user and comment as parameters.
function main(workbook: ExcelScript.Workbook, user: string, comment: string) { const tbl = workbook.getWorksheet('Sheet1').getTable('table1'); const userIndex = tbl.getColumnByName('User').getRangeBetweenHeaderAndTotal().getValues().map((nestedValue) => nestedValue[0]).indexOf(user); const commentRange = tbl.getColumnByName('Comment').getRangeBetweenHeaderAndTotal().getCell(userIndex, 0); commentRange.setValue(`${commentRange.getValue()}\n${comment}`.trim()) }

Just mentioning again that you should also be able to do this with the existing get row and set row ExcelOnline connectors too.
Sim123 Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks a lot for your time and effort in helping me resolve the issue. I appreciate it a lot! 🙂