Automated Excel Report Generation from SharePoint Lists with Power Automate

Whether you need a recurring report exported to Excel or want to give end users a refreshed file on demand, Power Automate can handle the entire process. The approach described here creates a brand new Excel file in a SharePoint document library, adds a table, and inserts rows from any data source — all without knowing the column names in advance.

The magic relies on two key pieces: the SharePoint REST API to produce a blank workbook, and a CSV table to derive column headers dynamically. The same pattern works with Dataverse, SQL Server, or any connector that returns simple rows.

The example scenario: Construction Project Budgets

To illustrate the flow, imagine a SharePoint list called Construction Budgets that tracks capital projects. The list contains these columns:

• ProjectName (single line of text)
• BudgetAmount (currency or number)
• SpentToDate (number)
• Status (choice: On Track, Over Budget, Completed)
• TargetCompletion (date only)

A few sample items:

The flow will grab these items, create a fresh Excel file named Budgets_Report_<timestamp>.xlsx, and populate a table with the five columns above.

Step‑by‑step flow construction

1. Retrieve the data and shape it

Add a Get items action from the SharePoint connector, pointed at the Construction Budgets list.

Next, use a Select action to keep only the columns that should appear in the Excel file. Configure the map like this:

• ProjectName → ProjectName
• BudgetAmount → BudgetAmount
• SpentToDate → SpentToDate
• Status → Status
• TargetCompletion → TargetCompletion

The output of the Select action will be an array of objects. Only simple data types (text, numbers, dates, booleans) are allowed — complex types will cause the later Excel steps to fail.

2. Generate a unique file name

Use a Compose action to create a timestamped filename.

formatDateTime(utcNow(),'yyyy-MM-dd_HHmmss')

The compose output will be something like 2026-06-03_143022. Append the .xlsx extension later when calling the API.

3. Create a blank Excel file via SharePoint REST API

Add a Send an HTTP request to SharePoint action. This call will produce an empty workbook in the document library.

Method: POST

Uri: The API endpoint that targets the document library’s folder.

_api/web/GetFolderByServerRelativeUrl('Shared%20Documents')/Files/add(url='@{outputs('Compose')}.xlsx',overwrite=true)

The overwrite=true parameter prevents errors if a file with the same name is being processed or locked. The expression inside url= produces the full filename by concatenating the compose output and .xlsx.

Headers:

The response will contain the file’s unique ID. We’ll need that ID in later steps.

4. Obtain column headers from a CSV table

Before we can create an Excel table, we need to know the column names. The easiest way is to convert the Select output into a CSV table and extract the first row.

Add a Create CSV table action:

• From: the output of the Select action (the array of rows).

Now the first row of that CSV holds the column headers. Use another Compose to parse them out.

first(split(body('Create_CSV_table'), decodeUriComponent('%0A')))

%0A is the URL‑encoded newline character. Splitting on it gives an array of lines; first() grabs the header line.

5. Create an Excel table

Insert an Add a table into Excel action (from the Excel Online (Business) connector).

• Location: Shared Documents (choose the same document library).
• File: Use the unique ID returned by the HTTP request.

body('Send_an_HTTP_request_to_SharePoint')['d']['UniqueId']

• Table Name: tblBudgets
• Table Range: A1
• Headers: The output of the compose from step 4 (the header row).

The action will create a table with the correct columns, but it will be empty (except the header row).

6. Populate the table with data

Now add an Apply to each loop that iterates over the array from the Select action. Inside the loop, place the Add a row into a table action.

• File: Same unique ID as before.
• Table: tblBudgets
• Row: The current item of the loop.

Because the table already has column headers, the row values will map automatically by property names. The loop runs once per list item and inserts all rows.

7. Run the flow

Trigger the flow manually (or on a schedule). Within seconds a new Excel file appears in the Shared Documents library. Open it to see the fully populated table.

Security, delegation, and performance considerations

• SharePoint REST API permissions: The flow runs under the account that created the connection. Ensure that account has at least Contribute rights to the document library.
• Large datasets: The Get items action applies a default page size of 5000 items. If your list contains more, configure pagination with a Do until loop or use the $top and $skiptoken parameters in the REST API directly.
• Delegation: The Create CSV table and Select actions handle the data in memory. For extremely large exports (e.g., >50 MB) consider chunking or using a different export method.
• File locking: The timestamped filename plus overwrite=true reduces the chance of conflicts. Avoid reusing the exact same filename within seconds.

Common pitfalls and how to avoid them

Final recommendation

This pattern is ideal for any scenario where you need to generate a fresh Excel report on demand — weekly status updates, budget snapshots, audit logs, or project dashboards. Because the column names are derived at runtime, you can reuse the same flow across different SharePoint lists simply by swapping the source and the Select mapping.

For even more flexibility, consider parameterising the document library path and the target table name using variables. This makes the flow a reusable template that you can call from child flows or trigger from a Power App button.

References

• Original article: Create An Excel File And Add Rows Using Power Automate by Matthew Devaney
• SharePoint REST API documentation — SharePoint REST service overview (Microsoft Learn)
• Excel Online (Business) connector reference — Excel connector for Power Automate (Microsoft Learn)