Create An Excel File And Add Rows Using Power Automate

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 ListCreate An Array Of Values To Export To ExcelGenerate A Blank Excel FileInsert A Table Into The Excel FileAdd Rows To A Table In The Excel FileRun 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:

TitleBid DateBid ResultAmount
Office Tower 123 River Ave.9/3/2023Won13,500,000
Strip Mall 1st St. N.9/10/2023Lost2,300,000
Big Box Store 734 Thames St9/16/2023Won1,700,000
Office Building 789 Reading Ave.9/20/2023Won8,900,000
Condo Tower 1003 Main St.9/24/2023Lost5,600,000
Fast Food Restaurant 423 2nd Ave.9/30/2023Lost2,500,000
Big Box Store 123 Erie St.10/2/2023Submitted1,300,000
Duplex Condo 532 Fay St.10/7/2023Submitted630,000



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.

Matthew Devaney

Subscribe
Notify of
guest

23 Comments
Oldest
Newest
Inline Feedbacks
View all comments
Adi
Adi
7 months ago

Thanks for this, Matt! Can I just point out the issue with the file name, mm is for minutes and MM for months (2023-21-20 is not a valid date). Not too important here as it’s just a name, but obviously would be if it needs date manipulation 🙂

Matt Wright
Matt Wright
7 months ago

You have MM and mm reversed in the formatDateTime call.

Carlos Andrews
Carlos Andrews
7 months ago

I think you got your MM for Month and mm for minutes swapped.

Uppercase “M” = Month.
Lowercase “m” = Minute.

Reference:
How to customize or format Date and Time values in a flow – Power Automate | Microsoft Learn

JRay
JRay
6 months ago

Hey Matthew, do you have instructions on how to do this when using a Dataverse backend?

Ashwin
Ashwin
5 months ago

Hi Matthew,

Thanks for providing this logic. Could you please also share the logic to generate the new excel for the input data with complex data type like Object.

I have below schema of input

{
“type”: “object”,
“properties”: {
“USER_ID”: {
“type”: “string”
},
“CORP_ID”: {
“type”: “string”
},
“AGGR_ID”: {
“type”: “string”
},
“URN”: {
“type”: “string”
},
“ACCOUNTNO”: {
“type”: “string”
},
“Record”: {
“type”: “array”,
“items”: {
“type”: “object”,
“properties”: {
“CHEQUENO”: {
“type”: “string”
},
“TXNDATE”: {
“type”: “string”
},
“REMARKS”: {
“type”: “string”
},
“AMOUNT”: {
“type”: “string”
},
“BALANCE”: {
“type”: “string”
},
“VALUEDATE”: {
“type”: “string”
},
“TYPE”: {
“type”: “string”
},
“TRANSACTIONID”: {
“type”: “string”
}
},
“required”: [
“CHEQUENO”,
“TXNDATE”,
“REMARKS”,
“AMOUNT”,
“BALANCE”,
“VALUEDATE”,
“TYPE”,
“TRANSACTIONID”
]
}
}
}
}

Webweaver
Webweaver
5 months ago

Hi,

Thanks for sharing this guide. I’ve followed it all the way to the end but I’m getting an error message on the Create Excel Table action after it takes 10 minutes to process. The message says something about a ‘Bad Gateway’. Am I forgetting something?

rocky
rocky
2 months ago

Me too facing the same issue

Michal
Michal
4 months ago

Thank a lot you are awesome…

Freya Lynch
Freya Lynch
4 months ago

Thanks, this is a great post. When I try the “Create table” step under the File is that an expression you add or dynamic content? See below, as it appears in your screenshot green (There is nothing I can pick under dynamic content for Send an HTTP request..)

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

Phil Nichols
Phil Nichols
4 months ago

When I create an excel file using the method described it can be opened in the web but when trying to open it in the desktop app I get an error.

I tried creating a word document using the same API call and it opened fine in the app and in the web editor.

Does something more need to be done to make this excel file ‘valid’ to the desktop app?

Nigel
Nigel
3 months ago

Hi Matthew I dont have access to SharePoint. Is there a version of this blog for Onedrive ? I think I just need to change creation of the Excel spreadsheet. Regards Nigel

Rachel
Rachel
3 months ago

For the create table step, are you entering the unique identifier code into the expression area? Based on the Sharepoint logo I’m assuming not but I can’t seem to figure out what you did.

Isaac
Isaac
2 months ago

I don’t know where to put this code so I can get d.UniqueId dynamic content to put it in the Create table action, please help me

Isaac
Isaac
2 months ago

The only output I get from “Send an HTTP request to SharePoint: Create XLSX File” is the dynamic content “body”

Ambika
Ambika
2 months ago

Hi Mathew, this was a great and really helpful blog. Do you know what the reason could be for the Send HTTP Request step taking so long to run? and then the flow run times out.

Ivan
2 months ago

I’m getting an error when I add rows to the Excel table: A value must be provided for item.
clientRequestId: 7182901c-7571-49a7-a220-8ee0ad0652e1. I’ve been searching for a solution, but no luck. At the moment, I have an Excel file in my document library that contains a header row, but no content.