Export SharePoint Users and Groups to Excel using PowerShell

Requirement: Export SharePoint Group members to Excel

PowerShell Script to Export Users & Groups:

We wanted to Export SharePoint Users and Groups to Excel for analyzing SharePoint Groups and Users along with their Account Name, E-mails! We can export SharePoint User Group to excel using PowerShell. Here is how:

Add-PSSnapin Microsoft.SharePoint.PowerShell -EA SilentlyContinue

#Parameter
$URL="https://sharepoint.crescent.com/sites/csaportal"
$CSVFile = "C:\Temp\UsersandGroupsRpt.txt"

#Get the Site
$site = Get-SPSite $URL 
   
#Write the Header to "Tab Separated Text File"
"Site Name`t  URL `t Group Name `t User Account `t User Name `t E-Mail" | out-file $CSVFile
        
#Iterate through all Webs
ForEach ($web in $site.AllWebs) 
{
    #Write the Header to "Tab Separated Text File"
    "$($web.title) `t $($web.URL) `t  `t  `t `t " | out-file $CSVFile -append
    #Get all Groups and Iterate through    
    foreach ($group in $Web.groups) 
    {
        "`t  `t $($Group.Name) `t   `t `t " | out-file $CSVFile -append
        #Iterate through Each User in the group
        foreach ($user in $group.users) 
        {
            #Exclude Built-in User Accounts
            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 "Report Generated at $CSVFile"

This script will Export the SharePoint user group to excel. Here is the report output:

Export SharePoint Users and Groups to Excel using PowerShell

PowerShell script to Get All Groups and Members of Each group:

Add-PSSnapin Microsoft.SharePoint.PowerShell -EA SilentlyContinue

$URL="https://sharepoint.crescent/sites/helpdesk/us"
 
$Site = Get-SPSite $URL 
     
If(Get-SPWeb($url).HasUniqueRoleAssignments -eq $true) 
{
    $Web=Get-SPWeb($url)
}
else
{
    $web= $site.RootWeb
}

#Get all Groups and Iterate through    
ForEach ($group in $Web.sitegroups)
{
    write-host " Group Name: "$group.name "`n---------------------------`n"
    #Iterate through Each User in the group
    foreach ($user in $group.users) 
    {
        write-host $user.name  "`t" $user.LoginName  "`t"  $user.Email  | FT
    } 
    write-host "=================================="  #Group Separator
}

How to Get members of a particular group in SharePoint using PowerShell?

Add-PSSnapin Microsoft.SharePoint.PowerShell -EA SilentlyContinue

$URL="https://sharepoint.crescent.com/sites/operations/"

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

#Get the Group by its name
$Group = $Web.sitegroups | Where-Object {$_.Name -eq "CSA Test All Entity Users"}
  
#Iterate through Each User in the group
ForEach ($user in $group.users)
{
    write-host $user.name  "`t" $user.LoginName "`t"  $user.Email
}

It’s also possible to read user properties from the User Profile.

PowerShell script to Get Members of Each Group at Web (Sub-Site) Level:

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue
 
$WebURL="https://portal.crescent.com/Teams/sales/"  
$Web = Get-SPWeb $WebURL
 
#Get all Groups and Iterate through    
foreach ($group in $Web.groups) 
{
    #Get Permission Levels Applied to the Group   
    $RoleAssignment = $Web.RoleAssignments.GetAssignmentByPrincipal($group)

    $RoleDefinitionNames=""
    foreach ($RoleDefinition in $RoleAssignment.RoleDefinitionBindings)
    {  
        $RoleDefinitionNames+=$RoleDefinition.Name+";"
    }
    
    write-host "Group Name: $($Group.name) : Permissions: $($RoleDefinitionNames) ----"
    #Iterate through Each User in the group
        foreach ($user in $group.users) 
        {
            write-host $user.name  "`t" $user.LoginName  "`t"  $user.Email  | FT
        } 
}

and the output: SharePoint PowerShell export group members

export users from sharepoint group powershell

Related Posts:

Salaudeen Rajack

Salaudeen Rajack is a SharePoint Architect with Two decades of SharePoint Experience. He loves sharing his knowledge and experiences with the SharePoint community, through his real-world articles!

11 thoughts on “Export SharePoint Users and Groups to Excel using PowerShell

  • How to get created date as well in the export?

    Reply
  • If i want the user phone number to show how to do that i tried $($user.Phone). Also, are the user name and user login different or they always will be same values

    Reply
  • Hi,I have 2 questions: first, if the user account is different than the user name for example user account is test123 but full name is john doe would the user name column display john doe. My sec question how do i get the user Telephone number to show in the report as well.
    Thank you for this amazing script.

    Reply
  • Hello Admin,

    Exporting SP groups and Members does not work for SharePoint 2013.
    Very first script.
    Please check and correct.
    Thanks.

    Reply
  • Nice post. Thanks a lot.

    How will I get user department.
    Tried with $($user.Department) , but not fetching.

    Reply
  • power shell script for getting unique users list to site collection in share point

    Reply
  • Nice Post…Worked fine… Thank u..

    Reply
  • Here the script for getting the list only fr site owner on the web application

    [System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SharePoint”)

    #Using Get-SPSite in MOSS 2007
    function global:Get-SPSite($url)
    {
    return new-Object Microsoft.SharePoint.SPSite($url)
    }

    function global:Get-SPWeb($url)
    {
    $site= New-Object Microsoft.SharePoint.SPSite($url)
    if($site -ne $null)
    {
    $web=$site.OpenWeb();
    }
    return $web
    }

    $farm = [Microsoft.SharePoint.Administration.SPFarm]::Local

    $farmWebServices = $farm.Services | where -FilterScript {$_.GetType() -eq [Microsoft.SharePoint.Administration.SPWebService]}

    #Write the Header to “Tab Separated Text File”
    “Site Name`t URL `t Group Name `t User Account `t User Name `t E-Mail” | out-file “d:UsersandGroupsRpt.txt”

    $SPWebApp = Get-SPWebApplication https://sharepoint

    foreach ($site in $SPWebApp.Sites)
    {

    $sites = Get-SPSite $site.url

    #Iterate through all Webs
    foreach ($web in $sites.AllWebs)
    {
    #Write the Header to “Tab Separated Text File”
    “$($web.title) `t $($web.URL) `t `t `t `t ” | out-file “d:UsersandGroupsRpt.txt” -append
    #Get all Groups and Iterate through
    $Group = $Web.sitegroups | Where-Object {$_.Name -Like “*Owners*” }
    foreach ($group in $Web.groups)
    {

    if(($Group.Name.ToLower() -Like “*owners*”))
    {

    “`t `t $($Group.Name) `t `t `t ” | out-file “d:UsersandGroupsRpt.txt” -append
    #Iterate through Each User in the group
    foreach ($user in $group.users)
    {
    #Exclude Built-in User Accounts

    “`t `t `t $($user.LoginName) `t $($user.name) `t $($user.Email)” | out-file “d:UsersandGroupsRpt.txt” -append

    }
    } }
    }

    }
    write-host “Report Generated at d:UsersandGroupsRpt.txt”

    Reply
  • Worked like a charm. Thanks much!

    Reply

Leave a Reply