Admin Reports CSV Groups Permission Permission Report PowerShell SharePoint SharePoint Online SharePoint Online Management Shell Users Users and Groups 

SharePoint Online: Site Users and Groups Report using PowerShell

Salaudeen Rajack 20 Comments , , , ,

Requirement: Generate a report with all groups and members of each group in a SharePoint Online site collection.

sharepoint online users and groups report using powershell

Prerequisites: Make sure you have SharePoint Online Management Shell installed!

Get all Site Groups and Members of Each group:

Managing users and groups is an important aspect of managing SharePoint Online. There may be instances where you need to export a list of all users and groups from SharePoint Online in order to perform a backup, a migration, or for auditing purposes. Using PowerShell, this process can be automated in an efficient and powerful manner. In this article, we will discuss how to export users and groups from SharePoint Online using PowerShell.

How do you get users and groups in SharePoint Online? Well, the answer is not as easy as it sounds. Admins can view users and groups in SharePoint Online by following these steps and quickly export a list of users and groups to excel for further analysis.

  1. Go to your SharePoint Online site >> Click on Settings gear >>Site Permissions >> Advanced Permissions Settings.
  2. On the permissions page, click on each group to get its members.
    get all groups and members in sharepoint online

But wait! There is a better way to view users and groups in SharePoint Online using PowerShell.

Using PowerShell to export SharePoint Online users and groups to Excel is a quick and easy way to get the user and group data on a regular basis. The first thing you need to do is connect to your SharePoint Online tenant using PowerShell. Once you’re connected, you can use the Get-SPOSiteGroup cmdlet to get information about your users and groups. To export this information to Excel, you can use the Export-CSV cmdlet. This will create a CSV file that you can then open in Excel. You can also use the Out-File cmdlet to output the data to a text file.

#Import SharePoint Online Management Shell
Import-Module Microsoft.Online.Sharepoint.PowerShell -DisableNameChecking

#Variables for processing
$AdminURL = "https://crescent-admin.sharepoint.com/"
$AdminName = "[email protected]"
$SiteURL="https://crescent.sharepoint.com/sites/sales"
 
#User Names Password to connect 
$Password = Read-host -assecurestring "Enter Password for $AdminName" 
$Credential = new-object -typename System.Management.Automation.PSCredential -argumentlist $AdminName, $Password

#Connect to SharePoint Online
Connect-SPOService -url $AdminURL -credential $Credentialexp

#get all sharepoint online groups
$SiteGroups = Get-SPOSiteGroup -Site $SiteURL

#Get Members of each group
foreach($Group in $SiteGroups)
{
    Write-host "Group:"$Group.Title
    Get-SPOSiteGroup -Site $SiteURL -Group $Group.Title | Select-Object -ExpandProperty Users
}

To get all users of the site collection, use the following script: 

Get-SPOUser -Site $siteURL | select DisplayName, LoginName | Where { $_.LoginName -like "*@crescent.com"}

How to export the list of users from SharePoint Online?

We can export all users of a SharePoint Online site and their Groups to CSV using PowerShell as:

#Get All users of the site collections and their Groups
Get-SPOUser -Site "https://Crescent.sharepoint.com/sites/marketing" | ForEach-Object { 
   $_.LoginName + "`t" + $_.DisplayName+ "`t" + ($_.Groups -join ",") | Out-File "C:\Temp\UsersRpt.csv" -Force -Append
}

Get Users and Groups for all SharePoint Online Site Collections:

Would you like to get users and groups from SharePoint Online? PowerShell can help!

#Import SharePoint Online Management Shell
Import-Module Microsoft.Online.Sharepoint.PowerShell -DisableNameChecking

#Variables for processing
$AdminURL = "https://Crescent-admin.sharepoint.com/"
$AdminName = "[email protected]"
 
#User Name & Password to connect
$Password = Read-host -assecurestring "Enter Password for $AdminName" 
$Credential = new-object -typename System.Management.Automation.PSCredential -argumentlist $AdminName, $Password

