Export SharePoint Group Members to Excel Availing PowerShell

Creating and managing SharePoint groups helps reduce the administrator’s tasks. Using SharePoint groups, administrators can allow the permissions collectively. Apart from several advantages of groups, these are also beneficial when the administrator needs to export SharePoint group members to Excel. There are several scenarios in which SharePoint administrators are required to download the list of all group members. But not knowing how to do it seamlessly, creates hurdles. Most of the SharePoint administrators use PowerShell commands to perform their regular tasks.

These commands reduce repetitive tasks and save a lot of time for them. In this article, we will discuss how to export SharePoint group members to Excel through PowerShell command. Let’s dive into it.

Benefits of Exporting SharePoint Group Members to Excel

There are ample benefits to downloading the list of SharePoint group members. Let’s discuss some of them in a detailed manner.

• After downloading the list of all group members into Excel, it becomes easier to perform analysis on it. SharePoint administrators can process the data and convert it into visualized charts. It will simplify the process of extracting insights from it.
• As per some compliance requirements of the organizations, it is also crucial to save the group members list in an Excel file.
• Having all the details of the group members in an Excel file helps to present the team members in front of clients.
• It becomes easier to access users offline after downloading the list of all group members in an Excel file.

Prerequisites to Export SharePoint Group Members to Excel Through PowerShell

Before initiating the process of using PowerShell commands to download the list of group members. You need to fulfill the below prerequisites.

1. You have the SharePoint administrator credentials.
2. Do not forget to update SharePoint Online management shell for an error-free process.
3. Ensure all of the group members are active that you are going to export.
4. Also, backup SharePoint Online to local storage to ensure data safety.

PowerShell Script to Export Members of a Particular SharePoint Group

If you want to save the group members of a particular group only. Then you can go with the below script.

Add-PSSnapin Microsoft.SharePoint.PowerShell -EA SilentlyContinue

$SharePoint_Site_URL="Enter Site URL"

$site = Get-SPSite $SharePoint_Site_URL
$web = $site.OpenWeb()

$SharePoint_Group = $Web.sitegroups | Where-Object {$_.Name -eq "CSA Test All Entity Users"}

ForEach ($user in $group.users)
{
write-host $user.name "`t" $user.LoginName "`t" $user.Email
}

How to Export SharePoint Group Members to Excel Through PowerShell?

Using the below small script, you can download the list of SharePoint group members to Excel file.

Add-PSSnapin Microsoft.SharePoint.PowerShell -EA SilentlyContinue

$SP_Group_URL="Enter URL of the SharePoint Group"
$Report_CSV_File = "C:\Temp\FinalGroupUsersRpt.txt"

$get_site = Get-SPSite $SP_Group_URL

"Site Name`t URL `t Group Name `t User Account `t User Name `t E-Mail" | out-file $Report_CSV_File

ForEach ($web in $get_site.AllWebs)
{
"$($web.title) `t $($web.URL) `t `t `t `t " | out-file $Report_CSV_File -append
foreach ($group in $Web.groups)
{
"`t `t $($Group.Name) `t `t `t " | out-file $Report_CSV_File -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 $Report_CSV_File -append
}
}
}
}
write-host " Exporting Report is Generated at $Report_CSV_File"

Download the List of All Users & Groups Across All SharePoint Online Site Collections

Import-Module Microsoft.Online.Sharepoint.PowerShell -DisableNameChecking

$SharePoint_Admin_URL = "https://Crescent-admin.sharepoint.com"
$Administrator_Name = "SPAdmin@Crescent.com"

$Pswd = Read-host -assecurestring "Enter Password for $Administrator_Name"
$Credential = new-object -typename System.Management.Automation.PSCredential -argumentlist $Administrator_Name, $Pswd

Try {

Connect-SPOService -url $SharePoint_Admin_URL -credential $Credential

Get-SPOSite -Limit ALL | ForEach-Object {

$SP_Site_Groups = Get-SPOSiteGroup -Site $_.URL

foreach($Group in $SP_Site_Groups)
{
Write-host "Group:"$Group.Title
Get-SPOSiteGroup -Site $_.URL -Group $Group.Title | Select-Object -ExpandProperty Users
}
}
}
catch {
write-host "Error has been occurred:”
}

Export SharePoint Group Members to Excel Without PowerShell

Follow the below steps sequentially to download the list of group members without PowerShell.

1. Launch your SharePoint Online account and go to the Site Settings.
2. Open the People and Groups tab.
3. Click on the group whose members you want to export to Excel.
4. Copy the URL of the SharePoint group and note the Membership group ID.
5. After that launch your Microsoft Excel and click on Get Data.
6. Now, hit on the option of from other sources and choose from the OData Data feed.
7. Paste the copied URL in the opened window.
8. Hit the Next button and choose the user’s table.
9. Set a name for the connection and then Finish.

Export SharePoint Group Members to Excel Through PowerShell – Best Practices

To download the SharePoint group members locally, follow the best practices below.

1. Optimization of PowerShell Scripts – Apply the commands necessary for exporting group members. Do not include the irrelevant commands, they will delay the result.
2. Ensuring Data Privacy – Always handle the credentials of SharePoint securely to maintain data security.
3. Test Thoroughly – Before executing any PowerShell script directly, test them thoroughly to identify and address the issues.
4. Documentation – Prepare the complete documentation of the PowerShell scripts. This will help you and other team members to understand the script.

Concluded Words

In this article, we have discussed how to export SharePoint group members to Excel through PowerShell commands. Now, you can download the list of all group members seamlessly. But do not forget to execute the commands appropriately to get the expected results.