Can this be done within Power Automate?

(0) Share

Report

Posted on by manamaga

I've been playing around with this in PA for 3/4 weeks on and off now, but not able to get the config right or not working at all. I'll try keep it short but happy to explain further if needed. N.B. I'm new to PA but picked most of it up pretty quickly :-)

I have a list of colleagues names and their email addresses in a sharepoint list (happy to store elswhere if needed)

Once per month I need to get a response from each person on this list, and their reponse must be one of either Yes or No. If their repsonse is No, I need to be notified in some way (maybe email?). Likewise, if any of the people don't respond at all within x days, I need to be notified.

I have tried setting up a monthly email (scheduled cloud flow) to said list of people. This has worked fine. The email contains a link to a Form whereby they respond either yes or no. Again, this works fine
What I'm then struggling with is how do I somehow automate the notification to myself of "No" answers and non-respondents?
The List has a rule which emails me when the Yes/No column changes to No - This works fine but I'm struggling to automate the process of updating the Yes/No column based on the No repsonse from the Form

I'm not even sure if Forms and Lists is the right way to go about this but I feel they are the right tools, it's just getting the config right.

Any guidance would be much appreciated :-D

Categories:

General topics

I have the same question (0)

All responses (3)

Answers (0)

Suggested answer

Ninjasabi 122 on at

Like (0)

Report
You can absolutely achieve this using Power Automate. The setup involves leveraging your existing SharePoint list and Forms while adding automated steps to notify you of "No" responses and non-respondents. Here’s a breakdown of how to configure the process:

Step-by-Step Solution

1. Setup Overview

Use a scheduled flow to send out monthly emails with the link to the Microsoft Form.

Capture responses from the Microsoft Form in Power Automate and update the SharePoint list accordingly.

Notify you of:

Any "No" responses.

Any non-respondents within a specific timeframe.

2. Update the SharePoint List with Form Responses

Trigger: Use the "When a new response is submitted" trigger for Microsoft Forms.

Get response details: Retrieve the form data using the "Get response details" action.

Find the person in the SharePoint list: Use the "Get items" action to search for the respondent in the SharePoint list (filter by their email address):

Filter Query:

Email eq '@{triggerOutputs()?['body/responder']}'

Update the SharePoint list item: Use the "Update item" action to update the Yes/No column in the list based on their response.

3. Notify of "No" Responses

After updating the SharePoint list, add a condition to check the response:

Condition:

Yes/No Column equals No

If Yes: Send yourself an email notification with the respondent's details.

4. Handle Non-Respondents

Scheduled Flow to Check Non-Respondents:

Set up a second scheduled flow to run X days after the initial email is sent.

Use the "Get items" action to retrieve all list entries where the response is still blank:

Filter Query:

Response eq null

Send yourself an email notification with the names of these non-respondents.

Flow 1: Monthly Email with Form Link

Trigger: Scheduled flow (runs on the first day of each month).

Action: Use "Get items" to retrieve the SharePoint list of names and emails.

Action: Use an "Apply to each" loop to send an email to each person:

Subject: "Monthly Feedback Request"

Body: Include a link to the Microsoft Form.

Flow 2: Process Form Responses

Trigger: "When a new response is submitted".

Action: "Get response details" to retrieve the form data.

Action: "Get items" to find the corresponding entry in the SharePoint list.

Action: "Update item" to update the Yes/No column based on the response.

Condition: If the response is "No," send an email notification to yourself.

Flow 3: Check for Non-Respondents

Trigger: Scheduled flow (runs X days after Flow 1).

Action: "Get items" to retrieve all list items where the response column is null.

Action: Send an email notification with the names and emails of non-respondents.

Tips for Configuring the Flows

Avoid Duplication in Form Responses: Ensure the form submission is tied to the respondent’s email to avoid updating the wrong list item.

Non-Respondent Logic: Use the Modified column in the SharePoint list to check if an entry has been updated within X days.

Backup Logic: Before clearing or resetting the list monthly, archive the responses in another list or Excel file for historical tracking.

Example Notifications

For "No" Responses:

Subject: Alert - Negative Response Received Body: [Name] has responded "No" to the monthly feedback form.

For Non-Respondents:

Subject: Alert - Non-Respondents Body: The following individuals did not respond: - Name1 (Email1) - Name2 (Email2)

Let me know if you need help setting up these flows or further clarification! 😊

Was this reply helpful? Yes No
manamaga 4 on at

Like (0)

Report

You're an absolute star!! I have set this up for the most part, saved the flows etc and tested. However, on the updating sharepoint list with form response, the test is failing with BadRequest (I've omitted the actual email address):

The expression "xxxxx@xxxxx.com" is not valid. Creating query failed.
clientRequestId: 0c6a8d1a-26e8-4fde-9a03-f762bea4d084
serviceRequestId: c1236ea1-c04a-a000-f73c-afbc887420fd

Was this reply helpful? Yes No
manamaga 4 on at

Like (0)

Report

Scrap my last post, I appear to have resolved that. My flow 2 saves and runs successfull, according to the green banner. However I do get an Action Condition Failed at the Update item part?

Was this reply helpful? Yes No