Get All Site Collections Of Office 365 Tenant In MS Flow

Introduction

In this article, we will learn how to create an MS flow to get a list of all site collections in a tenant. This use case can be used when we need to have a scheduled MS Flow performing some activity daily based on all site collections in the tenant.

Step 1

Create a scheduled workflow to run daily.

Go here.
Select "My flows" from the left navigation.
Select New-> Scheduled - from blank.

Step 2

Name your workflow and select schedule.

Step 3 - Get the list of site collections

We don't have any REST API directly available to get the list of all site collections in the tenant. So, we will query a list to the SharePoint admin center site which contains the list of all site collections.

List Name - DO_NOT_DELETE_SPLIST_TENANTADMIN_AGGREGATED_SITECOLLECTIONS

REST Endpoint which we will use is below. Please note that we have only filtered columns to get only site title and site URL. You can add additional columns are per your requirement.

/_api/web/lists/getbytitle('DO_NOT_DELETE_SPLIST_TENANTADMIN_AGGREGATED_SITECOLLECTIONS')/items?$select=Title,SiteUrl

Add the action 'Send an HTTP request to SharePoint'.

Step 4 - Save the MS Flow and run it once.

I will explain below why the below step is required before even completing our MS Flow. For now, run the workflow. Once run successfully, go to the flow run history and expand 'Send an HTTP request to SharePoint'.

Go to the output section of action and copy everything inside 'body'. This is the actual response received when calling the REST API.
Keep this JSON handy somewhere; we will use it in the next step.

Step 5

Now, to use SharePoint REST API response data easily in MS Flow, we will parse the JSON that we got from the above action. If you want to know more about Parse JSON and when and why it has been used, refer to this link.

Add action 'Parse JSON'
The content property of the Parse JSON action should be the response body of the above action.
Next is to add payload schema. Remember, we copied some JSON in Step 4? It is time to paste it here and click on "Done".

Step 6

Next, we will initialize two array variables to hold the Site Titles and Site URLs respectively.

Step 7

Now, we know that the above REST API will return us item collections for each site collection in the tenant. We will have to loop through each item collection. So, here what we will do is to use 'Apply to each'.

Add 'results' in *Select an output from previous steps,

Step 8

Add two actions one by one to 'Append to array variable'.

Select SiteURLs in one and SiteTitle in second as name from dynamic content
Select 'SiteUrl' in one and 'Title' in second as a value from dynamic content.

Save the flow and we are done with the design. Please note that you can always add additional actions here (inside "apply to each" loop) and perform any operation for each item. If you wanted to perform common action, you can create new action outside the loop and use array variables for any operation.

For the sake of this example, we will test the Flow and see if the array variables are assigned with values.

Save the workflow and test it by clicking 'I will perform trigger manually'.
Once the workflow has run successfully, let us go to flow history to see the output of our past actions.

Note the numbers highlighted in the circle in below screenshot. Currently, we are seeing the first element of the array and the total items are 6. This means that I would have a total of 6 site collections created in my tenant.

Below is the screenshot when we click on Next... this is the 4th element in the array.

You can use this array data further as per your requirement or you can always add actions in "Apply to each" loop itself.

This concludes our article today, we have learned the below simple things while implementing this use case.

Create a schedule MS flow
Calling REST API of SharePoint
Usage of Parse JSON action and how to get sample payload to use JSON object's name in the dynamic content window
Loop through JSON objects items array
Usage of compose, initializing an array, appending the value to an array, etc...

Thanks for reading, hope you enjoyed it..!!!

5 Comments

Join the conversation! Your thoughts help the community grow.

A o2y ago
Hello, can a variable be added that would provide site owners? Effectively I want to check my ownership across the tenant sites.. I'm hoping it's this flow with one additional variable.thanks
Alessandro Picardi3y ago
For all the people asking how to get more than 100 sites try to change the api at Step 3 like this: _api/web/lists/getbytitle('DO_NOT_DELETE_SPLIST_TENANTADMIN_AGGREGATED_SITECOLLECTIONS')/items?$select=Title,SiteUrl&$top=5000
Jeanne Darche3y ago
Hi, it works, but it only get 100 sites. I have 400 sites in my tenant. How do I get them all in the output?
Vaishali Sapkal3y ago
Good article. Thanks for sharing. I have tried all steps but its returning only 100 records, but we have more than 1000 site collections. How we will get that?
Sagar Pardeshi7y ago
How this flow will work for different time zone???