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