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.

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.