How to Export SharePoint Group Members to Excel? Full 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 how to download the list of SharePoint group members in Excel.

In this write-up, we will delve into the step-by-step process of exporting SharePoint group members to Excel. This write-up covers three different approaches: Excel, PowerShell, and Power Automate.

You will get a deep understanding of all of these methods. Let’s get started.

Reasons to Export List of SharePoint Group Members

Excel file provides a simpler way to manage the users data. Below are some major reasons that stimulate to download list of SharePoint group members in Excel file. Most of the SharePoint administrators also backup SharePoint Online to local storage for some compliance purposes.

1. Data Analysis – Excel application allows several advanced data analysis filters that can help you to extract useful insights.
2. Auditing and Compliance – Exported Group members data can also be stored for auditing purposes, ensuring you meet organizational compliance requirements.
3. Report Generation – Using the Excel file, it becomes easy to create detailed reports of group memberships for presentations or documentation.
4. Team Restructuring – You can also analyze group compositions to optimize the current teams. Additionally, restructure SharePoint groups efficiently.
5. Backup of Membership Data – To ensure SharePoint document management best practices, you can keep a record of SharePoint group members in Excel file.
6.  Cloud Migration – While planning to perform SharePoint migration, administrators also perform this step as the prerequisite.

Method 1. How to Export SharePoint Group Members to Excel Using Power Automate?

Execute the below Power Automate flow steps sequentially for exporting SharePoint group members to Excel.

1.  Start creating a new flow in Power Automate and choose the “Scheduled – from blank” template.

2. Set the time frame of flow to run automatically.

3. Now, add an action “Send an HTTP request to SharePoint” in the flow.

4. In the “Site Address” field, provide the complete site URL including group.

5. 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

6. Add a “Parse JSON” action to the flow.

7. In the “Content” field, choose the output from the “Send an HTTP request to SharePoint” action.

8. 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.
10. Choose the output from the “Parse JSON” action in the form field.
11. Assign a name for the Excel table.
12. Provide the column names: Group Name, User Name, User Email
13. Now, Add a “Create CSV table” action to the flow.
14. Select the “Create table” action in the “From” field,.
15. After that Add a “Create file” action in the flow.
16. Provide the name of the Excel file including .xlsx in the “File name” field.
17. Now in the “File content” field, choose the output from the “Create CSV table” action.
18. Also, add a “Send an email” action to the flow (optional).
19. In the “To” field, provide the email address of the receiver.
20. Enter the subject of the email in the “Subject” field.
21. Pick the “Create file” action In the “Attachments” field.
22. Finally, Save and test the flow.

Method 2. Export List of SharePoint Group Members Using Admin Center & Excel

You can also download the list of all users in the SharePoint groups without Power Autmate. 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 which users you want to export.
Step 1. Copy the current URL and note the Membership group ID.
[https://tenant.sharepoint.com/sites/sitename/_layouts/15/people.aspx?]
Step 4. Now open the Microsoft Excel > Get Data.
Step 5. 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.

Method 3. Export SharePoint Group Members to Excel Using 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"

Final Words

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 use any of the method as per technical expertise.