September 25, 2022

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.

Synchronize Excel to a SharePoint list

This template provides a full Flow that looks for new Excel files created in a folder, reads the information, and then synchronizes it to a SharePoint list. 

You can also find an example of the Excel file here.

Strategy

As always, let’s start with the strategy and the assumptions for this exercise.

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.

Let’s 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, 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:

int(items('For_each_item_in_Excel')?['ID'])

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 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:

addDays(outputs('Reference_Date'),sub(int(item()?['Date']),2))

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.

Final thoughts

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.

Have a suggestion of your own or disagree with something I said? Leave a comment or interact on Twitter and be sure to check out other Power Automate-related articles here.

Photo by Nick Fewings on Unsplash

Manuel Gomes

I'm a previous Project Manager, and Developer now focused on delivering quality articles and projects here on the site. I've worked in the past for companies like Bayer, Sybase (now SAP), and Pestana Hotel Group and using that knowledge to help you automate your daily tasks

View all posts by Manuel Gomes →

23 thoughts on “Power Automate: Sync Excel to a SharePoint list

  1. Hello Manual,
    Thanks for the sync Excel<>SPO.
    Q: how much time does this solution take for 8K rows?
    Gr. Lex

    1. Hi Lex,
      Unfortunately, it will take some time. The actual bottleneck here is Excel. SharePoint deals with parallel actions quite well, but Excel was not built for things like this.
      We could enable parallel executions, but then we would run the risk of incorrectly inserting or updating the data since we would not have control of the order.
      Can you please find a timeslot to do the sync and let it run for a while. Then, check how much time it takes, and then we have more information to make decisions.
      If it’s too long, please let me know, and we’ll do some testing and see where we can optimize.
      Sounds good?
      Cheers
      Manuel

    2. Hi Lex. I tried to download the template but it won’t let me, I don’t know if you can share it to my email, thanks

    1. Hi Manuel. I tried to download the template but it won’t let me, I don’t know if you can share it to my email, thanks

  2. Hi! Manuel. I tried to download the template but it won’t let me, I don’t know if you can share it to my email, thanks

  3. Hi Manuel,

    Have you got a link for doing a 2 way sync between excel and SharePoint? Most people will be entering data via SharePoint but sometimes it’s handy to export to excel, update there and push change back to SharePoint.

  4. The execution of template action ‘Tries_to_find_the_ID_in_the_SharePoint_List_items’ failed: The evaluation of ‘query’ action ‘where’ expression ‘@equals(item()?[‘ID’], int(items(‘For_each_item_in_Excel’)?[‘ID’]))’ failed: ‘The template language function ‘int’ was invoked with a parameter that is not valid. The value cannot be converted to the target type.’.

    1. Hi Amol,

      Can you please check if the value in the “ID” column is an integer or if there’s a place where there’s something else or even an empty value?

      Cheers
      Manuel

    1. Indeed you’re right; I struggled a lot when thinking about the title for this, and unfortunately, I think the title can be misleading, although I don’t have a better one yet. You can only sync things from Excel to SharePoint. If you want to do the opposite, you can create another flow but be careful not to trigger the other. For example, you add a new row in excel, the Flow will create a new item in the list, and then you have another Flow that triggers when a new item is added and adds it on Excel. One flow forces the other to trigger. Synchronization is not easy, so it’s always good to avoid and have only one single source of truth. I created this so that SharePoint is the source of truth, and excel is an excellent way of adding data without accessing the SharePoint list directly.

  5. This is a great resource, and the template is working for me somewhat, but I can’t figure out how to troubleshoot two issues:
    1. Even when I have an item in Excel that shares an ID with an item in the SharePoint list, it is adding a new item – when I look at the run results, it appears the SPO items 1-80 are pulled, but the item is 141, so it looks like not enough SPO items are evaluated to find a matching ID
    2. I have several date fields I need to reference in Excel – I tried just one, and I updated the addDays function to change the field name from ‘Date’ to ‘Target Start Date’ (one of my date fields), but when the record is created in SPO, it just populates 1-1-1900, so it is not actually adding my date value

    Do you have any suggestions to resolve these issues?

    Separately, I need to change my identifier field to a string field; if you have any references for that as well, I’d appreciate it.

    1. Hi Julie
      1. The issue is probably because you’re not getting all the items. You can use this strategy to get more than 100 items. I didn’t want to do it by fetching item by item from SharePoint; otherwise, you’ll have hundreds of calls, but the way to do this would be. “Get all Excel” -> For Each Excel item -> Find in SharePoint the record using an OData Expression (you can use the equals) -> check if we got something -> If not create a new one otherwise update. I know that this looks a bit scary, but if you need help, let me know, and I’ll help you build it.
      2. Dates are a pain :(. The dates come from Excel in a numeric value, and you need to convert them before sending them to SharePoint. I have an expression on my cookbook that you can use that converts the Excel date to a “proper” date.

      1. Thanks for this – I’ll take a look today! I did figure out the date issue was due to selecting the wrong Output to populate the field…I was selecting the reference date instead of the new calculated date – the date is working great now!

      2. I’m struggling a bit with the ODATA filter I need in the Get all items to sync step – I have a field in my excel with a string that I’d like to match with a related field in my SharePoint list – the general idea of what I want to filter is “SharePoint List string field” is in “matching Excel string column” and get a set of several issues where the two values match, but as I reviewed the ODATA references you have, it seems they need a specific value to match, not an array – is this possible?

  6. I’ve tried to build my own version of this template as the work internet won’t let me download, I’m getting this ‘ActionFailed. An action failed. No dependent actions succeeded.’ message – it’s coming at the end of the workflow.

    Any idea why?

  7. Following up on my previous comment, if I manually make the excel ID the same as the sharepoint auto-generated one, the flow completes successfully

  8. Hi Manuel

    This is great and fits exactly what i am trying to do, but I have an issue with importing this template to power automate and it fails everytime

  9. Hi,
    Thanks for this great article. I tried to import the package but encountered the error below that the table cannot be found. Would appreciate guidance on how to resolve it. Thanks!!

    jasbee

    Error message:
    Flow save failed with code ‘MultipleErrorsOccurred’ and message ‘The dynamic operation request to API ‘excelonlinebusiness’ operation ‘GetTable’ failed with status code ‘NotFound’. This may indicate invalid input parameters. Error response: { “status”: 404, “message”: “Item not found\r\nclientRequestId: 6a9b1fbe-e9e1-41e7-a816-da0552a23a23\r\nserviceRequestId: bc147b2b-4790-40dc-b046-a5a997c3aef5”, “error”: { “message”: “Item not found” }, “source”: “excelonline-ea.azconn-ea.p.azurewebsites.net” };The dynamic operation request to API ‘sharepointonline’ operation ‘GetTable’ failed with status code ‘Unauthorized’. This may indicate invalid input parameters. Error response: { “error_description”: “Exception of type ‘Microsoft.IdentityModel.Tokens.AudienceUriValidationFailedException’ was thrown.” }’.

    1. Hi!
      The issue is that Excel needs to contain a table that can be referenced in Power Automate. I have an explanation for this:
      https://manueltgomes.com/reference/power-automate-action-reference/excel-list-rows-present-in-a-table-action/

      The error above tells you that the value you’re providing doesn’t match a table in Excel. You need to either:
      1. Create a table and reference it by name
      2. Change the name of the table in Power Automate.

      Does it make sense?

      1. I believe the import function is failing because it doesn’t have access to the excel file referenced in the Flow. We can’t change the values in Power Automate as the template won’t import.

        The Flow definition is looking for this table:
        “/Articles/Power Automate/Sync Excel and SharePoint list/ExcelToSync.xlsx”,
        “tableId”:”{F12CBE5F-AC0A-6644-9E51-6E7864935B43}”

        I came across a problem after trying to create this flow from scratch and I’m curious what I did different than you. In the SharePoint “Update item” step, I don’t have Excel ‘ID’ as a Dynamic Content choice for the mandatory “Id” field. I also don’t have “Title” as a mandatory field. I got around the problem by instead putting the “int(items(‘For_each_item_in_Excel’)?[‘ID’])” function in the Id field.

        Thanks for posting this article!

Leave a Reply

Your email address will not be published.