How To Automatically Add Excel Data to a SharePoint List with Power Automate

Summary

This tutorial demonstrates how to automate the process of updating a SharePoint list with data from an Excel workbook using Power Automate. The presenter guides viewers through setting up a scheduled cloud flow that reads data from an Excel table stored on OneDrive, matches it with existing data in SharePoint, and updates or adds new entries accordingly. The video also highlights the importance of using unique identifiers for each row in Excel to ensure accurate data synchronization.

Takeaways

• 📊 The goal is to automate the process of updating a SharePoint list with data from an Excel workbook.
• 📈 Data from the Excel table needs to be in a cloud storage location, such as OneDrive, for easy access by Power Automate.
• 🔗 The Excel data should be formatted as a table to be recognized and utilized by Power Automate.
• 📅 A scheduled Cloud flow in Power Automate is set to run daily to keep the SharePoint list updated.
• 🔍 The 'List rows present in table' action is used to read data from the Excel table.
• 🔄 A 'Get items' action with an OData filter query is employed to match and update existing items in the SharePoint list.
• 🔄 A 'For each' loop is used to iterate over each row from the Excel table and match it with the SharePoint list.
• 📝 Conditions are set to either update an existing item or create a new one in the SharePoint list based on whether a match is found.
• 🔧 The 'Update item' and 'Create item' actions are used to modify or add data to the SharePoint list.
• ⚙️ It's recommended to have a unique identifier for each row in Excel for more accurate matching with SharePoint list items.

Q & A

What is the main goal of the script?

The main goal is to automatically update data from an Excel workbook to a SharePoint list, ensuring that any changes made in the Excel workbook are reflected in the SharePoint list.

Why is it important to format the data as a table in Excel?

Formatting the data as a table in Excel is important because it allows Power Automate to easily pull the values and add them to the SharePoint list.

What cloud storage location is used to store the Excel file in the script?

The cloud storage location used in the script is OneDrive.

How does the script ensure that updated values in the Excel workbook are also updated in the SharePoint list?

The script ensures updates by using a scheduled flow in Power Automate that runs daily, checking for changes in the Excel workbook and updating the SharePoint list accordingly.

What is the purpose of using a filter query in the script?

The purpose of using a filter query is to filter down the SharePoint list to ensure that only the rows with matching titles from the Excel file are updated or added.

How does the script handle rows in the Excel table that do not exist in the SharePoint list?

The script uses a 'for each' loop to iterate over each row from the Excel table and checks if there is a matching row in the SharePoint list. If there is no match, it creates a new item in the SharePoint list.

What action is taken when a matching row is found in the SharePoint list?

When a matching row is found in the SharePoint list, the script updates that row with the new data from the Excel file.

How does the script handle the choice column in SharePoint?

The script handles the choice column in SharePoint by using the 'enter custom value' option, allowing it to pass dynamic content from the Excel table into the SharePoint list.

What is the recommended best practice mentioned in the script for improving the matching process?

The recommended best practice is to have a unique identifier for each row in the Excel table, such as a row ID, to make the matching process cleaner and more efficient.

How often does the flow run according to the script?

The flow is set to run every single morning, ensuring that the SharePoint list is updated daily with the latest data from the Excel workbook.

What is the benefit of automating the process between Excel and SharePoint as described in the script?

The benefit of automating the process is that it ensures all data is consistently updated and stored in SharePoint, providing a reliable backup source and streamlining data management.