Check if anything has changed

Import an Excel spreadsheet into SharePoint lists with Power Automate

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.

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.

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

Select action getting all the fields and Import an Excel spreadsheet

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

12 thoughts on “Import an Excel spreadsheet into SharePoint lists with Power Automate

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

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

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

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

Leave a Reply

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

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 4,909 other subscribers

Recent Posts

Archive

Thank you for visiting SharePains