You can export SharePoint groups and their users to an Excel file using Power Automate. Here are the steps:
-
Create a new flow in Power Automate and select the "Scheduled - from blank" template.
-
Set the frequency of the flow to run, for example, daily or weekly.
-
Add a "Send an HTTP request to SharePoint" action to the flow.
-
In the "Site Address" field, enter the SharePoint site URL containing the groups you want to export.
-
In the "Method" field, select "GET".
-
In the "Uri" field, enter the following URL:
_api/web/sitegroups?$select=Title,Users/Title,Users/Email&$expand=Users
-
This URL retrieves a list of all the site groups in the site, along with their members' names and email addresses.
-
Add a "Parse JSON" action to the flow.
-
In the "Content" field, select the output from the "Send an HTTP request to SharePoint" action.
-
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"
}
}
}
}
}
}
}
}
}
}
}
-
This schema defines the structure of the JSON response from the SharePoint API.
-
Add a "Create table" action to the flow.
-
In the "From" field, select the output from the "Parse JSON" action.
-
In the "Table name" field, enter a name for the Excel table.
-
In the "Columns" field, enter the following column names:
Group Name, User Name, User Email
-
Add a "Create CSV table" action to the flow.
-
In the "From" field, select the output from the "Create table" action.
-
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.
-
Add a "Create file" action to the flow.
-
In the "File name" field, enter a name for the Excel file, including the ".xlsx" extension.
-
In the "File content" field, select the output from the "Create CSV table" action.
-
Add a "Send an email" action to the flow (optional).
-
In the "To" field, enter the email address of the person or group that should receive the Excel file.
-
In the "Subject" field, enter a subject for the email.
-
In the "Attachments" field, select the output from the "Create file" action.
-
Save and test the flow.
- 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.