by 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:
- 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:
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.
Photo by Nick Fewings on Unsplash
Hello Manual,
Thanks for the sync Excel<>SPO.
Q: how much time does this solution take for 8K rows?
Gr. Lex
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
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
Hola Manuel Intente descargar la plantilla pero no me lo permite no se si puedas compartírmela a mi correo
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
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
Sorry, the URL was broken.
I’ve updated the URl but here’s the direct download link.
Thanks for pointing this out :).
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.
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.’.
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
While the change or modify in excel data only push data Excel to SharePoint cant SharePoint to Excel
While the change or modify in excel data only push data Excel to SharePoint cant SharePoint to Excel
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.
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.
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.
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!
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?
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?
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
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
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.” }’.
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?
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!
Unable to process template language expressions in action ‘Convert_Excel_Number_to_update’ inputs at line ‘0’ and column ‘0’: ‘The template language function ‘int’ was invoked with a parameter that is not valid. The value cannot be converted to the target type.’.
Can this ignore certain columns from being updated? The source excel doc has about 5 columns that we don’t want updated in the SharePoint list allowing the user to update the list.
For example, demand_id, project_name, project_manager are columns that I want sync’d to SPO, and the priority and project_status are empty in excel because we want users to update that in the SPO list directly. Afraid that the sync will take the blanks in excel and overwrite the priority and project_status data.
Yeah, it’s possible. If you don’t want to sync the values for a column, don’t add it to the “Update Item” in SharePoint. Only map the fields that you want to be updated. SharePoint won’t touch the values in the list when the fields are not filled in.
Can you please try and let me know?
Ok. I will try it. I was asking beforehand in case I did not understand the purpose or implementation you had here. Time to tinker!
Yeah, go for it, and please let me know if it works.
Is it possible to make this formula work for entries where the date field is blank?:
addDays(outputs(‘Reference_Date’),sub(int(item()?[‘Date’]),2))
How could it be adapted to this case scenario? Thanks in advance 🙂
I have similar issues to some others. Template fails on import
‘Import failed for one more package resources.
Next steps …
One or more resources didn’t import. See the error details to resolve the issues, and try importing again.
One or more resources might have been skipped during the import. See the import details to find out which resources were created, updated or skipped.’
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: 0871e33a-af80-49a9-b493-c6d54f07b5e3\r\nserviceRequestId: 2c14ac7f-e2ca-4edb-b6f7-1566753947da”, “error”: { “message”: “Item not found” }, “source”: “excelonline-ae.azconn-ae-001.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.” }’.
Any suggestions? The connectors seem to be ok.
same issue any suggestions?
If anyone wants to sync a lot of data to a SharePoint List, you can also try this template:
https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Batch-Update-Create-and-Upsert-SharePoint-Lists/td-p/1365410
If anyone wants to sync a lot of data to an Excel table, you can also try this template:
https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Excel-Batch-Create-Update-and-Upsert/td-p/1624706
Hello,
Unfortunately I am not able to import your flow. Can you clarify what we are putting in the length function? I can’t figure it out for the life of me. For each item in the excel file, if it doesn’t exist in the filtered array, what am I checking the length of to determine whether I create it or update it?
Say I have 60 items in my Excel file, 50 in Sharepoint, what am I checking the length of to determine that the 10 items need to be added to Sharepoint?
Delete a SharePoint list item when the Excel row is deleted using Power Automate, can you help with the flow
hello,
I read above that is also possible to sync in both ways, but I can’t find the second part of the explanation.
what I need is to have 5 excel outside and one microsoft list to collect all the data and this list should be updated from the excel.
but in case I update some column (not adding a new item just updating data of the existing items) is it possible to update the excel?
thanks in advance