How do I populate Word template from a live Excel Sheet?

(1) Share

Report

Posted on by KB-08100142-0

Apologies if I've not placed this question in the correct forum, or even in the right category. I've recently migrated from Google to Microsoft and struggling to replicate the function of Autocrat from Google.

I have created a form that will be used, let's say, for an entire year. The form will have scoring and comments that need to be captured. I have created the form responses sheet as well. In that sheet, I've created a new sheet called "With Formula" that adds in additional columns for averages of different areas scored. I would like to pull the data from the "With Formula" sheet to autopopulate a Word Template. Then, I would like to automatically send that populated word template to the individuals who did the scoring.

I'm currently trying to use Power Automate and have been following the "When a new response is submitted>get response details>populate Microsoft Word template" flow but then I realised it would not capture the averages and total scores in the formula I've created.

Additional info; I'm working on a Macbook because I'm a designer by trade.

Any help would be appreciated. Many thanks!

Categories:

General topics

I have the same question (0)

All responses (3)

Answers (0)

David_MA 12,982 Super User 2025 Season 2 on at

Like (0)

Report

Can you show your flow as how you describe what you are doing doesn't make sense. Seeing it should clear that up. Your initial question is about Excel, but then your explanation is about Microsoft Forms. They are two completely different things.

Was this reply helpful? Yes No
KB-08100142-0 4 on at

Like (0)

Report

Thank you for responding, David,

Sorry I'm not sure how to reply directly to your comment hence I'm posting a reply to my post.

Sure! Here's a screenshot of my flow thus far;

But I realised halfway through inputting the "response id", that it doesn't capture the scores I calculated in the Excel response sheet. Here's what I mean, the response sheet is called Sheet1 and I assume the data is pulled from Sheet 1;

However, I would like to pull the average scores in the "With Formula" sheet;

So along with the criterias and comments, these scores should be autopopulated in the Word Template too;

I hope I explained this better. Thank you again.

Was this reply helpful? Yes No
Suggested answer

David_MA 12,982 Super User 2025 Season 2 on at

Like (0)

Report
Easy now that I know what you need, and the spreadsheet is the one created by the form. Just do the following:

Add an Excel List rows present in a table action after the Get response details action.

Since you modifed the Excel spreadsheet, you know where it is located in OneDrive, so point the Excel action to the spredsheet and the table OfficeForms Table.

Add a filter query to the action where Id eq the ID from the trigger using this expression: int(triggerOutputs()?['body/resourceData/responseId'])

To populate your Word Template, I would suggest the following since the List rows present in a table is going to return an array even though you filter it to one item:

Initialize string variables to store the data you need to populate into the Word template

After the Excel Action, and an Apply to each action and set it to the value output of the Excel action.

Then set the variables to the correct values.

That should do it... But, you mention the values you need are from the "With Formula" sheet. The Excel action doesn't care about that, it cares about named tables in your spreadsheet. You will need to convert the data into a table and name it. You can then point to that table in the Excel action and you'll need to be sure you have the Id column corresponding to it or some way to reference the row with the data you need.

Was this reply helpful? Yes No