@paulwestgate
This is such a common scenario and even in SharePoint Online this is not properly supported.
I am afraid the short answer is no.
Long answer
As an administrator you can access a list in the SharePoint admin center site named "DO_NOT_DELETE_SPLIST_TENANTADMIN_AGGREGATED_SITECOLLECTIONS
https://techcommunity.microsoft.com/t5/power-apps-power-automate/can-i-get-a-list-of-all-site-collec...
Another approach is to use graph api. See link in above mentioned post.
You can also use PowerShell to generate a list with sites. This provides a snapshot of the sites in time and requires special permissions. You can also run the PowerShell script using task manager (e.g., daily) and publish the results to a SharePoint list or page.
Another approach is to execute a search query and only request site collections and site using contentclass STS_Site and STS_Web. The search result list is security trimmed so users will only see sites they have read access to. You need to cater for duplicates and pagination. Plus you need to show the results in a decent way (e.g. indent subsite and show below the actual site collection).
We developed a solution where the challenges were even more complex. On-premise SharePoint, strict performance requirements, stale search index required additional validation checks, complex permission setup resulting in false positives which needed to be detected, need to exclude certain sites, .. It can be done though: see example
Summary: OOTB this is not possible. you will need to invest time/resources to address this.
Paul | SLIM Applications
