Over the last few weeks I have seen quite a few people asking questions about comparing two lists, synchronize lists and import an excel into SharePoint lists. Within this post I’m going to include an approach to getting all of this done with just 1 flow.
Excel Spreadsheet
In my case I’ve got a document library where I’m uploading spreadsheets. From this spreadsheet I’m going to read the data on a sheet called New. I want to control the import of my data by setting a document property. This property is called Import Status. Once this status has been set to Ready the flow will start to process my data and create SharePoint lists item or update the lists items when they already exist.
Triggering the process
To start the process I will use a trigger of the When a file is created or modified (properties only). There isn’t anything else that is needed for this.

Selecting the Ready documents only
Now that the flow knows when a file is uploaded and/or updated we will now need to check the status of the document as we only really want to process the spreadsheet once the user has given us the “OK” by setting the import status to ready. We could of course have used the trigger that starts on a selected item, but in this case I’m using the status field.

In this case I probably could use just a condition, however I might extend my flow with some additional actions in the new branch and the processed branch. I could for example email someone informing them that the process is complete when the import status is set to Processed.
Import an Excel spreadsheet
For the reading of the excel files I’m going to refer to one of my previous posts. Read large excel files within seconds without creating tables using Microsoft Graph
At this point of got an array of items available and read from Excel ready to update the data in my SharePoint list. For convenience I’m pushing the data through a select action. This way it will be easier to refer my data later on in my flow

Stepping through the items
Using the apply to each control I’m now stepping through my items. In general I find it useful to include a compose action here so that when I look through the run history I can fairly quickly see which item within the Apply to each I’m looking at. Imagine looking at 5000 item,stepping through the items can be a bit of a pain if you don’t know which item is processed. I typically use the unique field in the list that identifies item for me. For lookup lists this can be as simple as the title of the items.

Now we are ready to create SharePoint list items. Although I don’t want to end up with double list items hence, I first need to check if the list item that I’m about to create already exists or not.
Getting the SharePoint list items
Now to check the items, I’m going to collect the items that have a title matching my unique key field that can be found in the data returned by the earlier mentioned select action.

With an easy expression in a condition I can now check if an item was found or not:
@equals(length(body(‘Get_items_2’)?[‘value’]), 0)

Creating a list item is something quite basic and I’m going to ignore that branch in this post.
Updating lists items
We will need to use an update item action, but we don’t just want to update the list item. We only want to update items when there is something to update in my SharePoint list item.

@equals(concat(items('Apply_to_each')?['DDM'], items('Apply_to_each')['Polygon'], items('Apply_to_each')['Chunk'], items('Apply_to_each')['Site Type'], items('Apply_to_each')['Co-Location'], items('Apply_to_each')['Operator View']), concat(items('Apply_to_each_2')?['DDMName'], items('Apply_to_each_2')?['Polygon'], items('Apply_to_each_2')?['Chunk'], items('Apply_to_each_2')?['SiteType'], items('Apply_to_each_2')?['CoLocation'], items('Apply_to_each_2')?['OperatorView']))
Ok, that is quite an expressions. A bit of an explanation could be useful here.
We’ve got two piece of data, a SharePoint list item and a record from my excel spreadsheet. The SharePoint list item is stored in items(‘Apply_to_each_2’) while the Excel spreadsheet row is stored in items(‘Apply_to_each’).
By combining all of the columns in both records, I’m creating two strings of text.
concat(items('Apply_to_each')?['DDM'], items('Apply_to_each')['Polygon'], items('Apply_to_each')['Chunk'], items('Apply_to_each')['Site Type'], items('Apply_to_each')['Co-Location'], items('Apply_to_each')['Operator View'])
and
concat(items('Apply_to_each_2')?['DDMName'], items('Apply_to_each_2')?['Polygon'], items('Apply_to_each_2')?['Chunk'], items('Apply_to_each_2')?['SiteType'], items('Apply_to_each_2')?['CoLocation'], items('Apply_to_each_2')?['OperatorView'])
now with the equals function I simply compare the two strings and I know if I need to update anything or not.
I’m not real experienced in Flow so I got a little lost in some steps. Could you share a screen shot of the full flow because I was not sure if I missed some steps. I did not understand how or why you created the compose part of the apply to each step. This is exactly the solution I needed and looks like a great solution but I got a little lost.
Hi Brandon,
Can you email me the screen shots of the flow in edit mode and run history.
please email me on contact@veenstra.me.uk
Hi Pieter,
Great post! This is what I was looking for.
Can you email me the screenshots of the flow to better understand the logic, so that I can implement it on my end?
Best Regards,
RRT
This is a great post. Can you please share the screen images with formulas?
the elements showing as item() are expressions like: item()[0] or item()[1] and so on.
Thank you.
Does this flow allow multiple excel files to be read regardless of file name?
You could run multiple actions running the graph api call if you need to run multiple files.
really basic question. How do you get the import status doc property set up. I see where you can add custom properties to excel but I don’t understand how these get populated with the values you indicated
Hi Eliza,
To get that Status field I added a column on my document library.
Minor typo:
We’ve got two piece of data. a SharePoint list item and a record form my excel spreadsheet. The SharePoint list item is stored in items(‘Apply_to_each_2’) while the Excel spreadsheet row is stored in items(‘Apply_to_each_2’).
Is the Excel spreadsheet row items(‘Apply_to_each’) – without the _2?
Thank you Russell. I’ve now fixed that in the post.
Hi Pieter,
This is exactly I am looking for, my use case is user update/modifies the excel sheet on sharepoint. Once that is done, the flow should update the sharepoint list. Could you pleases share the complete screenshot becasue I am a bit lost.