Power Automate - Flow to Add Update Delete items from Excel to SharePoint - Part 03- Full Build 

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!

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.

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 .

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.

This is Excellent! Thanks a lot Jamie for such a clear step by step flow creation. I love it!

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?

Just came upon this and it was exactly what I was looking for. Thank you!

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.

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.

Thank you for the video. It is really helpful. Works like a charm.

So grateful for you, Jamie!! You are amazing.

This is priceless! Everything I needed to my problem! Thank you so much Jamie!

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!

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

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 ?

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

Thanks for this BKM, amazing, one question at 25:10 , what value have you entered in condition 2 ?

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.

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.

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?

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.

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.

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???

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!

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! :-)

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.

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?

Excellent tutorial. Thank you.

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.

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 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!

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?

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!

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.'.

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?

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?

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.

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.

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!

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?

Hi Jamie, can I know how to get the Parse JSON Schema I try using from excel. Thanks

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!

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 , 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

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??

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.

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?

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

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 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.

The video quality makes it a little hard to follow. It's great content.

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.

Hello Jamie, In Parse JASON --> Schema : I don't now how to fill the the data Could you explain detail ? Thank you so much

Is there anyway to run this flow with an OData filter or Unique Value to help with duplication issues?

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

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?

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.

Do we have to create the Schema specifically to our dynamic content or is there a template we can use?

Amazing content !! Worth trying it ....!!

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.

Would this flow work between two sharepoint lists instead of excel to sharepoint?

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?

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!

How can this be updated so that it does not duplicate and only adds records if they are new?

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?

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.

Hi I have an issue with this flow when it runs second time adds all same data gain creating multiple same items

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

The flow works great but I continue to get duplicated items during the create item task, do you have an idea why?

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?

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.

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?

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.

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 ?

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 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.

Hi Jamie ,where do I get the jason data for paste on the schema?Thank you.

At 25.06 - you jumped a very important part - what am I adding into the intersection comparison? This is isn't shown

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

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.'."

I couldn't follow the parse Jason part at all...

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

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.

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.'.

"Invalid type. Expected Object but got Array" Still walking through what's happening & I'll reply. Feel free to pile in.

Hi Jamie, do you think this flow can be used with the following technique: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-X-urscD106Y.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.