Canvas App Excel Export: Power Automate Flow Pattern (Dataverse Source, SharePoint Storage)

#Purpose

Document the approved pattern for exporting data from a Dataverse-backed Canvas app to an Excel file that is (a) saved to a SharePoint document library for record-keeping/audit, and (b) downloaded directly to the user's browser in the same action, without a manual "open and save" step. This complements the general build guidance in canvas-apps-build-guide.md and should be followed for any new export feature built on this pattern.

#Architecture Overview

Canvas App (button)
   -> Power Automate flow (PowerApps V2 trigger)
        -> List rows from Dataverse (filtered, delegable)
        -> Populate a Microsoft Excel Template (Excel Online Business)
        -> Create file in SharePoint document library (save generated .xlsx)
        -> Get file content from the saved SharePoint file (base64)
        -> Respond to PowerApps (file content, MIME type, saved file URL)
   -> Canvas App receives base64 file content
        -> Download() function saves file to browser downloads

Dataverse is the system of record for the underlying data. SharePoint is used purely as document storage for the generated export file (audit trail, later retrieval, sharing a link), not as the data source. The file returned to the Canvas app for download is read back from the copy just saved in SharePoint, so the saved file and the downloaded file are always identical.

#Prerequisites

1. Excel template file stored in the target SharePoint document library (or another library reachable by the flow's connection), containing a properly defined Excel Table (Insert > Table, not just formatted cells) with column headers matching the Dataverse columns being exported.
2. Target SharePoint document library where generated export files will be saved. Decide naming/folder conventions up front (for example, a dated subfolder per export run) to avoid an unbounded flat file list.
3. Connection references for Dataverse, Excel Online (Business), and SharePoint, created in the solution rather than ad hoc, per the ALM guidance in the build guide (items 20-21).
4. Environment variables for the Dataverse table logical name, the SharePoint site URL, the target document library, and the template file path. Do not hard-code these (build guide item 19).
5. Canvas app running on a Power Apps player version that supports the Download() function (2022+). If supporting older players, see the fallback note below.

#Flow Design

#1. Trigger: PowerApps (V2)

Add input parameters for anything the app needs to control the export, for example:

• FilterColumn (Text) - optional Dataverse column name to filter on
• FilterValue (Text) - optional value to filter on
• FileName (Text) - desired output file name, generated in the app so naming stays consistent (for example, MarketPlaceReport_Export_[yyyyMMdd].xlsx)

#2. List rows (Dataverse)

Use List rows against the target Dataverse table with a Filter rows (OData) expression rather than pulling all rows and filtering in the flow. This keeps the query delegable and consistent with the "design the backend data model first with delegable queries" principle in the build guide (item 10).

• Set Row count and, if the result set can exceed the default page size, enable pagination in the flow's Settings so large exports are not silently truncated.
• Use Select columns to return only the fields needed for the export. This reduces payload size, avoids unnecessary option-set/lookup expansion, and speeds up the subsequent steps.
• If option set or choice columns are being exported, decode them to display text (for example with a Select action and a Switch()/lookup expression) before mapping into Excel, so the workbook shows readable labels instead of numeric values.

#3. Populate a Microsoft Excel Template (Excel Online Business connector)

This step builds the .xlsx content in memory from the Dataverse rows:

• Location: SharePoint Site Address (from environment variable)
• Document Library: the library containing the template
• File: the template file path (from environment variable)
• Table: the Table name defined inside the template
• Map each Dataverse column (post-decode, if applicable) to the corresponding table column.

This action returns a temporary populated file reference; it does not save anything yet.

#4. Create file (SharePoint) — save the generated export

Use Create file (SharePoint) to persist the populated workbook:

• Site Address: target site (environment variable)
• Folder Path: the target document library/folder for saved exports (environment variable), optionally with a dated subfolder built in the flow (for example, /Exports/ & formatDateTime(utcNow(), 'yyyy-MM'))
• File Name: the FileName input passed from the app
• File Content: the output of the Populate step

This gives every export a permanent, auditable copy in SharePoint, independent of whether the user successfully downloads it.

#5. Get file content (SharePoint)

Use Get file content against the file just created in step 4 (its Id is returned by Create file), with the output set to base64. Reading the saved copy back — rather than reusing the Populate step's temporary output — guarantees the downloaded file matches exactly what was persisted to SharePoint.

#6. Respond to PowerApps

Return outputs to the app:

• FileContent (File) - the base64 file content
• FileName (Text) - echo back the file name
• SharePointFileUrl (Text) - the saved file's web URL, useful if the app wants to offer "open in SharePoint" in addition to the direct download
• Success (Boolean)

#7. Error handling

Wrap steps 2-5 in a Scope with a parallel Configure run after error-handling scope, per the build guide's error-handling section (items 15-17):

• Use Configure run after (has failed / has timed out / is skipped) as the try/catch equivalent.
• On failure, respond to PowerApps with a clear error message and Success = false so the app can show a user-friendly message rather than a raw flow error.
• Log unexpected failures (for example, to a Dataverse audit table or Application Insights) rather than surfacing technical details to the user.

#Canvas App Side

1. Call the flow, passing filter parameters and a generated file name:

   Set(
       varExportResult,
       'ExportToExcelFlow'.Run(
           txtFilterColumn.Text,
           txtFilterValue.Text,
           "MarketPlaceReport_Export_" & Text(Today(), "yyyymmdd") & ".xlsx"
       )
   );
   

2. On success, trigger the browser download using the Download() function:

   If(
       varExportResult.Success,
       Download(
           varExportResult.FileContent,
           varExportResult.FileName
       ),
       Notify("Export failed. Please try again or contact support.", NotificationType.Error)
   );
   

3. Optionally surface varExportResult.SharePointFileUrl as a secondary "View in SharePoint" link/button for users who want to access the saved copy later without re-running the export.

4. Fallback for older player versions (no Download() support): use a hidden PDF viewer or HTML text control with a data: URI built from the base64 string, and prompt the user to click a generated link. This is a legacy workaround only; new builds should require a player version that supports Download().

#Performance Considerations

• Keep the exported column set as narrow as possible; wide exports with many lookup or choice columns slow down both the Dataverse query and the Excel population step.
• If exports commonly exceed a few thousand rows, evaluate whether a pre-aggregated Dataverse view or a scheduled export (rather than on-demand, synchronous export) better fits the volume, to avoid the flow timing out on large payloads.
• The extra Create file / Get file content round trip (saving to SharePoint, then reading it back) adds a small amount of latency versus reading directly from the Populate step's output. This is an intentional trade-off for having a persisted, auditable copy; if latency becomes a problem for very frequent exports, consider returning the Populate step's file content directly to the app and saving to SharePoint asynchronously in a parallel branch instead.
• Test with production-sized datasets, not sample data, before promoting to UAT (build guide item 14).

#Security Considerations

• Use Dataverse security roles/teams to control who can read the underlying data the export pulls from, and Entra ID group-based access to control who can trigger the export flow, consistent with the build guide's security section (items 23-24).
• Confirm the SharePoint connection used by the flow has write access scoped only to the target export library/folder, not broader site-wide write access.
• Do not embed connection references, site URLs, table names, or file paths directly in flow actions; reference environment variables so the same flow promotes cleanly across Dev/Test/UAT/Prod (build guide items 18-19, 22).

#ALM Notes

• Build and test this flow inside the approved solution and publisher for the environment, not as a default-solution asset (build guide items 20-21).
• Promote the flow (and its template file, connection references, and environment variable values, including the SharePoint site/library references) to UAT as soon as the pattern is stable, rather than holding it until the entire export feature set is complete (build guide item 22).

#Known Limitations

• The Excel Online (Business) connector's "Populate a Microsoft Excel Template" action can be a throughput bottleneck for very large row counts; if this becomes an issue, evaluate an Azure Function or Office Scripts-based generation path as an alternative, keeping the same Canvas app-facing contract (base64 file content + file name).
• This pattern assumes the template file and its Table structure remain unchanged; adding or reordering template columns requires updating the mapping in the Populate step.
• Saved exports accumulate in the target SharePoint library over time; define a retention or cleanup policy (for example, a scheduled flow to archive/delete files older than N days) if this is a frequently used export.