Smartsheet Project Status Rollup Digest

Overview

This flow produces a weekly portfolio status rollup from a Smartsheet project sheet. Every Monday at 7:00 AM it reads all project rows, classifies them into at-risk, milestones due, and over-budget sets, builds an HTML digest, posts it to a Teams channel, and emails the same summary to leadership via Outlook.

Why it matters: Compiling cross-project status by hand is slow and inconsistent. This flow turns a Smartsheet project sheet into an automatic, repeatable portfolio readout for PMs and executives.

Use Case

A PMO maintains project rows in a Smartsheet sheet (Status, Percent Complete, Due Date / Milestone, Budget, Actual). Leadership wants a weekly cross-project status summary without each PM assembling slides. This flow replaces the manual status report with an automatic Teams post + email digest.

Flow Architecture

Environment Variables

Connectors & Connections

Connector	API name	Actions used
Smartsheet	shared_smartsheet	GetSheetData
Microsoft Teams	shared_teams	PostMessageToConversation
Office 365 Outlook	shared_office365	SendEmailV2

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.

Risk sensitivity

Adjust varAtRiskThreshold (0-1) to change how far behind a project must be to count as at risk.

Risk label

Set varAtRiskStatusLabel to match your sheet's Status value (e.g. Red, Off Track).

Milestone window

Change varMilestoneWindowDays to widen/narrow the upcoming-milestone horizon.

Column names

The filter/compose expressions reference column titles (Percent Complete, Status, Due Date, Budget, Actual); rename them to match your sheet.

Distribution

Point the Teams env vars and flowlibs_LeadershipDigestEmail at your team/channel/recipient. Add per-PM DMs or a RAG color scheme if desired.

Multi-sheet

Wrap Get_Project_Rows in an Apply-to-each over a list of sheet IDs to roll up a portfolio spanning several sheets.

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.

@or(less(float(coalesce(item()?['Percent Complete'], 0)), variables('varAtRiskThreshold')), equals(item()?['Status'], variables('varAtRiskStatusLabel')))

@and(not(empty(item()?['Due Date'])), lessOrEquals(formatDateTime(item()?['Due Date'],'yyyy-MM-dd'), addDays(utcNow(), variables('varMilestoneWindowDays'),'yyyy-MM-dd')), greaterOrEquals(formatDateTime(item()?['Due Date'],'yyyy-MM-dd'), utcNow('yyyy-MM-dd')))

@greater(float(coalesce(item()?['Actual'], 0)), float(coalesce(item()?['Budget'], 0)))

string(length(body('Filter_At_Risk_Items')))

@concat('<h2>Weekly Portfolio Status Rollup</h2>', ... , string(body('Create_At_Risk_Table')), ...)

Customize & download