Export SharePoint groups and their users to an Excel file using Power

You can export SharePoint groups and their users to an Excel file using Power Automate. Here are the steps:

1. Create a new flow in Power Automate and select the "Scheduled - from blank" template.

2. Set the frequency of the flow to run, for example, daily or weekly.

3. Add a "Send an HTTP request to SharePoint" action to the flow.

4. In the "Site Address" field, enter the SharePoint site URL containing the groups you want to export.

5. In the "Method" field, select "GET".

6. In the "Uri" field, enter the following URL:

   _api/web/sitegroups?$select=Title,Users/Title,Users/Email&$expand=Users
   

7. This URL retrieves a list of all the site groups in the site, along with their members' names and email addresses.

8. Add a "Parse JSON" action to the flow.

9. In the "Content" field, select the output from the "Send an HTTP request to SharePoint" action.

10. In the "Schema" field, enter the following JSON schema:

    {
        "type": "object",
        "properties": {
            "d": {
                "type": "object",
                "properties": {
                    "results": {
                        "type": "array",
                        "items": {
                            "type": "object",
                            "properties": {
                                "Title": {
                                    "type": "string"
                                },
                                "Users": {
                                    "type": "array",
                                    "items": {
                                        "type": "object",
                                        "properties": {
                                            "Title": {
                                                "type": "string"
                                            },
                                            "Email": {
                                                "type": "string"
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
    }
    

11. This schema defines the structure of the JSON response from the SharePoint API.

12. Add a "Create table" action to the flow.

13. In the "From" field, select the output from the "Parse JSON" action.

14. In the "Table name" field, enter a name for the Excel table.

15. In the "Columns" field, enter the following column names:

    Group Name, User Name, User Email
    

16. Add a "Create CSV table" action to the flow.

17. In the "From" field, select the output from the "Create table" action.

18. When the flow runs, it will retrieve the list of SharePoint groups and their members, create an Excel table from the data, and save it as a file in your SharePoint library. You can also configure the flow to send an email with the Excel file as an attachment to the appropriate recipients.

19. Add a "Create file" action to the flow.

20. In the "File name" field, enter a name for the Excel file, including the ".xlsx" extension.

21. In the "File content" field, select the output from the "Create CSV table" action.

22. Add a "Send an email" action to the flow (optional).

23. In the "To" field, enter the email address of the person or group that should receive the Excel file.

24. In the "Subject" field, enter a subject for the email.

25. In the "Attachments" field, select the output from the "Create file" action.

26. Save and test the flow.

27. When the flow runs, it will retrieve the list of SharePoint groups and their members, create an Excel table from the data, and save it as a file in your SharePoint library. You can also configure the flow to send an email with the Excel file as an attachment to the appropriate recipients.