Compare 2 SharePoint libraries at lightning speed with Power Automate

Yesterday Jon Levesque asked me if I had any posts helping to compare two SharePoint document libraries with Power Automate.

Some compare background

Speed is everything!

If you need to compare two document libraries with 100s or 100s of documents you could loop through the documents in one library and then compare them with another library but that isn’t going to perform at all.

So we need a quicker way to compare two libraries. The solution in flow is simple (fast flows are always simple!)

The approach to comparing SharePoint document libraries

To make this flow perform I will create a flow that first collects all the files from the two libraries. Then the second step will be to remove any information about the files other than the file names.

So in short I just want to get a list of file names. Actually I want to get two lists of filenames. One for each library

Then when I have the two lists I need to very quickly compare the two lists.

How many people would now use an apply to each? Well you shouldn’t! unless you like to make your self a cup of coffee and while your at it make a coffee for all your colleagues.

An Apply to each is not the right option!

The implementation

The approach that I went for is quite simple. All I need in my shopping basket is:

• 1 trigger
• 2 Send an HTTP request to SharePoint actions
• 2 Select actions
• 1 Filter action

Ok, this is great! 0 seconds and I’ve got my output. So how was this done?

I’m using a manual trigger to start my flow, but you could also schedule this flow to run every hour or whatever you need it to be.

Get all files in a Library

To get all the files in my libraries I’m configuring my Send an HTTP request to SharePoint action using the SharePoint REST API as shown below. My two document libraries are called Library A and Library B.

The site address is the site where my libraries live. The Method is set to GET. and the uri is _api/web/lists/getbytitle(‘Library A’)/files

For Library B I’m repeating the same step. Just update the name of the library in the URI and you’re done for both Document Libraries!

Ok, that is the first part of 0 seconds spent already. We better hurry up!

Now the select actions for both document libraries, but before I look at the selects I want to have a look at the junk that the HTTP requests give me. Remember I just want the file names of each of the files.

I will simplify this junk a bit so that it is easier to understand the next steps.

{
  "d": {
    "results": [
      {
        ...,
        "Name": "Filename.xyz",
        ...
      }
      ,
      {
        ...,
        "Name": "Filename2.xyz",
        ...
      }
    ]
  }
}

We now want to end up with an array that looks like this:

[
  "Filename.xyz",
  "Filename2.xyz"
]

Using the select to transform json

For both Select actions we will use the following expression in the from setting:

outputs('Send_an_HTTP_request_to_SharePoint_-_Library_A')?['body/d/results']

And for the mapping we use the item function. The item function gives you the item that is currently being processed.

item()?['Name']

For Library B just repeat the same step just replace the library name in the from configuration.

Now we are nearly there! time for some flow magic!

Filter the right items

I’m going to use a filter action with the following configuration:

From: body(‘Select_-_Library_A’)

Filter options:

• join(body(‘Select_-_Library_B’), ‘;’)
• contains
• item()

And now my filter will select give me all the file names of the documents in Library A and exist in Library B and all within less than 1 second. Did anybody say that flows do not perform so well?

Other Performance posts

https://sharepains.com/2020/02/10/1-flow-performance-tip-to-get-your-flows-out-of-the-stone-age/

https://sharepains.com/2019/06/25/performance-in-powerapps/

https://sharepains.com/2018/10/15/microsoft-flow-improve-your-flows-performance-in-a-few-easy-steps/