Azure DevOps Sprint Burndown Email Report
At the end of each sprint, calculate completed vs remaining story points and email the team a burndown summary.
Provided as-is, without warranty of any kind. Review and test each pattern in a non-production environment before deploying it to live automations. See our Terms.
Overview
This flow queries Azure DevOps for all work items in a configured sprint iteration path, calculates burndown metrics (total story points, completed points, remaining points, percent complete), logs each work item snapshot to an Excel Online workbook on SharePoint, and sends a polished HTML burndown report via Outlook email. It runs daily during the sprint at 5:00 PM UTC.
Use Case
Project managers and scrum masters need daily visibility into sprint progress without manually pulling data from Azure DevOps. This flow automates the burndown snapshot, archives the data for trend analysis in Excel/Power BI, and delivers an executive-ready summary email to stakeholders.
Flow Architecture
Recurrence
RecurrenceRuns daily at 5:00 PM UTC during the sprint.
Initialize Variables (12 parallel)
Initialize variableBinds 9 environment variables to runtime variables (varAdoOrganization, varAdoProject, varIterationPath, varSharePointSiteURL, varRecipientEmail, varSprintName, varExcelFilePath, varExcelTable, varDriveId) plus 3 working variables (varTotalStoryPoints integer, varCompletedStoryPoints integer, varBurndownTableHtml string).
Query Sprint Work Items
HTTP request (Azure DevOps)POST a WIQL query filtering by iteration path for User Stories, Bugs, and Tasks to return the list of work item IDs in the sprint.
Parse WIQL Response
Parse JSONExtracts the work item ID array from the WIQL response.
For Each Work Item
Apply to eachFor each work item ID: get full details, parse fields, accumulate totals, conditionally count as completed, append an HTML row, and log a snapshot row to Excel (6 nested actions).
Get Work Item Details (inside loop)
HTTP request (Azure DevOps)GET the work item with $expand=all to retrieve all fields including story points, state, and assignee.
Parse Work Item (inside loop)
Parse JSONExtracts Title, State, WorkItemType, StoryPoints, RemainingWork, CompletedWork, and AssignedTo from the work item payload.
Accumulate Total Story Points (inside loop)
Increment variableAdds the work item's story points to varTotalStoryPoints using a coalesce null-safe pattern.
Environment Variables
| Schema name | Type | Default | Description |
|---|---|---|---|
| flowlibs_AdoOrganization | String | your-org | Azure DevOps organization name. |
| flowlibs_AdoProject | String | FlowLibs | Azure DevOps project name. |
| flowlibs_AdoSprintIterationPath | String | FlowLibs\Sprint 1 | Iteration path used in the WIQL UNDER clause; child iterations are included. |
| flowlibs_SharePointSiteURL | String | https://your-tenant.sharepoint.com | SharePoint site that hosts the Excel workbook. |
| flowlibs_SprintBurndownRecipientEmail | String | you@yourcompany.com | Email recipient(s) for the burndown report. Supports semicolon-separated addresses. |
| flowlibs_SprintBurndownSprintName | String | Sprint 12 | Display name for the sprint, shown in the email subject and body. |
| flowlibs_SprintBurndownExcelFilePath | String | /FlowLibs Sprint Burndown/FlowLibs - Sprint Burndown Data.xlsx | Server-relative path to the Excel workbook that stores the burndown snapshots. |
| flowlibs_SprintBurndownExcelTable | String | Sprint Backlog | Name of the table inside the Excel workbook where snapshot rows are added. |
Connectors & Connections
| Connector | API name | Actions used |
|---|---|---|
| Azure DevOps | shared_visualstudioteamservices | HttpRequest (WIQL query for sprint work items) HttpRequest (Get work item details ($expand=all)) |
| Excel Online (Business) | shared_excelonlinebusiness | AddRowV2 (Write burndown snapshot row) |
| Office 365 Outlook | shared_office365 | SendEmailV2 (Send burndown email report) |
Note — All connections are referenced as solution connection references; the flow is portable between environments as long as a connection is mapped at import time.
Customization Guide
Almost every realistic variant of this flow can be implemented by changing environment variable values. A few cases require small edits inside the flow definition — those are called out explicitly below.
- Change the sprint
- Update flowlibs_AdoSprintIterationPath to the new iteration path (e.g., FlowLibs\Sprint 2) and flowlibs_SprintBurndownSprintName for the email display name.
- Change recipients
- Update flowlibs_SprintBurndownRecipientEmail — supports semicolon-separated addresses for multiple recipients.
- Change frequency
- Edit the Recurrence trigger — for example, switch to weekly for end-of-sprint-only reports.
- Filter work item types
- Modify the WIQL query inside Query Sprint Work Items to add or remove work item types (e.g., add 'Feature' or remove 'Task').
- Add Teams notification
- Add a Teams PostMessageToConversation action after the email send for channel-based delivery.
- Move to another tenant
- Update all 9 environment variable values, authorize the 3 connections (Azure DevOps, Excel Online, Outlook), and turn on the flow.
Key Expressions
The flow is intentionally light on Power Fx / WDL gymnastics — the heaviest expressions are the branch-name concatenation and the approval outcome check. They are listed below in the order they appear in the flow.
EXPR.01WIQL iteration filter
The UNDER operator includes child iteration paths under the configured sprint.
EXPR.02Null-safe story points
Defaults missing story points to 0 to prevent null increment errors.
EXPR.03Burndown percentage
Division-by-zero-safe percent-complete calculation for the email summary.
EXPR.04Snapshot date
Daily snapshot key written to the Excel table for trend analysis.
Comments
Sign in to join the conversation.
Sign inNo comments yet. Be the first to share your experience with this flow.