Add & Update Excel Data to SharePoint List with Date Field using Power Automate
Вставка
- Опубліковано 14 жов 2024
- This video is about how you can add and update your SharePoint List from Excel Table data using Power Automate. this video will cover Date and number field as well . Power automate will fetch all the excel table rows and then check weather that row already there in the List or not based on UserID field. watch full video to learn in detail.
or further queries do WhatsApp message on - +91 9557196693 or mail us to lernentech@gmail.com or drop a message to our Facebook page - / lernentech
Expression - addDays('1899-12-30',int(DATE TO CONVERT),'yyyy-MM-dd')
#powerautomate #exceltolist #updateexcel
![[UA] NAVI vs MOUZ | BO5 | IEM Rio 2024](http://i.ytimg.com/vi/CMBYk0cbGoA/mqdefault.jpg)
genius, and in 16 mins, legend, very helpful!!
Thank you so much for this tutorial and the one about updating a sharepoint list using power automate so you don't have to copy cell by cell. Total lifesaver
Thanks, that's so helpful!!
I have only one problem: the date field in Excel is not mandatory, and when it's empty, it causes an error. How to avoid this error?
I've tried to apply the condition, but not sure how and when I should write the condition.. could you help with this, please?
I think you can do it in the column mapping by going to custom expresiion...it's a good point. i will try to address this in upcoming videos
Great help, your videos are very useful. If u can also share tutorial as to how to send emailer to employees post new item is updated in this process and another email post 30 days from joining date, it ll be very helpful
Thanks... You helped me a lot!!! Regards from Brazil
Microsoft really knows how to integrate its tools into a beautiful ecosystem
Thank you for your help!
I need a similar flow to include or update more than 20,000 items, you mentioned at the end of the video, that you would do a next video to show how we can do this, do you have any plans to release? 😀
yes few more days.
@@LernenTech Is there any updates on this?
Great, Thank you so much...., i was stuck here for the date!!!
Great video! But, if a column blank, the flow doesn't create the record in SharePoint list? How to solve that? Thanks!
i am trying to do the same but the automate is able to check only for the first row in the excel file...i have 4 items in my excel and i see only first one being executed and the rest is not recognised and i have to do it via mannually.
can we update data from lined table of SharePoint list without using PowerAutomate ?? i have seen and used once but option has been changed alot.
Nice, any suggestions for blank date errors.
Thank you so much for explain it deeply I used to have an issue in my flow every time I make the same flow and it never worked until you showed the date function trick
Glad you follow other videos as well
Hi Lernen Tech, how about delete from excel row and auto update into the list? Can you kindly advise. Thanks.
Is there a way to create a flow that would check if any document in a document library is modified, then add/update item to SharePoint list based on that?
I plan to have 14 similarly named documents (with different last name appended at end), and would like to update a SharePoint list any time one of those documents is modified.
doesn't work for me. only works if I already have the ID in sharepointlist. But if my list is blank, it doesn't transfer the data from excel to sharepointlist
will it work with multiple excel sheets located in the same folder? that has the same table structure?
Hi there, I really need to use this solution, but in my test in the Condition section > If Yes and If no > Apply to each. When using value from Get Items it doesn't load any data to my sheet, and if I use the value from List Rows Present in Table it prints duplicates. Any suggestions as to what I may be doing wrong? for the Get Items I am using the title column as my ID is this causing the issue?
Amazing, thank you for this great video....... 🤟
Hi, can you tell me if this formula works with datetime format? or which one will can use to flow works?
In addition to adding and modifying excel data, there are instances where I have rows . Could you add a step to this video to show how to delete a sharepoint row that no longer exists in the excel file? Thanks in advance
(correction to first sentence) there are instances where I have rows deleted also.
If one data is deleted in excel sheet, it is not deleted from List, is there any suggestion?
Hi, thanks for the video. I'm having a specific problem could you help me please? When running the flow in the conditional section of creating new items in shaire point a problem occurs. the flow says it completed successfully, however the data is not transferred to the shairepoint list. there are no error messages however, does not work to transfer new data to the list.
try creating new list and flow again just for testing.
@@LernenTech I had the same issue but it worked when I use a new list
I had the same issue ,but it worked when I use a new list.
Hi Thank you this is exactly what I was trying to do.. But can you advise tell me how to include time? as I have a column Date - the field is display DateandTime. Thanks
Is there a way to do the same but using Smartsheet instead of excel file?
In Get itesm filter query i am unable to adda any expression of dynamic value.When i click on pop up id just disappears.
Hello sir thanks for video but i have issue about my flow and the issue is "Unable to process template language expressions in action 'Create_item' inputs at line '1' and column '29383': 'The template language function 'int' was invoked with a parameter that is not valid. The value cannot be converted to the target type.'" can u help me abiut this ?
Any solution to this please? Having the same problem.
@@pieteroosthuizen9912 any solution?
@@DNKENTERTAINMENT I bit late by you can change the expression to if(empty(items('Apply_to_each')?['YOUR DATE FIELD']),null,addDays('1899-12-30',int(items('Apply_to_each')?['YOUR DATE FIELD']),'MM/dd/yyyy')) and it should work then
@@pieteroosthuizen9912 @Adam Chesbrough A bit late by you can change the expression to if(empty(items('Apply_to_each')?['YOUR DATE FIELD']),null,addDays('1899-12-30',int(items('Apply_to_each')?['YOUR DATE FIELD']),'MM/dd/yyyy')) and it should work then
is there a way to convert excel file dropped a folder to a list ? For example , when I upload an excel file in to a folder , i want those fields in the excel file to populate in a sharepoint list . Is that possible? As always thank you for your great videos!!
Hey, thank you so much for posting this! Solved me a massive headache!! :D
This is really helpful, would you be able to show how to do something similar for a document library?
what exactly you are looking for?
Thanks for the video! If I delete a row from excel, is there a way to remove that data automatically from the list?
Tough one
Nice video, only issue I have is, some reason table name does not pop up, I have tried giving table name but still table name does not show, any idea? Thank you
This isn't working for me. I am using the excel sheet of a form in sharepoint (Forms for Excel) since it automatically captures the name. When the form is updated, it is captured immediately in the excel sheet. When this is done, I want that information to go to a list. I have followed this video and a previous one. I can't get it to work. Please help!
Hi, is there a way to update/ add sharepoint list to excel online with automated query
ua-cam.com/video/xwvLGbyhssI/v-deo.html
Love this and thank you but I'm running into a slight problem where even though the flow is successful the create item does not seem to be triggered so no items are created?
Same issue
@@JeromeKrehbiel Hi, I realised that by copying and editing another instance of this flow I may have got me apply to each out of order and confused it. It started completely fresh and it is working notwithstanding some date issues in excel which are nothing to do with this flow.
Me too... I even change the columns name to be the same as Lernen example and as I don't have Date field, I thought It'll be easier to make it work, with no success :(
How to triggered this flow when a button is pressed in power apps canvas app. ??
Hi,
After about a week of trying to get this to work properly, I've finally managed to get it functioning, but only to a certain degree.
I am getting errors if one of the excel fields is blank, which then cancels the operation for that item.
Does anyone know how to overcome this?
Excellent!
I have created a flow but it fails when the excel file have blank values for the columns formatted as Number and Date in the share point list. Please help me regarding this issue.
Thank you, This exactly what I was looking for.
However, whenever an excel data is updated and a flow is run, the outcome is a new record/ row (as seen in the video)
Can you please tell me, how to update on the existing row in SharePoint list.
How about the other way round
SharePoint list items older than 7 days to OneDrive online please
This is good too👍
Thanks for posting this. how would we deal with a table which has date and time? use float instead of int perhaps?
no will have to apply the same logic.
Can you share some efficient way to add bulk items (more than 5000). It takes more than 2 hours currently.
Thank you!
Sure thing!
It won't work if the date column is empty. what is the solution
This is amazing, But I am having one issue, In my case, it always returns length as 0 and create an item even though it already exists.
run with formula only first
I have watched and tried your method, but when I run my flow it duplicates each row by the amount of records in the excel sheet.. only on create, not update file. How do I get around the Apply to Each on create item?
Im having the same issue ever figure it out?
Getting dupliacte rows when ran the flow
Really amazing content and nicely explained, Will this flow work effectively for large number of records approx 8k?
till 5000 yes
amazing
Thank you for posting this!
Welcome :)
thank you thank you thank yoU!!!
Thank you - it is working!!!
You're welcome!
Sorry if this has already been answered - If you delete a row in the table of the excel file, will this be removed in the SharePoint List?
I got lost between 5:56 and 5:57, how did you manage to add the Sharepoint task? Its not showing on my end.
In the condition part, I can't seem to add expressions for some reason. Do you know why?
check you Power Automate setting you might have turned on experimental feature
What do you have your columns set to within your sharepoint?
Hello, I have the problem, if the date cell is empty the flow fails. Is there a way how I can leave the date field blank or empty?
Hi yes you can do that, add isempty or isblank
@@LernenTech do you need to include an if statement in the addDays expression? I am trying this "if(empty(items('Apply_to_each')?[], ,addDays('1899-12-30',int(items('Apply_to_each')?[]),'yyyy-MM-dd'))" but get an invalid expression error :(
Edit: needed to include a null; if(empty(items('Apply_to_each')?[],null,addDays('1899-12-30',int(items('Apply_to_each')?[]),'yyyy-MM-dd'))
@@Bren4000 thank you for solution but you missed a bracket on empty -
if(empty(items('Apply_to_each')?[]),null,addDays('1899-12-30',int(items('Apply_to_each')?[]),'yyyy-MM-dd'))
I think there was a gap on the discussion at 5:57 , it jumped right to the next step.
I have empty values in date field to overcome with this issue
apply condition if you have empty value
InvalidTemplate. Unable to process template language expressions in action 'Update_item' 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.'.
In case you still need an answer to this try and change the expression to if(empty(items('Apply_to_each')?['YOUR DATE FIELD']),null,addDays('1899-12-30',int(items('Apply_to_each')?['YOUR DATE FIELD']),'MM/dd/yyyy')) and it should work then
Not working for me :(
Thank you for your effort but when I tried it like your steps it doesn't working because my date in excel sheet like this : 15/10/2019
how can I fix it without change date in my excel file.
Thank you in advance.
hmm let me check!
@@LernenTech Ok Dear I am waiting for your response.
@@LernenTech Any update on this?
good
Thanks
sahi hai
spent hours and hours, watching over and over doing everything the same !!! JUST WILL NOT CREATE NEW ITEMS!!!
Same problem here. :(
Well, after a while I realized if you remove the 'Apply to each' part on the 'no' side of the 'Condition' and just keep the 'Create item' w/o the 'Apply to each' then it works.
@@MrPolszka once again, a random comment saves my day. It works !!!! Thank you so much