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:

How to create excel file from sharepoint online list using microsoft flow

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
create an Excel file from SharePoint list using Power Automate

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.

power automate create excel file from sharepoint list

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

create excel file from sharepoint list using power automate

Step:2

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

create excel file from sharepoint list using power automate flow

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.
How to create excel file from sharepoint list using power automate flow

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']}
}
How to create excel file from sharepoint list using power automate

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')
create excel file from sharepoint list using the power automate

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'))),',')
Example to create excel file from sharepoint list using the power automate

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')))
Example to create excel file from sharepoint list using the power automate flow

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.
create excel file from sharepoint list using the power automate flow

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()
How to create excel file from sharepoint list using power automate example

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.
create excel file from sharepoint list using power automate flow

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.

create excel file from sharepoint list using microsoft flow

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.
create excel file from sharepoint online list using microsoft flow

Step 12:

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

create excel file from sharepoint list using flow

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

How to create excel file from sharepoint online list using the microsoft flow

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

create excel file from sharepoint online list using the microsoft flow

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:

  • >