How To Automatically Add Excel Data to a SharePoint List with Power Automate
Vložit
- čas přidán 10. 07. 2024
- Using Power Automate we can get the data from an Excel table and automatically add it to a SharePoint list. This flow will check the Excel table every morning and not only add in any new records from the Excel table, but also update and records that were modified on that table.
👍 If you enjoy this video and are interested in formal training on Power BI, Power Apps, Azure, or other Microsoft products you can use my code "Jonathon30" at check out when purchasing our On-Demand Learning classes to get an extra 30% off - pragmaticworks.com/pricing/
00:00 - Introduction
00:35 - Preparation: Excel Data in Table Format in OneDrive and SharePoint List Setup
01:26 - Building a Scheduled Cloud Flow in Power Automate
02:32 - Configuring "List Rows Present in Table" for Excel Online for Business
03:50 - Retrieving All Items from the SharePoint List Using "Get Items"
05:03 - Matching Rows and Applying a Loop for Each Row in Power Automate
07:05 - Conditional Check: Update SharePoint List if Row Exists, Create New Item if Not
08:33 - Update Action: Updating Existing Item in SharePoint List
09:53 - Create Action: Creating a New Item in SharePoint List
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -- - - - - - - - - - - - - - - - - - - - - - - -
Next step on your journey:
👉 On-Demand Learning - Start With The FREE Community Plan: tinyurl.com/2ujujsb5
🔗Pragmatic Works On-Demand Learning Packages: pragmaticworks.com/pricing/
🔗Pragmatic Works Boot Camps: pragmaticworks.com/boot-camps/
🔗Pragmatic Works Hackathons: pragmaticworks.com/private-tr...
🔗Pragmatic Works Virtual Mentoring: pragmaticworks.com/virtual-me...
🔗Pragmatic Works Enterprise Private Training: pragmaticworks.com/private-tr...
🔗Pragmatic Works Blog: blog.pragmaticworks.com/
Let's connect:
✔️Discord: prag.works/yt-discord
✔️Facebook: / pragmaticworks
✔️Instagram: / pragmatic.works
✔️LinkedIn: / pragmaticworks
✔️CZcams: / pragmaticworks
Pragmatic Works
7175 Hwy 17, Suite 2 Fleming Island, FL 32003
Phone: (904) 638-5743
Email: training@pragmaticworks.com
#PragmaticWorks
![Power Automate: Reminder Emails By Date [Power Platform Series - Ep. 16]](http://i.ytimg.com/vi/g_SqkW9ZCR0/mqdefault.jpg)
Thank you for this video. Super helpful and easy to follow.
this was extremely helpful. thank you so much
Very helpful! Thanks for posting this vid!
huge help ,thanks!
Great !!!! Thank you very much for sharing
Great video, thank you. I'll try it.
thank you. works great
Great tutorial video created. I like how simple and straightforward it is, and it also explains why we did certain things. I would suggest updating the video again if we get the item directly from the excel table with condition, which takes more than 15 minutes to run if the excel table has more than 500 rows with condition. So I think I should convert it into an array to make things easier and faster.
thank you so much, i reached to the point to figure out to create items in sharepoint and due to the condition being wrong it couldnt update the sharepoint list, this video saved my from getting crazy on the error, mistake i was doing was using the condition as "Not eq to Zero". Thanks a lot for great explaination.
thanks a lot man, it was grate
hopefully this will help me work out how to automate when an email is received to add to an excel sheet in sharepoint or also check and update the cells if required
This one is best vide
Great video. I have a question. If I delete an item in Excel, will that item, or can that item get deleted in the SharePoint List?
Thanks for this, quick, or maybe long question. I have a List with various column headings. I have a Form being completed by staff to say "They Agree" with something that, Form is exported to a separate List. How do I get the flow to match the user in one List with their answer and update the correct user? Am I missing something here, very new to FLow , but thank you. Tim
Hi! Very useful tips! What if my excel is not in table format? I have a dataset with columns but not in format table. That's how the system pulls in their reports that I want to automatically bulk into a Sharepoint list
Thank you so much! I got this to work on my SharePoint list, but I need to be able to filter out rows from my Excel table. Has anyone found a way to do that with this Flow?
This was incredibly helpful, thank you! I am trying to add an Excel table that has over 1000 rows, but it looks like the resulting List only brought in 256 rows. Is there a limit to how many rows we can add to a list?
Followed this perfectly. Ran great now I get a message that “an action failed no dependent actions succeeded”.
This seems great. But I am wondering how it runs with multiple rows in the table ? "List rows" is limited to 254 unless you turn on pagination. But not sure how fast it will run then. I have data that is gathered 3 times per day with 87 columns. That make it a big data, may be list isn't the best option but that's what I have available online/sharepoint.
Extremely helpful. You literally saved me
Hi, Great video!
I have a problem, in my excel file there are some empty cells which power automate didnt liked. How can I let power automate ignore that they are empty and just copy blank cells?
This video answered questions not answered by other answers provided by a google search. Namely that one must identify the part of the spreadsheet that is a table to have access to the column names for pairing. Other answers start assuming this selection has already been made. Thanks!
This is great but what if I have a date field?
Awesome, but could you share with us share points rules related to the excel file uploaded..like ..
Shall be saved in Doc library
Shall be create a site for that file..etc
Great tip, Jonathan! What would you recommend for lists and files with more than 10k rows, this process would be veeeery slow, is there another way of doing the same thing faster, please?
+1
If you select the 'more options' on the loop you can go into the settings and change the Concurrency Control to 50 which allows 50 loops (rows) to run at the same time. It doesn't solve the major issue but speeds up the process a bit.
How does this handle missing values in a column? If the user forget to put a value in the status column while creating a new row, is it going to transfer the row with the inserted values and with the blank value in the status column?
what happens if there are same items twice in the title column. like another row with data for laoptop ?
Hey, whats the limit of excel rows in a file that can be updated in SharePoint, can we update more than a million plus rows from excel into SharePoint.
It works well only the first 100 items (rows) Do I have to do anything else?
Great! I had an issue when trying creating my flow. I am getting error message stating it cannot convert String to date type. In My dataset a have two columns formated as dates, and one column as custom, it's storying dates and hours (2023-06-23 01:50). I removed the column from the flow (steps: update and create itens ) and it worked. What should I do to make it work including the columns I've mentioned.
I had this issue too and found that you can fix it by going to the first action "List rows present in table" click "Show advanced options" then select "ISO 8601" in the "DateTime Format"
Let me know if it works :)
would this work with CSV file?
Any idea why in the new interface it wont select the correct data, but in the old interface it selects the right information. I.E.,
old designer,
Items('For_each')?['EXCELINFO']
works fine but in new designer selecting the excel items,
Items()?['EXCELINFO']
I followed this step by step-- everything matches, but I cannot get it to create anything to the list. It goes through the conditions, then under the 'false' create item-- it says NotSpecified. That step of create item isn't tough-- but I cannot get it to work.
Thoughts?
From all the threads, i dont think updating a SP list from an Excel source is that straight forward. I replicated a similar scenario, but my flow error mentions Status column does not play nice using boolean (y/n) columns. It failed to 'create item'. Back to the drawing board. Thx anyway for your video.
What about going the other direction, getting a SharePoint List to update an Excel table? Basically the ability to keep them in synch with each other. Thanks :)
This could be done in both ways? Update an Excel from a SharePoint List, and the way that ur explaining in the video?
how about this issue? I have multiple columns that are empty, can you help me wih this?
OpenApiOperationParameterTypeConversionFailed. The 'inputs.parameters' of workflow operation 'Create_item' of type 'OpenApiConnection' is not valid. Error details: Input parameter 'item/field_10' is required to be of type 'Number/double'. The runtime value '""' to be converted doesn't have the expected format 'Number/double'.
I successfully updated the SPO list using the excel but for some reason when excel record is deleted, it does not reflect on SPO list. can some help?
If a flow is made using the recurring flow, will it add duplicates or know not to add them?
I have been having issues with duplicates with this flow, as there are some empty cells in my Excel, meaning everytime the loop goes over an empty cell, it duplicates the row, regardless if there's data in the other cells in that row.
Thanks for the instruction! The only problem I am having is in the Get Items filter query. My List and Excel first column heading is "Company Name" so I typed it in as Company Name eq ' and then added the Excel File for company name from List Rows Present in a Table. However, it does not add the Select an output from Previous steps as it does in your flow. Get Items says Bad Request. Can you help me fix this?
Same here...look forward to answers
I used to have the same problem but it us important that you write: "Title eq'Title' " in the Get items Filter Query and not just the value: 'Title'
Iam saying you do it this issue. Title eq'Company name' :D copy-paste. Have a nice day
QUESTION: Please does this still work if your STATUS column on SharePoint is a lookup data type??? the flow shows successful but I don't see the values on SharePoint
I have the same issue... actually doesn't update the list with data from Microsoft?? but the flow states it completed successful.
Hi, I followed the video step exactely as you did it, however I do not get the results I were looking for. I get the "NotSpecified" error in the new create item in my list. I tried everything to make it work with not avail. Will you be able to assist me and please let me know what information you need to be able to assist me. My methodology is:
* I export data out of Dynamics 365 to excel and then use power quiry to get the data needed.
* Created Sharepoint List with the same data fields, this does include an date field.
* Setup the power automate as per your example but it will not work for the reason above mentioned.
Hi All,
Great video, I am following step by step and can't for the life of me can't get past the Action 'Get_items' It fails every time, I have check ed the path and the detestation. Any thoughts?
Same here.
@@user-nr9uy4rg5o make sure both list (excel and sharepoint) have the same type of value
Run through this a dozen times an continue to get a failure at the Control Condition. Anyone able to help?
Hi if i add another item will it work in second flow
Did this work for you? This is my current problem
I have the same issue as Jamie...successful but nothing shows in the list in sharepoint...any suggestions?
Same, I read about adding a compose to join the conditions but that didn't help either.
I was following along nicely, I found the OneDrive location and file but I ran into a problem with the sharepoint file, the App is not finding the location and file I need to update. It is only showing a general shared location on the site, not where a user is sharing a specific file with me. Any suggestions?
I believe you will only have access to the files that are stored in a shared location. You may also not be able to view the file due to permissions on the other user's end.
Did you create a site via sharepoint ?
Not sure what I did wrong but the run was successful 3 times but the list didn't update...it is blank.
I'm having the same issue.
Did you find the reason why the list is not updating? Thanks
I have followed the steps in this video and my data is not loading from Excel to List 😞
Hi! Is this possible but using excel desktop? (not excel online)
Yes, it's working normally with me
I also followed along fully and it runs without an error, but again no update to the list? Any suggestions?
Same. I read all replies and it looks like that's a common thing. No response from the creator so I'm assuming he doesn't know either.
The flow runs, but nothing gets updated on my sharepoint list, what...
Hi, I followed this and the first time it worked, but I had an issue with my Excel file so it created a ridiculous number of rows. I tried again, but now I get the error "ActionFailed. An action failed. No dependent actions succeeded." for my condition and raw data shows "expressionResult": false. I watched the video multiple times and I can't see what's wrong with my flow. Please help!
same issue
Also experiencing the same issue. The only difference I can see between the video and my environment is that there is no dynamic "value" field; rather, the dynamic "body/value" fields are all that show.
:( It's not working! All my test flows failed. I have quite a lot of data to import in the first instance - could this be the reason?
If the flow is failing it might be a connection or authentication issue. What kind of error are you getting?
@@Jonathon_Silva Hi Jonathon, thanks for your reply. The error is in the "Get Items" command. I'm getting the following error:
The expression "Training ID '22'" is not valid. Creating query failed.
My set up is a bit different to what you've shown. I might try and build it all again and see if I have better luck.