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

%3CLINGO-SUB%20id%3D%22lingo-sub-1770091%22%20slang%3D%22en-US%22%3EExport%20sharepoint%20group%20and%20users%20to%20cvs%20using%20poweshell%20in%20a%20particular%20format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1770091%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%3CBR%20%2F%3EI%20have%20been%20able%20to%20export%20Groups%20and%20users%20in%20this%20format%3CBR%20%2F%3EGroups%26nbsp%3B%20%26nbsp%3B%20Users%3CBR%20%2F%3EGroupA%26nbsp%3B%20%26nbsp%3BUser%20A%3B%20User%20A1%3CBR%20%2F%3EGroupB%26nbsp%3B%20%26nbsp%3B%20UserB%3B%20User%20B1%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUsing%20this%20code%20below%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-powershell%22%3E%3CCODE%3E%23Connect%20to%20PnP%20Online%0AConnect-PnPOnline%20-Url%20%24SiteURL%20-UseWebLogin%0A%20%0A%23Get%20All%20Groups%20from%20Site%20-%20Exclude%20system%20Groups%0A%24Groups%20%3D%20Get-PnPGroup%20%7C%20Where-Object%20%7B%24_.OwnerTitle%20-ne%20%22System%20Account%22%7D%0A%24GroupData%3D%40()%0A%20%0A%23Get%20Group%20Details%0AForEach(%24Group%20in%20%24Groups)%0A%7B%0A%20%20%20%20%23Get%20Group%20data%0A%20%20%20%20%24GroupData%20%2B%3D%20New-Object%20PSObject%20-Property%20(%5Bordered%5D%40%7B%0A%20%20%20%20%22Group%20Name%22%20%3D%20%24Group.Title%0A%20%20%20%20%22Users%22%20%3D%20%24Group.Users.Title%20-join%20%22%3B%20%22%0A%20%20%20%20%7D)%0A%7D%0A%24GroupData%0A%20%0A%23Export%20Users%20data%20to%20CSV%20file%0A%24GroupData%20%7C%20Export-Csv%20-NoTypeInformation%20%24CSVFile%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20what%20i'm%20after%20is%26nbsp%3B%3CBR%20%2F%3EGroups%26nbsp%3B%20%26nbsp%3B%20Users%3CBR%20%2F%3EGroupA%26nbsp%3B%20%26nbsp%3BUser%20A%26nbsp%3B%3C%2FP%3E%3CP%3EGroupA%26nbsp%3B%20%26nbsp%3BUser%20A1%3CBR%20%2F%3EGroupB%26nbsp%3B%20%26nbsp%3B%20UserB%3C%2FP%3E%3CP%3EGroupB%26nbsp%3B%20%26nbsp%3BUser%20B1%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20this%20achievable%20and%20if%20yes%26nbsp%3B%20how%20can%20i%20do%20that%20in%20powershell%3F%3C%2FP%3E%3CP%3EThanks%20in%20advance%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1770091%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESharePoint%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1774074%22%20slang%3D%22en-US%22%3ERe%3A%20Export%20sharepoint%20group%20and%20users%20to%20cvs%20using%20poweshell%20in%20a%20particular%20format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1774074%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F89836%22%20target%3D%22_blank%22%3E%40Patrick%20Rote%3C%2FA%3E%2C%3C%2FP%3E%3CP%3EYes%20it%20is%20achievable.%20One%20of%20the%20options%20would%20be%20to%20add%20one%20more%20loop%20for%20users.%3C%2FP%3E%3CP%3EAlso%20I%20would%20strongly%20recommend%20not%20to%20use%20%3CSTRONG%3E%2B%3D%3C%2FSTRONG%3E%20as%20if%20you%20have%20many%20groups%2Fusers%20it%20will%20take%20forever%20to%20complete.%20Please%20use%20List(%3CSTRONG%3ESystem.Collections.Generic.List%3C%2FSTRONG%3E)%20instead.%3C%2FP%3E%3CP%3EHere%20is%20adjusted%20code%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-powershell%22%3E%3CCODE%3EConnect-PnPOnline%20-Url%20%24SiteURL%20-UseWebLogin%0A%24Groups%20%3D%20Get-PnPGroup%20%7C%20Where-Object%20%7B%24_.OwnerTitle%20-ne%20%22System%20Account%22%7D%0A%23Creating%20List%20object%20instead%20of%20Array%0A%24GroupData%3D%5BSystem.Collections.Generic.List%5Bobject%5D%5D%3A%3Anew()%0A%23Loop%20to%20enumerate%20all%20Groups%0AForEach(%24Group%20in%20%24Groups)%0A%7B%0A%20%20%20%20%23Loop%20to%20enumerate%20all%20Users%0A%20%20%20%20foreach(%24User%20in%20%24Group.Users.Title)%7B%0A%20%23Get%20Group%20%20%26amp%3B%20Add%20to%20list%0A%20%24GroupData.Add(%24(New-Object%20PSObject%20-Property%20(%5Bordered%5D%40%7B%22GroupName%22%20%3D%20%24Group.Title%3B%22User%22%20%3D%20%24User%20%7D)))%0A%20%20%20%20%7D%0A%7D%0A%24GroupData%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22pnpgroups.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F226234i91180C1CA5E5447B%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22pnpgroups.png%22%20alt%3D%22pnpgroups.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1786882%22%20slang%3D%22en-US%22%3ERe%3A%20Export%20sharepoint%20group%20and%20users%20to%20cvs%20using%20poweshell%20in%20a%20particular%20format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1786882%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F672105%22%20target%3D%22_blank%22%3E%40AndySvints%3C%2FA%3E%26nbsp%3B%20thanks%20for%20the%20response.%20It%20worked%20like%20a%20charm%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Contributor

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

2 Replies
Highlighted

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

pnpgroups.png

 

Hope that helps.

Highlighted