Try {
#Connect to SharePoint Online
Connect-SPOService -url $AdminURL -credential $Credential

#Get all Site collections
Get-SPOSite -Limit ALL | ForEach-Object {
Write-Host "Site Collection:"$_.URL

#Get all Site Groups 
$SiteGroups = Get-SPOSiteGroup -Site $_.URL
   
    #get sharepoint online group members powershell
    foreach($Group in $SiteGroups)
    {
        Write-host "Group:"$Group.Title
        Get-SPOSiteGroup -Site $_.URL -Group $Group.Title | Select-Object -ExpandProperty Users
    }
  }
}
catch {
    write-host "Error: $($_.Exception.Message)" -foregroundcolor Red
}

SharePoint Online Groups and Permissions Report

Let’s get all groups and permissions and members of each group who have access to the site.

#Import SharePoint Online Management Shell
Import-Module Microsoft.Online.Sharepoint.PowerShell -DisableNameChecking

#Variables for SharePoint Online Admin & Target site collection
$AdminSiteURL="https://crescent-admin.sharepoint.com"
$SiteCollURL="https://crescent.sharepoint.com/sites/sales"

#Get the Credentials
$Credential = Get-credential
#Connect To SharePoint Online 
Connect-SPOService -url $AdminSiteURL -Credential $Credential

#Get the Site collection
$Site = Get-SPOSite $SiteCollURL

#Get all Groups of the site collection     
$GroupColl = Get-SPOSiteGroup -Site $Site | Where { $_.Roles -ne $NULL -and $_.Users -ne $NULL}

Foreach($Group in $GroupColl)
{
    #Get Permissions assigned to the Group
    $GroupPermissions=""
    ForEach($Role in $Group.Roles)
    {
        $GroupPermissions+= $Role+";"
    }
    Write-host -f Yellow "Group Name: $($Group.Title) - Permissions: $($GroupPermissions)"

    #sharepoint online powershell to get group members
    foreach($User in $Group.Users)
    {
         write-host -f Green $user
    }               
}

Next, let’s export SharePoint Online users and groups to excel using PowerShell.

Export Users and Groups of All SharePoint Online Site Collections to CSV:

If you need to export SharePoint Online users and groups to excel, PowerShell cmdlets can help. Here is the PowerShell to export all groups and users to a CSV file:

#Admin Center & Site collection URL
$AdminCenterURL = "https://Crescent-admin.sharepoint.com/"
$CSVPath = "C:\Temp\GroupsReport.csv"

#Connect to SharePoint Online
Connect-SPOService -url $AdminCenterURL -Credential (Get-Credential)

$GroupsData = @()

#Get all Site collections
Get-SPOSite -Limit ALL | ForEach-Object {
    Write-Host -f Yellow "Processing Site Collection:"$_.URL
 
    #get sharepoint online groups powershell
    $SiteGroups = Get-SPOSiteGroup -Site $_.URL

    Write-host "Total Number of Groups Found:"$SiteGroups.Count

    ForEach($Group in $SiteGroups)
    {
        $GroupsData += New-Object PSObject -Property @{
            'Site URL' = $_.URL
            'Group Name' = $Group.Title
            'Permissions' = $Group.Roles -join ","
            'Users' =  $Group.Users -join ","
        }
    }
}
#Export the data to CSV
$GroupsData | Export-Csv $CSVPath -NoTypeInformation

Write-host -f Green "Groups Report Generated Successfully!"

And a sample report:

SharePoint Online Users and Groups Report using PowerShell

If you need to get SharePoint Online groups and users reports using CSOM or PnP PowerShell methods, use: Get All Users and Groups in SharePoint Online Site using PowerShell

Wrapping up

In this article, we have discussed how to export users and groups from SharePoint Online using PowerShell. Following the steps outlined in this guide, you can quickly and easily export a list of all users and groups from SharePoint Online to a CSV file. With PowerShell, you can automate this process and make managing and maintaining your SharePoint Online environment easier.

Salaudeen Rajack

Salaudeen Rajack - SharePoint Expert with Two decades of SharePoint Experience. Love to Share my knowledge and experience with the SharePoint community, through real-time articles!

20 thoughts on “SharePoint Online: Site Users and Groups Report using PowerShell”

Leave a Reply

Your email address will not be published. Required fields are marked *