Automating Recurring Team Status Reports with Power Automate: Collecting Responses via Microsoft Forms, Aggregating Data in SharePoint, and Delivering Formatted Summaries to Teams Channels on a Schedule

Introduction

Picture this: it's Friday at 4:45 PM, and you're still manually chasing five engineers for their weekly status updates. Someone sends a wall-of-text email, someone else drops a bullet list in a random Teams chat, and one person — predictably — forgot entirely. You spend the next 30 minutes copy-pasting fragments into a coherent summary that you then post to the #project-status channel, by which point half the team has already logged off for the weekend.

This is the kind of problem that feels small until you realize you've done it 52 times in a year. The fix isn't a better email template or a Jira add-on that costs $15 per seat. The fix is a Power Automate flow that handles the entire loop: sends the form on a schedule, collects responses into a structured list, waits for a cutoff window, then assembles and posts a clean summary — automatically, every week, without you touching it.

By the end of this lesson, you'll have built exactly that system. We'll use Microsoft Forms to collect structured input, SharePoint as the aggregation layer, and the Teams connector to deliver a formatted card to your channel on a predictable schedule. The architecture handles a real team scenario: a 10-person software delivery team tracking sprint progress, blockers, and confidence scores each Friday.

What you'll learn:

• How to design a Microsoft Forms questionnaire optimized for automated aggregation
• How to build a scheduled "collection window" flow that captures form responses into a SharePoint list
• How to use expressions to compute aggregate summaries (averages, counts, conditional flags) from structured list data
• How to format and post a rich, readable Teams message using Adaptive Cards
• How to chain both flows together so the whole system runs hands-free on a weekly cadence

---

Prerequisites

You should already understand how to create a basic cloud flow in Power Automate and have worked with at least one connector. Familiarity with SharePoint lists and how to read/write items via Power Automate will save you time — if you need a refresher, Power Automate with SharePoint: Automate Document Approvals covers the connector fundamentals well. You should also know the difference between scheduled and automated flow types; if you don't, start with Power Automate Flow Types Explained: Automated, Instant, and Scheduled Flows before continuing.

You'll need:

• A Microsoft 365 account with Power Automate (per-user plan or included via M365 license)
• Owner or edit access to a SharePoint site
• Permissions to create Forms and post to a Teams channel
• Basic comfort with Power Automate expressions — we'll use formatDateTime, length, float, and div throughout

---

Designing the Form and SharePoint List

Before you write a single action in Power Automate, you need to nail the data model. Automation magnifies whatever structure you put in place — a well-designed form makes aggregation trivial, and a sloppy one turns every expression into archaeology.

Building the Microsoft Forms Questionnaire

Open Microsoft Forms and create a new form called Sprint Status — Weekly Check-In. You're going to ask five questions. The specific content matters less than the types of answers you're collecting, because those types determine how you can aggregate them.

Here's the question structure for our scenario:

1. "Which team member are you?" — Choice question, with each team member's name as an option. Do not use free text for this; you want consistent values you can match against later.

2. "How many story points did you complete this sprint?" — Number question, min 0, no max. This gives you something to sum and average.

3. "Are you currently blocked on anything?" — Choice question with two options: Yes and No. Simple binary, easy to count.

4. "If blocked, briefly describe the blocker." — Long text, optional. This is the only free-text field, and you'll include it verbatim in the summary rather than trying to aggregate it.

5. "How confident are you that the team will hit the sprint goal? (1–5)" — Rating question, scale 1–5. This gives you a number you can average into a team sentiment score.

Once you've saved the form, copy its response URL — you'll need it in a moment. Also note the Form ID from the URL bar; it looks like a long alphanumeric string after id=.

Setting Up the SharePoint List

