Power Automate: Sync Excel to a SharePoint list

Introduction

Syncing is one of the most complex challenges in all platforms. We all suffer from issues where our files don’t sync appropriately on OneDrive, or our phones are out of sync with our email servers, but that doesn’t mean it’s impossible. Today, I want to tackle an interesting question from the Power Automate Community: they want to sync Excel to a SharePoint list.

Syncing strategy:

First, we’ll always sync Excel to a SharePoint List, but not the other way around. In the next version of the template, we can think about syncing data both ways but, for now, let’s keep things simple. We will trigger the Flow, check Excel’s data, match it with the items in the SharePoint list, and update (or create) the values.

We’ll override changes in SharePoint:

We need to define who is the “master” of the information. We could check by date and other elements, but this would make things extremely complex, so let’s start with the simple first. Excel will always contain the “source of truth,” meaning we’ll eventually override the data regardless of what’s done in the SharePoint List. So all changes need to be in Excel first, and only then will they be migrated to SharePoint.

Also, we won’t check if the values are different before updating them, so we’ll update all values even if they don’t change.

We will create a list of items if they don’t exist:
If the value doesn’t exist in SharePoint, we’ll create them. This will help us keep things similar on both sides.

The comparison key:
We need a comparison key that is unique and identifies things on both sides. For the sake of our exercise, we’ll use the ID, but you can change it to be any field. The fields don’t even need to be named the same way, so feel free to change that.

The trigger:

The trigger is currently “Manually Trigger a Flow,” but you can easily change it for any other that makes sense for you. For example, you can have a “Recurrence” Trigger that periodically fetches the information and updates the SharePoint list items.

When create we need to update the Excel ID:

Since SharePoint creates the IDs automatically, we can’t force them when inserting the data. It’s a problem because we use the ID as the comparison key, so when we create a new item, we need to update the Excel ID. Otherwise, we’ll keep creating new items for the same row in Excel, and we don’t want that.

You don’t need to fetch all items:

If you have items that you don’t want to sync, you can filter them using Odata queries. Please be sure that you have equivalent queries on both Excel and SharePoint. If you only set up e, the values will be synced to the other, and we don’t want that.

It’s pretty helpful to limit the values that you want to sync. The fewer values, the faster your Flow runs, so think of this if you can.

The template:

Now that we have all the strategies in place, let’s check the template. We’ll start by defining the Excel and SharePoint lists. Both have the same structure, but this is not mandatory. You can have different names for the fields as long as you map them in the template. Also, you can have more columns on both sides; make sure that they are optional; otherwise, the sync will fail with not enough data. Here’s the full view of the template.

Get all items to sync:

One would be tempted to do the following:

1. Fetch all Excel elements
2. Do a SharePoint “Get Items” or Get Item action and search for the corresponding ID for each.
3. Insert or update depending on if the item exists or not.

The strategy works, but it will generate a lot of calls to SharePoint. If there are 100 rows in Excel, we’ll call SharePoint at least 100 times. But how about calling it only once and filter the data that we have? Much more efficient. That’s what we’re doing here. At the start of the Flow, we’ll get all elements from SharePoint and then filter them when we need them in the Flow. To do that, we have the Apply to ForEach Action with a Filter Array action.

For Each element in the Excel (apply to each), filter the array (items from the SharePoint list) comparing the ID of the SharePoint item with the ID from the Excel.

There’s a small detail that we have to take into consideration. The ID comes from Excel as a string, so we need to use the “int” function to convert it to an integer.
Here’s the formula:  int(items('For_each_item_in_Excel')?['ID'])

Update the data:

If we can find an item in the SharePoint list, then we need to update it. To do it, it’s simple. We only need a SharePoint “Create item” action to add the values from Excel:

we need to update Excel’s ID since we can’t control the ID that SharePoint creates in the previous step. We can, however, get that ID and update Excel with the “Update a row” action.

Update the data:

Updating the data is much more straightforward. We only need a SharePoint “Update item” action with the data from Excel and the converted date.

Conclusion

Leveraging Power Automate to sync Excel data with SharePoint lists streamlines data management processes, enhancing efficiency and productivity. By automating this synchronization, organizations can ensure data consistency across platforms, eliminate manual errors, and facilitate seamless collaboration.

With the step-by-step guide provided, integrating Excel and SharePoint becomes a straightforward task, empowering users to automate repetitive tasks and focus on more strategic initiatives. Embrace the power of automation to optimize your workflow and maximize the value of your data within the SharePoint environment, driving productivity and success in your organization.

Hire our skilled Power Automate developers to automate workflows, boost efficiency, and unlock the full potential of your organization. Let's transform your operations together!