Weekly Open Case Report to SharePoint
Weekly scheduled flow extracts all open Salesforce cases, writes them to an Excel workbook on SharePoint with pivot-ready formatting (by priority, age, owner), and notifies stakeholders.
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
An intermediate-level scheduled flow that extracts all open cases from Salesforce, writes each case as a row in an Excel workbook on SharePoint (pivot-ready by priority, age, and owner), and sends a summary notification email to stakeholders. It runs weekly on Monday mornings and produces a living report that accumulates weekly snapshots for trend analysis.
Use Case
Operations and executive teams need a recurring view of open Salesforce cases to track support health, identify bottlenecks by priority, and monitor case aging. This flow automates the extraction and formatting so stakeholders receive a ready-to-analyze Excel report without manual CRM exports.
The flow is ideal for teams that:
- Operations teams tracking support health and case backlog
- Executives monitoring open case trends week-over-week
- Support managers identifying bottlenecks by priority and aging
- Teams that prefer Excel-based reporting over CRM dashboards
Flow Architecture
Weekly Recurrence
RecurrenceFires every Monday at 8:00 AM Eastern Standard Time.
Initialize Variables (5x Parallel)
Initialize variableLoads all configurable values from environment variables: varSharePointSiteUrl from flowlibs_SharePointSiteURL, varExcelFilePath from flowlibs_CaseReportExcelFilePath, varExcelTableName from flowlibs_CaseReportExcelTableName, varRecipientEmail from flowlibs_CaseReportRecipientEmail, and varCaseCount initialized to 0 (integer).
Get Open Cases from Salesforce
GetItems_table_caseSalesforce connector action with OData filter 'IsClosed eq false', ordered by Priority asc, CreatedDate desc, top 500.
Set Case Count
Set variableSets varCaseCount to length() of returned case array.
Write Cases to Excel
Apply to each / AddRowV2For each case: calls Excel Online AddRowV2 to write 8 columns (CaseNumber, Subject, Status, Priority, Owner, CreatedDate, AccountName, ReportDate) into the target table.
Send Notification Email
SendEmailV2Outlook sends an HTML email summarizing total open cases, report date, and the file location link to the recipient.
Environment Variables
| Schema name | Type | Default | Description |
|---|---|---|---|
| flowlibs_SharePointSiteURL | String | https://your-tenant.sharepoint.com | Root SharePoint site for the document library hosting the Excel workbook. |
| flowlibs_CaseReportExcelFilePath | String | /Shared Documents/FlowLibs - Weekly Open Case Report.xlsx | Relative path to the Excel workbook on the SharePoint site. |
| flowlibs_CaseReportExcelTableName | String | OpenCases | Name of the Excel table that AddRowV2 will append rows to. |
| flowlibs_CaseReportRecipientEmail | String | alerts@yourcompany.com | Email recipient (or distribution list) for the weekly notification. |
Connectors & Connections
| Connector | API name | Actions used |
|---|---|---|
| Salesforce | shared_salesforce | GetItems_table_case (Retrieves open cases via OData filter) |
| Excel Online (Business) | shared_excelonlinebusiness | AddRowV2 (Appends each case as a row in the table) |
| Office 365 Outlook | shared_office365 | SendEmailV2 (Sends the weekly summary email) |
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.
- Create the Excel workbook on SharePoint
- Provision a workbook with a table named OpenCases containing columns: CaseNumber, Subject, Status, Priority, Owner, CreatedDate, AccountName, ReportDate.
- Update environment variables
- Set flowlibs_SharePointSiteURL to your SharePoint site, flowlibs_CaseReportExcelFilePath to the workbook path, flowlibs_CaseReportExcelTableName to your table name, and flowlibs_CaseReportRecipientEmail to your distribution list.
- Authorize connections
- Open the flow in the designer and authorize the Salesforce, Excel Online, and Outlook connections.
- Turn the flow On
- Enable the flow — it will run every Monday at 8 AM Eastern.
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.01Case count
Counts the returned case array from Salesforce.
EXPR.02Report date stamp
ISO date string used in the ReportDate column.
EXPR.03Nested object access
Safely navigates Salesforce relationship fields inside the foreach.
EXPR.04OData filter
Standard Salesforce OData filter to retrieve only open cases.
Comments
Sign in to join the conversation.
Sign inNo comments yet. Be the first to share your experience with this flow.