How to bulk add rows to a SharePoint list using Power Automate and Excel 

Managing large datasets in SharePoint manually can be both time-consuming and error-prone. However, if you frequently work with Excel spreadsheets and need to import and bulk add rows into a SharePoint list, Power Automate provides a streamlined and efficient solution. In this guide, we will walk you through the step-by-step process of automating bulk data entry from Excel to SharePoint using Power Automate.

Why use Power Automate for bulk data import? 

Manually adding rows to a SharePoint list can be highly inefficient, especially when dealing with large volumes of data. However, Power Automate streamlines this process by automating data entry, thereby reducing errors and saving valuable time.

• Automating data entry not only eliminates repetitive manual work but also enhances efficiency and ensures greater accuracy in data management.

• Reducing human errors associated with manual input ensures greater data accuracy and reliability.

• Improving efficiency and saving time allows you to focus on more strategic tasks and higher-value work.

• Ensuring seamless integration with Microsoft 365 applications enhances workflow efficiency and improves data connectivity.

Prerequisites

Before setting up the Power Automate flow, ensure you have the necessary permissions, a properly formatted Excel file, and a corresponding SharePoint list ready: 

• A SharePoint list with columns that precisely match your Excel file ensures smooth data mapping and error-free automation.

• An Excel file stored in OneDrive or a SharePoint document library, properly formatted as a table, ensures seamless data integration and automation.

• You need a Power Automate license to create, run, and manage flows effectively.

Step-by-step guide to bulk add rows to a SharePoint list 

Prepare the Excel file 

1. Open Excel and create a table with the data you want to import, ensuring proper formatting for seamless integration.

2. Ensure that the column names in Excel exactly match those in the SharePoint list to prevent mapping errors and ensure a smooth data transfer.

3. Save the Excel file in OneDrive for Business or SharePoint Document Library to allow Power Automate to access it. 

Create a Power Automate flow 

1. Sign in to Power Automate (https://flow.microsoft.com). 

2. Click on Create → Automated cloud flow to begin setting up your Power Automate workflow for automating data transfer.

3. Choose the trigger “When a file is created or modified” for OneDrive or SharePoint to automatically detect changes and initiate the flow.

4. Click Create to begin configuring the flow and setting up the automation process.

Configure the flow actions 

1. Add the trigger action 

• Select the OneDrive or SharePoint folder where the Excel file is stored. 

• Specify the Table Name that contains the data. 

2. Retrieve data from excel 

• Add the action “List rows present in a table” under OneDrive for Business (or SharePoint if applicable). 

• Select the file and table where the data is stored. 

3. Loop through excel data 

• Click “Add an action” → “Apply to each” to loop through and process each row in the Excel table efficiently.

• Use “value” from the previous step as the output to correctly retrieve and process the data from the Excel table.

4. Create items in the SharePoint list 

• Add the action “Create item” under SharePoint. 

• Select the SharePoint Site and List Name where the data will be added to ensure proper integration and data placement.

• Map each column in Excel to its corresponding field in the SharePoint list to ensure accurate data transfer and proper alignment.

• Click Save, then select Test to confirm that the flow executes properly and processes the data as expected.

Run and monitor the flow 

Once the flow is set up, test it by uploading a sample Excel file. Power Automate will then detect the data and seamlessly add it to the SharePoint list.

To monitor the flow execution: 

• Navigate to Power Automate > My Flows to view, manage and modify your existing workflows.

• Select the flow and navigate to Run History to review past executions and identify any errors for troubleshooting.

• If necessary, troubleshoot and adjust the flow settings to enhance accuracy and ensure optimal performance.

Common issues and solutions 

1. Flow does not trigger automatically 

Solution –Store the Excel file in OneDrive or SharePoint rather than on a local computer to ensure seamless automation, improve accessibility, and maintain data synchronization.

2. Data mapping errors 

Solution –Verify that the column names in Excel exactly match those in the SharePoint list to prevent errors and ensure a smooth data transfer.

3. Handling large datasets 

Solution – Enable the pagination settings in the “List Rows Present in a Table” action to efficiently handle large files and process all data.

Conclusion 

Power Automate provides a seamless and efficient way to bulk add rows to a SharePoint list from Excel, thereby significantly reducing manual effort while also improving data accuracy. By carefully following this step-by-step guide, you can easily automate the process, ultimately saving time and ensuring consistency in data management.

Check out the related blogs for further reading