Listing all SharePoint sites and lists used in cloud flows

While doing an interesting tenant-to-tenant migration as part of a governance case, my colleague Jukka and I we ran into a requirement to list all SharePoint sites and lists used in cloud flows. As part of the migration, SharePoint was naturally also being migrated and there was thus a need to map flows to SharePoint sites and lists. This was one of those occasions where I had a strong hunch that a flow’s flow definition would include the necessary details. Let’s dissect how we can dig out that information from our flows!

Building the flow

Before we look how the flow for sniffing out SharePoint sites and lists is built, we need to understand what a flow definition is and what it contains. A flow definition is the JSON of a cloud flow, that, for example, the Get flow as Admin connector spits out in its output. Understanding what a flow definition contains is naturally key to understanding what a cloud flow contains. While it’s definitely not required to memorize all the properties present in different flow definitions for different flows, it’s important to understand that the way to figuring out the contents of a flow is to look at its flow definition and the different properties in it. Image 1 below illustrates an example of a flow definition in a cloud flow.

Now that we understand the basics of where to look, we can go about dissecting the could flow for this use case. Whatever your trigger, the first thing we need to do is to initialize an array variable that will eventually hold the SharePoint sites and lists that we want to get our hands on. Next, we need to list all environments in our tenant so that we’re able to loop through all cloud flows in it. The connector used for doing this is the List My Environments connector found under the Power Automate Management connectors.

Next, we need to loop through all the environments in an apply to each so that we can process all environment specific cloud flows. Composing the display name of an environment in the apply to each makes it easier to diagnose flow runs. The next step is then to list all flows in an environment with the List Flows as Admin connector. This way we have our hands on all flows in an environment.

An unavoidable side effect of our flow are the nested loops that are required to get to every single individual SharePoint site and list. While I generally really hate nested loops, I’m fine using them in a background admin flow like this, since my use case doesn’t have any performance requirements for the flow. As we now have our hands on an environment (loop 1) and we’ve listed all flows in it, we need to loop through all listed flows to dissect each individual flow. This means another apply to each for listed flows (loop 2 inside loop 1).

In loop 2 for listed flows, composing the display name of a cloud flow in the loop helps diagnose flow runs. The following step is to actually get the flow being looped through with the Get Flow as Admin action as it returns the previously mentioned flow definition of a cloud flow. This is where things require finesse: We need to dig out the service property in the referencedResources array to make sure we’re extracting values of a SharePoint connector. Before we look at the output of the connector, let’s look at image 4 below to understand the actions in loop 2.

Let’s now look at an example of what the Get Flow as Admin connector spits out. Image 5 illustrates the flow definition returned by the Get Flow as Admin connector for the referencedResources array.

To get to the service property, a filter array action is used as seen in image 4. We want to filter the referencedResources array for properties that are equal to sharepoint. That way we’ll get our hands on all SharePoint related connectors in a cloud flow. In this example I’ve used the following expression as input to the array we want to filter:
outputs('Get_Flow_as_Admin')?['body/properties/referencedResources']. The property (service) or rather item to evaluate is entered using the following expression: item()?['service'].

Next, the JSON of the filter array is parsed, and the referencedResources array is looped through. This naturally means that yet another loop will form and we’re now in loop 3, which is naturally inside loop 2. And loop 2 is of course inside loop 1. Loopy loop but hey they get the job done just fine! As not every SharePoint connector is about a list, a simple expression will add N/A to the array variable’s SharePointList property if a list ID isn’t present in the JSON. The expression used is:
if(equals(item()?['resource']?['list'],null),'N/A',item()?['resource']?['list']). Loop 3 and its append to array variable action are illustrated in image 6 below.

The final actions are for documenting the site URLs and list IDs that are held in the variable. In this example the contents of the variable are added to an Excel table, from which they can be easily read.

And there we have it! Listing SharePoint site URLs and lists used in flows is actually pretty simple. If you want to take a look at the flow, you can download it from our GitHub repo. A special thanks to Daniel “Mr. Blue Badge” Laskewitz for answering some of my silly questions!

Interested in reading our latest Power Platform blog posts?

You’re welcome to subscribe to our email newsletter: Forward Forever News. Max 1 email per month, with a curated list of latest insights and articles on Power Apps, Power Automate, Power BI.

Check it out