How to Create an Excel File from SharePoint list Items using Power Automate?

Last week, I worked on a Power Automate scenario where a client needed to extract data from a SharePoint list and organize it into an Excel file for reporting purposes. The goal was to create a fully automated process that retrieves all items from the list, generates an Excel workbook, and populates it with the list data in a structured table format.

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

Create an Excel file from SharePoint list items using Power Automate

For this example, I created a SharePoint list called Product List with the following columns and data types:

Before I explain how to create the flow, we must create an Excel sheet in a SharePoint Document Library:

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

Select the created Excel workbook and rename it to Excel Template, as shown below screenshot:

Now follow the below steps:

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

2. Add the Get items action to retrieve all items from the SharePoint list. Provide the SharePoint site address and the list name.

3. Add a 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']}
}

4. From the previous step, we will get the output as an array of values. To convert it into comma-separated values, add a Create CSV table action and pass the dynamic content value from the output of the Select action.

body('Select')

5. Now, add a Compose action to get all the column headers using the following expression:

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

6. We will get the file content from the Excel template workbook (created in Step 1). Select the SharePoint Get file content action, configure the SharePoint site address, and in the File Identifier, select the location where the created Excel template workbook is stored.

7. Now, we will create an Excel file. Add the Create file action. Configure the SharePoint site address and folder path. In the File Name, add the following expression with the .xlsx extension, and in the File Content, add the body from the Get file content action.

Guid()

8. Add Create table action from Excel Online and provide the below parameters:

• Location: Select the SharePoint site address from the dropdown menu.
• Document Library: Select the document library where the Excel file is stored.
• File: Provide the dynamic content from the Create file action, using body/Id.
• Table Range: Specify the range based on the number of columns you have. For example, if you have seven columns, use $A1:$I1. Adjust this range according to your columns.
• Table Name: Enter a name for the table.
• Column Names: Provide output of dynamic content value for Column Header compose action.

9. Next, add an Apply to each action and pass the output of a select action dynamic content value.

10. Next, click Add an action. 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 the created file from the dynamic content.
• Table: Provide the name from the dynamic content.
• Row: Provide the current item from the dynamic content.

Now, click Save and run the flow manually. Once the flow runs successfully, you can see that the SharePoint Document Library has created the file.

Once we open the Excel file, we can see that all the list items from the SharePoint list have been exported to the Excel file.

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 of 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

Bijay Kumar

After working for more than 18 years in Microsoft technologies like SharePoint, Microsoft 365, and Power Platform (Power Apps, Power Automate, and Power BI), I thought will share my SharePoint expertise knowledge with the world. Our audiences are from the United States, Canada, the United Kingdom, Australia, New Zealand, etc. For my expertise knowledge and SharePoint tutorials, Microsoft has been awarded a Microsoft SharePoint MVP (12 times). I have also worked in companies like HP, TCS, KPIT, etc.