Automating Consolidated MS Planner Bucket & Task Reports for Stakeholder Using Power Automate

Background & Real‑World Scenario

In many organizations, daily or alternate-day Scrum meetings are conducted using Microsoft Teams Planner. Each Planner typically contains:

• One Microsoft 365 Group
• Multiple Buckets
• Multiple Tasks inside each bucket
• Tasks assigned to different users

As work progresses, assigned users update their tasks regularly—status, percentage complete, or due dates. While Microsoft Planner handles individual task notifications well, it does not provide a built‑in way to send a consolidated report of:

• All buckets
• All tasks
• Across an entire Group / Plan
• In one single email
• On a daily basis to managers or stakeholders

The Problem

By default:

• Planner sends individual notifications when a user updates their assigned task
• Bucket owners, plan owners, or managers receive multiple scattered emails
• There is no consolidated view showing:
  • Overdue tasks
  • Upcoming tasks
  • Tasks that are still open or not updated

This creates visibility gaps for managers during Scrum reviews and status meetings.

---

The Solution

To solve this, I developed a custom Power Automate flow named:

✅ “Notify: Upcoming & Overdue Planner Tasks”

This flow automatically:

• Scans all tasks across all buckets in a Planner
• Identifies:
  • Overdue tasks
  • Upcoming tasks (within next 7 days)
  • Incomplete tasks
• Sends a single consolidated HTML email report
• Helps managers quickly understand:
  • What is pending
  • What is overdue
  • What requires immediate attention

---

Prerequisites

Before creating this flow, ensure:

• You are at least a Member or Owner of the Microsoft 365 Group
• You have access to the Planner Plan you want to monitor
• You have permission to create Power Automate flows

---

Power Automate Flow Design – Step by Step

---

Step 1: Recurrence Trigger

• Use the Recurrence trigger
• Configure based on your requirement:
  • Interval (e.g., 1)
  • Frequency (Day)
• Important: Always set your Time Zone
  • If not set, the flow runs based on UTC
• Optionally configure:
  • Start time
  • Specific days
  • Hours and minutes

✅ This ensures reports are sent at a consistent local time.

---

Step 2: List Buckets

Add Planner – List buckets action:

• Select Group Id
• Select Plan Id

If you have permission, these values will auto-populate in the input fields.

---

Step 3: List Tasks

Add Planner – List tasks action:

• Use the same Group Id
• Use the same Plan Id

This retrieves all tasks across all buckets.

---

Step 4: Prepare HTML Table for Email

Add a Compose action to build an HTML table:

• This table will be used in the email body
• Use custom CSS for better readability
• You can style:
• Table headers
• Borders
• Font size
• Background colors
• Paste the following CSS:

  table {
    border: 1px solid #1C6EA4;
    background-color: #EEEEEE;
    width: 100%;
    text-align: left;
    border-collapse: collapse;
  }
  table td, table th {
    border: 1px solid #AAAAAA;
    padding: 3px 2px;
  }
  table tbody td {
    font-size: 13px;
  }
  table thead {
    background: #686E9F;
    border-bottom: 2px solid #444444;
  }
  table thead th {
    font-size: 15px;
    font-weight: bold;
    color: #FFFFFF;
  }
  

  ✅ This makes the email manager‑friendly and easy to read.

---

Step 5: Filter Overdue Tasks

Add a Filter array action:

• From: Output of List tasks
• Use Advanced mode expression: 

  @and(
    not(empty(item()?['dueDateTime'])),
    less(item()?['dueDateTime'], utcNow()),
    less(item()?['percentComplete'], 100)
  )
  

  🔹 This filter returns:

• Tasks with a due date
• Past the due date
• Not yet completed

---

Step 6: Filter Upcoming Tasks (Next 7 Days)

Add another Filter array action with this expression:

@and(
  not(empty(item()?['dueDateTime'])),
  greaterOrEquals(item()?['dueDateTime'], utcNow()),
  lessOrEquals(item()?['dueDateTime'], addDays(utcNow(), 7)),
  less(item()?['percentComplete'], 100)
)

Step 7: Apply to Each – Iterate Through Buckets (First Loop)

At this stage, the goal is to process bucket‑wise data so tasks can later be mapped correctly to their respective buckets.

Configuration

• Add Apply to each
• Select an output from previous steps
  → Use List buckets (value)

Inside this loop

Add a Condition action with AND logic.

Condition 1

Check that the task has a due date:

empty(item()?['dueDateTime']) is equal to false

Condition 2

Check whether the due date is within the next 7 days:

items('Apply_to_each')?['dueDateTime'] 

is less than or equal to 

addDays(utcNow(), 7)

Why this step is required

Some Planner tasks may not have a due date.
This condition ensures that only valid tasks with a due date, and within the defined time window, are processed further.

---

Step 8: Apply to Each – Second Bucket Iteration Loop

This step repeats the bucket iteration logic in a separate loop to avoid dependency and concurrency issues when multiple filters and loops are used in the same flow.

