cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sim123
Microsoft
Microsoft

Power Automate - Automatic Population of Email Responses onto Excel Spreadsheet

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!

1 ACCEPTED SOLUTION

Accepted Solutions

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.

View solution in original post

8 REPLIES 8
GeoffRen
Microsoft
Microsoft

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?

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.

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?

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!

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
Microsoft

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

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.

View solution in original post

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

Helpful resources

Announcements
Process Advisor

Introducing Process Advisor

Check out the new Process Advisor community forum board!

MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (2,131)