Syncing is one of the most complex challenges in all platforms. We all suffered 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.
Let’s try to build the template, but as always, I’ll leave the finished template so that you can download it and try it for yourself. You can also check other templates in my cookbook section.
You can also find an example of the Excel file here.
As always, let’s start with the strategy and the assumptions for this exercise.
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 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.
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.
Let’s look at some sections and things you should know.
Get all items to sync
One would be tempted to do the following:
- Fetch all Excel elements
- Do a SharePoint “Get Items” or Get Item action and search for the corresponding ID for each.
- 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 Each action with a Filter Array action.
OK, there are many arrows here, but I wanted you to understand where things come from. By looking at the structure, we can understand what’s happening. If you want to understand in more detail, I wrote another article focus on how to “How to parse an array more efficiently“.
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:
Nothing too complex.
Do we have an item in the SharePoint list?
Now that we filter the array, we need to check if we have an item or not. We need to use the Length function that will return the number of items found. Since we are comparing by ID, we will either find one or zero. With this, we can decide to create (if we get zero) or update the item (if we find one).
Now that we know what to do, we need let’s understand Excel dates. Excel stores the dates internally as numbers, and they are returned from the action as numbers. I detail how to convert excel numbers to date and date to the number on other articles and explain why 1900 is not a leap year. Here’s the short explanation. Microsoft has a “feature” in Excel that considers 1900 as a leap year. It’s not a leap year, and this is a well-known bug that Microsoft won’t be able to fix. But since we need to parse the numbers to dates, we need to consider this. Long story short, the number represents the number of days since the 1st of January 1900, so the formula is the following:
We convert the date of the item that we’re parsing now and convert it to an integer since Excel returns it as a string. After that, we use the “sub” function to take two from the number (one for the “bug” and another because the start date is one, not 0). After that, use the addDays function to generate the final date.
We calculate this before the “Condition” action because it will be used in both cases.
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:
As I explained above, 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.
With this, we “link” the items together.
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.
There is a lot to improve here, and I’m always looking for ways to improve. Since we’re doing a sync Excel to a SharePoint list, things can get tricky, but I think this is a nice compromise between being straightforward and efficient. I’m sure I’ll improve this template over time, but please use it and submit changes if you think other things should be done.