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.
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.
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
Enjoyed the video. I have a SharePoint list that gets updates, th e n those same changes have to be entered into another system. I have a excel template that can be uploaded. Is there a way update the template with the ID and only the items that changed? I would like for it to save the template with that days date to track changes. Can you help me with this?
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.
30 M rows and 76 column, so for you it won’t work, I just created a flow for my excel file 120k of rows, it just uploaded 512 and stop running, if you guys know something please share, see my steps below: Power Automate - Transfer Excel table to a SharePoint List. Create > Instant Cloud Flow > Manually Trigger a Flow [Flow Name] 1. Manually Trigger a flow. * Remember to turn off the ‘New Design’ on the top right to see more available options. - Click on the "..." (three dots) in the top right corner of the action. Select "Settings". - Toggle the "Pagination" option to "On". - Set the "Threshold" value to the number of rows you need (e.g., 100,000). 2. List rows present in a table. 2.1 Location: Group - LM27 2.2 Document Library: Documents. 2.3 File: /data_test/Tariffs/UNLOCODE.xlsx 2.4 Table: TGEOALL 3. For each. 3.1 Site Address: 3.2 List Name 3.4 Advanced Parameters: Match each SharePoint column name with Excel Column Name and remove 'Title'
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?
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!
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?
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?
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.
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
How would this be done if one of the items is a hyperlink? for example: I have a list of people in excel and the next column is a hyperlink displaying as " about *Person*" Following current steps in this video returns "OpenApiOperationParameterTypeConversionFailed" error stating that the item doesnt have the expected string/uri
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 :)
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']
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
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?
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.
Is it possible to do this but for updating columns? Lets say i add some columns on my excel file, is there any way to create a new column on powerlist?
this is really a great one, but i have one doubt, i have to update the SharePoint with almost 3000 items every week, sometimes we may have new items from excel, and mostly it will be the update for the existing items, how much efficient this method if i run the flow for such big count of line items?
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 :)
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.
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?
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?
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.
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 great video. Quick question i am trying to replicate with json step to parse the table before the get items. The problem i have is the Filter query seems to prevent the fields that dont exist getting passed to the No condition. It reads all the lines okay, i can see the 11 records in JSON but the filter seems prevent the new records being passed to the No of condition branch so new records not created in the list? The length condition result is false for new records but no data passed to No branch. Any ideas? Or has anybody else had a similar issue?
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'.
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?
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.
Thanks, I tried and my flow ran succesfully, the first test was doing a change, so the condition was true (yes), all the steps were OK, but my list does not show any change..., I already review my access to the list, all permisions are OK, .., do you have any comment about my issue?
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 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?
No, you can map them in Power Automate. An Issue with this automation is that it is to incorporate data to your list, but not top update it. Also it does not address the issue of having new data coming in and allowing power automate to differenciate from old data.
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.
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!
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.
You can add a separate Send Email action after the Update Item and Create Item actions, then include only the dynamic content from the action directly above it. By doing this, you will have access to the fields you are looking at specifically for the email
Thanks for the reply, Yes I was added an send email action but here the problem is I am getting all the items which is SharePoint list as updated items, I need only whatever is modified those items, for example I am edited two items out 100 items I need send email only that two items, currently I am receiving 100 items as updated items, please suggest, thanks
@@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.
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?
Thank you for this video. Super helpful and easy to follow.
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.
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.
Followed this perfectly. Ran great now I get a message that “an action failed no dependent actions succeeded”.
I am getting the same thing! Did you figure it out??
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
Can you have a short video teaching how to use Power automate to turn excel data into an excel table?
Enjoyed the video. I have a SharePoint list that gets updates, th e n those same changes have to be entered into another system. I have a excel template that can be uploaded. Is there a way update the template with the ID and only the items that changed? I would like for it to save the template with that days date to track changes. Can you help me with this?
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.
30 M rows and 76 column, so for you it won’t work, I just created a flow for my excel file 120k of rows, it just uploaded 512 and stop running, if you guys know something please share, see my steps below:
Power Automate - Transfer Excel table to a SharePoint List.
Create > Instant Cloud Flow > Manually Trigger a Flow [Flow Name]
1. Manually Trigger a flow.
* Remember to turn off the ‘New Design’ on the top right to see more available options. - Click on the "..." (three dots) in the top right corner of the action. Select "Settings".
- Toggle the "Pagination" option to "On".
- Set the "Threshold" value to the number of rows you need (e.g., 100,000).
2. List rows present in a table.
2.1 Location: Group - LM27
2.2 Document Library: Documents.
2.3 File: /data_test/Tariffs/UNLOCODE.xlsx 2.4 Table: TGEOALL
3. For each.
3.1 Site Address:
3.2 List Name
3.4 Advanced Parameters: Match each SharePoint column name with Excel Column Name and remove 'Title'
worked perfectly! my first flow btw
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?
You can't you will need to consider SQL an premium connectors.. Or even MS Fabric and Data engineering
Turn on pagination, by default excel is limited to 256 rows and SharePoint get items is limited to 100 rows.
Worked like a charm!!!
This is great but what if I have a date field?
this was extremely helpful. thank you so much
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!
Amazing 😱 you saved my life
Could you create a tutorial video or offer guidance on matching fields when I already have a unique ID field in my spreadsheet?
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?
THANK YOU!!!! This was so helpful and very clear instruction!!! you da best!
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.
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
How would this be done if one of the items is a hyperlink? for example: I have a list of people in excel and the next column is a hyperlink displaying as " about *Person*"
Following current steps in this video returns "OpenApiOperationParameterTypeConversionFailed" error stating that the item doesnt have the expected string/uri
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 :)
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']
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
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
Great video, thank you. I'll try it.
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.
Is it possible to do this but for updating columns? Lets say i add some columns on my excel file, is there any way to create a new column on powerlist?
this is really a great one, but i have one doubt, i have to update the SharePoint with almost 3000 items every week, sometimes we may have new items from excel, and mostly it will be the update for the existing items, how much efficient this method if i run the flow for such big count of line items?
go for batch update sir
@@ThreeWhiteSoldiers-jr2sy thank you. let me try
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 :)
Extremely helpful. You literally saved me
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.
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.
@@SamuelBishop-Taylor make sure both list (excel and sharepoint) have the same type of value
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?
Very helpful! Thanks for posting this vid!
It works well only the first 100 items (rows) Do I have to do anything else?
Do you have video for SharePoint List > Excel?
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.
This could be done in both ways? Update an Excel from a SharePoint List, and the way that ur explaining in the video?
What is different apply to each and for Each?
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 great video.
Quick question i am trying to replicate with json step to parse the table before the get items.
The problem i have is the Filter query seems to prevent the fields that dont exist getting passed to the No condition.
It reads all the lines okay, i can see the 11 records in JSON but the filter seems prevent the new records being passed to the No of condition branch so new records not created in the list?
The length condition result is false for new records but no data passed to No branch.
Any ideas? Or has anybody else had a similar issue?
Great !!!! Thank you very much for sharing
thank you. works great
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'.
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?
what happens if there are same items twice in the title column. like another row with data for laoptop ?
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
@@juandiegojp Same issue! any idea?
huge help ,thanks!
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?
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.
thanks a lot man, it was grate
Thanks, I tried and my flow ran succesfully, the first test was doing a change, so the condition was true (yes), all the steps were OK, but my list does not show any change..., I already review my access to the list, all permisions are OK, .., do you have any comment about my issue?
This! I cannot get anything to change. It will add, but not change
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.
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.
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.
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?
Hi Aubrey, did you manage to find a solution for the create item?
This one is best vide
The flow runs, but nothing gets updated on my sharepoint list, what...
switch to the old designer - same problem
Run through this a dozen times an continue to get a failure at the Control Condition. Anyone able to help?
does columns in the SharePoint list have to be exactly the same than excel file?
No, you can map them in Power Automate. An Issue with this automation is that it is to incorporate data to your list, but not top update it. Also it does not address the issue of having new data coming in and allowing power automate to differenciate from old data.
would this work with CSV file?
I have followed the steps in this video and my data is not loading from Excel to List 😞
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 ?
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.
After creating this flow, I need to send an email that includes only updated or created items, How do we send an email
You can add a separate Send Email action after the Update Item and Create Item actions, then include only the dynamic content from the action directly above it. By doing this, you will have access to the fields you are looking at specifically for the email
Thanks for the reply, Yes I was added an send email action but here the problem is I am getting all the items which is SharePoint list as updated items, I need only whatever is modified those items, for example I am edited two items out 100 items I need send email only that two items, currently I am receiving 100 items as updated items, please suggest, thanks
Hi! Is this possible but using excel desktop? (not excel online)
Yes, it's working normally with me
:( 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.
❤❤❤
The video doesn't follow my Automate. It is a waste of time.