Automatically Add Excel Data to a SharePoint List with Power Automate

hey everyone today our goal is to be able to take data living inside of an Excel workbook and automatically update that data into a SharePoint list not only do we want to take all the values that we currently have add them to that SharePoint list but anytime we have a value that is updated that currently exists on that SharePoint list let's go ahead and update the list as well so every day we can always make sure that anything we change in though Excel workbook is automatically added to that SharePoint list thank you so here we are with our Excel table you can see here I have just a couple of different values four columns here and it is formatted as a table in order to let this work within power automate in order to let this work within power automate we need to make sure that this data is formatted as a table so you can tell here if I click on the table itself we go to our table design you can see I have a table and the table name is table one right there now that's what we can use inside of our flow with power automate to make sure we are being able to pull these values and add it into our SharePoint list so first things first let's make sure that we do have this data inside of a storage location in the cloud in this case I am choosing OneDrive you can see there it's called office supply so I'm going to go ahead and use that location I'm also going to make sure I have my Excel file closed in this instance to make sure I can actually pull the full data and what we also have is a SharePoint list here with the exact same columns that we saw before so I have title category quantity and Status all four of these are going to match what we have in Excel making it really easy for us to automatically take that Excel data and place it here inside of our SharePoint list so let's go ahead and build our flow and get the process started to do that we're going to come here to power automate and you can see here I already started the piece here to build a scheduled Cloud flow what I like for this case is for this flow to run every single morning so I know every day that my SharePoint list is fully updated with the newest data coming from that exact Excel workbook so I'm going to go ahead and select create and now what we're going to do is set up how we can read the data from that Excel workbook and add it into our SharePoint list so our first step that we are going to add here is we can come into our Excel online for business options and what we want to do is list rows present in table this allows us to look at the entire table to see every single row there so we can write every single row into the SharePoint list and now I just need to point to the location of that Excel workbook this this time it is going to be in my OneDrive then I can point to the document Library again it is going to be one drive the exact file there it is and the table that I want to point to it is possible we have multiple tables so in this case we are just going to point to the one that we have but if we had multiple Pages or worksheets in that workbook we can see which exact table we want to use so there's our first option list rows present in our Excel table our next step that we want to add is to go to SharePoint and make sure that we have all the available rows there just in case the idea here is we don't want to just add data into SharePoint all the time we want to ensure that if that data is already there we update it if need be if it has a change go ahead and update to the newest version of that if not just go ahead and leave it there as the original so we we want to do is go to SharePoint in under our SharePoint actions we want to use the get items connection and this get items action allows us to get every single item on that list essentially get all the rows on the table then I can point to my site address and my list and now what we want to do is be able to filter down our SharePoint list to ensure that we're only matching the right rows if we take a look at our Excel file once again we can see that we have this title column right it's the primary column essentially it's the first one we see in a lot of tables usually called titles especially in SharePoint and what we're going to do is match up all the values that have the same title to be able to pull them across and they would go on a row by row basis matching the title and then looking across the row to see all the data that we want to write in so here for our get items we select show Advanced options we want to use a filter query an odata filter query and in this case we're going to say where the title column from SharePoint is equal to the title column from Excel in order to use that we're going to use our single quotes wrapped around our Dynamic content that we are pulling from Excel so we're going to use that in here in order to make sure that those two values are matched up on a row by row basis once that is the case we can ensure that the data is actually going to be presented properly for us so we can add in our title and once we do that you will notice we are going to have this apply to each action add it in what this Loop does this for each Loop does for us here is it ensures that every single row coming from our Excel table there you go there's our value list of items from our Excel table every single row that we are having on that table is going to be iterated over in order to match up to all the rows possible from our SharePoint list and now once we have that get items what we can do is go ahead and check our list build in a condition by adding an action using the condition control we're going to build in a condition to check over our SharePoint list to see is there something there for that specific row if that row is empty based on that title then go ahead and create something if it's not empty then there's something actually there well let's go update that row in order to do that we're going to come into our condition here we're going to add an expression this expression is going to test the length of that specific collection that row that we are passing in here so we're going to see the length of our value list of items we're going to pass that in from our SharePoint list so there we have it the length of that share that specific Row in the SharePoint list hit okay we're going to say if that is greater than zero if it's greater than zero which means there's something on that row what we want to do is update that row if there's if it is less than zero or it's equal to zero we want to then create a new item for that specific title so our first action here for if yes we're going to go back into our SharePoint actions and we're gonna go get the update item action it might be faster if I search for it and it probably will but it's I'm already down here there it is update item and now once again we need to point back to the same address same list that we had before and now what we need to do is pass in the unique identifier of the item what we want to do is utilize the ID from our original get items so we're going to use the dynamic content if you click in here for the ID there it is the list item id this is the unique identifier that we can pass through and now what we can do is go ahead and add in our next steps here okay the title has to match so we're going to go ahead and use the title of the item that we want to update okay we can go ahead and use the title from Excel in this case so we're going to match that across we're going to update the category okay now we can add in our quantity here from Excel it's going to be all the way down at the bottom there it is and finally we have our status value now when we look at our choice options here because this is a choice column sorted in SharePoint if we want to pass in that Dynamic content we're going to come and select this option to show options here at the the far right and choose to enter custom value when you choose enter custom value it does then allow you to pass through Dynamic content into this so then it can pull from the Excel table and actually pass in the exact value that you want so then we can come here and choose status so now we have our ability to update an item if it already exists so any of the new changes will automatically be written in here then what we'll also do on the if no path of this condition if there is nothing already there if the length of that specific row any of the values within it is not greater than zero meaning there's nothing there let's go ahead and then create an item rather than updating an item so we can go and just simply choose create item for SharePoint and we can pass through this same data just as we did with our update item so our site address our list name and now we'll put in our title this will all be coming from Excel we can put in our category again from Excel so it's all going to be all the way at the bottom quantity again from Excel and finally the status just as we have with the update item we're going to make sure we select to enter custom value within this field and take a look and add in our status now as this flow works it's going to check over every single Row from Excel and from SharePoint match up those rows on the title if the title matches from one row to the next then we're going to go ahead and either update that item or if it it's not there because there's nothing there to match then it's going to go ahead and create an item so we're going to go ahead and save and we'll run this right now we'll do a test now one of the things to keep note about as we work within power automate and Excel it is going to be better if we had some type of row ID some type of unique identifier for each row coming from Excel in order for this to match up even cleaner in this case I just chosen some generic titles for things in the office but it would be much better if for Row one it was listed as one and then it'd be a chair or row two for a monitor whatever it might be so we're using that unique identifier instead of uh having the this kind of the general title there in in case so keep that in mind as you're working through here that'll just make this work even better in the future so we're matching up row by row just in case that title does exist more than once within our SharePoint list so let's go ahead and do our test here I want to make sure I just close that Excel file and do our save and test and let's take a look and now we're having the iteration over each row here you can see that's what they apply to each is providing us we've we've seen that it's gone through five different times so we should have five different values added to our SharePoint list let's go to SharePoint and we have one two three four and five there they are all five added in including our choice column now if we were to go to the Excel file this office applies here we should see that we do have one two three four five that are added in there and now let's try something just to make sure this is working the exact way we want let's come in here and update some of these so let's say for desk I'm just going to add in uh actually Furniture let's just do this updated add some Fields here let's say we also we've received our chair so we're going to change that status to delivered so we're now going to update two of our different fields the desk row and the chair row here within Excel and what we'll have is when this flow is intended to work when we have it running every single day tomorrow when this runs again it will look through and add in the new data now instead of waiting for tomorrow what we can do here is we can do another test run on our flow so let me go ahead and close that Excel file do another test another manual test go ahead and run the flow from here hit done rows we have run successfully it's only five rows so it should go pretty quick we can see it is going to look through all five once again if I go back to our SharePoint site there it is desk was updated with the update I could just add that in there and share the status was updated to delivered so we're now able to not only add in new values from that Excel workbook but also any value any row that has been modified since the last run so now we can utilize a shared Excel file like a lot of us do within our organizations that can be shared somewhere through our OneDrive go ahead and let our users write directly into it add stuff remove things as we work as a team and then we can know we can have all that data stored once again inside of SharePoint that we use for a collection of other data for other purposes that we may want to have as a backup source for everything that we are doing thanks for joining me here once again taking a look at how we can utilize power automate to make our lives better working with Excel and SharePoint this time you notice it's really nice and simple it's a great way for us to make sure every piece of data that we are working with on a daily basis is in the exact location and we get to automate all those processes at the same time stay tuned for future videos until then see you next time foreign foreign

Channel: Pragmatic Works

Views: 29,516

Keywords:

Id: -ZaktFuS1KA

Length: 16min 6sec (966 seconds)

Published: Thu Apr 13 2023