Forum Discussion

Patrick Rote's avatar
Patrick Rote
Iron Contributor
Oct 12, 2020

Export sharepoint group and users to cvs using poweshell in a particular format

Hi All,
I have been able to export Groups and users in this format
Groups    Users
GroupA   User A; User A1
GroupB    UserB; User B1

 

Using this code below

#Connect to PnP Online
Connect-PnPOnline -Url $SiteURL -UseWebLogin
 
#Get All Groups from Site - Exclude system Groups
$Groups = Get-PnPGroup | Where-Object {$_.OwnerTitle -ne "System Account"}
$GroupData=@()
 
#Get Group Details
ForEach($Group in $Groups)
{
    #Get Group data
    $GroupData += New-Object PSObject -Property ([ordered]@{
    "Group Name" = $Group.Title
    "Users" = $Group.Users.Title -join "; "
    })
}
$GroupData
 
#Export Users data to CSV file
$GroupData | Export-Csv -NoTypeInformation $CSVFile

 

But what i'm after is 
Groups    Users
GroupA   User A 

GroupA   User A1
GroupB    UserB

GroupB   User B1

 

Is this achievable and if yes  how can i do that in powershell?

Thanks in advance

SharePoint
  • AndySvints's avatar
    AndySvints
    Steel Contributor
    Oct 13, 2020

    Hello Patrick Rote,

    Yes it is achievable. One of the options would be to add one more loop for users.

    Also I would strongly recommend not to use += as if you have many groups/users it will take forever to complete. Please use List(System.Collections.Generic.List) instead.

    Here is adjusted code:

    Connect-PnPOnline -Url $SiteURL -UseWebLogin
$Groups = Get-PnPGroup | Where-Object {$_.OwnerTitle -ne "System Account"}
#Creating List object instead of Array
$GroupData=[System.Collections.Generic.List[object]]::new()
#Loop to enumerate all Groups
ForEach($Group in $Groups)
{
    #Loop to enumerate all Users
    foreach($User in $Group.Users.Title){
	#Get Group  & Add to list
	$GroupData.Add($(New-Object PSObject -Property ([ordered]@{"GroupName" = $Group.Title;"User" = $User })))
    }
}
$GroupData

     

    Hope that helps.

Resources