Power Automate - MS Forms to Excel to Word Document Help

Question

I am needing help creating a flow. I have a Microsoft forms template that I am using to compile answers. What I want to do is have the responses compiled for the week using the Forms Excel file and have that data pulled and input into a Word document I have. I need help with 2 parts:

1. How can I have it pull only the data for the past week into my document instead of all responses since the inception of the form?
2. How can I have the data put under certain headings in the document based on the category the response is under on my forms? (Please see info below for a better idea on what I need help with).

MS Forms Excel Spreadsheet When You Export Responses

Name	Email	Business Unit	Legal Category	Update
John	******@company.com	Business	Regulatory	Regulatory Update summary
Jane	******@company.com	Legal	HR	HR update summary

Word Document that we are manually updating on a weekly basis:

For the week of __________ the following updates have been made:<br><br><br><br>Regulatory<br><br>[Insert Any Update from Excel Spreadsheet under the Regulatory Category]<br><br><br><br>HR<br><br>[Insert Any Update from Excel Spreadsheet under the HR Category]<br><br><br><br>Legal<br><br>[Insert Any Update from Excel Spreadsheet under the Legal Category]<br><br><br><br>Finance<br><br>[Insert Any Update from Excel Spreadsheet under the Finance Category]

Answer 1

Dear Stephanie,

The Excel Form content type file will generate all dates' responses by default. If you don't want the Excel file in your final result, I suggest you use the SharePoint Online list to record the filtered response data and populate the data to the Word file finally.

You can create a list as follows and use Filter array to filter the responses from the last week.

Here is an example flow for your reference:

Get all responses from the form.

Site Address: https://forms.office.com

Method: Get

Uri: formapi/api/forms('formid')/responses

Use Filter array to filter the responses from last week such as the following.

formatDateTime(item()?['submitDate'], 'yyyy-MM-dd hh:mm:ss tt') is less than formatDateTime('the first day and time of this week ', 'yyyy-MM-dd hh:mm:ss tt'). You may need use multiple condition expressions in the action.

Compose: items('Apply_to_each')?['answers']

Compose 2: json(item()['answers'])[0]['answer1']

Compose 3: json(item()['answers'])[1]['answer1']

Compose 4: json(item()['answers'])[2]['answer1']

item()?['Title']

item()?['Email']

Finally use the Populate a Microsoft Word template action to populate the filtered data. You may need to buy the premium license to use the action.

Result:

For your reference: Power Automate: MS Forms responses to Sharepoint - Microsoft Community

Generate Word Document Template Using Power Automa... - Power Platform Community (microsoft.com)

Thanks for your effort and time.

Sincerely

Cliff | Microsoft Community Moderator

Answer 2

Thank you. :)

Answer 3

Dear Stephanie,

Thanks for your feedback and kindness.

Welcome to post a new thread on our forum if you encounter any issues in your environment.

Please also use the feedback tool below our reply to submit your feedback for us (pictured below), which will definitely help others in the community who are experiencing similar problems to find solutions to their problems more quickly.

Have a nice day!

Sincerely

Cliff | Microsoft Community Moderator