Power Automate Create item in SharePoint list from Excel

Syncing is one of the most complex challenges in all platforms. We all suffered from issues where our files dont sync appropriately on OneDrive, or our phones are out of sync with our email servers, but that doesnt mean its impossible. Today, I want to tackle an interesting question from the Power Automate community: they want to sync Excel to a SharePoint list.

Lets try to build the template, but as always, Ill 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.

Strategy

As always, lets start with the strategy and the assumptions for this exercise.

Syncing strategy

First, well 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, lets keep things simple. We will trigger the Flow, check Excels data, match it with the items in the SharePoint list, and update [or create] the values.

Well 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 lets start with the simple first. Excel will always contain the source of truth, meaning well eventually override the data regardless of whats 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 wont check if the values are different before updating them, so well update all values even if they dont change.

We will create a list of items if they dont exist.

If the value doesnt exist in SharePoint, well 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, well use the ID, but you can change it to be any field. The fields dont 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 cant force them when inserting the data. Its 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, well keep creating new items for the same row in Excel, and we dont want that.

You dont need to fetch all items.

If you have items that you dont 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 dont want that.

Its 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, lets check the template. Well 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. Heres the full view of the template.

Lets look at some sections and things you should know.

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, well call SharePoint at least 100 times. But how about calling it only once and filter the data that we have? Much more efficient. Thats what were doing here. At the start of the Flow, well 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 whats 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.

Theres 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. Heres 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].

Excel dates

Now that we know what to do, we need lets 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. Heres the short explanation. Microsoft has a feature in Excel that considers 1900 as a leap year. Its not a leap year, and this is a well-known bug that Microsoft wont 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 were 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, its simple. We only need a SharePoint Create item action to add the values from Excel:

As I explained above, we need to update Excels ID since we cant 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.

Final thoughts

There is a lot to improve here, and Im always looking for ways to improve. Since were 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. Im sure Ill improve this template over time, but please use it and submit changes if you think other things should be done.

Photo by Nick Fewings on Unsplash