idesignandwrite.com Graphic Design Website
Update Sharepoint List From Excel – Synchronization is one of the most complex challenges across all platforms. We’ve all experienced problems where our files on OneDrive don’t work properly or our phones don’t work with our email server, but that doesn’t mean it’s impossible. Today, I’d like to address an interesting question from the Power Automation community: Want to sync Excel with a SharePoint list?
Let’s try making the template, but as always, I’ll leave the template finished so you can download it and try it yourself. You can also see other templates in my cooking section.
Update Sharepoint List From Excel
This template provides a complete flow that searches for newly created Excel files in a folder, reads the data, and syncs it to a SharePoint list.
How To Export And Import Excel Into Sharepoint List
First, we always sync Excel with 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 trigger the flow, check the Excel data, match it to the items in the SharePoint list, and update (or create) the values.
We need to define who the “owner” of the data is. We could check by date and other elements, but that would make things too complicated, so let’s start with the simple ones first. Excel always has a “source of truth”, which means that we will write the data no matter what is done in the SharePoint list. So all changes should be in Excel first and then only migrated to SharePoint.
We also don’t check if the values are different before updating, so we update all values even if they haven’t changed.
If the value does not exist in SharePoint, we will create it. This helps us to keep the same things on both sides.
Create A Sharepoint List From Microsoft Excel
We need a comparison key that is unique and separates the objects on both sides. For our exercise, we’ll use ID, but you can change it to any field. The fields don’t even have to be named the same, so feel free to change that.
The trigger is currently “trigger flow manually”, but you can easily change it to anything else that makes sense to you. For example, you might have a “recurring” trigger that periodically fetches data and updates SharePoint list items.
SharePoint creates the IDs automatically, so we can’t force them when we enter the data. It’s a problem because we’re using the ID as a comparison key, so when we create a new object, 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.
If you have objects that you don’t want to sync, you can filter them using Odata queries. Please make sure you have the same queries on both Excel and SharePoint. If you set only E, the values will match the other, and we don’t want that.
How To Create A List In Sharepoint From An Excel Spreadsheet
It is very useful to limit the values you want to synchronize. The lower the values, the faster the flow will work, so consider that if you can.
Now that we have all the styles in place, let’s examine the template. We will start by explaining the details of Excel and SharePoint. 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. You can have multiple columns on either side; Make sure they are optional; Otherwise, synchronization will fail with insufficient data. Here is a full view of the template.
The method works, but generates many calls to SharePoint. If there are 100 rows in Excel, we call SharePoint at least 100 times. But what about calling once and checking the data we have? More efficient. That’s what we do here. At the beginning of the flow, we get all the items from SharePoint and then filter them as we need them in the flow. To do this we have an application with a filter array for each action.
Okay, lots of arrows here, but I wanted you to understand where things are coming from. By looking at the structure, we can understand what is going on. If you want to understand in detail, I wrote another article that focuses on “how to analyze negotiations effectively”.
Flow To Update Spreadsheet In Sharepoint From Exce…
For each item in Excel (applicable to each), filter the array (the items in the SharePoint list) by comparing the ID of the SharePoint item to the ID in Excel.
There is one small detail that we should consider. 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:
Now that we are checking the array, we need to check whether we have an item or not. We need to use a length function that returns the number of items found. Since we are comparing by ID, we get either one or zero. With this, we can decide to create (if we get zero) or update the element (if we get).
Now that we know what to do, we need to understand Excel data. Excel stores data internally as numbers and returns from the function as numbers. In other articles I will detail how to convert Excel numbers to date and date to number and explain why 1900 is not a leap year. Here is the brief explanation. Microsoft has a “function” in Excel that considers 1900 as a leap year. This is not a leap year and this is a known bug that Microsoft cannot fix. But since we have to analyze numbers into data, we have to take this into account. In short, the number represents the number of days since January 1, 1900, so the formula is:
Create Sharepoint List From Excel
Now we’ll change the date of the item we’re looking at and convert it to an integer because Excel returns it as a string. We then use the “sub” function to take two of the numbers (one for “error” and one because the start date is one, not 0). Then use the adddays function to generate the last date.
If we find an item in the SharePoint list, we need to update it. To do this, it is easy. We only need the “Create Item” SharePoint action to add the values from Excel.
As mentioned above, we need to update the Excel ID because we cannot control the ID that SharePoint creates in the previous step. But we can get that ID and update Excel with “Update Row” action.
Updating data is very easy. We only need the “Update Item” SharePoint action with the data from Excel and the changed date.
Getting Column Name/value From Excel File To Updat…
There’s a lot to improve here and I’m always looking for ways to improve. Since we’re doing an Excel sync with a SharePoint list, things can get tricky, but I think this is a good compromise between simplicity and efficiency. I’m sure I’ll improve this template over time, but please feel free to use it and submit changes if you think other things need to be done.
Have your own opinion or disagree with what I said? Leave a comment or connect on Twitter and see other related articles from Power Automate here.
I was a former project manager and now a developer focused on providing quality articles and projects on the site. I have previously worked for companies such as Bayer, Sybase (now SAP) and Pestana Hotel Group and have used this knowledge to help you automate your daily tasks.
Hello, but we have to answer. I’m glad you did. Can you share some links so that everyone can…
Sp Formatter: The Big Update
Hi Edison, of course Flow can’t call itself, but there is a solution. “as if…
Dear Manuel, Thank you for your contribution on various articles, it has helped me a lot in my learning journey…
Hi, thanks for your input, I have a main flow that I call child flow…
Automated Documentation Automation Best Practices Funny Corporate Championship Design Development Feature Fiction Basics Research Day Music New Feature News News Overview Personal Development Privacy Problems Solved Productivity Security Single Source of Truth Technology Templates Small Tips Helpful Writing
Update Sp (sharepoint) List Based On Excel Changes
We use cookies to ensure we give you the best experience on our website. If you continue to use this website, we will assume that you are satisfied with it. You have accepted the privacy policy.
Import excel into sharepoint list, import data into sharepoint list from excel, sharepoint list import from excel, sharepoint dashboard from excel, export sharepoint list to excel, how to import data into sharepoint list from excel, import excel data to sharepoint list, update sharepoint from excel, create sharepoint list from excel, auto update powerpoint from excel, import data from excel to sharepoint list, update database from excel