Export SharePoint Online Group Users to Excel using Powershell

Sometimes, we have a requirement to generate user reports from SharePoint groups or validate which user is added to which groups etc. If we do this activity manually, it may take forever (obviously based on the user added to group 😉). 

Classic example, recently in my project i got a requirement where i need to find all the users who are added to multiple SharePoint groups. Now i have 2500 users added to my SP Groups and it is difficult to check one by one. So i generated CSV/Excel for each group and added a vlookup in excel and got my report.

Below is the Powershell script, which helps to generate CSV file based on group name. 
Note: All the input variables are mentioned in Green Color, Please update before executing this script.

$aClient = [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client")
$aClientRuntime = [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client.Runtime") 
Function Get-UserInfo {
    Param ([string]$id)
    Process {   
        $uList = $web.Lists.GetByTitle('User Information List')
        $userItem = $uList.GetItemById($id);
        $ctx.Load($userItem)
        $ctx.ExecuteQuery()
        $obj = New-Object PSObject
        $obj | Add-Member -type NoteProperty -Name Title -Value $userItem['Title']
        $obj | Add-Member -type NoteProperty -Name UserName -Value $userItem['UserName']
        $obj | Add-Member -type NoteProperty -Name JobTitle -Value $userItem['JobTitle']
        $obj | Add-Member -type NoteProperty -Name Department -Value $userItem['Department']
        return $obj
    }
} 
$siteUrl = "Site Collection URL"
$grpName = "Group Name"
$exportFile = "Export Path in Local Drive\Users.csv" 
$ctx = New-Object Microsoft.SharePoint.Client.ClientContext($siteUrl)
$username = "Login Name"
$password = Read-Host -Prompt "Enter password" -AsSecureString
$ctx = New-Object Microsoft.SharePoint.Client.ClientContext($siteUrl) 
$credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($username, $password)
$ctx.Credentials = $credentials 
$web = $ctx.Web
$ctx.Load($web)
$group = $web.SiteGroups.GetByName($grpName)                          
$ctx.Load($group)
$ctx.Load($group.Users)
$ctx.ExecuteQuery() 
 

$group.Users | ForEach-Object { Get-UserInfo($_.Id) } | Export-Csv $exportFile -NoTypeInformation -append

 

Comments

Post a Comment

Popular posts from this blog

C# Copy files from one server to another

Suppress StyleCop SA1600

Telerik Rad Grid Sorting