Create An Excel File And Add Rows Using Power Automate

This is the pattern I use to create a new Excel file with Power Automate and add rows. It can take data from any datasource (SharePoint, Dataverse, SQL, etc.). And the file is dynamically generated so it is not necessary to know the column names in advance. The code can be copied and pasted into any flow where you need to create an Excel report.

Table of Contents
• Setup The SharePoint List
• Create An Array Of Values To Export To Excel
• Generate A Blank Excel File
• Insert A Table Into The Excel File
• Add Rows To A Table In The Excel File
• Run The Flow To Create A New Excel File In The SharePoint Document Library

Setup The SharePoint List

Create a new SharePoint list named Project Bid Opportunities with the following columns and types:

• Title – single-line text
• Bid Date – date-only
• Bid Result – choice column (submitted, won, lost)
• Amount – number
  

Then populate the list with this data:

Once completed the Project Bid Opportunities SharePoint list will look like this.

Create An Array Of Values To Export To Excel

Our first goal is to get the list of items from SharePoint and build an array that can be exported into an Excel table. Create a new Power Automate flow named SharePoint List To New Excel file and use an instant trigger.

Make the first flow action SharePoint – Get Items. Point the action to the Project Bid Opportunities list.

Use the Data Operations – Select action to keep only 4 columns we want to include in the Excel file and drop the rest:

• Title
• Bid Date
• Bid Result
• Amount
  
  

When the Select flow action is run it will generate the array below. Note that we must only use simple data types in our array such as text, number, date times and true/false values when exporting to Excel. A complex data type such as object or array will make this process fail.

Generate A Blank Excel File

A blank excel file must be created before we can begin to populate it with data. Start by assigning a filename with a Data Operations – Compose action.

Use the formatDateTime function to ensure a unique filename is generated everytime. A non-unique filename can lead to failures when the flow attempts over overwrite a locked file.

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

For the next step we must know the location of the SharePoint document library the Excel file will created within. In this example we will use the Documents folder included with every SharePoint site.

The path to the folder can be found in the document library URL as shown below.

To create a new Excel file we will use the SharePoint Rest API. Add the SharePoint – Send An HTTP Request action to the flow.

Add this API endpoint code to generate the blank Excel file inside the Documents library. Notice that the Documents library path is used inside the brackets for GetFolderByServerRelativeUrl.

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

Insert A Table Into The Excel File

To write data into the Excel file requires a table. The first step is to determine the table column names. We will convert the output of the Select – Export To Excel Data action to a CSV table and extract the column names from the first row.

Add a Data Operations – Create CSV Table action to the flow.

Then insert an Excel – Create Table action. Point the action to the Documents Document Library. Then set the Table Range as A1 and the Table Name as tblProjectOpportunities.

The File property of the action requires the unique identifier of the Excel file. We can get this from the SharePoint – Send an HTTP request to SharePoint action.

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

The column headers can be found in the first row of the Data Operations – Create CSV table action. Use this expression to split the CSV file by each new row and extract the first row. The new line character is represented by %0A in the decodeUriComponent function.

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

Add Rows To A Table In The Excel File

Now we can populate the Excel table with the items from our Project Bid Opportunities SharePoint list. Add a Control – Apply To Each action to the flow and place the Excel – Add A Row Into A Table action inside of it.

Use the output of the Select – Export To Excel Data action in the Apply To Each action.

body('Select:_Export_To_Excel_Data')

Then supply the File field with the unique identifier of the Excel file.

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

The Table custom value should use the name field from the Excel – Create Table action.

And finally define the Row as the Current Item of the Apply to Each loop.

Run The Flow To Create A New Excel File In The SharePoint Document Library

We are now finished building the flow. Run the flow to try it out.

A new Excel file is created in the Documents SharePoint Document Library.

When we open the Excel file we can see the SharePoint list items are found in a table.

Questions?

If you have any questions or feedback about Create An Excel File And Add Rows Using Power Automate please leave a message in the comments section below. You can post using your email address and are not required to create an account to join the discussion.