While working with Power Automate, I needed to create an Excel file from a SharePoint list item. The goal was to ensure that every new item added to the SharePoint list would create a corresponding Excel file containing all the details in our document library.
In this article, I will describe how to create an Excel file when an item is created in SharePoint list using Power Automate. Creating these Excel files can be a bit tricky, so I will provide a step-by-step process for achieving it.
Create an Excel File When an Item is Created in SharePoint List Using Power Automate
Before creating the flow, we need to create a SharePoint list. Here, I created a SharePoint list with the following columns and data types:
| Columns | Data Types |
|---|---|
| First Name(Title) | Single line of text |
| Last Name | Single line of text |
| Single line of text | |
| Phone Number | Single line of text |
| Company | Single line of text |
| Certification | Choice |
| Study Material Request | Yes/No |
| Comments | Multiple lines of text |
Also, create a blank Excel file in your SharePoint document library, as shown below.
Now follow the below steps:
1. Open the Power Automate home page, click + Create, select the Automated cloud flow, enter the flow name, and choose the trigger’s flow (When an item is created).
2. Now, click on the ‘When an item is created‘ option on the left side. Under Parameters, choose your Site Address and List Name.
3. Next, click the + icon to add an action. choose a Get file content action for SharePoint Connector and configure the required parameters:
- Site Address: Select the SharePoint site address from the dropdown where we create the blank Excel file.
- File Identifier: Choose the Excel file we created before.
4. Then save the flow and test it. After the flow runs, copy the body of the “Get file content” action.
5. Then edit the flow and add a Compose action. Inside the Compose action’s input field, paste the body of the “Get file content” action.
After that, delete the Get file content action and that blank Excel file in your document library.
6. Now, add an action to store Excel files in a SharePoint library. Click the + icon to add the Create file action (under SharePoint connector). Provide the required parameters:
- Site Address: Select the SharePoint site address from the dropdown menu where you store all your Excel files.
- Folder Path: Select the folder path.
- File Name: Provide the Excel file name with the .xlsx extension. Here, I put the SharePoint ID column from dynamic content.
- File Content: Provide the dynamic content from the Compose action, ‘Output.’
7. Now, let’s create an Excel table. Click the + icon to add the Create table action (under the Excel Online (Business) connector). Provide the required parameters:
- Location: Select the SharePoint site address from the dropdown menu
- Document Library: Select the SharePoint site address from the dropdown menu
- File: Provide the dynamic content from the Create file, body/Id.
- Table Range: I have eight columns, so I specify the range as A1:H1. Adjust this according to your columns.
- Table Name: Give the table name.
- Columns Names: Put all your column names.
8. Now, let’s add a row to the table. Click the + icon to add the “Add a row into a table” action (under the Excel Online (Business) connector) and provide the required parameters:
- Location: Choose SharePoint Site.
- Document Library: Choose the Documents folder.
- File: Provide the dynamic content from the Create file, body/Id
- Table: Provide the table name from the dynamic content.
- Row: Put below the table value
| Column Name | Dynamic Content |
|---|---|
| First Name | triggerBody()?[‘Title’] |
| Last Name | triggerBody()?[‘Last Name’] |
| triggerBody()?[‘Email’] | |
| Phone Number | triggerBody()?[‘Phone Number’] |
| Company | triggerBody()?[‘Company’] |
| Certification | triggerBody()?[‘Certification’][‘Value’] |
| Study Material Request | triggerBody()?[‘Study Material Request’] |
| Comment | triggerBody()?[‘Comment’] |
Now click on Save and run the Flow manually. Once the flow runs, add an item to the SharePoint list.
After running the flow, you can see one Excel file created in your selected Document library.
When you open that Excel file, you will see the exact data that you added to your SharePoint list.
Conclusion
I hope you follow all the steps to create an Excel file when an item is created in the SharePoint list using Power Automate. Check the link below if you need to create an Excel file from SharePoint list items using Power Automate.
You may also like:
- Convert Word Files to PDF Using Power Automate
- Create an Excel file from SharePoint list Items using Power Automate
- Power Automate Read Excel File From SharePoint
- Create SharePoint site columns from an Excel file in Power Automate
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.
