If you’re like me, you like to keep a site register for your users so they can see who the site owners are. Of course, this information is available for SharePoint Online administrators in the SharePoint administration center, but there isn’t a dashboard available for end users.
In Power Automate, we can query a SharePoint list which captures all the sites that are created in SharePoint Online. From here, we can update another SharePoint list that is available for all users.
To get started, create a new Power Automate flow with the When an item is created trigger. Set the site address to your SharePoint admin URL. For example, https://tenant-admin.sharepoint.com. The List Name is DO_NOT_DELETE_SPLIST_TENANTADMIN_ALL_SITES_AGGREGATED_SITECOLLECTIONS.
This does not populate in real time. You will get notified that a site has been created hours after the fact, and in most cases in batches of sites. However, this call gives you the site ID (GUID), the site URL, site template, who created the site, and when the site was last modified.
Since we can query when the site was last modified, I have also created another flow that runs every night that queries all the sites that have been modified in the last 24 hours. I then compare the two lists (the admin list and my own list) for changes and update my list when a site owner has changed. In my below example, I have omitted OneDrive for Business sites (i.e. site templates that equal 21).
_api/Web/Lists(guid'<GUID>')/Items?$select=Title,SiteId,SiteOwnerName,SiteOwnerEmail,SiteUrl,TemplateName,Created,CreatedBy,Modified&$filter=TemplateId ne 21 and (Modified ge 'formatDateTime(addDays(utcNow(),-1),'yyyy-MM-ddThh:mm:ssZ')' and Modified lt 'utcNow()')&$top=5000
The possibilities of what you can do with the site admin list is endless. All you need is to be able to query the list and go from there!