How can i use Power Automate for sending downstream notification to group of users and track the impact

Question

Hi All

I have an excel sheet containing the list of systems/countries onboarded in a data lake. This also contains the list of downstream systems who consumes the data

In another excel sheet, i have the SPOC details for each of the downstream systems.

My Problem Statement:

I want to create a form where in if i input system, and country - it should fetch the list of impacted downstream from excel 1 and the corresponding SPOCS from excel 2

Once i have this information ready, I need to send an email to the impacted users via Power Automate

I need to send reminders (automatically) every 3 working days for 3 times and conclude the impact

I need to gather the responses as well and collate

Kindly advise how we can achieve this via Power Automate

Thanks

This question is related to the following Learning Module

Answer 1

This scenario can be built with Power Automate using Excel as the data source, Outlook for notifications, and (optionally) a tracking store such as another Excel table or SharePoint list.

A high‑level approach, based only on supported patterns in the context, is:

1. Prepare the Excel files
   Similar to the Forms→Excel pattern, both workbooks must be stored in OneDrive or SharePoint and use tables:
   • Excel 1: table with columns like System, Country, DownstreamSystemId (and any other metadata).
   • Excel 2: table with columns like DownstreamSystemId, SpocName, SpocEmail, etc.
   The context shows that Power Automate can reliably work with Excel tables and add/read rows using Excel Online (Business) actions such as Add a row into a table and table lookups. See the “Add a row into a table” pattern in the Forms→Excel example.
2. Create the input mechanism (Form)
   Use Microsoft Forms to collect the initial input (System, Country, and any other details).
   • Create a form with questions that match the columns needed to filter Excel 1.
   • This is analogous to the examples where Forms is used to collect data and Power Automate routes it to Excel or SharePoint.
3. Trigger a flow from the Form submission
   In Power Automate, create an automated cloud flow:
   • Trigger: Microsoft Forms – When a new response is submitted.
   • Next action: Get response details for that form (as shown in the “Add form responses to an Excel worksheet” scenario).
4. Look up impacted downstream systems and SPOCs in Excel
   Use Excel Online (Business) actions to query the tables:
   • Use List rows present in a table on Excel 1 and filter by System and Country from the form response.
   • For each matching downstream system, use another List rows present in a table (or a filter) on Excel 2 to get the corresponding SPOC records.
   • Build a collection of SPOC email addresses (for example, by using an Apply to each loop and composing a semicolon‑separated list).
5. Send the initial notification email
   Use Outlook actions as in the email modules:
   • Action: Microsoft 365 Outlook – Send an email (V2).
   • To: dynamic list of SPOC emails from step 4.
   • Body: include the system, country, and any impact details.
     This follows the same pattern as “Send emails by using Microsoft 365 Outlook” and “Use Microsoft Forms and Microsoft Power Automate to automatically track potential customers,” where a form drives an email to a target audience.
6. Track notifications and responses
   To track impact and responses, use a structured store (Excel or SharePoint):
   • Create a table or list with columns such as NotificationId, System, Country, DownstreamSystemId, SpocEmail, SentDate, ReminderCount, ResponseStatus, ResponseDate.
   • After sending the initial email, add a row for each SPOC using Add a row into a table (Excel) or the equivalent SharePoint action, similar to the “record form responses in SharePoint” pattern.
   For collecting responses:
   • Include a link in the email to another Microsoft Form (or an Updates request, or a SharePoint form) where SPOCs can indicate impact.
   • Use a second flow triggered by that response to update the tracking table/list (look up by NotificationId or a unique key and set ResponseStatus/ResponseDate).
     This mirrors the way Forms responses are captured into SharePoint or Excel in the provided scenarios.
7. Implement automatic reminders every 3 working days (3 times)
   Use a scheduled or recurrence‑based flow that checks the tracking table and sends reminders:
   • Create a second scheduled cloud flow in Power Automate that runs daily.
   • In that flow, query the tracking table for rows where:
     • ResponseStatus is still Pending (or blank), and
     • ReminderCount < 3, and
     • At least 3 working days have passed since SentDate or the last reminder date (this can be handled with date columns and expressions).
   • For each such row, send a reminder email using Send an email (V2) and increment ReminderCount and update the last reminder date in the table.
   The pattern is similar to the “Send weekly email reminders based on spreadsheet data” tutorial, where a script and flow read a schedule from Excel and send reminder emails on a schedule.
8. Conclude the impact after 3 reminders
   In the same scheduled flow, when ReminderCount reaches 3 and ResponseStatus is still Pending, set ResponseStatus to something like No response – auto‑closed.
   This gives a clear conclusion state in the tracking table.
9. Reporting and collation
   Because all notifications and responses are stored in a structured table/list, impact can be collated by:
   • Filtering by System/Country to see which downstream systems responded and how.
   • Exporting the table to Excel or using it directly for reporting.

This design uses only supported patterns from the context: Forms as a trigger, Excel/SharePoint as data stores, Outlook for email, and Power Automate flows for scheduled reminders and data movement.

---

References:

• Common ways to use a form in a flow
• Setting Up an Automated Workflow Between Microsoft Forms and Excel Through Power Automate
• Manage leads and sales
• Email with Power Automate
• Tutorial: Send weekly email reminders based on spreadsheet data