Salesforce Report Export via Analytics API
Scheduled flow calls the Salesforce Analytics API via SendHTTPRequest, parses the tabular results, and writes them to an Excel workbook on SharePoint for Power BI consumption.
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
Runs every morning at 6:00 AM Eastern. Calls the Salesforce Analytics API to execute a saved report, parses the tabular grand-total fact-map rows out of the JSON response, and appends each row (plus a run-date stamp) to an Excel table hosted on SharePoint. The Excel table is a persistent, append-only source that Power BI can connect to directly — no Salesforce connector needed on the BI side.
Out-of-the-box Power BI Salesforce reports auth to Salesforce per-user and are opaque to anyone without licensed Salesforce seats. This flow externalises the report payload into a Microsoft-native destination, so BI downstream (Power BI, Excel, Fabric) can operate on the data without Salesforce credentials. It also captures a timestamped history: every run appends new rows rather than overwriting, so you get day-over-day trending for free.
Use Case
Sales operations and executive reporting teams that need to surface Salesforce report data inside the Microsoft BI stack without granting Salesforce seats to every consumer. By using the Salesforce Analytics API through SendHTTPRequest and persisting results to an Excel table on SharePoint, the flow creates a self-serve, append-only history that Power BI, Excel, and Fabric can read directly. A preflight SharePoint call fails the run early when the workbook is mis-pathed, conserving the daily Salesforce API quota.
The flow is ideal for teams that:
- Sales and revenue operations teams who want Salesforce report data in Power BI without Salesforce-licensed consumers
- Executive reporting where day-over-day trending of report metrics is required
- Organisations standardising BI on Microsoft 365 / Fabric and wanting to avoid per-user Salesforce auth on dashboards
- Advanced makers comfortable with Salesforce's SendHTTPRequest action and parsing the Analytics API JSON fact-map
Flow Architecture
Recurrence_Daily_6AM_Eastern
RecurrenceFires daily at 06:00 Eastern Standard Time.
Init varSalesforceReportId
Initialize variableLoads the Salesforce report ID from the flowlibs_SalesforceReportId env var into a string variable for downstream use.
Init varSalesforceApiVersion
Initialize variableLoads the Salesforce REST API version segment (e.g. v60.0) from flowlibs_SalesforceApiVersion.
Init varSharePointSiteUrl
Initialize variableLoads the SharePoint site absolute URL from flowlibs_SharePointSiteURL.
Init varSharePointDriveId
Initialize variableLoads the SharePoint drive ID (b!… format) from flowlibs_SharePointDocumentsLibraryID.
Init varExcelFilePath
Initialize variableLoads the relative workbook path from flowlibs_SalesforceReportExcelPath.
Init varExcelTableName
Initialize variableLoads the target Excel table name (not worksheet name) from flowlibs_SalesforceReportExcelTableName.
Preflight_Get_Excel_Workbook_Metadata
SharePoint — GetFileMetadataByPathPreflight probe that fails fast if the workbook path is missing or the site/library is misconfigured — runs before the Salesforce call so a bad config does not waste the daily Salesforce API quota.
Compose_Report_Api_Path
ComposeBuilds the Analytics API URI by concatenating /services/data/{apiVersion}/analytics/reports/{reportId}?includeDetails=true.
Environment Variables
| Schema name | Type | Default | Description |
|---|---|---|---|
| flowlibs_SalesforceReportId | String | <configure> | 15 or 18-char Salesforce report ID for the saved tabular report to export (e.g. 00O5g00000AbCdEFG). Grab from the report's Salesforce URL. |
| flowlibs_SalesforceApiVersion | String | v60.0 | Salesforce REST API version segment used when building the Analytics API path (e.g. v60.0). |
| flowlibs_SharePointSiteURL | String | https://your-tenant.sharepoint.com/sites/SalesOps | Absolute URL of the SharePoint site that hosts the Excel workbook history. |
| flowlibs_SharePointDocumentsLibraryID | String | <configure> | SharePoint drive ID (b!… format) for the document library that contains the workbook. Get this from Graph or a Get site / Get drives action. |
| flowlibs_SalesforceReportExcelPath | String | /Shared Documents/Sales/SFReportHistory.xlsx | Site-relative path to the destination Excel workbook. The workbook must exist and contain a named Excel table (Insert → Table). |
| flowlibs_SalesforceReportExcelTableName | String | SFReportRows | Name of the Excel table inside the workbook that AddRowV2 will append to — must be a named table, not a worksheet or loose range. |
Connectors & Connections
| Connector | API name | Actions used |
|---|---|---|
| Salesforce | shared_salesforce | HttpRequest (GET against /services/data/{apiVersion}/analytics/reports/{reportId}?includeDetails=true) |
| Excel Online (Business) | shared_excelonlinebusiness | AddRowV2 (Append one row per fact-map entry to the target Excel table) |
| SharePoint | shared_sharepointonline | GetFileMetadataByPath (Preflight probe to fail fast on bad workbook path) |
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 schedule cadence
- Edit the Recurrence_Daily_6AM_Eastern trigger's schedule / frequency / timeZone to run hourly, weekly, or in a different time zone.
- Capture more report columns
- Extend the item object on Append_Row_To_Excel_Table with additional Col6..ColN keys and matching dataCells?[n]?['label'] selectors, then add matching column headers in the Excel table.
- Project multiple aggregates
- aggregates?[0] is the first aggregate on each row. For multi-aggregate reports, add AggregateValue2, AggregateValue3 entries (and matching Excel table columns) pointing to aggregates?[1], aggregates?[2], etc.
- Retarget a summary or matrix report
- Tabular reports use the T!T factMap key. Summary and matrix reports use grouping keys such as GroupingKey_0!T. Swap the factMap path in Compose_Grand_Total_Fact_Rows when retargeting a non-tabular report.
- Rename the Col* headers
- The default Col1..Col5 labels are positional placeholders. Rename them in the Excel table header to match the actual field names returned by the Salesforce report — the flow does not need any change.
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.01Build the Analytics API path
Used in Compose_Report_Api_Path — assembles the relative URI passed to the Salesforce HttpRequest action.
EXPR.02Pull the grand-total row list safely
Used in Compose_Grand_Total_Fact_Rows. T!T is the Salesforce grand-total key for tabular reports; the safe-navigation operators plus coalesce(..., createArray()) prevent a null-deref when a report returns zero rows.
EXPR.03Append-row item shape (Excel AddRowV2)
Item payload passed to Excel AddRowV2 inside Foreach_Report_Row. The five Col* positions map to the first five fields in the Salesforce report's column list — rename the Excel headers to match your real report columns.
Comments
Sign in to join the conversation.
Sign inNo comments yet. Be the first to share your experience with this flow.