How to Create an Excel file from SharePoint list items using Power Automate?

This Power Automate tutorial will show us how to create an Excel file from SharePoint list items using Power Automate.

Scenario:

The flow uses a Manual trigger, which use to get items to retrieve all the list items from the SharePoint list. Later, we will create a blank Excel workbook to get the file content value based on that file content value; we will create an Excel File and Excel table and Add rows to the Excel table by using the flow.

Once we created the flow, it looks like the below:

To achieve this, follow the below steps:

Create an Excel file from SharePoint list items using Power Automate

Let us see how to create an Excel file from SharePoint list items using Flow.

In this example, we will use the below SharePoint list consisting of columns:

• Product Code no
• Product Name
• Customer Name
• Customer Location
• Unit Price
• Quantity
• OrderDate
• Order priority
• Total Price

Step:1

We will Create a blank Excel workbook in SharePoint Document Library by selecting +New -> Excel workbook, to get the file content value we will use later in our flow.

Select the created Excel workbook and rename it as Excel Template as displayed below:

Step:2

Log in to Power Automate, create an Instant Cloud Flow, then select Manually triggered Flow. Then click on Create.

Step:3

• So click on the Next step and select Get items action to retrieve all the items from the SharePoint list.
• Then Configure the SharePoint site address and list name.

Step:4

Add +new step and choose Select action to map the key-value pair like below. In the Form section, pass the dynamic content values.

{
  "Product Code No": @{item()?['Title']},
  "Product Name": @{item()?['ProductName']},
  "Customer Name": @{item()?['CustomerName']},
  "Customer Location": @{item()?['CustomerLocation']},
  "Unit Price": @{item()?['UnitPrice']},
  "Quantity": @{item()?['Quantity']},
  "Order Date": @{item()?['OrderDate']},
  "Order Priority": @{item()?['OrderPriority']},
  "Total Price": @{item()?['Total_x0020_Price']}
}

Step:5
From the above step, we will get the output as an array of values. To convert it into Comma separated values, we will add a Create CSV table action and pass the dynamic content value as the output of the select action.

body('Select')

Step:6

Now we will get the first row of data, Then select Compose action and provide the below expression:

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

Step:7

Now add a compose action to get all the column headers by using the below expression.

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

Step:8

• we will get the file content from the Excel template workbook (i.e. created in Step 1).
• select SharePoint Get File content action Configure the SharePoint site address, and in the File Identifier, select the location where the created Excel template workbook exists.

Step:9

• Now we will create an Excel file, so click on the Next step and select Create File action. Configure the SharePoint site address and Folder Path.
• And in the File name, add the below expression with the .xlsx extension, and in the File content, add the body from the get file content action.

Guid()

Step:10

• Now we will create an Excel table dynamically, so click on the Next step and select Create table-Excel Online action.
• Configure the SharePoint address and Document Library.
• Then in File provide the ID to create a file from the dynamic content, in the table range write $A1:$I1 (based on the column header).
• Then provide the table name of the Excel sheet, and in the column name pass the Output of dynamic content value for Column Header compose action.

Step:11

Select apply to each action and pass the output of a select action dynamic content value, so that it loops through all the list items.

Next, click on Add an action and select Add a row into a table -Excel Online action. Then provide the below details:

• Location – Choose SharePoint Site
• Document Library – Choose the Documents folder
• File – Provide the Id of created file from the dynamic content
• Table – Provide the name from the dynamic content.
• Row – Provide the current item from the dynamic content.

Step 12:

Now click on Save and run the Flow manually, Once the flow ran successfully like below:

We can see the file got created in the SharePoint Document Library.

Once we open the Excel file, we can see all the list items presented in the SharePoint list are exported to the Excel file.

This is how to create an Excel file from a SharePoint list using Flow.

Conclusion

We can easily create an Excel file from SharePoint list items using Power Automate. I have shown you, step by step, how to create an Excel file from a SharePoint list items using Power Automate.

You may also like:

• Import Data from Excel to a SharePoint list using Power Automate
• Create SharePoint site columns from an Excel file in Power Automate
• Add Item to SharePoint List from Excel using Power Automate
• Create PDF from Excel using Power Automate