Add & Update Excel Data to SharePoint List using Power Automate
Вставка
- Опубліковано 20 лип 2024
- This video is about how you can add and update your SharePoint List from Excel Table data using Power Automate. i will fetch the data from excel and then check condition with SharePoint List Item, if data already exist it update the item else it create a new item. watch full video to learn.
apology for Update spell mistake :P
This is what I’ve been searching for 🔥 thanks man!!! Please don’t stop posting your knowledge
I used this video to create a SP list which is syncing with an Excel table with 0 Flow experience. Well, maybe not zero, but anyway, very very little, as couldn't understand all the words and had to learn the hard way, by googleing and looking at other sources as well. But in the end, it's simple and does the job. Now I need to start doing something with that list. Thank you for this!
For further queries do WhatsApp message on --> +91 9557196693 or mail us to
Thank you! Been searching for this for days! Short and precise. :)
Great vid! Does the raw data have to be stored on OneDrive for SharePoint to know if new data exist on it and will reflect those on the SharePoint list? Thanks!
This is great video . just a humble suggestion to improve this further , Kindly talk little more about get Items columns vs list rows present in table columns . A beginner can easily mix up and not get desired results
Ok, Sure.
This is an easy to follow video. I'm just curious if this is going to work with SharePoint even though it's not a List. I guess I'll have to try but if it doesn't work, we'll see if we can transition to the new Microsoft Lists instead. Thank you!
This is Awesome! Thank you for making this tutorial.
Glad it was helpful!
Great work, Prakash. Keep it going. Nice
Old but gold!!! Thanks a million.
Thank you so much, you have explained it so well that I succeeded in first attempt.
Thank You!
Work very good.
I have a question. How can I make the list refresh as a donation on the home page? Currently, it is only released in the LISTS tab. And if the list is added as an element on the main page, it only refreshes if I refresh the browser.
Thanks for the guide! Does the Excel data have to be formatted as a table in Excel or can it just be rows/columns of data without it being a table?
Yes as table.
Man, you rock.
Thanks a lot for this explanation, amazing
Just a Quick question, and if the Excel file excluded a row, how can this exclusion be reflected on SP? Great work many thanks
Hi there, great video! I have a question: When I get to If yes, Update item, I'm only getting the Title Column to fill in (My other columns aren't showing up). Any ideas on what's going on? Thanks!
Thanks SO MUCH, this really helped get this done!🙂
Hi @Lerne Tech, can you also add in the delete from excel and auto delete in SP list? and also the Date column if it's a DATETIME how to pick up in flow. Thank you.
My exact problem, both of them. Have you solved this?
I really appreciate you tutorials on this channel. I have found several solutions that work that I have not found elsewhere. I do have a question regarding this flow. I'm using the current version of O 365 and I followed your instructions to the letter. The only difference is that my source is sharepoint not onedirce. However, have a few issues: 1 - When I run the flow all of the entries repeat themselves and have to force to flow to stop after several minutes. 2- I'm unable to create the filter query as there is not a "Title" field in my selections. 3 - The condition results are always true even when I perpusfully duplicate entries to test the update. I look forward to you feedback.
Did you ever solve this issue? I'm getting the same error with repeat rows.
I will create same video with updated MS option and keeping your issues in mind as well…sorry for late reply
@@LernenTech thank you so much for your time and help - this flow will save me so much time and effort! PS - I am not tech savvy at all lol
Great video, I"m just diving into Power automate for work. trying to cut down the number of clicks, copy/paste, and de-duplicate functions on a daily tracker.
Thank you so much. This works perfectly for me!
Glad it helped!
I have followed this tutorial to the letter but it is not creating new items. Only updating the matched one. Is it possible anything has changed since this was uploaded which would stop this from working? Thank you.
This is what I am looking for! 🙏 thx
Great tutorial but i'm getting some weird behavior. My list isnt pulling through all of the entries. When i look at the entries that havent bene added in flow it reports ActionBranchingConditionNotSatisfied. The execution of template action 'Create_item' skipped: the branching condition for this action is not satisfied. Any ideas?
This is exactly what I looked for today for almost 4 hours... and I copied how you did it step by step and worked! My only initial mistake was I grabbed the SP data for each row instead of the table data, but easy fix for a lot of columns :)
Thanks for taking the time to do this for us!
Do you have one where no matter the file name you can update the same master file? Example - 9 managers post a file into the same folder but all named differently but each has the same content and columns?
I am doing a workaround and just copying the logic 9 times and changing the file name in each but pointing to the same list in SP. (SharePoint).
Thanks for writing, please message on our facebook page.
it is not at all working
Wonderful Stuff! Thank you for sharing your knowledge!
My pleasure!
Thanks, great video. I followed steps in video and it worked on first try. Gracias.
Great to hear!
always short and to the point with great examples !
Glad it was helpful!
Thank you for putting together this video...very helpful
Glad it was helpful!
Totally useful thank you for the big assist!
When I add the "length" formula I get a different formula (length(outputs('Get_items')?['body/value'])) resulting in an error. Any idea?
Hello! great video, helped me a lot.
Just one question, apply what you put, but when creating a new one with data in my table, it doesn't update me, but it doesn't show an error either... the flow runs without problems... I made sure that all the fields passed as text. ..and the list that you create in sharepoint use the "create list from an Excel" option, then I try to update the data and it works, but when creating a new row it doesn't.
On the "List rows present in table" excel step click the 3 dots, choose settings and enable pagination.
Thanks for this great Class!!!
Thanks for the useful guide its really helped.
This was really useful. I'm running into one problem though: When I run the workflow, if there are ten lines to be added to the SP List, the workflow produces 100 lines (each line 10 times) even though I have the Top Count set to 1 in the Get Items step. What would cause this?
I had the same issue and realized that I need to have an extra column on my excel file specific for ID. In my case, I used student numbers and the repetitive lines stopped. Make sure the ID field is not blank. I had N/A on some of the students' # and it was repeating the same name every time the flow run. Hope this helps.
HI! Nathan, I also got the same problem as yours, Have you take any solution? Can you share to me ? Thanks !
Hiya. Thanks for the video. I'm having issues with the last step. My other column headers not including Title don't show up in the dynamic content window. For 'Title' they al appear but when I clock into another column box to match them up there is nothing from the 'List rows present in table'. Any ideas? Thanks
check latest video in channel
Thank you so much for the tutorial. It made my life easier.
Hi, I think there's a limitation. I am copying 50,000 records but it only copied 256 records.. Any thoughts? Thanks.
It looks like my issue is about the "Flow Run Time" wherein the maximum flow frequency of my flow license is 5 minutes. Is this upgradable?
Glad it helped!
enable pagination and change the limit
will create a video on that soon
Are there any changes required if the excel file from which data must be taken is hosted by Sharepoint rather than OneDrive?
Thanks. If I add new rows in Excel e.g. Employee 6 and Employee 7, will it add them to the Sharepoint list? Also, if I delete an Employee in Excel, will it delete the employee in SharePoint? I can see this working with a condition checking a column called "Employment Status" whether the employee is "Active" or "Inactive". Would love a tutorial on this.
Sure will try.
how to i edit this flow if i want it to check for updates throughout each cell in the excell sheet and to overwrite old data and update new???
I tried this however this flow is only adding new row and not updating . I added a row in sharepoint and then modified a column however in excel it is adding new row for modified also instead of just updating the record. also, what needs to be done for deletion of record as if I delete a record from list it should also delete it from excel .
thank you so much, it helps a lot!!! GREAT!
Glad it helped!
I really appreciate your help.
Thanks for this wonderful tutorial. It worked, but I had to change get item top count so its working.. I made the top count as 5000
Can you pls help..I want to use MS form for filling the data, then needs to get approval using power automate..once approved it should save the data in Excel with approver name and also his comments..thanks
Hi how would you delete rows that have been removed from the original excel but still exist in SharePoint?
It works thanks! only have one problem if I include a Date format cell I get OpenApiOperationParameterTypeConversionFailed. The 'inputs.parameters' of workflow operation 'Update_item' of type 'OpenApiConnection' is not valid. Error details: Input parameter 'item/PublishingDate' is required to be of type 'String/date-time'. The runtime value '"43938"' to be converted doesn't have the expected format 'String/date-time'.
Any Idea
For further queries do Whatsapp message on --> +91 9557196693 or mail us to lernentech@gmail.com or drop a message to our Facebook page - facebook.com/Lernentech/
will it work if my excel has more than 5000 rows? Even if I change settings of my "List rows present in Table", the max I can go for is 5000.
Many Many thanks for this video :)😇
Nice video.
I have a trouble creating item in SharePoint list from a list rows present in a table which is filtered from another list rows present in a table's column. Do you have any suggestion?
Hi, Thanks for creating this video. I just want to know is it possible to only import few columns and not all from excel to SP and if yes can you please guide me to a helpful video of yours.
yes you can! For further queries do WhatsApp on --> +91 9557196693
Thanks for this great video.
Great video, thanks for sharing!
I would be grateful if you could let us know how to delete items from the list which are no longer in the excel table
For further queries do WhatsApp message on --> +91 9557196693 or mail us to
Fantastic guide, thank you. Just what I was after :). Is there a limit to how many rows you can have in the excel table? I have 50k but when I run the flow it only does 256 items. Thanks.
change flow setting
@@LernenTech I get the same issue, IF I set the top limit to 21000 . It still only does 256 lines.
@@onionman2117 I need this solution also. Did you figure out how to do it?
@@nobrecamarada click to three dots to open the menu of "list rows present in a table". Select settings. Turn on paging (the first option). Enter the row count you want to fetch. Save.
@@fyukselthis method get the maximum 5000 row from excel, but thank you for advances
Hi, thanks for sharing. I have a daily schedule download excel file where it automatically saved to sharepoint folder. I would like to add a unique column in this excel file, rename and save it and connect it to another excel file every time a new file added to the folder. How to automate this flow in Power Automate? appreciate your help.
Hi, thank you for your tutorial. I saved my file in sharepoint and named table as you said, but when I created flow with "List rows present in a Table" and linked the EXCEL file it cannot find the table. Could you please help ? What could be the reason?
For further questions and queries mail us to lernentech@gmail.com or drop a message to our Facebook page - facebook.com/Lernentech/
Pls let me know what is best approach for adding data to list without third party tool
this is perfect to add date from excel to the sharepoint list....how do i set this flow up to delete are row from the list when i delete it in excel......like update delete?? thanks!!!!
Hi,
is it possible to create a Flow that whenever a new row or record is created or added into an excel file
the Flow automatically creates a new item in SharePoint?
Thanks
well i am not sure about that. i will have to see that as there is no as such trigger option we have as of now i guess.
Hi Lernen Tech :),
i tried using this Flow and twas nice but then it is sending multiple emails everytime i change something in the excel file...i mean is there a flow that would send an email only once the excel file has been closed? so that the email is sent only once.
Thanks!
For further questions and queries mail us to lernentech@gmail.com or drop a message to our Facebook page - facebook.com/Lernentech/ or do a Whatsapp message on - +91 9557196693
I also suggest that you update the Pagination to 5000 in the Settings. By default only 256 rows will be retrieved from Excel. Thus updating this will help you when your data grows bigger.
how do you update the pagination to 5000?
would this method work, if I just wanted to update a small number of items from a sharepoint list? example: i have a list of 100 items, but only about 10 need to be updated. So the excel data has only 10 lines. Will the filter query work to just select the 10 items from the sharepoint list that i want to update, and leave the other 90 alone?
It should
Thanks for a great well explained video. I have a perfect application for this, unfortunately, the Update Item step doesn't work. I configure it to update a list column with an Excel value. But when I run it (successfully) no update occurs and when I edit the Update Item step, the Excel value has been changed to a Sharepoint value. Any idea why? Thanks.
Hi Sir, so thankful for your lesson when i tried i am getting the below error not sure what is the problem but i am get data updated in the list after that the flow has been failed "Unable to process template language expressions for action 'Condition' at line '1' and column '24757': 'The template language function 'length' expects its parameter to be an array or a string. The provided value is of type 'Null'".
For further queries do Whatsapp message on --> +91 9557196693 or mail us to lernentech@gmail.com or drop a message to our Facebook page - facebook.com/Lernentech/
muy bueno!! explicas muy bien
Hi, thank you for valuable video.
Can we add ms form excel attachement data on another excel file which stored in SharePoint.
not sure what you are looking for?
This video is a god-send
Thank you for this video! I tried replicating this flow but only the update item works for me, it doesnt create a new item although there's a checkmark when the flow runs. I wonder why my new items arent appearing, would you have any theories?
new video is soon
Thank you so much for your video. It works great for me. Can I run the flow if I have only Edit permission?
Yes
@@LernenTech Awesome! Thank you so much!
Hi can you help on capturing data in Excel once excel file is created in SharePoint with column name as file name, date and month and finally the name created.
Great video! Thank you. What we have attachments, how would that work?
in Excel?
Great Stuff! I see a few comments below regarding the deletion of an item from the SP list if it is removed from Excel. Have you yet, or will you soon post a video on how to remove items from the SP list that have been removed from excel? or even mark those items as "Removed?" - Thanks!
Not yet!, will post soon.
@@LernenTech that would be great!
@@LernenTech ok where is the video tutorial
You are such a prince :))
Hello, can you create a video on how to Delete Items on Excel File when Items on the SharePoint list gets deleted? Do this without using the "When Items get Deleted" trigger and use a recurrence action?
Do you perhaps have an example where a Document Library metadata is updated from an Excel file?
is it possible that SP has a limit for this? Somehow after the 258th row there are no updates or new items shown in our SP list. The output in flow ain't more then 258th rows
ua-cam.com/video/pWfXfnquKgo/v-deo.html
It seems it's not possible between Excel and SharePoint 2016 update, via Power Automate. Any help would be appreciated.
Awesome video. Works well. except add new item. In my case , it is updating but not appending new data .
it will
Thanks! But can it also sync when I delete a row in excel? It seems the item is still remained in my SharePoint list..
deletion is something a complex one.
in my excel table, i have some fields that have dates in them. The dates come in to sharepoint as numbers like 45341, etc. I know how to convert excel dates to sharepoint dates using an expression like: addDays('1899-12-30',int(item()?['DateField']),'MM.dd.yyyy'), but I don't see where i would use that in this flow, because i don't think the item() is exposed like that. Do you have some advice on how i could integrate a date conversion into this flow? I have tried several things and none of them have worked yet. Maybe i have to do the update as shown, and then iterate through my SP list a second time doing the conversion?
will create updated version of this and cover your point there.
@@LernenTech Hello, just checking in to see if you can help on this! I too am facing the same issue
@@BenVinar ua-cam.com/video/MtCqbRBOOb4/v-deo.html
Does this solution also work for Google Sheets? I've put together a flow, like yours but I'm struggling to get it to save as I've done something wrong with the flow
it says:
Flow save failed with code 'InvalidTemplate' and message 'The template validation failed: 'The repetition action(s) 'Apply_to_each_2' referenced by 'inputs' in action 'Get_row' are not defined in the template.'.'.
so I"m trying to apply this to a Google Sheet solution but not hitting the right commands.
Have you got something like this but for Google Sheets?
not tested yet
Hi Lernen, are you able to teach how to delete too? Thanks
Thanks... Very useful...
Welcome 😊
Does this also work with document libraries?
Goodday, Thank you for the guide on the flow! I tried implementing this solution but I want to import date format data from Excel to list. Somehow this does not work, resulting in an error that it cant convert it. Have you tried this with dates?
Video is coming in next 1 or 2 days.
@@LernenTech Thank you! I will be on watch for it!
@@LernenTech Have you posted the video yet? Struggling to convert dates!
Hi thank you so much for making this tutorial! I am successfully uploading entries from my excel table to my SharePoint list and updating entries. However, I am getting duplicate entries in my Sharepoint list. Do you have any suggestions on where I went wrong and how to fix this?
Do a WhatsApp message on - +91 9557196693.
@@LernenTech I have the same problem on duplicates, what is the solution please?
Is there a way to only update rows that have changed? in the condition to update it appears to modify all the records in sharepoint...
by default no but workaround can be done.
Can you update from an Excel wich contain more than 1 table?
The video is good but my flow doesn't work because "apply to each" automatically appears in "if no" scenario. Maybe any ideas how to get rid of it? Simple dragging doesn't help unfortunately...
Hi, this is very useful but is it possible to do similar power automate but not on list bun on library?
I have excel file with some data (with a column file name). I'd like to add files to library and automatically build columns with information from the file using file name as a key. I the video it works for list is it possible to do it for library?
Any ideas for documents library?
For further questions and queries mail us to lernentech@gmail.com or drop a message to our Facebook page - facebook.com/Lernentech/
Sir.if the Excel Title Is edited Means it will not update instead in will create new item in the list ... will you reply please this
really helpful!!!!
What if I put this on a day timer and changes test 1 to test 16, will it change the whole row? I want it to be exact like my excel file, even if an item is removed from the excel file, I want them to be removed from the SharePoint too
you will have to create delete action for that.
@@LernenTech What I want is this:
Maybe delete everything inside first, then paste the whole excel file back in, however, this is around 6000 rows so I do not know if it is possible. It is also possible to check if the first column matches the excel ones, if they dont the row can be deleted. I could only find 1 video on how to do this but he didn't explain how he did it sadly.
This video is exactly what ive been looking for!! followed step by step but unfortunately my run failed but as not a whiz with technology i am unsure how to rectify it! this video is create but is pretty fast. any help would be massively appreciated!
plese drop an email
Could you please tell me, how will flow work for .xlsm file
I keep getting a flow time Run error. It's running for 20 min or 30 min. Do you know what the issue may be?
Thank you 🥰🥰
THANK YOU ! THANK YOU !
Hi, Thanks for the information. I have a problem , I used it with a table with more columns and more than 1000 rows , it ran correctly but it only added 256 rows, and I would like to know the problem.
change your flow setting, there is a video in channel for that.
@@LernenTech Thanks!
Good video, helped. Is there one for deleting duplicate items in a SP List?
Not yet
@@LernenTech Is there one now?
I have tried but in excel when we update in Shear Point , flow create another line item in excel
not update existing one !
What to do ?
How can I resolve the issue, that when my list contains multiple items with the same title the loop mixes the items up?