Power Automate - Flow to Add Update Delete items from Excel to SharePoint - Part 03- Full Build

huge thanks for making it to video 3 of upload Excel to SharePoint with a power automate flow this will give you the final pieces for making the flow work in your learning environment I've had some great questions since making the last videos around property names and how to upload not just text values but also numbers and date times as such I've worked these examples into this video it makes it a little longer but will also get you positioned as a flow superhero when you learn the concepts involved to keep it exciting there's a bit more in the spreadsheet this time ie dates with times and also some numbers in data one okay so for this example we're going to use a scheduled flow although you could use a different trigger if you wanted to so we create the flow next step we're going to read that Excel spreadsheet so list rows present in a table is the name of the action these days it actually used to be called something else my file is going to be in my onedrive and I have an example file with dates and times and I've got the table defined that it contains that data within my Excel spreadsheet and just for fun I'm gonna put in a select query to limit what I pull back which is just good practice now I'm gonna set up a variable and this variable is going to hold my Excel values so this is going to be an array of objects always good practice to get some descriptive naming in here so I'm just going to do a rename so I know what this is all about okay now we're doing an apply to each because we're going to go through all those values that we've got from Excel just now I just need to choose the values that I got from that action before there we go so that's everything I pulled out of the excel sheet now within the applied to each obviously we need to look at those values I'm doing a compose and here I'm just going to paste the format that builds up the data structure in my compose here we go those are my columns of interest now just for information this is what that looks like a little closer up notice that these things have commas at the end except the last one doesn't there we go so put in the title for title this is coming from the excel sheet each each row now so I'm just building up my object and putting the values in the appropriate place except the day is a little bit tricky so this is the formula that I just paste it in its handling dates and times okay let's do a little bit of a concept spotlight see what's going on here with Excel dates and times and why is it so complicated okay so let's just look at the date first so an Excel serial day dates in Excel has stored as a number of representing the number of days since the start of the 20th century this is called a serial day if I pass any Excel date in a flow I'm gonna get this weird number back how do you actually turn this into a valid value you do an ad days actually based on the 30th of December 1899 because had days in flow does this a little bit wrong but basically the formula you see at the bottom here is what would work and then I format that year month and day so this is made from during an ad days and then I'm going to put in that 30th of December put in my item value so I'm adding the days together for those two things and that should give me a current date which I could then put in SharePoint except it has to be an integer so I have to make sure that I'm getting an integer rather than a string from my property doing the date formatting for date and time it's much more complicated in this instance for serial date time you get days and then a decimal point followed by fraction of day which indicates the time as you can see you get a much longer number out add days needs an integer so we can't use the same approach we have to do something else so I decided to use add seconds instead there are eight to 6400 seconds in a day so if I multiply that by the number I got from the excel that's going to give me a number of seconds and then I can do an add seconds and get a date so we're doing add seconds plus the 1899 thing then I'm doing a multiply it has to be a float because it's going to have decimal places once again I'm getting the target date from my properties in the Excel I have to do a format number to get rid of any spurious decimal places and then I'm changing it into an inn and then I'm passing it in to the add seconds so it's a pretty horrible long formula and I'm going to paste this into the description for the videos who don't have to type it all out if you ever get into the problem where you have to convert timezone you have to add this extra bit saying which time zones you're moving between my example is just using UTC so I'm not doing this there is a Microsoft page and here's a screenshot showing all those time zone names which you could possibly put into that function it's not that nice why couldn't it be a code okay back to the example then so that's my function added in I just do a rename again give the compose a meaningful name so I've built my object effectively now I need to pass that JSON which I just built select the output from that last step and then I'm just doing a paste of how I passed the JSON so this is the schema notice that for the target date it's still string all of these values are string even where I'm pulling a number out of my excel I can treat it as a string here go all renamed so each time we build these objects when they're complete like this we're going to add them to our array and then we're going to have an array of objects built from our excel rows why do we do this again it's so that we can do a really quick comparison between the other objects we're going to build from the SharePoint rose and it just allows this very quick comparison not having to go through property by property to compare ok so now we're going to get our values from SharePoint so get items select the site in question and we've got a list for this example nothing else needed here next step we're gonna build a fairy ball based on these SharePoint values and ultimately we're going to be able to compare this array with the array that we just built with the Excel values hopefully naming in a way that we're going to understand once again it's going to be an array of objects put some meaningful naming in there we're doing that same apply to each type processing which we did for the excel sheet but this time it's for the SharePoint so this is the data I got back from the SharePoint good items let's just name this properly again for later reference typo okay next step we're going to do a compose again give this a valid name and once again we're putting the skeleton of the JSON into here it's going to be the same for the excel and for the SharePoint objects that we create so you can just have it in a text file and just copy and paste it like I'm doing here so now I bring in the columns which I just got from SharePoint so we've got tidal data one man this is long list data - and for target date it's actually not going to be target date it's actually called action date this is just showing that my columns in SharePoint don't have to be the same name as the ones in Excel we're actually doing the mappings effectively in this JSON it's actually called action date so I'm doing the format again here year-month-day and the reason I'm doing that is so that it's going to match the format that I put into my Excel based object just now so the comparison is going to be valid now I am going to copy this step from before because actually we have the option to copy and paste from a clipboard now which we didn't use to have and then I don't have to paste that JSON so add an action and I can go to my clipboard and there it is so that saved me a little bit of typing just do the rename so this was just a massive error because I didn't change the data source in this and this causes bugs later in the video just so you know don't copy that mistake okay so we do a rename on this one shame we didn't change the content field as well so add an action and of course we're going to be adding these objects to the appropriate array again in this one it's the SharePoint objects okay so now we're going to create another variable and this is going to allow us to do an intersection operation if you remember from the other videos an intersection is where we see what elements are common amongst two arrays and it's going to check all our objects effectively it's checking all the properties for is at once and it's a really quick operation it's great great time-saver and that's why we've gone to the trouble of building these object to Ray's so we're going to do an intersection as the value for this one and it's going to be an intersection of those other two arrays which I just built and this is going to give us a picture of what has changed so SharePoint keys and of course the excel keys and we put a comment between them there we go okay that one this is what I just created with the expression builder so this is progress we've built the objects from Excel and from SharePoint and we've got the thing which is doing the comparison let's just give this a proper name so we know this was the intersection alright next step we better take some actions now based on what we've just found out in our comparison so we're going to go through again and apply to each let's just rename this sensibly again first thing we do is we're going to delete the SharePoint objects which have disappeared or have changed ie the things that are not in the intersection anymore they're not common between the Excel and the SharePoint anymore okay so we'll compare the SharePoint items we're not doing a second query against shape one here we're still using the values that we pulled in from SharePoint first time over but yeah we do have to do a compose again so I'm just doing a copy and paste from a notepad because I don't like to type these things out just a reminder again about the commas the first three have the comma but the last item never has the comma at the end putting our values to the JSON I know it's a shame to have to build these things up twice but it's the only way to do it once again we're formatting the date so that it's going to be a good comparison to what we got from the Excel dates give this a reasonable name so we know what we're doing here okay next section I'm pasting from my clipboard again JSON and once again I've made the major mistake this is bringing in the Excel data when I should be bringing in SharePoint data okay so we do a rename remember this is a mistake and I'm fooling myself let the SharePoint data okay now we're going to do a condition and we're going to see whether any given item is in the intersection or not so if the particular SharePoint item is in the intersection then we don't want to touch it because it's an unchanged item that means it's going to be the same in Excel and in SharePoint already but if the answer is no it's not contained then we're gonna do a SharePoint delete so I find delete here for SharePoint we're going to do a delete item is it my imagination or are they more than they used to be okay here we are so yeah the usual stuff we need to put in the site address the list name and we're going to have the ID from our data that we've already obtained from SharePoint okay so far so good let's do the usual rename I know I know but it saves us trouble if anything happens okay next thing so we've effectively got to the point where we've deleted chap on items which have changed or gone now we need to think about adding items so this is add an update as effectively so we're going to put in a loop again apply to each let's just make it clear again with a name that we're adding this new SharePoint item new or changed so of course is the Excel data which we need to write to Shepherd items in this case this is the new stuff from the spreadsheet once again a condition and once again we're looking at the intersection because if the items in the intersection that means there's no change between this excel item and the Shepherd list so no need to write it you see where we're going here this is why the intersection is quite useful so yeah SharePoint but this time we're going to be creating an item there we go okay usual information and now I'm gonna put those items in from the object now this is the critical bit because I want to write in the values from the current item but obviously it's an object so item here is going to reference the object itself but to get the properties I have to do question mark square brackets and then inside and then single quotes inside and the property name and this is how we reference for example title data 1 data 2 and the target day inside those objects that we built and put in the array before so we just have to do this several times item question mark square rackets property name inside the square brackets for each of these things so yeah data one I mean I could just type in idem each time but it's good to show the expression builder here we go data to hmm here we go and then the date notice as I type that it's called target date inside my objects that I built before it doesn't matter that that's different from the column name action day which I'm filling in here for SharePoint save that and the usual renamed new or changed okay so we just have a look at the spreadsheet we can see we've got some number values we've got some date and time values notice the 5:25 p.m. business several of these here so this is what we're hoping our flow will upload to this blank SharePoint list so let's give it a test and failed okay not a good stylist see what's going on hmm the add new items and the actual create item step so I must have done something wrong there okay what I'm seeing is for the title field it seems to have yeah it looks like the whole object has been written into the title field and not the individual property of title so if something must have done wrong when I did the whole question mark thing before same for action date okay let's have a bit of a change around see what happened you saw me put those question marks in there yeah it looks like the expression builder is helpfully got rid of the extra bit that I typed question mark and property name so I'm just gonna have to do it again yeah so the usual thing item question mark square brackets single quotes properly name get rid of these looks like data to is actually okay why am i showing you this repair well it's warts-and-all isn't it sometimes when you're building a flow this sort of thing happens so it's better that you know about it the tool is great but it's not perfect here we go item again question mark excetera well at least I get the practice okay and then last one action day and reference the value from my object target date okay well it's looking like it's kept my change this time save it give it a go okay test again [Music] run this hope we have a little better luck okay success nice looking good refresh okay values looking good I've got my times as well got my numbers I've got my text yeah tally with the spreadsheet so that seems to have worked let's just do a change so that we can check the update bit okay run again failure what's happened now apply to age past the JSON okay that doesn't look so bad what's this invalid type expected object but God array that implies let's have a look in here that it's not getting the individual property again strange well it's not leaping out at me this second ah excel content so this had the wrong data inside from the cut-and-paste before it should have the SharePoint object in there that is passing so actually it worked first time but then for later comparisons it's got the wrong stuff inside and this is what's happened okay change made that's given another test run flow and failed again okay what's going on now we passed the thing from the last time but it's a pass chase I'm not working again and same message expected object of a god array just have a look in here same issue I accidentally from the cut and paste of the past Jason step I didn't change the data type so fix that one get the SharePoint outputs inside there boy that could have gone bad I couldn't and I thought I was saving time with the cotton paste before they save time to retest hey let's have a look at this one [Music] successful okay that's looking good let's check the data yep okay yep the one where I change the values has updated yeah okay that looks pretty good well we've got a test again done we really now that we've got a working flow yep resubmit because this is updated as well so we've got to test it successful but the proof is in the SharePoint list isn't that refresh there we go and yeah it's got the right value from testing we have the working build just don't make the mistakes that I made [Music]

Channel: Jamie McAllister

Views: 15,075

Rating: 4.954802 out of 5

Keywords: Power Automate, Flow, Excel, SharePoint, Office 365, M365, Date, Serial Date Time, McAllister, microsoft flow, update sharepoint list from excel, import excel to sharepoint list, power automate sharepoint list, excel sharepoint, excel date, sharepoint excel, excel to sharepoint list, excel power automate, power automate excel to sharepoint list, ms flow, jon levesque, import excel to sharepoint list office 365, power automate excel, sharepoint list, sharepoint flow, power automate

Id: nlw5_qV8pIo

Length: 38min 26sec (2306 seconds)

Published: Mon Jul 13 2020