Go to your SharePoint site and create a new list called Sprint Status Responses. Add the following columns, in addition to the default Title column (which you'll repurpose as the submitter name):

Column Name	Type	Notes
Title	Single line of text	Rename label to "Team Member"
StoryPointsCompleted	Number	Integer
IsBlocked	Yes/No	Checkbox
BlockerDescription	Multiple lines of text	Optional
ConfidenceScore	Number	1–5
SprintWeek	Single line of text	e.g., "2024-W42"
FormSubmittedAt	Date and Time	UTC timestamp

The SprintWeek column is critical. It's how your summary flow will filter "this week's responses" without accidentally picking up last week's data. You'll populate it automatically from the flow using an expression like formatDateTime(utcNow(), 'yyyy-\\WW').

---

Flow 1: The Collection Flow (Triggered by Form Submission)

This is your first flow. It fires every time someone submits the form, grabs their response, and writes a structured record to SharePoint. Think of it as the intake valve — every response gets normalized and stored before your summary flow even runs.

Creating the Flow

In Power Automate, create a new Automated cloud flow. When prompted to choose a trigger, search for Microsoft Forms and select When a new response is submitted. Set the Form ID to your Sprint Status form.

Now add a Get response details action, also from the Microsoft Forms connector. Set the Form ID to the same form, and for Response ID, select the Response Id dynamic value from the trigger.

At this point you have all five answers available as dynamic content. The next step is transforming them into the shape your SharePoint list expects.

Handling the Blocker Translation

Remember that the IsBlocked column needs a boolean. Add a Condition action. Set the condition to:

Get response details > Are you currently blocked on anything?
is equal to
Yes

In the If yes branch, add a Create item action for your SharePoint list with IsBlocked set to true. In the If no branch, add the same action with IsBlocked set to false.

Both branches should populate all the other fields identically — only IsBlocked differs. Here's what the field mapping looks like for the "If yes" branch (fill in the "If no" branch the same way, flipping only IsBlocked):

SharePoint Field	Value
Title	Get response details > Which team member are you?
StoryPointsCompleted	Get response details > How many story points...
IsBlocked	true
BlockerDescription	Get response details > If blocked, briefly describe...
ConfidenceScore	Get response details > How confident are you...
SprintWeek	formatDateTime(utcNow(), 'yyyy-\\WW')
FormSubmittedAt	utcNow()

For working with conditions and branching logic like this, it's worth understanding that you could also solve the Yes/No translation with an expression instead of a full Condition block. In the Create item action, set IsBlocked using this expression in the formula bar:

if(equals(outputs('Get_response_details')?['body/r4f5a...'], 'Yes'), true, false)

The property name will be the actual internal field ID from your form — click into the expression editor, switch to Dynamic Content, find your question, then wrap it in the equals() call. The Condition approach is more readable for team members who maintain this flow later, so use whichever serves your audience.

Save and test this flow by submitting a test response through the form. Then check your SharePoint list to confirm the row appeared with correct values. This is your canary — if the data lands wrong here, the summary will be wrong too.

---

Flow 2: The Summary Flow (Scheduled, Friday Afternoons)

This flow runs on a schedule. It queries the SharePoint list for all responses from the current week, computes aggregate statistics, formats a Teams message, and posts it. If Flow 1 is the intake valve, Flow 2 is the report engine.

Setting the Schedule

Create a new Scheduled cloud flow. Name it something unambiguous like Sprint Status — Weekly Summary Post. Set it to run weekly, on Friday at 4:00 PM in your local time zone.

Querying This Week's Responses

Add a Get items action from the SharePoint connector. Select your site and the Sprint Status Responses list. In the Filter Query field, use OData syntax to grab only this week's responses:

SprintWeek eq '@{formatDateTime(utcNow(), 'yyyy-\\WW')}'

This ensures the summary only reads responses for the current ISO week. Without this filter, you'd be averaging story points from every sprint since you deployed the flow — not what you want.

Set Top Count to 25 (or however large your team is, with headroom). You don't need to paginate for a 10-person team, so leave "Get All" off to keep the action fast.

Computing the Aggregates

Here's where things get interesting. You need to derive four numbers from the list of responses:

1. Total story points completed — sum of all StoryPointsCompleted values
2. Average confidence score — mean of all ConfidenceScore values
3. Number of blocked team members — count of items where IsBlocked is true
4. Response count — how many people submitted (to flag if someone missed it)

Power Automate doesn't have a native "sum array" action, so you'll use a combination of Initialize Variable, Apply to Each, and Append to Array Variable or arithmetic variables. Let's walk through it.

Before your Apply to Each loop, add these variable initializations:

• varTotalPoints — Integer, initialized to 0
• varTotalConfidence — Float, initialized to 0
• varBlockedCount — Integer, initialized to 0
• varResponseCount — Integer, initialized to 0

Now add an Apply to Each action, with the input set to the value output from your Get items action. Inside the loop, add four Increment variable actions:

Increment varTotalPoints by: items('Apply_to_each')?['StoryPointsCompleted']
Increment varTotalConfidence by: items('Apply_to_each')?['ConfidenceScore']
Increment varResponseCount by: 1

For varBlockedCount, use a Condition inside the loop:

If: items('Apply_to_each')?['IsBlocked'] is equal to true
  Then: Increment varBlockedCount by 1

After the loop, compute the average confidence with a Compose action named compAvgConfidence:

div(variables('varTotalConfidence'), if(equals(variables('varResponseCount'), 0), 1, variables('varResponseCount')))

The if(equals(..., 0), 1, ...) guard prevents a division-by-zero error if no one has responded yet — a small touch that matters in production. For more expression techniques like this, mastering dynamic expressions and the Power Automate formula language is the most thorough reference in this learning path.

Collecting Blocker Descriptions

You also want to surface any blocker descriptions in the summary. You don't want to loop again — instead, use the Select action (from Data Operations) to extract just the blocker text from items where IsBlocked is true.

Add a Filter array action with input as the Get items value, and filter condition:

item()?['IsBlocked'] is equal to true

Then add a Select action on the filtered array output, with the map set to output only the BlockerDescription column:

From: Filter_array output
Map (key): Blocker
Map (value): item()?['BlockerDescription']

Finally, add a Join action (Data Operations) to concatenate those descriptions into a single string, separated by a newline character (\n). Use the Select output as your input array and \n as the delimiter.

The transforming and mapping data with Power Automate's Compose, Select, and Filter Array actions article dives into these data operation actions in depth if you want to fully understand what's happening under the hood.

---

Building the Teams Message

Now you have all your numbers and text assembled. It's time to format and post the summary to your Teams channel.

Choosing Between a Simple Message and an Adaptive Card

You have two options for posting to Teams: a plain text or basic HTML message using Post a message in a chat or channel, or a structured Adaptive Card using Post an Adaptive Card to a Teams channel.

For a status report, use the Adaptive Card. It renders as a visually structured block with headers, columns, and clear sections — much more readable than a blob of text, and it doesn't require the reader to parse markdown mentally.

Designing the Adaptive Card JSON

Add a Post an Adaptive Card to a Teams channel action. Select your Team and Channel. For the Adaptive Card field, you'll paste a JSON template and inject your dynamic values.

Here's a complete, production-ready card template for the sprint status report:

{
  "type": "AdaptiveCard",
  "version": "1.4",
  "body": [
    {
      "type": "TextBlock",
      "text": "📊 Sprint Status Summary",
      "weight": "Bolder",
      "size": "Large",
      "color": "Accent"
    },
    {
      "type": "TextBlock",
      "text": "Week: @{formatDateTime(utcNow(), 'yyyy-\\WW')} · Responses: @{variables('varResponseCount')} of 10",
      "isSubtle": true,
      "wrap": true
    },
    {
      "type": "ColumnSet",
      "columns": [
        {
          "type": "Column",
          "width": "stretch",
          "items": [
            {
              "type": "TextBlock",
              "text": "Story Points Completed",
              "weight": "Bolder"
            },
            {
              "type": "TextBlock",
              "text": "@{variables('varTotalPoints')}",
              "size": "ExtraLarge",
              "color": "Good"
            }
          ]
        },
        {
          "type": "Column",
          "width": "stretch",
          "items": [
            {
              "type": "TextBlock",
              "text": "Avg. Confidence Score",
              "weight": "Bolder"
            },
            {
              "type": "TextBlock",
              "text": "@{string(outputs('compAvgConfidence'))} / 5",
              "size": "ExtraLarge",
              "color": "Accent"
            }
          ]
        },
        {
          "type": "Column",
          "width": "stretch",
          "items": [
            {
              "type": "TextBlock",
              "text": "Blocked Members",
              "weight": "Bolder"
            },
            {
              "type": "TextBlock",
              "text": "@{variables('varBlockedCount')}",
              "size": "ExtraLarge",
              "color": "Warning"
            }
          ]
        }
      ]
    },
    {
      "type": "TextBlock",
      "text": "🚧 Active Blockers",
      "weight": "Bolder",
      "spacing": "Medium"
    },
    {
      "type": "TextBlock",
      "text": "@{variables('varBlockerText')}",
      "wrap": true,
      "isSubtle": false
    },
    {
      "type": "ActionSet",
      "actions": [
        {
          "type": "Action.OpenUrl",
          "title": "View Full Responses in SharePoint",
          "url": "https://yourtenant.sharepoint.com/sites/yoursite/Lists/SprintStatusResponses/AllItems.aspx"
        }
      ]
    }
  ],
  "$schema": "http://adaptivecards.io/schemas/adaptive-card.json"
}

Notice the @{...} placeholders — those are Power Automate expression injections. When you paste this JSON into the Adaptive Card field, the dynamic content engine will resolve those tokens at runtime. Replace the SharePoint URL in the Action button with your actual list URL.

The string() wrapper around outputs('compAvgConfidence') prevents a type error — Compose outputs aren't always treated as strings in the card body, and wrapping ensures the rendering engine gets a clean value.

Adding a "Missing Submissions" Flag

One more practical touch: if varResponseCount is less than your team size (10 in this scenario), you want the message to call that out. Before the Post Adaptive Card action, add a Condition:

If: variables('varResponseCount') is less than 10
  Then: Append "⚠️ Only @{variables('varResponseCount')} of 10 team members responded. Please submit your update: [form link]" to varBlockerText

This is more useful than a separate reminder system — it surfaces the gap right inside the report, in the channel where the team will see it.

---

Chaining the Two Flows

You now have two independent flows:

• Flow 1 (Automated): triggers on form submission, writes to SharePoint
• Flow 2 (Scheduled): runs Friday at 4 PM, reads SharePoint, posts to Teams

These flows are deliberately decoupled. Flow 1 runs multiple times throughout the week as people submit. Flow 2 runs exactly once. Keeping them separate means if someone submits on Tuesday, their data is already in SharePoint and the Friday flow doesn't need to re-query Forms at all.

If you ever need to chain flows directly — passing data from one to another without an intermediate store — the article on chaining Microsoft 365 connectors in a single flow covers that pattern well.

---

Sending the Form Reminder (Optional Flow 3)

A complete status report system also needs a nudge mechanism. Add a third scheduled flow that runs Thursday at 10 AM and posts the form link to the Teams channel with a message like:

"🗓️ Friday status check-in reminder! Please take 2 minutes to complete this week's sprint update: [form link]. Responses needed by 3:30 PM Friday."

This flow is just a Recurrence trigger → Post message in Teams channel — about five minutes to build. The reminder dramatically increases response rates because people see it in the same channel where the summary posts, which creates a visible feedback loop: submit → get summary.

---

Hands-On Exercise

Now build this yourself with the following constraints, which will test whether you've internalized the architecture rather than just copied it:

Scenario: You're automating a weekly "health check" report for a marketing team of 8 people. The form asks:

1. Which team member are you? (Choice)
2. How many campaigns are currently in-flight? (Number)
3. Any copy or asset blockers this week? (Choice: Yes / No)
4. If blocked, describe it. (Long text, optional)
5. How would you rate team workload this week? (1 = under-utilized, 5 = overloaded) (Rating)

Your tasks:

1. Build the SharePoint list with appropriate columns, including a ReportWeek column using the same ISO week pattern.

2. Build Flow 1 (collection) with the Yes/No-to-boolean translation. Test it by submitting three responses — two with blockers, one without — and verify all three rows land correctly in SharePoint.

3. Build Flow 2 (summary) that computes:

   • Total campaigns in flight across the team
   • Average workload score
   • Count of blocked members
   • A joined string of blocker descriptions

4. Format and post an Adaptive Card to a Teams channel. The card should show a "⚠️ High Workload" warning header (in red) if the average workload score is above 4.0.

   Hint for the conditional color: Use a Condition before building the card to set a variable varHeaderColor to either "Attention" or "Good", then reference @{variables('varHeaderColor')} in the card JSON's color property for the header TextBlock.

5. Add the Thursday 10 AM reminder flow.

6. Test the full pipeline end-to-end: submit responses, then manually trigger Flow 2 using the Test button in Power Automate with the "manually trigger" option. Verify the card appears correctly in Teams.

---

Common Mistakes & Troubleshooting

The SprintWeek filter returns no results. The most common cause is a mismatch between how the week string was written to SharePoint and how the filter expression computes it. Check a row in SharePoint directly — open the item and look at the SprintWeek value. If it says 2024-W42 but your OData filter is generating 2024-W42 (trailing space) or using the wrong escape sequence, the filter silently returns zero items. Use a Compose action to output your filter string and inspect it in run history before wiring it to the Get items action.

The Adaptive Card action says "Success" but nothing appears in Teams. This is always a JSON formatting error. Copy your full card JSON (including the dynamic expression tokens resolved to sample values) and paste it into the Adaptive Card Designer at adaptivecards.io. Look for any property names that are misspelled or values that are the wrong type — for example, a number value inside quotes where the schema expects a raw number.

Confidence score averages show as integers, not decimals. This happens when both the numerator and denominator variables are Integers. Power Automate's div() function does integer division if both operands are integers — div(7, 3) returns 2, not 2.33. Initialize your varTotalConfidence as a Float (not Integer) to force decimal arithmetic. If you've already created it as Integer, delete and re-add it.

Apply to Each runs sequentially and the flow is slow. For a 10-person team this doesn't matter, but if you scale this to a larger organization, turn on Concurrency Control in the Apply to Each settings and set the degree of parallelism to 10 or 15. Just be aware that parallel Apply to Each loops cannot safely use Increment Variable — the increments will race and produce wrong totals. Switch to a different aggregation strategy, like collecting values into an array and using a final expression to sum them.

Forms responses aren't triggering Flow 1. The "When a new response is submitted" trigger polls for new responses on a schedule (typically every minute for paid plans). If you submit a response and the flow doesn't fire within 2-3 minutes, check that the connection in Power Automate is authenticated with an account that has access to the form. The form must be owned by or shared with the account that owns the connection. For troubleshooting runs that should have fired but didn't, using Power Automate Run History and Flow Checker to debug and fix failing flows is the right place to start.

---

Summary & Next Steps

You've built a complete, production-ready status report automation system with three interlocking components: a structured Forms intake, a SharePoint aggregation layer, and a scheduled Teams delivery flow. The key architectural insight is the SharePoint list as a deliberate decoupling layer — it means your collection and reporting concerns can evolve independently, and you have a permanent, queryable history of all team responses that you could later analyze in Power BI or export to Excel.

Here's what you built:

• A Microsoft Forms questionnaire optimized for automated aggregation (structured choices, not free text)
• An automated collection flow that normalizes form responses and writes them to SharePoint with week-stamping
• A scheduled summary flow that filters by current week, computes totals and averages, flags blockers, and posts a formatted Adaptive Card to Teams
• A lightweight reminder flow to drive submission compliance

Where to go next:

If you want to push this further, consider adding a Power BI dashboard that reads the same SharePoint list — this gives stakeholders a trend view over multiple sprints without any additional data work. Building Real-Time Power BI Dataset Refresh Pipelines with Power Automate shows how to wire the refresh trigger.

For teams that need a more interactive check-in experience — where team members can update their status directly inside Teams without leaving the app — look at Implementing Adaptive Card-Based Human-in-the-Loop Approvals in Power Automate, which teaches you how to build Adaptive Cards with input fields so the submission is the card interaction.

Finally, if your organization runs this system for multiple teams simultaneously, you'll want to look at how automating Microsoft Forms responses handles routing logic for multi-team scenarios, where a single form feeds different SharePoint lists and Teams channels based on which group the respondent belongs to.

The best automation systems are invisible. Team members just see their update get acknowledged, the summary appears reliably, and nobody chases anyone. That's what you've built here.