Add & Update Excel Data to SharePoint List with Date Field using Power Automate

welcome back to channel everyone in this video i'm gonna talk about how you can add and update your excel data to sharepoint list using power automate which will cover the date field as well so as you can see this is my source list and as of now it's blank and i'm having one excel sheet with a couple of rows here and which include the data field as well so what i want is when i run a flow i should be able to copy all this data to my sharepoint list so next time if i make any changes to this excel or add any new item here that should get updated here as well so let's just run this flow which i've already created i click on run flow it's done we'll check the status your floor ends successfully all right i'll go to my sharepoint list and i can see all the data here all the data which in my excel sheet are here so the next step is now if i add new data here and make any changes to this one and the date will be 2021 for me and i'll make changes to my employee list as well so let's suppose i say this employee double one i go to this third column i make this as in sharepoint and make this one as an open item and change some experience as well and change the date as well so let's suppose i make this as in 2021 20 21 and now everything is there so we'll go to our list check there is no update now i have to rerun this flow run flow done it's running you can see it says uh flow ran successfully and if i go and check my this list you can see there is one more item has been added this one with employee 6 as an username so you can see that employee 6 with the username has been added and we'll check whether the item has been updated you can see the employee11 is there you can see this open is there you can see the new experience which i've added is there and you can see all these date column as well so it is working perfectly fine for me and the date column is updating fine as well even if i go here and try to make changes to my date field which is this one i can do that and can save the changes into my sharepoint list as well so the point is now how we can do that as well using power automate so this flow i've already created and it is running on a manual mode so let's uh do one thing i created a scheduled flow and it should run you know every day i would say 6 am my time and repeat every day i want to repeat this every day so it's up to you how you want to you know repeat this you can repeat in a month a week hours minute it's up to you so even if you want to repeat within 30 minutes you can mention this and say every 30 minutes your flow will run the flow name will be date means date column click on create the next step is so we have this excel sheet present here into my this document library with the name of excel data so first thing i have to do is excel online for business list row present in table so one thing to mention when you're creating entries to your excel make sure you are formatting that as a table i have already formatted that as a table here so you can do that as well and name your table so i've done that you can do that as well next step is i have to select the locations means my site name there is a group or sharepoint site difference so you can choose that select a document library select the import from here and under that file which i have to choose is a excel data dot xls and the table name will be table one for me which i have created so the table which i'm talking about here forming as a table you have to select that here so once this is done nothing i have to do here next step is a control and apply to each i'll select a value of this list item means this our list representing table item i'll say action get items and in this one i'll select again my site and then i have to select item from this source list get item from this source list so i'll choose source list and under that you have this filter query option so this could be a different experience for you so in case you want to have this drop down option in field query you can go to your power automata setting and from there you can enable this experimental feature as on so you can get this one as well so you don't have to write the query for that get items from sharepoint and i have to compare that with the user id is equals to when i say the user id is equal to control space will be user id of list represent in table so means the excel user id so what i'm doing exactly is so this get item will get the item from this sharepoint list where the user id of sharepoint list is equal to user id of my excel sheet that means if the user id of this excel sheet is equal to user id of this in that case what i'm gonna do next is i'm gonna make a control statement based on that so it will get one item at a time and top count i will keep one so get one item at a time because we are under apply to each any which way it will count to one by one so the next step will be the conditions for me so the condition will be choose so i'm going to write an expression here length length of what length of get items value is either equal to or not equal to whatever you want to keep i'll say not equal to zero that means if there is one item returned i will update the item here i'll say update sharepoint update item update where i'll update item into my again dev my source list where i have to update the items and the unique identifier will be in my case will be id of this get items from sharepoint list so it will automatically keep this into apply to each section and now i can map each and every column the way i want to map so username will include user name of my not get items user name of my this list represent table and department value will be custom item so it is again my list represent department because it's a drop down value so when you have drop down values you don't have to select the drop down values you have to click on add a custom value and then you can select the value from your list represent status click ok you will have the custom value in the experience section i'm having the experience listed represented table experience okay the next step we have is a joining date which is our this joining date and how this joining dates work in power automate is it does take this as in string and give you a numbers that could be any numbers and how that number is generated basically you can see this formula which i have just written here so let's suppose i have a joining date of first december 2021 so it will count the number of days from this date to this date will give you that number so in case you're getting that numbers don't worry about that after using this formula you can solve your problems so i'll copy this one i'll go to my flow here and you can see in my joining section i'll click on my this fx function one and here i'm having the expression so you can add this expression from here as well but i'm already having that so i'll say i'll paste the entire one here and in this date to convert i have to put the date here so how can i get the date is from my excel i will say joining because my column name is joining list representing table joining date and i am ok so you can see that in the joining date column i have functions written there so it will convert my date automatically for me now and user id i can say user id to map that with my user id so this is done for update item and if the length is equal to zero in that case i have to create item as well so now i'll go here go to my sharepoint create item create itemware to my dev site my source list and i'm gonna create items here as well so i'll repeat the same process b so i'm all good for this particular flow and your flow is ready to go we recommend you to test it go to our list first of all we'll delete all the items from here and now we'll test our this flow manually our flow ran successfully i'll go to my source list and you can see all of the data is here so i'll now make some changes to this excel so this is done from my end i'll go to my flow again here and i'll redone this flow started and it's saying it's rain successfully now you can see and check user id from 1 to 7 has been created and all these data which i have made changes here i can see all the changes here the 2010 is there and that closed is there and that employee changes is there into my employee section as well all right this one you can see so that's the way you can actually add and update your actual data to sharepoint list using power automate so a lot of people has query about data in my previous video so i've covered that into this particular one so in case you're not able to copy more than 100 or 200 items so what you have to do is you have to go to your this list represent excel setting and then you can enable your paginations and can set the threshold limits 5000 which is default limit and you can set this and you can save this one and also in case i think a rest you don't have to worry about that because your get item is only getting one item at times and even if you want to do that you can do that here as well the paginations part that we will be able to copy around five thousand item in a one shot so in case you want to do more i'll talk about that in my next video so for this video that's all from my end i hope you like this video if you do like this video don't forget to subscribe down on tech thank you so much for watching

Channel: Lernen Tech

Views: 16,560

Rating: 4.9423075 out of 5

Keywords: power automate excel to sharepoint list, microsoft flow update sharepoint list item, microsoft flow excel, microsoft power automate tutorial, microsoft power automate examples, update list from excel to sharepoint, update sharepoint list from excel using flow, import excel to sharepoint list, update sharepoint list from excel, import excel to sharepoint list office 365, export excel to sharepoint list, flow, power automate, sharepoint, power automate update sharepoint list from excel

Id: MtCqbRBOOb4

Length: 16min 24sec (984 seconds)

Published: Sun Jan 03 2021