Check if anything has changed

Import an Excel spreadsheet into SharePoint lists with Microsoft Flow

Over the last few weeks I have seen quite a few people asking questions about comparing two lists, synchronising lists and importing data into SharePoint lists. Within this post I’m going to include an approach to getting all of this done with just 1 flow.

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.

When a file is create or modified (properties only) trigger

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.

Switch by status

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.

Reading the 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

Select action getting all the fields

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.

Stepping through all the steps

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.

Getting SharePoint list items and filtering by Title

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)


Check the number of items returned

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.

Check if anything has changed

[code lang=text]
@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'])


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.


11 thoughts on “Import an Excel spreadsheet into SharePoint lists with Microsoft Flow

  1. commented on January 29, 2019 by Brandon Knight

    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.

  2. commented on March 11, 2019 by Rohan Thakkar

    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,

  3. commented on April 26, 2019 by bottj1995

    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.

      • commented on April 26, 2019 by bottj1995

        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.

  4. commented on May 30, 2019 by Eliza

    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

  5. commented on January 21, 2020 by Russell Wright

    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?

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: