Export SharePoint Group Members to Excel [Updated 2025 Guide]
SharePoint groups serve as a central location whose permissions are easy for administrators to manage. In SharePoint groups, administrators can add multiple users with similar requirements. By creating groups, administrators can easily export SharePoint group members to Excel. This is particularly useful when auditing a SharePoint site or performing data analysis. However, many of the administrators are still unaware of SharePoint export Group Members to Excel.
Table of Contents
In this write-up, we will delve into the step-by-step process of exporting SharePoint group members to Excel. You will get a deep understanding of all of these methods. Let’s get started.
Why I Needed to SharePoint Export Group Members to Excel?
I want to manage my SharePoint lists offline, and exporting them to an Excel file is a wise decision for me. Not only manage, but more reasons to export SharePoint Group Members to Excel:
- The Excel application allows several advanced data analysis filters that can help you extract useful insights.
- Using the Excel file, it becomes easy to create detailed reports of group memberships for presentations or documentation.
- To ensure SharePoint document management best practices, you can keep a record of SharePoint group members in an Excel file.
- You can also analyse group compositions to optimise the current teams. Additionally, restructure SharePoint groups efficiently.
- While planning to perform SharePoint migration, administrators also perform this step as a prerequisite.
How to Export SharePoint Group Members to Excel?
I found three practical soluton to export data quickly and hassle-free, which are as follows:
#1. Using Power Automate
Execute the below Power Automate flow steps sequentially for exporting SharePoint group members to Excel.
- Start creating a new flow in Power Automate and choose the “Scheduled – from blank” template.
- Set the time frame of the flow to run automatically.
- Now, add an action “Send an HTTP request to SharePoint” in the flow.
- In the “Site Address” field, provide the complete site URL including group.
- In the “Method” field, select “GET” and in the “Uri” field, enter the URL:_api/web/sitegroups?$select=Title,Users/Title,Users/Email&$expand=Users
- Add a “Parse JSON” action to the flow.
- In the “Content” field, choose 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" } } } } } } } } } } }
- 9. Add the “Create table” action to the Power Automate flow.
- Choose the output from the “Parse JSON” action in the form field.
- Assign a name for the Excel table.
- Provide the column names: Group Name, User Name, User Email
- Now, add a “Create CSV table” action to the flow.
- Select the “Create table” action in the “From” field.
- After that, add a “Create file” action in the flow.
- Provide the name of the Excel file, including .xlsx in the “File name” field.
- Now, in the “File content” field, choose the output from the “Create CSV table” action.
- Also, add a “Send an email” action to the flow (optional).
- In the “To” field, provide the email address of the receiver.
- Enter the subject of the email in the “Subject” field.
- Pick the “Create file” action in the “Attachments” field.
- Finally, click Save to export SharePoint Group Members to Excel.
#2. SharePoint Export Group Members to Excel Using Admin Center & Excel
You can also download the list of all users in the SharePoint groups without Power Automate. You can simply use the Admin Center and Excel application. Let’s see how.
- Step 1. Open the SharePoint and move to the Site Settings.
- Step 2. Now, click on the People and Groups section.
- Step 3. Open the SharePoint group to export.
- Step 4. Copy the current URL and note the Membership group ID.
[https://sharepointtenant.sharepoint.com/currentsites/currentsitename/_layouts/15/people.aspx?] - Step 5. Now open Microsoft Excel > Get Data.
- Step 6. Click on from other sources > from OData Data feed.
Step 6. In the opened wizard, provide the URL in the Link or File section.- Step 7. Click on Next and select the user’s table.
Step 8. Assign a name to the connection and hit Finish.
#3. Export SharePoint Group Members to Excel PowerShell
Although SharePoint groups and members lists can be downloaded. But use this method when you already have expertise in SharePoint PowerShell. Because inaccurate execution of the PowerShell commands can lead you data loss.
Add-PSSnapin Microsoft.SharePoint.PowerShell -EA SilentlyContinue
$Group_URL="Enter complete Group URL"
$CSVFile = "C:\Temp\UsersandGroupsRpt.txt"
$get_site = Get-SPSite $Group_URL
"Site Name`t URL `t Group Name `t User Account `t User Name `t E-Mail" | out-file $CSVFile
ForEach ($web in $get_site.AllWebs)
{
"$($web.title) `t $($web.URL) `t `t `t `t " | out-file $CSVFile -append
foreach ($group in $Web.groups)
{
"`t `t $($Group.Name) `t `t `t " | out-file $CSVFile -append
foreach ($user in $group.users)
{
if(($User.LoginName.ToLower() -ne "nt authority\authenticated users") -and ($User.LoginName.ToLower() -ne "sharepoint\system") -and ($User.LoginName.ToLower() -ne "nt authority\local service"))
{
"`t `t `t $($user.LoginName) `t $($user.name) `t $($user.Email)" | out-file $CSVFile -append
}
}
}
}
write-host "A Complete Report is Generated at $CSVFile"
Author’s Verdict
There can be several reasons to export SharePoint group members to Excel. Although the process of downloading the list of users in a SharePoint group is complex. But using the right approach can simplify this process.
You can opt for any of the methods to SharePoint export Group Members to Excel, as I use all of them according to the requirements and data sensitivity.