Have you ever tried to get the all site collection details using Power Automate?
SharePoint Connector
Table of Contents
After yesterday’s news about the new actions in the SharePoint Connector, today I thought I’ll try and take this a step further.
The challenge I gave myself is can I get to all the site collections and collect the details of my site collections using a flow? the answer is yes!
Get to all your site collection details with the REST API
First of all I found a list that contains all the site collection information for my tenant.
The api to get to this is:
/_api/Web/Lists(guid’6f5b13c6-7732-47c4-b70a-8963fd7512c7′)/Items
So I added this uri to my Send an HTTP request to SharePoint action
And well the rest is easy!
As you can see above it is possible to display the Title of my site.
But you can get to more:
{
.....
"FileSystemObjectType": 0,
"Id": 1,
"ServerRedirectedEmbedUri": null,
"ServerRedirectedEmbedUrl": "",
"ContentTypeId": "0x010038A268F2641BCA4FA94C95C6CD37607A",
"Title": "Team Site",
"AllowGuestUserSignIn": true,
"Classification": null,
"ExternalSharing": "On",
"FileViewedOrEdited": 51,
"GroupId": "00000000-0000-0000-0000-000000000000",
"IsGroupConnected": false,
"LastActivityOn": "2018-04-19T00:00:00Z",
"NumOfFiles": 114,
"PageViews": 90,
"PagesVisited": 24,
"PrimaryAdmin": "Company Administrator",
"ShareByEmailEnabled": true,
"ShareByLinkEnabled": false,
"SiteId": "08c033be-cb3c-4d4b-b972-025791286235",
"SiteOwnerName": "Company Administrator",
"SiteSecondaryOwnerEmail": null,
"SiteSecondaryOwnerName": null,
"SiteUrl": "https:/ /pieterveenstramvp.sharepoint.com",
"StorageQuota": 27487790694400,
"StorageUsed": 7261335295,
"TemplateId": 1,
"TemplateTitle": "Team site (classic experience)",
"TimeCreated": "2017-07-10T13:16:48Z",
"TimeDeleted": null,
"ComplianceAssetId": null,
"State": null,
"OperationStartTime": null,
"HubSiteId": "00000000-0000-0000-0000-000000000000",
"ID": 1,
"Modified": "2018-04-23T20:50:11Z",
"Created": "2017-09-08T03:56:11Z",
"AuthorId": 1073741823,
"EditorId": 1073741823,
"OData__UIVersionString": "1.0",
"Attachments": false,
"GUID": "70424877-a75a-4acf-8bca-209a04b60cf0"
}Things like Modification dates, HubSiteId and Site Owners are extracted within no time. And how about Storage Used? All of this information could come handy one day.
This is interesting! Can you please let me know how to get the list that contains all the site collection information for my tenant.
I used the rest API to get to all the lists in my admin center:
https://tenant-admin.sharepoint.com/_api/Web/Lists?$select=Title
and then simply go through all of the lists. The list that you need has an empty Title.
DO I need to have global admin privileges to get this list? My account just has SharePoint admin rights and I donot see an empty title list. However I see this: DO_NOT_DELETE_SPLIST_TENANTADMIN_AGGREGATED_SITECOLLECTIONS. Can you please suggest?
Interesting I used to have that list:
https://veenstra.me.uk/2017/11/03/microsoft-teams-get-all-your-team-sites-using-pnp-powershell/
I wonder if this list was renamed.
I have found that list now! But the result is empty array. I am pretty sure I am using the current URL and there are thousands of site collections in our tenant. Can you help me on this?
I think that you have the wrong list.
When you go to :
https://tenant-admin.sharepoint.com/_api/Web/Lists?$select=Title
You should see quite a few entries like this:
href=”Web/Lists(guid’8822a3c0-3099-4942-9a25-caa23dec131c’)” />
One of these lists is the one you are looking for.
so I go through each one of these and visit the following url:
https://tenant-admin.sharepoint.com/_api/Web/Lists(guid'8822a3c0-3099-4942-9a25-caa23dec131c‘)/Items
then keep replacing the guid in that url until you get items back that include your site titles:
In my browser output when visiting the above url I search for Title and find something like this:
…d:Title>PMO</d:Title…
Body
{
“d”: {
“results”: []
}
}
I see the output for Send Http request to Shareoint as above. Is this the expected result? Really appreciate your time and helping me out.
That measn that nothing was foud
I am able to get the result. However I am trying to get the site collection owner names for all of the site collections retrieved. Is this possible using FLOW?
Isn’t that the Primary Admin field that you are after?
“PrimaryAdmin”: “Company Administrator”
Thank you for the response. Its actually the members in the owner group of a site that I am looking for.
How to get the Title from the list of array? How is the set variable action configured?
you can use item()?[‘Title’]
Hi Pieter, can you show us how you build the rest of the flow from the Compose to the end? At the moment I don’t understand how you extract the Ttile out of the HTTP response and put them in an array.
This post should help. it talks about how to get to the data returned by any action or trigger:
https://veenstra.me.uk/2018/12/06/microsoft-flow-the-ins-and-outs-of-triggers-and-actions/
Thanks, I used the triggerbody() expression in the Compose action but ater running the flow the input and output is empty. The output of Send HTTP request to Sharepoint however show the same results as when I visit /_api/Web/Lists(guid’8f287b97-a3a2-4b64-9b82-c313c9480235′)/Items.
What am I missing?
the triggerbody function will give you the output from the trigger only.
If you want to get the output from the HTTP request action you will need to use something like:
body(‘Send_an_HTTP_request_to_SharePoint’)
The name of the HTTP request action may of course vary, and you will need to adjust that.
Great post, this is so useful and easy to setup. I am bumping into one problem, I only get 100 items (sites) returned. Is there a limit on the http request?
Have you tried adding this to the url:
?$top=500
The default is 100 items, but you should be able to increase the number of items returned by specifying the top
Hi ,
Is there any possible to create a new site collection under main site collection. Please kindly let me know.
Thanks & Regards
Jacob
Hi Jacob,
You can create as many site collections as you want. However there isn’t such a thing as a sub site collection. You can ccreate subsites within a site collection or if you want to create hub sites as a parent site collection then that is something that is possible.
For more info on hub sites have a look at this article: https://docs.microsoft.com/en-us/sharepoint/planning-hub-sites
I’d like to get the sub sites name and its URL of my root site collection which are created based on date parameter passed. If i passed today’s date then i need to get the sub sites created today. Could you please help how to achieve that?
Do you want to get the sub sites or the site collections?
calling something like this could work:
https://mycompany.sharepoint.com/_api/search/query
?querytext=’contentclass:STS_Site contentclass:STS_Web’
&selectproperties=’Title,Path’&rowlimit=500