Configuration

• Add another Apply to each (rename it to Apply_to_each_2)
• Select an output from previous steps
  → Use List buckets (value)

Inside this loop

Add a Condition action with the same AND logic:

Condition 1

empty(item()?['dueDateTime']) is equal to false

Condition 2

items('Apply_to_each_2')?['dueDateTime'] 

is less than or equal to 

addDays(utcNow(), 7)

Why this loop exists

Power Automate may throw validation or runtime issues when complex loops share the same references.
Using a second iteration loop ensures stable execution and clean data separation.

---

Step 9: Initialize Variable – Overdue Tasks Array

Now we prepare a container to store overdue task details.

Configuration

• Action: Initialize variable
• Name: TaskArray
• Type: Array
• Value: []

Purpose

This array will hold structured data for overdue tasks, which will later be converted into an HTML table.

---

Step 10: Initialize Variable – Upcoming Tasks Array

Create a second array for upcoming tasks.

Configuration

• Action: Initialize variable
• Name: TaskArray2
• Type: Array
• Value:[]

Step 11: Apply to Each – Build Overdue Task Dataset

This step links overdue tasks with their respective bucket names and stores them in a clean format.

Configuration

• Add Apply to each
• Select an output from previous steps
  → Output of Filter array (Overdue Tasks)

---

Inside this loop

Step 11.a: Lookup the Bucket Name

• Add Filter array
• From: Output of List buckets (value)

Condition (Basic mode):

item()?['id'] 

is equal to 

items('Apply_to_each_FilteredTasks')?['bucketId']

---

Step 11.b: Compose Bucket Name

Add a Compose action with the following expression:

coalesce(first(body('Filter_array_(Lookup_bucket)'))?['name'],'Unknown Bucket')

This prevents failure if a bucket no longer exists.

---

Step 11.c: Append Task Details to Overdue Array

• Action: Append to array variable
• Name: TaskArray
• Value:

{
  "Bucket Name": "@{outputs('Compose_BucketName')}",
  "Task Name": "@{items('Apply_to_each_FilteredTasks')?['title']}",
  "Overdue Date": "@{formatDateTime(item()?['dueDateTime'], 'dd-MMM-yyyy')}"
}

---

Step 12: Apply to Each – Build Upcoming Task Dataset

Repeat the same logic for upcoming tasks.

Configuration

• Add Apply to each
• Select an output
  → Output of Filter array (Upcoming Tasks)

---

Inside this loop

Step 12.a: Lookup Bucket

item()?['id'] 

is equal to 

items('Apply_to_each_FilteredTasks_2')?['bucketId']

Step 12.b: Compose Bucket Name

coalesce(first(body('Filter_array_(Lookup_bucket)_2'))?['name'],'Unknown Bucket')

Step 12.c: Append Task Details

• Action: Append to array variable
• Name: TaskArray2
• Value:

{
  "Bucket Name": "@{outputs('Compose_BucketName_2')}",
  "Task Name": "@{items('Apply_to_each_FilteredTasks_2')?['title']}",
  "Due Date": "@{formatDateTime(item()?['dueDateTime'], 'dd-MMM-yyyy')}"
}

Step 13: Create HTML Table – Overdue Tasks

• Action: Create HTML table
• From: TaskArray
• Columns: Automatic

This table represents all overdue tasks.

---

Step 14: Create HTML Table – Upcoming Tasks

• Action: Create HTML table
• From: TaskArray2
• Columns: Automatic

This table represents all tasks due within the next 7 days.

---

Step 15: Final Condition and Email Notification Logic

Condition

Check whether overdue tasks exist:

length(body('Filter_array')) is equal to 0

---

✅ If TRUE (No Overdue Tasks)

Send email containing only upcoming tasks:

Hi Username,
 
Please review the Planner tasks scheduled to expire within the next 7 days:
 
@{outputs('Compose_Table_CSS')}
@{body('Create_HTML_table_2')}

Regards,
MS Planner

---

❌ If FALSE (Overdue Tasks Exist)

Send a consolidated email:

Hi Username,
 
Please review the Planner tasks that are overdue or still open:
 
@{outputs('Compose_Table_CSS')}
@{body('Create_HTML_table')}

Please review the Planner tasks scheduled to expire within the next 7 days:

@{outputs('Compose_Table_CSS')}
@{body('Create_HTML_table_2')}

Regards,
MS Planner

---

---

Business Value & Benefits

✅ One consolidated email
✅ Clear visibility across all buckets and tasks
✅ Saves time during Scrum and status meetings
✅ Improves accountability and planning
✅ 100% customizable and reusable

---

Conclusion

Microsoft Planner is excellent for task execution, but Power Automate transforms it into a management‑ready reporting solution.

This automation bridges the gap between:

• Team‑level work
• Stakeholder‑level visibility

and enables better planning, tracking, and decision‑making.

👉Output of this flow:- 

👉Full structure of this flow:-