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:
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
Related Posts:
I had to change URL to – https://yourtenant.sharepoint.com/sites/sitename/_api/web/sitegroups/GetbyID(id)/Users
How to get created date as well in the export?
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
“Phone Number” is not available in SPUser object, But you can query user profile properties to get phone number and other fields (If they are available in AD!) How to Query User Profile Properties using PowerShell
User.Name is the Display Name of the userf and User.LoginName is the Login ID (DomainLoginID)
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.
Hello Admin,
Exporting SP groups and Members does not work for SharePoint 2013.
Very first script.
Please check and correct.
Thanks.
Nice post. Thanks a lot.
How will I get user department.
Tried with $($user.Department) , but not fetching.
Well, Department property is not part of SPUser object. Query either UIL or SharePoint user profile store How to Query SharePoint User Information List using PowerShell
Query SharePoint User Profile Properties using PowerShell
power shell script for getting unique users list to site collection in share point
Nice Post…Worked fine… Thank u..
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”
Worked like a charm. Thanks much!