Using Power Automate to create a list of SharePoint sites you have access to

Below are the steps for creating the flow.

  1. Create a table in an Excel Spreadsheet and save to OneDrive

2. Create a Power Automate flow with a manual trigger.

Add an Action Send an HTTP request to SharePoint.

The URI should be /_api/search/query?querytext='contentclass:STS_Site'

3. Add a Parse JSON action

In the Content field add:

body('Send_an_HTTP_request_to_SharePoint')?['d']?['query']?['PrimaryQueryResult']?['RelevantResults']?['Table']?['Rows']?['results']

In the Schema add:

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "Cells": {
                "type": "object",
                "properties": {
                    "results": {
                        "type": "array",
                        "items": {
                            "type": "object",
                            "properties": {
                                "Key": {
                                    "type": "string"
                                },
                                "Value": {
                                    "type": [
                                        "string",
                                        "null",
                                        "boolean",
                                        "number"
                                    ]
                                },
                                "ValueType": {
                                    "type": "string"
                                }
                            },
                            "required": [
                                "Key"
                            ]
                        }
                    }
                },
                "required": [
                    "results"
                ]
            }
        },
        "required": [
            "Cells"
        ]
    }
}

4. Add a Select action.

In the From cell add items('For_each')['Cells']['results'] (This will add the For Each).

The Key will be item()['Key']

The Value will be item()?['Value']