At 25:09 I've reviewed the video and I see the confusion, looks like the video jumps. I select "Current Item" (which has the section heading Apply to each - Add new and changed items and is probably right at the bottom of your list). The reason being we are iterating through the keys in the MyExcelKeys array and seeing what matches in the Intersection. Hope this helps!
Thnaks for the video :) Very helpful. you choice"current item" of what ?. In your video I see just "current item" of "apply .. SP keys" and ""current item" of "apply...delete SP keys". you dont have the choice "current item" of Excel... Here you compare Intersection with "current item" of Excel, if not add/create new records from Excel.
Thanks for sharing this, Jamie. Exactly what I needed for a challenge at work. I incorporated the formula shared below for the "Apply to each Populate SP Keys Array" issue. It also worked well with the trigger "When a file is created in a folder (deprecated) " which resulted from a different flow. Really appreciate your explanation and method of teaching .
Outstanding video and solved something we were working on today. For viewers-don’t forget enabling pagination on both the excel and get items SharePoint steps.
Hi Jamie, this is tremendous work both content- AND stylewise. I especially like how you leave time to the viewer to conduct the steps by themselves and also go into details when it comes to the nitty gritty expressions. Your Flow works for me, but only the first time. Let me get into the details of what is not working starting with the overall use case: My use case is that my enduser is to update the table content within the stored Spreadsheet. That can result in him adding or deleting rows within the spreadsheet. To finish things off, the user then diminishes or enlarges the new table accordingly. He saves it and leaves. In my case the first table I uploaded counts 21 rows and is hosted in a Onedrive folder. I save it and let the Flow run and everything works properly. I succesfully move rows from excel to SP. But now the odd things happen. I now enlarge the table to 84 rows, adjust the table accordingly, save and leave. Then the flow starts as scheduled and is now supposed to add the new rows automatically to the SP list. However here my flow crashes. To be concise it crashes at the first 'Apply to each'-loop, at the Compose Action. It runs smoothly all the way to the 21st item and crashes at the 22nd. This must have something to do with excel reckognizing the newly sized table properly. I have tried hosting the Spreadsheet both in a SP folder as well as in a Onedrive folder and it does not change the outcome of crashing. Do you have any guesses why this happens or more importantly how to solve this issue?
Thank you for the detailed feedback, this is really appreciated. In a case like this it's important to go into the run history as you see me do in Video 3 and find out exactly what happened. I don't see a reason why extra rows would cause a failure but it's essential to peek at the actual runtime data and messages to determine the issue. Have a look and let us know what you find.
This is fantastic and your tutorials are very forgiving for the novice user, like me 🙂. Also, I noticed that in 'List rows present in a table'; 'Advanced Options' there is a TimeDate Format field that can be set to 'ISO 8601', which eases the Expression items Date and Time issues in Compose steps. At least that worked for me in my Excel to SharePoint update flow.
Thank you for this video! Made my life as an analyst so much easier! Nice show piece for Management as well. Tip to those converting dates: build a separate excel sheet that feeds into SharePoint using Vlookups and =Text functions, convert everything to text and lock the sheet. Then hide the sheet, and make the Power Automate flow. No one can break the formulas, easy to format exactly as you like, and you work around those pesky date formatting issues.
I know this is a few years old (Seriously, thank you for this video - it was very helpful 2 years later!)- but I ran into a problem around 25:09. I noticed the comments said it should be "current item" but that didn't work for me. It added everything from change down and not just the current item that had a change. I fixed it for my use by using a similar process to the delete items a step before. took the excel values from an early step, composed again, parsed the json again, and then did the condition on intersection contains body of the parse json like the video shows. (for those with big files - click settings on the excel get rows and turn on pagination and raise the threshold - same with the sharepoint list) .
This is awesome.... a great learning experience.... But there is a much simpler and faster way to accomplish the same goal. Just fetch all items from the SP list and delete them all... then fetch all items from the Excel table and for each apply create new items in the SharePoint list. I have done this for a list with about 150 items and the execution time is under 3 minutes. Also a heads up if you have more than 100 items in a SP list you have to turn on pagination; otherwise you will get only the first 100 items. Again thank you for sharing this as it taught me a lot about power flow.
@@JamieMcA are you able to link to the other video that shows the 'delete all and create all new items' method? I can't seem to find it on your channel. I don't need to maintain version history, I just want to wipe what's there and replace with updated list. Cheers.
At the Apply to Each - Add new and changed Items part, you set up a condition at 25:09 in the video. For the Excel (MyExcelKeys) You have an "intersection contains ????". Was this the body of items or the value of items???
The intersection compares the values in the two arrays of objects we created. One array has all the Excel values, the other all the SP values. The Intersection compares the whole lot almost instantly, which is why we use it.
@@JamieMcA I really appreciate the video, but you have me stumped on this one. Back to the original question, what goes in that third box after IntersectionArray contains "what"? The video doesn't show you actually clicking on anything, and the result shows a "current item" of some kind.
@@mikeaimee1611 I've reviewed the video and I see the confusion, looks like the video jumps. I select "Current Item" (which has the section heading Apply to each - Add new and changed items and is probably right at the bottom of your list). The reason being we are iterating through the keys in the MyExcelKeys array and seeing what matches in the Intersection. Hope this helps!
Jamie, thanks for the content. I'd like to know if you could make a flow, that instead of deleting and adding up again the changed itens, we could just use the operation Update Item. And for the records that are not found in the excel table anymore, those would be deleted from the share point list. Thank you!
Hi Jamie, found this video very useful and it has progressed my Power Automate aptitude greatly. There is something that has proven a road block for me that I'm hoping you might be able to point me to a solution. I have a form collecting an attachment that goes to both a SharePoint list and an Excel document. I then use the flow from this video to compare the two and update as required. Unfortunately because I am not capturing the attachment in the Excel document, this gets deleted from the SharePoint list on successful completion. Any way that this can be prevented? Oh, and while I'm asking what I consider to be the tough questions, Any way to prevent the flow from breaking when trying to read an empty "Date" record? The Float function doesn't like null.. Many thank in advance
Fantastic! It worked!! Thank you so so much! Also thank you for the pinned comment below. But I got an error about the expression for the date in the excel compose. i tried the excel field instead (just like the other "normal columns") and it worked, the date ends up as a date in SP, so I dindt had to do any expression for the excel array, only for the SP one.
Hello Jaime, Thank you for sharing the information; it's been really helpful. However, I have a question regarding the process. Is it designed to remove and then re-add the data in each cycle? I am asking because when I have information on SharePoint, the intersection variable still appears as if there are no values, even though I can see values in the outputs of the variables. I would appreciate your assistance in resolving this. My goal is to update the existing data if it's present and maintain the same SharePoint ID. Additionally, I want to delete the data if it isn’t on the Excel worksheet but exists on SharePoint, or update it if the data exists in both Excel and SharePoint. Thanks in advance for your help. Best regards
Hi Jamie, Thank You very much for the video. However, I am getting the below error for the Date and Time field Flow save failed with code 'InvalidTemplate' and message 'The template validation failed: 'The inputs of template action 'Compose__-Excel_Content_' at line '1 and column '2212' is invalid. Action 'Apply_to_each' must be a parent 'foreach' scope of action 'Compose-__Excel_Content_' to be referenced by 'repeatItems' or 'items' functions.'.
The validation doesn't like something. It's a case of double checking, maybe even removing the action and re-adding it (as clearing the validation in a Flow isn't always straightforward)
Hi Jamie and All, How do I write the schema for the Parse Jason. I am currently stuck at this step as it is not mentioned how to actually write the schema. You did mention that you copy and paste the schema from somewhere else. Please kindly help me on this issue. Thanks!
Hi, thanks for the comment. I paste the schema in for brevity, but you would usually type it, following the form in the video. If you want a copy of the JSON used that's available in the Patreon group for download.
In the condition for adding or updating new items what item was selected? Was it current item or something else… the video didn’t show what you clicked on. My flow is running but not deleting. Update: the flow runs but is not deleting anything. More than happy to reach out to you via Patreon. Thank you for any response.
There is a fully finished export file in the Patreon if that would help. I can also review screenshots for you there to check for errors (within reason).
Olá JamieMcA, tudo bem com você? Sou membro do seu canal e tenho gostado muito de seus vídeos e soluções no power automate. Infelizmente, tenho tido os mesmos problemas e sem solução, inclusive, com este seu vídeo, os problemas persistem na colunas de datas, pois quando as células de datas do excel estão vazias, os erros persistem e não consigo resolver, nem com incremento de recursos nos fluxos. Neste caso, envolvendo células do excel vazias, você conseguiu solucionar?
I suggest you build some error handling into your Flow, see my Try Catch video for that; th-cam.com/video/a6rGbJXsH1U/w-d-xo.html Further you can put a condition into the expression so that it will substitute a value if the date is null.
Is there a way to do the opposite, i.e. have any changes made to a SharePoint list update an Excel spreadsheet? Ideally both flows would work in tandem so that changes can be made to either the SharePoint list OR Excel and these would be captured via the Flow.
You can operate on an Excel sheet in the same way indeed. You can use the basic of the Flow in the same way but swap around the data sources and make adjustments.
what can i do when my data in my date columns in excel is 0? (there is nothing in the cell). The formula in flow does not work for null or empty values
I've seen several examples on how to add/update/delete from Excel, but this is the most extensive explanation and it works great ! Only one question, what is the maximum records that can be used ? Is it only limited by the pagination of the List rows present in a Table ?
Jamie, Thanks so much for this video. It is amazing. Unfortunately, I do have a problem. The workflow works perfectly if I change a cell in the Excel table. It changes the SharePoint list. However, if I don't change a cell in Excel, then the row replicates in the SharePoint list. Any thoughts would be greatly appreciated.
Hi Jamie. At 3:17, in the Apply to Each step when I go to include a dynamic output. I don't have "List of Items" as an option under the List rows present in a table section. I have value - list of items, body - list of items, body/value - Item in addition to all of my column items but not "List of Items - List of items" I've associated the List rows present in a table with an excel chart in one drive like in the example of above. It seems this is what's causing the Add new or changed item step to not work as expected. It's just adding duplicate rows. I tried the value and body dynamic options but that's causing an error. Does anyone know why the "List of Items - List of items" option is not available as a dynamic options?? Please help - I'm sooo close.
Hi Jamie, Thanks for this great video. I am trying to update a column of type hyperlink. Is it possible to do it using the schema you showed or what changes can I make?
Hi Kim, I know the use of JSON is a bit complicated but it makes the solution a lot cleaner. You can type the JSON into the control following the same sort of format I used. If you want a copy of the JSON used that's available in the Patreon group for download and we also have a bunch of extra discussion and Q&A that the community has generated..
Jamie, awesome work on this. Slight issue I find.. power automate is caching my excel data, so changes are not being recognised. I have even deleted the excel file and replaced it, still PA used old day. Strange.
@@JamieMcA thanks for getting back. I left the flow over night, gave up. And during the night the flow processed and was successful every hour without updating the excel data, but never failed. Come 0000 GMT it failed saying it couldn’t find the excel table GUID.. In the morning when I saw this I replaced the original excel with a new one and updated the flow and it now works perfectly :) thank you for taking your time to reach others, much appreciated!!
At 25:00, would it make send that if “yes” to do an update to the intersection items? So if “no” then create the items but if yes then update the items?
Thank you for this fantastic solution, very efficient. My particular issue is I have a second SP List that looks up data from the SP List that is being maintained by this solution, so the delete-then-update breaks the lookup connections, the data is deleted, and I have to populate the lookup fields again. Is there a way to have this solution update without first deleting?
Have a look at this older Flow which just updates without a Delete. Could be adapted to your case. Unique key likely needed for your Excel Rows though; th-cam.com/video/qxuTVjQbECo/w-d-xo.html
Massive Learning Curve This One! Im getting all the way to the Last "Add New or Changed Items" on the Test. But Im getting a Failure: The 'inputs.parameters' of workflow operation 'Create_item_-_New_or_Changed' of type 'OpenApiConnection' is not valid. Error details: Input parameter 'item/Agreeing_x0020_Costs' is required to be of type 'Number/double'. The runtime value '""' to be converted doesn't have the expected format 'Number/double'. Ive Checked my SharePoint List, but, i can see a column name Agreeing_x0020_Costs. Any Suggestions??
I am getting the very same error listed above...I made a test change to one row within the excel file to check the delete and replace flow...it does delete the row but gets stuck on replacing it with the updated row....please help ! 😀
hello Jamie, I am getting an error from Parse JSON Excel Content: 'Invalid character after parsing property name. Expected ':' but got: ". How do I resolve this? Thank you!
Hi Everything works great except the date element. My flow runs as far as "Apply to each Populate SP Keys Array" In your video you use an expression beginning with formatDateTime yet if this is being compared to the excel Keys array, you use expression addDays. At this stage of the flow, I have tried both ways but I still can't get past this stage. Any advice.
There are several examples of this Flow saved as a template in Patreon. This might be useful as there are those without dates, for dates as days only, and also those with time. It might also be worth posting your formula in the forum there so I can take a look at what you have.
Hi @@JamieMcAThanks I have manged to get it to work by applying formula if(empty(item()?['ItemDate']), null, formatDateTime('1899-30-12', int(item()?['TestDate']),'yyyy-MM-dd')) Which was suggested in posts below. It works a treat although it takes a bit of time in the delete stage, even when there are no changes made. Anyway I have really enjoyed learning with your tuition and will join the Patreon as I know I will be using more Power automate in the future. Thanks again.
2 ปีที่แล้ว
@@raymorrison2177 Thanks! I had been trying to solve the error for a long time and you helped me to fix the formula, however it does not bring me the data when there are dates in the excel
Hello, when I use this flow and have it set to recur daily, I find that it will duplicate items that were already on the SharePoint list. Is there a way to prevent that? Also, if an item is updated instead of updating the current item, it will create a new one and keep the older version as well.
Hi Jamie, Could you please provide any resource on how to validate an excel file before proceeding like is that file already present on onedrive or not?
Thanks Jamie, I have followed everthing here. Do you have to delete and add in order to update items? are you able to use Update Item to change fields in the SP list?
Hi Jamie , its really wonderful video.. but I m getting error in 'list of rows present in a table' if I add select query...it's saying as "inner exception: Term 'supply' is not valid in a $select or $expand expression. Kindly guide this
Hi Jamie, this is really awesome, I have a question. My data is in SP not onedrive. Do I still need to compose and I have a date time field in one column but no ss. How to change the express? Thanks.
I ran into some issues with dates. Some of the value were empty and I was getting an error. I updated the formula: 1.) if(empty(item()['DateField']),null,addDays('1899-12-30',int(item()?['DateField']),'yyyy-MM-dd')) 2.) if(empty(item()['DateField']),null,formatDateTime(item()?['DateField']),'yyyy-MM-dd')) 3.)if(empty(item()['DateField']),null,item()?['DateField']) Also in List rows and Get items - go to setting and enable Pagination and increase the Threshold.
In the video 14:50, under Compose - SP Object, I cannot formatDateTime when the date is empty. I tried but it is giving me "Unable to process template language expressions....The template language function 'formatDateTime' expects its first parameter to be of type string. The provided value is type 'Null'. Does the empty(item().. need a item()? instead? I tried both but still the same error.
Excellent Video. It's working perfectly fine on my test list as well. Just one question, will it work the same way if we run it for an excel containing more than 10k records.
There are default throttle limits throughout that you'll need to change. Also the 5k list view threshold in SPO means you'll have to chunk your SharePoint queries.
I'm getting a different output format for the Date field between ExcelKeys and SharepointKeys e.g. Excel Input: "2019-12-05 16:07:11", SP Input:"2019-12-05T16:07:11Z", how would i update the AddSeconds(... formula to marry the formats up ?
thank you so much for this Jamie. I have been working on one question, that i hope you can help me with. In the second to last step the flow we iterate through the SP items and find whether they exist in the intersection array. If they do not exist in the intersection array, and are therefore not matching delete them, clearing the way for writing any new or changed items in the next step. I would like to update the items in the SP list, rather than deleting them and recreating them. I have a unique identifier for each one (in Title, on the SP list), so i know i can do this. but i don't see how to do it within the logic of this flow?
Hi Natan, this is correct. It's a bit more challenging to do it that way and you may not want to use the Intersection method at all. One of my older videos better covers your case but be aware that some of the options for a Flow have been renamed since. It can be found here; th-cam.com/video/qxuTVjQbECo/w-d-xo.html
Hello Jamie, Thanks for the demo, very nice stuff, i would like to change some Sting for a number in my excel step, but in the JSON i keep getting a error saying a number was expected but it got a string. In my excel everything seem good, i have number in cell i need. I Have change the string portion in the JSON for a number. So im a bit confuse, otherwise, if i let everyting in a string it work.
Hi Jamie, Any thoughts on how to Update items instead of When a row/cell has been updated - it just creates a new record with the update and deletes the initial one? Thanks!
I can appreciate that. Wish it were simpler. There is a completed example you can download and import to your test environment in our Patreon community.
Hi Robert, thanks for commenting. It is the current item from the MyExcelKeys array you're iterating through. We are checking for items in the Excel we need to add.
@@JamieMcA so at 25:08 what value did you select? I used "Current Item" from "Add new or changed items" however the result was that all SP list items are deleted and then created again rather than leaving the items that have not been changed. Thanks very much, excellent flow.
@@Jonathan-gd3rp I've reviewed the video and I see the confusion, looks like the video jumps. I select "Current Item" (which has the section heading Apply to each - Add new and changed items and is probably right at the bottom of your list). The reason being we are iterating through the keys in the MyExcelKeys array and seeing what matches in the Intersection. Hope this helps!
There is a Template built for this here; emea.flow.microsoft.com/en-us/galleries/public/templates/d2888cb0373811e7870df906aa521b7a/on-new-sharepoint-items-create-planner-task-and-assign-to-creator/
Yes, duplicated items mean the comparison of the JSON arrays isn't right. Possible typo. Go into the Flow Run History and see where that might be occurring.
Everything working to a point but one of my columns is a choice field example "Category":{"@odata.type":"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference","Id":1,"Value":"samplechoice"},"Category#Id":1 and is causing the text '@odata...' to appear in the list from the create item action. Any ideas how to change the format that is held in the array and also to update the SP list?
That is a Lookup column, not a Choice. See if Flow is offering your a Value version of the field in the list of fields. Also check this out; sympmarc.com/2018/02/05/powerapps-setting-a-sharepoint-list-lookup-column/
Hi, Jamie ! Thank you very much for this masterpiece! In the JSON code of the blocks, the "Title", "Data1", "Data2"... are referred to SP List columns or excel table columns? I'm trying to use this automation for a SP list that has different column names from excel's columns. Thank you in advance!
In the JSON itself it doesn't matter what they're called. They're the same as the SP columns for understanding sake. Actually the JSON is helping you do the comparison in your Flow to see what is missing before you go to write anything to SPO.
Hello Jamie, thank you for your video. How could I create a new item when I have a lookup column in Sharepoint? My excel file has the value of the column matching with the lookup value. Thank you.
You can update the Field Value to get your value in, check this out; prairiedeveloper.com/2018/01/using-microsoft-flow-to-update-a-field-from-a-lookup-column/
Finding issue with the excel serial date, if the excel sheet has empty rows can that cause an error? Not doing time thankfully, only using the function at 5:20
Hi, this video has been really helpful and allowed me to create an updating sharepoint list. however for some reason one field is not pulling through to the Sharepoint list. Is there anyway of getting some advice on this problem?
This looks exactly what I need however I am having issues when trying to connect it to an excel file saved in Documents on Sharepoint. Is this possible with this flow or are some changes required?
Hi Jamie, thanks for this fantastic tutorial. I have question regarding this. I have different excel files on my SharePoint with same table structure. When I run this flow it update table from Excel to SharePoint but when I add a new file then previous entered data has been removed and replaces with a new one from a added Excel file on a SharePoint. Any Idea how to make this to work? Regards Igor
You're saying you want this to be additive for multiple sheets? This isn't really designed for that. Perhaps you could grab the multiple sheets in your Flow up front and merge in the array then update from there?
@@JamieMcAI have files that are been created every day with same table structure and I want to fallow Status Update on Items if there are open or closed. But there is no why that I can make this work. Can you create tutorial or share flow how to archive this?
Hi Jamie- this is great work and a great video! I have a problem though. I have a SP list with many columns that don't match the Excel spreadsheet. I want the values in these columns to remain during any updates of the matching columns. In your previous version (without JSON), there wasn't a problem. With this new version, I'm finding that it is updating the row and clearing the values from the non-matching columns. Any ideas? Thanks!
That's a tough one. Sounds like your objects will never match if you include everything. How about building the first JSON objects with just the columns that DO need comparison? Then the SP objects that get built for the actual update can be created with those extra columns that need to go up?
@@JamieMcA Thanks Jamie- I think it wasn't working because I had incorrectly set up the JSON parsing in the delete step and it was deleting everything and then of course re-creating. Seems to be working now!
@@lindt4-w2x Hi! I am a bit late for the party. I have tge same issue, where if additional columns are fille in sharepoint with next update the flow deletes and recreates the item from excel. Can you provide more details on how to parse Jason so it would not delete already existing items which have added info in other columns?
Great tutorial. However when the flow runs no data is appearing in my sharepoint list. The flow has no errors. The test and daily flows run successfully. The analytics show no errors. Any suggestions?
Thanks for the video... I tried this flow...It works well when I tried using 10 rows of data...But when I tried using 300rows of data...The data gets duplicated and the flow creates it for all the time when it is scheduled (For example: every 1 minute) please guide
Great video ! With little modifications i got my version which takes excel file from my local folder to sharepoint. Then uses that sharepoint excel to update etc. the sharepoint list. Only problem im now facing is that locks the excel file (me as the user) and doesnt even unlock it after 24hrs. So i cant make it a daily occurrance to update data from local storage. Any ideas on how to get around this issue ? So it works only once, after that fails on the locked excel file.
Hi Minun, did you manage to fix the issue? I believe I have the same or similar issue. I have a daily file with latest info. When I upload this file to sharepoint (or onedrive) and run the flow to read, is only reading the "original" file (even if I have deleted this one already) so the new files with the update are not working (same collums, same table name, file name, etc...) ideas? can point me somewhere? please/thanks.
@@RoBin-ip6fi Sadly i found no way around the locking issue. I had to use msgraph api to change the values of the sp list based on my local excel file. If i understood correctly youre trying to just delete and upload a new file with the same name and i am guessing it references to the files by id and not the name so it wont work. If you have worked with api:s you could try with the graph explorer ( developer.microsoft.com/en-us/graph/graph-explorer ). Atleast i couldnt solve my problem with just power automate.
Hey Jamie, Amazing video, for some reason when I run the create item, it doesn't recognize the items in the array, so every time I run the data i just keep getting duplicates? any ideas?
Sounds like an issue with the comparison in the Intersection. Check the JSON is identical for both Excel and SP and any formatting on e.g. dates are the same. Check the details in your run history to really zero in on what values are being compared.
@@JamieMcA Hi. if the condition of the interesection is false , how flow can know the new records ? via ID ? normally ID is for all records. Because in the Video I dont see " Add JUST new recors", maybe this is because there is a duplicate for the majority here
@@bentaybijamal6191 The key is the whole row in effect if any value has changed we tell this from the Intersection and can delete and replace i.e. update.
Any thoughts on using filter array instead of apply to each and a condition? Using Filter array - from: varExcelArray, varIntersectionArray does not contain item(). Still gets kind of slow on high count lists though. Oh and my flow does the opposite in that it automatically exports a sharepoint to a tabled excel that gets emailed.
Honestly I wrote 4 versions of this Flow before recording the video. There are multiple ways to achieve the same results with different trade offs. I try to settle on something that works and is explainable! :) Then I tend to get queries on special cases like Dates and blanks and 5k row limit which are answered in the Patreon community or extra videos.
I am getting an error from the "addseconds" expression. How do I resolve this? It says, "Unable to process template language expressions in action 'Compose_-_Excel_Content' inputs at line '1' and column '22707': 'The template language function 'float' was invoked with a parameter that is not valid. The value cannot be converted to the target type.'."
Hi, I was fighting with this for hours today. In my case, in turned out some of my records had empty date fields, and that caused the step to fail. I found a blog on using if(empty(....) with addDate/addSeconds, and it worked! www.shanebart.com/ms-flow-excel-dates/
@@daman1220 As it happens I've been working on a video on the same principle. A few folks in the Patreon group asked about this and we got a formula together.
Hi Jamie, this is Amazing! I only want to update existing items or add/create new items (not delete anything). Is this possible using your existing flow and by taking out some elements or do I need to do all of what is in the video? Any help is much appreciated! :-)
Taking out the delete kinda stops the updates. This older video does updates without delete due to having a key value in your Excel; th-cam.com/video/qxuTVjQbECo/w-d-xo.html&t
Hi Jamie, My excel and SharePoint list contain around 4000 records and it took around 4 hours to run, is that reasonable? Moreover, what if the number of records exceeds 5,000? The solution will only cover the first 5000 records, correct?
Yes, if greater than 5k you have to chunk any operations with SPO due to list view threshold. With 4k it is slow yes, but you can speed it up by parallel running Flows, but very hard to show that technique in a tutorial.
Hi Jamie, I am facing issue with flow, if key doesn't matches in comparison it deleting data from SharePOint List. Could you provide any guidance on this. I have cross verified last 2 for each of the flow..it consist contain condition
Hi, not sure I follow. For the Add/Updare/Delete version of the Flow it does indeed delete non matching items as they are considered to have been removed. Maybe you could explain the situation a little more?
at 25:08 - when you do the Condition and check the intersection, what is the value you compare against? the Excel Title? You do this too quickly in the video, I'm getting errors and concerned its with this part.
The intersection is checking the two collections of objects. One collection has all the Excel values as objects, the other the SharePoint values as objects. It is a rapid way to find what has changed.
@@andrecruz6121 Please see my reply above. It is the whole object values from Excel and SP that are being compared. Therefore all column changes are captured in a single comparison.
@@JamieMcA i think what they are saying and is my exact same question... what item did select "specifically for Intersection contains ?????. Was it the Value List of Items or the Body List of Items???
I had the same question, it was way too fast in the video. I could not save the Flow because of a "invalid template" error. Just in case someone else (still) needs it, picking "current item" under "add new or changed" solved it for me. Renaming steps as instructed really made a difference here.
Your videos are done really well, kudos! I just have one question. How would you build your flow if you were to use "When a file is created or modified (properties only) trigger? I have a contains condition upfront to check if the filename contains a particular word. If yes, than i start with your example. The files that come into the folder have similar names but are dated and have the same schema... After using Identifier for File name in "List rows present in a table" what do i do from Apply to each?
This approach really needs a table defined in the Excel file, though there have been workarounds posted by John Lui to get around that. When you say same schema the table is also in there? I think using this with such dynamic content is a tough ask.
thank you so much for this. I am following it very closely. and i have two major questions. 1) does order of items in the the excel table and the sharepoint list matter, or does the intersection function match them up regardless? and 2) my list is much more complex than yours. It has 14 columns, and about 1200 records. when i ran it (only through step 4, building the entire excel array (after the parse JSON and append steps under apply to each) it took 3 minutes, but it looked like it only returned 256 records. Are there limits here that i am running into. If that's the case, then i will simply overwrite the list every time, but i was hoping to avoid that, and make it a more automated workflow. maybe that's a mistake. overwriting the list wouldn't hurt that much at this point. the createdate , modifydate issues don't impact me currently, and i guess as long as i keep that in mind it doesn't really matter.
I just found an answer to this! The List rows present in a table has a threshold of 256 by default to improve flow performance. You can increase that threshold (up to 5000 I believe) by clicking on the ellipses, going the setting of "List row present in a table", turning on Pagination and setting a higher threshold.
Thank you so much for such amazing tutorial! it is really helpful to bypass the date issue. Here are some interesting inquiries: 1. What if some of the "date" in the excel or sharepoint list is empty? I ran a demo version test, and it wasn't able to process those data: "Unable to process template language expressions in action 'Compose__-Excel_Content' inputs at line '1' and column '22820': 'The template language function 'int' was invoked with a parameter that is not valid. The value cannot be converted to the target type.'". or "Unable to process template language expressions in action 'Compose-__SP_Object' inputs at line '1' and column '22820': 'The template language function 'formatDateTime' expects its first parameter to be of type string. The provided value is of type 'Null'." 2. One of the person below is asking about larger than 5000 rows process. Do you have any advise or ideas about how to run an excel or access to update the list? 3. I tried with 1300 rows, sorry, was just curious, and it took about an hour to run the flow. Is there a way to run the process faster? or possible JSON script that can help to speed it up? I saw one post mentioned API, i think that might be too advanced. :) Anyway, I will be a patron for sure, since you are so awesome providing knowledge to the community! Thank you so much!
You'll get that template language error message if you rename those compose variable actions after you've created it. I had to select the value for the output steps and the dynamic content associated in the compose function for the error to resolve itself.
Hi Jamie, Thanks for the video, its very useful. I was using similar flow but i am getting error in get items as " The API 'sharepointonline' returned an invalid response for workflow operation 'Get_items' of type 'OpenApiConnection'. Error details: 'The API operation 'GetItems' is missing required property 'body/value/5/Title'.'" could you please help? where I am going wrong.
We have 5000 k items, it can update till 120 but if more than then it takes more than 24hours but it keep running but it's not updating max it can 2000
This was stress tested for a few hundred items. If you have more, it can be done but it increases the complexity of the Flow. Not suitable for videos! :)
Thank you for this tutorial. I have had issues on the first compose action when trying to pass a date. Any thoughts ? ConvertTimezone(addDays('1899-12-30',item()?['Submitted']),'GMT Standard Time','W.Europe Standard Time') Unable to process template language expressions in action 'Compose_-_Excel_Content' inputs at line '1' and column '10782': 'The template language function 'addDays' expects its second parameter to be an integer. The provided value is of type 'String'. Please see aka.ms/logicexpressions#adddays for usage details.'.
@@christopherlove8878 Hi I have the same problem with empty data I am using this formula but I get the same error that you mention in the comment if you could solve it I appreciate if you can help me. This is the formula if(empty(items('Apply_to_each_2')?['Date of Birth']),'',addDays('1899-12-30',int(items('Apply_to_each_2')?['Date of Birth']),'yyyy-MM-dd'))
Hi Jamie, do you think this flow can be used with the following technique: th-cam.com/video/X-urscD106Y/w-d-xo.htmlsi=qHY62iYmICr7N7VU accelerating the querying of items in large SP lists? Ive been trying myself unsuccessful so far (im kind of new to flows). Thank you.
![[FINALE] 💸ที่แท้พ่อเป็นผู้ถือหุ้นบริษัทพันล้าน เขากลับใจตอนนี้ก็สายไปแล้ว🙇🏻♂️#ละครคุณธรรม #short](http://i.ytimg.com/vi/yFwgjTFISUI/mqdefault.jpg)
At 25:09 I've reviewed the video and I see the confusion, looks like the video jumps. I select "Current Item" (which has the section heading Apply to each - Add new and changed items and is probably right at the bottom of your list). The reason being we are iterating through the keys in the MyExcelKeys array and seeing what matches in the Intersection. Hope this helps!
Thnaks for the video :) Very helpful. you choice"current item" of what ?. In your video I see just "current item" of "apply .. SP keys" and ""current item" of "apply...delete SP keys". you dont have the choice "current item" of Excel... Here you compare Intersection with "current item" of Excel, if not add/create new records from Excel.
@@bentaybijamal6191 Please see the pinned comment above that covers this point.
Thank god you cleared this up!
Thanks for sharing this, Jamie. Exactly what I needed for a challenge at work. I incorporated the formula shared below for the "Apply to each Populate SP Keys Array" issue. It also worked well with the trigger "When a file is created in a folder (deprecated) " which resulted from a different flow. Really appreciate your explanation and method of teaching .
Hi when I create this flow, it brings same data when it runs second time creating multiple same data in list any solution
@@jareermohammed6349 how you resolved this issue
Outstanding video and solved something we were working on today. For viewers-don’t forget enabling pagination on both the excel and get items SharePoint steps.
Hi Jamie,
this is tremendous work both content- AND stylewise. I especially like how you leave time to the viewer to conduct the steps by themselves and also go into details when it comes to the nitty gritty expressions.
Your Flow works for me, but only the first time. Let me get into the details of what is not working starting with the overall use case:
My use case is that my enduser is to update the table content within the stored Spreadsheet. That can result in him adding or deleting rows within the spreadsheet. To finish things off, the user then diminishes or enlarges the new table accordingly. He saves it and leaves.
In my case the first table I uploaded counts 21 rows and is hosted in a Onedrive folder. I save it and let the Flow run and everything works properly. I succesfully move rows from excel to SP.
But now the odd things happen. I now enlarge the table to 84 rows, adjust the table accordingly, save and leave. Then the flow starts as scheduled and is now supposed to add the new rows automatically to the SP list. However here my flow crashes. To be concise it crashes at the first 'Apply to each'-loop, at the Compose Action. It runs smoothly all the way to the 21st item and crashes at the 22nd.
This must have something to do with excel reckognizing the newly sized table properly.
I have tried hosting the Spreadsheet both in a SP folder as well as in a Onedrive folder and it does not change the outcome of crashing.
Do you have any guesses why this happens or more importantly how to solve this issue?
Thank you for the detailed feedback, this is really appreciated. In a case like this it's important to go into the run history as you see me do in Video 3 and find out exactly what happened. I don't see a reason why extra rows would cause a failure but it's essential to peek at the actual runtime data and messages to determine the issue. Have a look and let us know what you find.
Hi @@JamieMcA,
thank you for your reply, highly appreciated you taking the time.
I will retry and will let you know.
This is fantastic and your tutorials are very forgiving for the novice user, like me 🙂. Also, I noticed that in 'List rows present in a table'; 'Advanced Options' there is a TimeDate Format field that can be set to 'ISO 8601', which eases the Expression items Date and Time issues in Compose steps. At least that worked for me in my Excel to SharePoint update flow.
Thank you for this video! Made my life as an analyst so much easier! Nice show piece for Management as well.
Tip to those converting dates: build a separate excel sheet that feeds into SharePoint using Vlookups and =Text functions, convert everything to text and lock the sheet. Then hide the sheet, and make the Power Automate flow. No one can break the formulas, easy to format exactly as you like, and you work around those pesky date formatting issues.
Just came upon this and it was exactly what I was looking for. Thank you!
You're very welcome!
I know this is a few years old (Seriously, thank you for this video - it was very helpful 2 years later!)- but I ran into a problem around 25:09. I noticed the comments said it should be "current item" but that didn't work for me. It added everything from change down and not just the current item that had a change. I fixed it for my use by using a similar process to the delete items a step before. took the excel values from an early step, composed again, parsed the json again, and then did the condition on intersection contains body of the parse json like the video shows. (for those with big files - click settings on the excel get rows and turn on pagination and raise the threshold - same with the sharepoint list) .
This is Excellent! Thanks a lot Jamie for such a clear step by step flow creation. I love it!
This is awesome.... a great learning experience.... But there is a much simpler and faster way to accomplish the same goal.
Just fetch all items from the SP list and delete them all... then fetch all items from the Excel table and for each apply create new items in the SharePoint list.
I have done this for a list with about 150 items and the execution time is under 3 minutes.
Also a heads up if you have more than 100 items in a SP list you have to turn on pagination; otherwise you will get only the first 100 items.
Again thank you for sharing this as it taught me a lot about power flow.
Hi Levente, I have some other videos that show that method too, and others that do a true update and maintain version history.
@@JamieMcA are you able to link to the other video that shows the 'delete all and create all new items' method? I can't seem to find it on your channel. I don't need to maintain version history, I just want to wipe what's there and replace with updated list. Cheers.
So grateful for you, Jamie!! You are amazing.
Very kind of you, hope you continue to enjoy the content.
At the Apply to Each - Add new and changed Items part, you set up a condition at 25:09 in the video. For the Excel (MyExcelKeys) You have an "intersection contains ????". Was this the body of items or the value of items???
The intersection compares the values in the two arrays of objects we created. One array has all the Excel values, the other all the SP values. The Intersection compares the whole lot almost instantly, which is why we use it.
@@JamieMcA current items of what? you have used
@@JamieMcA I really appreciate the video, but you have me stumped on this one. Back to the original question, what goes in that third box after IntersectionArray contains "what"? The video doesn't show you actually clicking on anything, and the result shows a "current item" of some kind.
@@mikeaimee1611 I've reviewed the video and I see the confusion, looks like the video jumps. I select "Current Item" (which has the section heading Apply to each - Add new and changed items and is probably right at the bottom of your list). The reason being we are iterating through the keys in the MyExcelKeys array and seeing what matches in the Intersection. Hope this helps!
Jamie, thanks for the content. I'd like to know if you could make a flow, that instead of deleting and adding up again the changed itens, we could just use the operation Update Item. And for the records that are not found in the excel table anymore, those would be deleted from the share point list. Thank you!
Thank you for the video. It is really helpful. Works like a charm.
Hi Jamie, found this video very useful and it has progressed my Power Automate aptitude greatly. There is something that has proven a road block for me that I'm hoping you might be able to point me to a solution. I have a form collecting an attachment that goes to both a SharePoint list and an Excel document. I then use the flow from this video to compare the two and update as required. Unfortunately because I am not capturing the attachment in the Excel document, this gets deleted from the SharePoint list on successful completion. Any way that this can be prevented?
Oh, and while I'm asking what I consider to be the tough questions, Any way to prevent the flow from breaking when trying to read an empty "Date" record? The Float function doesn't like null..
Many thank in advance
I need to ponder your first question a while. May I suggest my error handling Try Catch video for the breaking flow though?
This is priceless! Everything I needed to my problem! Thank you so much Jamie!
Fantastic! It worked!! Thank you so so much!
Also thank you for the pinned comment below.
But I got an error about the expression for the date in the excel compose. i tried the excel field instead (just like the other "normal columns") and it worked, the date ends up as a date in SP, so I dindt had to do any expression for the excel array, only for the SP one.
Hello Jaime,
Thank you for sharing the information; it's been really helpful. However, I have a question regarding the process. Is it designed to remove and then re-add the data in each cycle? I am asking because when I have information on SharePoint, the intersection variable still appears as if there are no values, even though I can see values in the outputs of the variables.
I would appreciate your assistance in resolving this. My goal is to update the existing data if it's present and maintain the same SharePoint ID. Additionally, I want to delete the data if it isn’t on the Excel worksheet but exists on SharePoint, or update it if the data exists in both Excel and SharePoint.
Thanks in advance for your help.
Best regards
Hi Jamie, Thank You very much for the video. However, I am getting the below error for the Date and Time field
Flow save failed with code 'InvalidTemplate' and message 'The template validation failed: 'The inputs of template action 'Compose__-Excel_Content_' at line '1 and column '2212' is invalid. Action 'Apply_to_each' must be a parent 'foreach' scope of action 'Compose-__Excel_Content_' to be referenced by 'repeatItems' or 'items' functions.'.
The validation doesn't like something. It's a case of double checking, maybe even removing the action and re-adding it (as clearing the validation in a Flow isn't always straightforward)
Hi Jamie and All,
How do I write the schema for the Parse Jason. I am currently stuck at this step as it is not mentioned how to actually write the schema. You did mention that you copy and paste the schema from somewhere else.
Please kindly help me on this issue. Thanks!
Hi, thanks for the comment. I paste the schema in for brevity, but you would usually type it, following the form in the video. If you want a copy of the JSON used that's available in the Patreon group for download.
What would need to be changed so that only new items get created and no updates are required? I am trying that figure that one out. Thanks.
The earlier Flow videos I created cover this case. It's actually very easy to achieve.
Thanks', I followed the video and each step but got an error 'Apply_to_each' contains invalid expression(s), and as a result, I can't save the flow.
In the condition for adding or updating new items what item was selected? Was it current item or something else… the video didn’t show what you clicked on. My flow is running but not deleting.
Update: the flow runs but is not deleting anything. More than happy to reach out to you via Patreon. Thank you for any response.
There is a fully finished export file in the Patreon if that would help. I can also review screenshots for you there to check for errors (within reason).
Excellent tutorial. Thank you.
Olá JamieMcA, tudo bem com você? Sou membro do seu canal e tenho gostado muito de seus vídeos e soluções no power automate. Infelizmente, tenho tido os mesmos problemas e sem solução, inclusive, com este seu vídeo, os problemas persistem na colunas de datas, pois quando as células de datas do excel estão vazias, os erros persistem e não consigo resolver, nem com incremento de recursos nos fluxos. Neste caso, envolvendo células do excel vazias, você conseguiu solucionar?
I suggest you build some error handling into your Flow, see my Try Catch video for that; th-cam.com/video/a6rGbJXsH1U/w-d-xo.html Further you can put a condition into the expression so that it will substitute a value if the date is null.
Is there a way to do the opposite, i.e. have any changes made to a SharePoint list update an Excel spreadsheet? Ideally both flows would work in tandem so that changes can be made to either the SharePoint list OR Excel and these would be captured via the Flow.
You can operate on an Excel sheet in the same way indeed. You can use the basic of the Flow in the same way but swap around the data sources and make adjustments.
Thanks for this BKM, amazing, one question at 25:10 , what value have you entered in condition 2 ?
I had issues seeing that too. I scrolled to the bottom and there is the apply to each that we created for this step. I used this.
@@samjames1 Hi. if the condition of the interection is false , how flow can know the new records ? via ID ? normally ID is for all
Same issue, big piece left out, anyone find out the answer? It looks like it was edited out and then jumped to the next step.
what can i do when my data in my date columns in excel is 0? (there is nothing in the cell). The formula in flow does not work for null or empty values
Make the formula into an IF statement to check for empty and substitute something suitable.
I've seen several examples on how to add/update/delete from Excel, but this is the most extensive explanation and it works great !
Only one question, what is the maximum records that can be used ? Is it only limited by the pagination of the List rows present in a Table ?
It can be large scale with paging, but is limited with throttling by default. Don't have the precise number to hand.
Jamie, Thanks so much for this video. It is amazing. Unfortunately, I do have a problem. The workflow works perfectly if I change a cell in the Excel table. It changes the SharePoint list. However, if I don't change a cell in Excel, then the row replicates in the SharePoint list. Any thoughts would be greatly appreciated.
Is anything about the data e.g. date format being changed when written to SP?
Hi Jamie. At 3:17, in the Apply to Each step when I go to include a dynamic output. I don't have "List of Items" as an option under the List rows present in a table section. I have value - list of items, body - list of items, body/value - Item in addition to all of my column items but not "List of Items - List of items" I've associated the List rows present in a table with an excel chart in one drive like in the example of above. It seems this is what's causing the Add new or changed item step to not work as expected. It's just adding duplicate rows. I tried the value and body dynamic options but that's causing an error. Does anyone know why the "List of Items - List of items" option is not available as a dynamic options?? Please help - I'm sooo close.
Hi Jamie,
Thanks for this great video. I am trying to update a column of type hyperlink. Is it possible to do it using the schema you showed or what changes can I make?
Hello Jamie,
In Parse JASON --> Schema : I don't now how to fill the the data
Could you explain detail ?
Thank you so much
Hi Kim, I know the use of JSON is a bit complicated but it makes the solution a lot cleaner. You can type the JSON into the control following the same sort of format I used. If you want a copy of the JSON used that's available in the Patreon group for download and we also have a bunch of extra discussion and Q&A that the community has generated..
Jamie, awesome work on this. Slight issue I find.. power automate is caching my excel data, so changes are not being recognised. I have even deleted the excel file and replaced it, still PA used old day. Strange.
Hi, that is a strange one. Flow runs are stateless. Please check your initial step and that you are querying the Excel sheet that you think you are.
@@JamieMcA thanks for getting back.
I left the flow over night, gave up. And during the night the flow processed and was successful every hour without updating the excel data, but never failed. Come 0000 GMT it failed saying it couldn’t find the excel table GUID..
In the morning when I saw this I replaced the original excel with a new one and updated the flow and it now works perfectly :) thank you for taking your time to reach others, much appreciated!!
At 25:00, would it make send that if “yes” to do an update to the intersection items? So if “no” then create the items but if yes then update the items?
Thank you for this fantastic solution, very efficient. My particular issue is I have a second SP List that looks up data from the SP List that is being maintained by this solution, so the delete-then-update breaks the lookup connections, the data is deleted, and I have to populate the lookup fields again. Is there a way to have this solution update without first deleting?
Have a look at this older Flow which just updates without a Delete. Could be adapted to your case. Unique key likely needed for your Excel Rows though; th-cam.com/video/qxuTVjQbECo/w-d-xo.html
@@JamieMcA yes I already found it and it works perfectly with a few customizations for my scenarios, thank you!
Is there anyway to run this flow with an OData filter or Unique Value to help with duplication issues?
oData filters are indeed built in.
Massive Learning Curve This One! Im getting all the way to the Last "Add New or Changed Items" on the Test. But Im getting a Failure:
The 'inputs.parameters' of workflow operation 'Create_item_-_New_or_Changed' of type 'OpenApiConnection' is not valid. Error details: Input parameter 'item/Agreeing_x0020_Costs' is required to be of type 'Number/double'. The runtime value '""' to be converted doesn't have the expected format 'Number/double'.
Ive Checked my SharePoint List, but, i can see a column name Agreeing_x0020_Costs. Any Suggestions??
I am getting the very same error listed above...I made a test change to one row within the excel file to check the delete and replace flow...it does delete the row but gets stuck on replacing it with the updated row....please help ! 😀
@JamieMcA some help would be great.
hello Jamie,
I am getting an error from Parse JSON Excel Content: 'Invalid character after parsing property name. Expected ':' but got: ". How do I resolve this? Thank you!
Hi I have an issue with this flow when it runs second time adds all same data gain creating multiple same items
There is likely a typo in the JSON in your comparison step.
Hi Jamie, can I know how to get the Parse JSON Schema I try using from excel. Thanks
Would this flow work between two sharepoint lists instead of excel to sharepoint?
Same idea, of course. Data source doesn't matter.
Hi Everything works great except the date element. My flow runs as far as "Apply to each Populate SP Keys Array" In your video you use an expression beginning with formatDateTime yet if this is being compared to the excel Keys array, you use expression addDays. At this stage of the flow, I have tried both ways but I still can't get past this stage. Any advice.
There are several examples of this Flow saved as a template in Patreon. This might be useful as there are those without dates, for dates as days only, and also those with time. It might also be worth posting your formula in the forum there so I can take a look at what you have.
Hi @@JamieMcAThanks I have manged to get it to work by applying formula if(empty(item()?['ItemDate']), null, formatDateTime('1899-30-12', int(item()?['TestDate']),'yyyy-MM-dd')) Which was suggested in posts below. It works a treat although it takes a bit of time in the delete stage, even when there are no changes made. Anyway I have really enjoyed learning with your tuition and will join the Patreon as I know I will be using more Power automate in the future. Thanks again.
@@raymorrison2177 Thanks! I had been trying to solve the error for a long time and you helped me to fix the formula, however it does not bring me the data when there are dates in the excel
I had the same issue and I appreciate you sharing the solution . @@raymorrison2177
Hello, when I use this flow and have it set to recur daily, I find that it will duplicate items that were already on the SharePoint list. Is there a way to prevent that? Also, if an item is updated instead of updating the current item, it will create a new one and keep the older version as well.
That's sounds like a slight misconfiguration. Check the steps again.
Hi Jamie, Could you please provide any resource on how to validate an excel file before proceeding like is that file already present on onedrive or not?
Thanks Jamie, I have followed everthing here. Do you have to delete and add in order to update items? are you able to use Update Item to change fields in the SP list?
For true updates this other approach is needed; th-cam.com/video/qxuTVjQbECo/w-d-xo.html
Hi Jamie , its really wonderful video.. but I m getting error in 'list of rows present in a table' if I add select query...it's saying as "inner exception: Term 'supply' is not valid in a $select or $expand expression.
Kindly guide this
Double check your field name
Hi Jamie, this is really awesome, I have a question. My data is in SP not onedrive. Do I still need to compose and I have a date time field in one column but no ss. How to change the express? Thanks.
You're saying it's data in a SharePoint list, not an Excel in OneDrive? That's very different and indeed the date processing will be simpler.
I ran into some issues with dates. Some of the value were empty and I was getting an error. I updated the formula:
1.) if(empty(item()['DateField']),null,addDays('1899-12-30',int(item()?['DateField']),'yyyy-MM-dd'))
2.) if(empty(item()['DateField']),null,formatDateTime(item()?['DateField']),'yyyy-MM-dd'))
3.)if(empty(item()['DateField']),null,item()?['DateField'])
Also in List rows and Get items - go to setting and enable Pagination and increase the Threshold.
Totally agree, for empty dates a conditional statement is the only way forward. Good work!
In the video 14:50, under Compose - SP Object, I cannot formatDateTime when the date is empty. I tried but it is giving me "Unable to process template language expressions....The template language function 'formatDateTime' expects its first parameter to be of type string. The provided value is type 'Null'. Does the empty(item().. need a item()? instead? I tried both but still the same error.
Excellent Video. It's working perfectly fine on my test list as well.
Just one question, will it work the same way if we run it for an excel containing more than 10k records.
There are default throttle limits throughout that you'll need to change. Also the 5k list view threshold in SPO means you'll have to chunk your SharePoint queries.
I'm getting a different output format for the Date field between ExcelKeys and SharepointKeys e.g. Excel Input: "2019-12-05 16:07:11", SP Input:"2019-12-05T16:07:11Z", how would i update the AddSeconds(... formula to marry the formats up ?
I would re-check your format functions as you should be able to fix it in that step.
thank you so much for this Jamie. I have been working on one question, that i hope you can help me with. In the second to last step the flow we iterate through the SP items and find whether they exist in the intersection array. If they do not exist in the intersection array, and are therefore not matching delete them, clearing the way for writing any new or changed items in the next step. I would like to update the items in the SP list, rather than deleting them and recreating them. I have a unique identifier for each one (in Title, on the SP list), so i know i can do this. but i don't see how to do it within the logic of this flow?
Hi Natan, this is correct. It's a bit more challenging to do it that way and you may not want to use the Intersection method at all. One of my older videos better covers your case but be aware that some of the options for a Flow have been renamed since. It can be found here; th-cam.com/video/qxuTVjQbECo/w-d-xo.html
@@JamieMcA can i use the same date conversion expression in that flow? i have tried something similar and the int conversion has failed
@@zimands5 Yes the date conversion should work fine. Check the values you're getting back in the Flow Run History to see why you get a failure.
Hello Jamie, Thanks for the demo, very nice stuff, i would like to change some Sting for a number in my excel step, but in the JSON i keep getting a error saying a number was expected but it got a string. In my excel everything seem good, i have number in cell i need. I Have change the string portion in the JSON for a number. So im a bit confuse, otherwise, if i let everyting in a string it work.
Has the data type of the cells in the Excel been changed?
Hi Jamie,
Any thoughts on how to Update items instead of When a row/cell has been updated - it just creates a new record with the update and deletes the initial one? Thanks!
I couldn't follow the parse Jason part at all...
I can appreciate that. Wish it were simpler. There is a completed example you can download and import to your test environment in our Patreon community.
At 25.06 - you jumped a very important part - what am I adding into the intersection comparison? This is isn't shown
Hi Robert, thanks for commenting. It is the current item from the MyExcelKeys array you're iterating through. We are checking for items in the Excel we need to add.
@@JamieMcA so at 25:08 what value did you select? I used "Current Item" from "Add new or changed items" however the result was that all SP list items are deleted and then created again rather than leaving the items that have not been changed. Thanks very much, excellent flow.
@@Jonathan-gd3rp I've reviewed the video and I see the confusion, looks like the video jumps. I select "Current Item" (which has the section heading Apply to each - Add new and changed items and is probably right at the bottom of your list). The reason being we are iterating through the keys in the MyExcelKeys array and seeing what matches in the Intersection. Hope this helps!
@@JamieMcA got it working!! thank you so much Jamie.
You are amazing. Thank you for this great flow. I´m also looking for a flow from Sharepoint to Planner (new Task and update). Can you help me?
There is a Template built for this here; emea.flow.microsoft.com/en-us/galleries/public/templates/d2888cb0373811e7870df906aa521b7a/on-new-sharepoint-items-create-planner-task-and-assign-to-creator/
@@JamieMcA Hallo, i have found this link also, but my problem is, that i want also update the task title if the title was update on sharepoint
The flow works great but I continue to get duplicated items during the create item task, do you have an idea why?
Yes, duplicated items mean the comparison of the JSON arrays isn't right. Possible typo. Go into the Flow Run History and see where that might be occurring.
Thank you, this flow was exactly what I was looking for.
Do we have to create the Schema specifically to our dynamic content or is there a template we can use?
I put text you can just paste in and adapt in our Patreon space, plus a working export of the Flow itself.
Everything working to a point but one of my columns is a choice field example "Category":{"@odata.type":"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference","Id":1,"Value":"samplechoice"},"Category#Id":1 and is causing the text '@odata...' to appear in the list from the create item action. Any ideas how to change the format that is held in the array and also to update the SP list?
That is a Lookup column, not a Choice. See if Flow is offering your a Value version of the field in the list of fields. Also check this out; sympmarc.com/2018/02/05/powerapps-setting-a-sharepoint-list-lookup-column/
Hi, Jamie ! Thank you very much for this masterpiece! In the JSON code of the blocks, the "Title", "Data1", "Data2"... are referred to SP List columns or excel table columns? I'm trying to use this automation for a SP list that has different column names from excel's columns. Thank you in advance!
In the JSON itself it doesn't matter what they're called. They're the same as the SP columns for understanding sake. Actually the JSON is helping you do the comparison in your Flow to see what is missing before you go to write anything to SPO.
Hello Jamie, thank you for your video. How could I create a new item when I have a lookup column in Sharepoint? My excel file has the value of the column matching with the lookup value. Thank you.
You can update the Field Value to get your value in, check this out; prairiedeveloper.com/2018/01/using-microsoft-flow-to-update-a-field-from-a-lookup-column/
How can this be updated so that it does not duplicate and only adds records if they are new?
Finding issue with the excel serial date, if the excel sheet has empty rows can that cause an error? Not doing time thankfully, only using the function at 5:20
Thanks for pointing that out. Much more validation needed for Productions Flows for all examples posted here
if(empty(item()?['ItemDate']), null, addDays('12-30-1899', int(item()?['ItemDate']), 'MM-dd-yyyy'))
Hi , I have tried this. It's gud if the Excel rows are below 10...if I add 300 items it's getting duplicated for every run
Hi, this video has been really helpful and allowed me to create an updating sharepoint list. however for some reason one field is not pulling through to the Sharepoint list. Is there anyway of getting some advice on this problem?
What is the data type of the field in question? If it's lookup for example I wouldn't be hugely surprised.
This looks exactly what I need however I am having issues when trying to connect it to an excel file saved in Documents on Sharepoint. Is this possible with this flow or are some changes required?
The initial connector should be a SharePoint on, but aside from that it should indeed be possible.
Amazing content !! Worth trying it ....!!
Hi Jamie, thanks for this fantastic tutorial.
I have question regarding this. I have different excel files on my SharePoint with same table structure. When I run this flow it update table from Excel to SharePoint but when I add a new file then previous entered data has been removed and replaces with a new one from a added Excel file on a SharePoint. Any Idea how to make this to work?
Regards
Igor
You're saying you want this to be additive for multiple sheets? This isn't really designed for that. Perhaps you could grab the multiple sheets in your Flow up front and merge in the array then update from there?
@@JamieMcAI have files that are been created every day with same table structure and I want to fallow Status Update on Items if there are open or closed. But there is no why that I can make this work. Can you create tutorial or share flow how to archive this?
@@igorbulatovic1047 I'll be happy to put this on the list for a future video. Thank you for the suggestion.
Hi Jamie- this is great work and a great video! I have a problem though. I have a SP list with many columns that don't match the Excel spreadsheet. I want the values in these columns to remain during any updates of the matching columns. In your previous version (without JSON), there wasn't a problem. With this new version, I'm finding that it is updating the row and clearing the values from the non-matching columns. Any ideas? Thanks!
That's a tough one. Sounds like your objects will never match if you include everything. How about building the first JSON objects with just the columns that DO need comparison? Then the SP objects that get built for the actual update can be created with those extra columns that need to go up?
@@JamieMcA Thanks Jamie- I think it wasn't working because I had incorrectly set up the JSON parsing in the delete step and it was deleting everything and then of course re-creating. Seems to be working now!
@@lindt4-w2x Hi! I am a bit late for the party. I have tge same issue, where if additional columns are fille in sharepoint with next update the flow deletes and recreates the item from excel. Can you provide more details on how to parse Jason so it would not delete already existing items which have added info in other columns?
Great tutorial. However when the flow runs no data is appearing in my sharepoint list. The flow has no errors. The test and daily flows run successfully. The analytics show no errors. Any suggestions?
Check the If condition where you choose to write an item. What is the result? Do the arrays contain the values they should?
@@JamieMcA thanks I will check this
Thanks for the video... I tried this flow...It works well when I tried using 10 rows of data...But when I tried using 300rows of data...The data gets duplicated and the flow creates it for all the time when it is scheduled (For example: every 1 minute) please guide
Check throttling in the query, usually 100 by default.
@@JamieMcA Thanks a lot for the reply... Please guide how to check throttling in query?
Great video ! With little modifications i got my version which takes excel file from my local folder to sharepoint. Then uses that sharepoint excel to update etc. the sharepoint list.
Only problem im now facing is that locks the excel file (me as the user) and doesnt even unlock it after 24hrs. So i cant make it a daily occurrance to update data from local storage.
Any ideas on how to get around this issue ? So it works only once, after that fails on the locked excel file.
Locking can be a real pain with Excel Online but it shouldn't be locking for that long. I do not have a fix to suggest though I'm afraid. Forum time.
Hi Minun, did you manage to fix the issue? I believe I have the same or similar issue. I have a daily file with latest info. When I upload this file to sharepoint (or onedrive) and run the flow to read, is only reading the "original" file (even if I have deleted this one already) so the new files with the update are not working (same collums, same table name, file name, etc...) ideas? can point me somewhere? please/thanks.
@@RoBin-ip6fi Sadly i found no way around the locking issue. I had to use msgraph api to change the values of the sp list based on my local excel file. If i understood correctly youre trying to just delete and upload a new file with the same name and i am guessing it references to the files by id and not the name so it wont work. If you have worked with api:s you could try with the graph explorer ( developer.microsoft.com/en-us/graph/graph-explorer ). Atleast i couldnt solve my problem with just power automate.
Hey Jamie, Amazing video, for some reason when I run the create item, it doesn't recognize the items in the array, so every time I run the data i just keep getting duplicates? any ideas?
Sounds like an issue with the comparison in the Intersection. Check the JSON is identical for both Excel and SP and any formatting on e.g. dates are the same. Check the details in your run history to really zero in on what values are being compared.
Jamie McAllister thank you so much I will
@@JamieMcA Hi. if the condition of the interesection is false , how flow can know the new records ? via ID ? normally ID is for all records. Because in the Video I dont see " Add JUST new recors", maybe this is because there is a duplicate for the majority here
@@bentaybijamal6191 The key is the whole row in effect if any value has changed we tell this from the Intersection and can delete and replace i.e. update.
Any thoughts on using filter array instead of apply to each and a condition? Using Filter array - from: varExcelArray, varIntersectionArray does not contain item(). Still gets kind of slow on high count lists though. Oh and my flow does the opposite in that it automatically exports a sharepoint to a tabled excel that gets emailed.
Honestly I wrote 4 versions of this Flow before recording the video. There are multiple ways to achieve the same results with different trade offs. I try to settle on something that works and is explainable! :) Then I tend to get queries on special cases like Dates and blanks and 5k row limit which are answered in the Patreon community or extra videos.
I am getting an error from the "addseconds" expression. How do I resolve this? It says, "Unable to process template language expressions in action 'Compose_-_Excel_Content' inputs at line '1' and column '22707': 'The template language function 'float' was invoked with a parameter that is not valid. The value cannot be converted to the target type.'."
Sounds like a typo in the expression. Can you paste your expression here?
Hi, I was fighting with this for hours today. In my case, in turned out some of my records had empty date fields, and that caused the step to fail. I found a blog on using if(empty(....) with addDate/addSeconds, and it worked! www.shanebart.com/ms-flow-excel-dates/
@@daman1220 As it happens I've been working on a video on the same principle. A few folks in the Patreon group asked about this and we got a formula together.
Hi Jamie, this is Amazing! I only want to update existing items or add/create new items (not delete anything). Is this possible using your existing flow and by taking out some elements or do I need to do all of what is in the video? Any help is much appreciated! :-)
Taking out the delete kinda stops the updates. This older video does updates without delete due to having a key value in your Excel; th-cam.com/video/qxuTVjQbECo/w-d-xo.html&t
Hi Jamie, My excel and SharePoint list contain around 4000 records and it took around 4 hours to run, is that reasonable? Moreover, what if the number of records exceeds 5,000? The solution will only cover the first 5000 records, correct?
Yes, if greater than 5k you have to chunk any operations with SPO due to list view threshold. With 4k it is slow yes, but you can speed it up by parallel running Flows, but very hard to show that technique in a tutorial.
Hi Jamie, I am facing issue with flow, if key doesn't matches in comparison it deleting data from SharePOint List. Could you provide any guidance on this. I have cross verified last 2 for each of the flow..it consist contain condition
Hi, not sure I follow. For the Add/Updare/Delete version of the Flow it does indeed delete non matching items as they are considered to have been removed. Maybe you could explain the situation a little more?
@@JamieMcA hi, thanks for your reply. I am able to understand logic
at 25:08 - when you do the Condition and check the intersection, what is the value you compare against? the Excel Title? You do this too quickly in the video, I'm getting errors and concerned its with this part.
The intersection is checking the two collections of objects. One collection has all the Excel values as objects, the other the SharePoint values as objects. It is a rapid way to find what has changed.
@@JamieMcA I am having the same problem. Jamie, what is the value that you compare in that step?
@@andrecruz6121 Please see my reply above. It is the whole object values from Excel and SP that are being compared. Therefore all column changes are captured in a single comparison.
@@JamieMcA i think what they are saying and is my exact same question... what item did select "specifically for Intersection contains ?????. Was it the Value List of Items or the Body List of Items???
I had the same question, it was way too fast in the video. I could not save the Flow because of a "invalid template" error. Just in case someone else (still) needs it, picking "current item" under "add new or changed" solved it for me. Renaming steps as instructed really made a difference here.
Your videos are done really well, kudos! I just have one question. How would you build your flow if you were to use "When a file is created or modified (properties only) trigger? I have a contains condition upfront to check if the filename contains a particular word. If yes, than i start with your example. The files that come into the folder have similar names but are dated and have the same schema... After using Identifier for File name in "List rows present in a table" what do i do from Apply to each?
This approach really needs a table defined in the Excel file, though there have been workarounds posted by John Lui to get around that. When you say same schema the table is also in there? I think using this with such dynamic content is a tough ask.
thank you so much for this. I am following it very closely. and i have two major questions. 1) does order of items in the the excel table and the sharepoint list matter, or does the intersection function match them up regardless? and 2) my list is much more complex than yours. It has 14 columns, and about 1200 records. when i ran it (only through step 4, building the entire excel array (after the parse JSON and append steps under apply to each) it took 3 minutes, but it looked like it only returned 256 records. Are there limits here that i am running into. If that's the case, then i will simply overwrite the list every time, but i was hoping to avoid that, and make it a more automated workflow. maybe that's a mistake. overwriting the list wouldn't hurt that much at this point. the createdate , modifydate issues don't impact me currently, and i guess as long as i keep that in mind it doesn't really matter.
Have you got solution for this?
I am also looking for the solution.
I am also looking for this solution
I just found an answer to this! The List rows present in a table has a threshold of 256 by default to improve flow performance. You can increase that threshold (up to 5000 I believe) by clicking on the ellipses, going the setting of "List row present in a table", turning on Pagination and setting a higher threshold.
Thank you so much for such amazing tutorial! it is really helpful to bypass the date issue. Here are some interesting inquiries:
1. What if some of the "date" in the excel or sharepoint list is empty? I ran a demo version test, and it wasn't able to process those data: "Unable to process template language expressions in action 'Compose__-Excel_Content' inputs at line '1' and column '22820': 'The template language function 'int' was invoked with a parameter that is not valid. The value cannot be converted to the target type.'". or "Unable to process template language expressions in action 'Compose-__SP_Object' inputs at line '1' and column '22820': 'The template language function 'formatDateTime' expects its first parameter to be of type string. The provided value is of type 'Null'."
2. One of the person below is asking about larger than 5000 rows process. Do you have any advise or ideas about how to run an excel or access to update the list?
3. I tried with 1300 rows, sorry, was just curious, and it took about an hour to run the flow. Is there a way to run the process faster? or possible JSON script that can help to speed it up? I saw one post mentioned API, i think that might be too advanced. :)
Anyway, I will be a patron for sure, since you are so awesome providing knowledge to the community! Thank you so much!
Happy we were able to discuss these points in the Patreon community. Great to have you join us.
You'll get that template language error message if you rename those compose variable actions after you've created it. I had to select the value for the output steps and the dynamic content associated in the compose function for the error to resolve itself.
Hi Jamie ,where do I get the jason data for paste on the schema?Thank you.
You can define that based on your own data. Remember the lesson about the commas.
@@JamieMcA please direct me which videos that I can learn about the comma. thank you for your help
Hi Jamie, Thanks for the video, its very useful. I was using similar flow but i am getting error in get items as " The API 'sharepointonline' returned an invalid response for workflow operation 'Get_items' of type 'OpenApiConnection'. Error details: 'The API operation 'GetItems' is missing required property 'body/value/5/Title'.'" could you please help? where I am going wrong.
Is the SP item with an item ID of 5 in SharePoint missing Title where Title is mandatory?
@@JamieMcA I have a similar problem. The ID is madatory in the Get Items action. What should I put?
@@JamieMcA it's working fine now.. one row was empty in excel table and the same was updated in list
@@sangeethavaratharajan8432 Great to hear! Thank you!
The video quality makes it a little hard to follow. It's great content.
We have 5000 k items, it can update till 120 but if more than then it takes more than 24hours but it keep running but it's not updating max it can 2000
This was stress tested for a few hundred items. If you have more, it can be done but it increases the complexity of the Flow. Not suitable for videos! :)
Thank you for this tutorial. I have had issues on the first compose action when trying to pass a date. Any thoughts ?
ConvertTimezone(addDays('1899-12-30',item()?['Submitted']),'GMT Standard Time','W.Europe Standard Time')
Unable to process template language expressions in action 'Compose_-_Excel_Content' inputs at line '1' and column '10782': 'The template language function 'addDays' expects its second parameter to be an integer. The provided value is of type 'String'. Please see aka.ms/logicexpressions#adddays for usage details.'.
Do you have any blank values for dates?
Yes , now you have mentioned it. This makes sense. Is there a way of working around this other than completing each cell with a date?
@@christopherlove8878 You can make the parsing conditional by adding an If statement to the expression.
Thanks Jamie
@@christopherlove8878 Hi I have the same problem with empty data I am using this formula but I get the same error that you mention in the comment if you could solve it I appreciate if you can help me. This is the formula if(empty(items('Apply_to_each_2')?['Date of Birth']),'',addDays('1899-12-30',int(items('Apply_to_each_2')?['Date of Birth']),'yyyy-MM-dd'))
Hi Jamie, do you think this flow can be used with the following technique: th-cam.com/video/X-urscD106Y/w-d-xo.htmlsi=qHY62iYmICr7N7VU accelerating the querying of items in large SP lists? Ive been trying myself unsuccessful so far (im kind of new to flows). Thank you.
"Invalid type. Expected Object but got Array" Still walking through what's happening & I'll reply. Feel free to pile in.
It's thinking you're referencing a collection when you're not. Check what variable you're referencing.