question

Dhamley avatar image
0 Votes"
Dhamley asked TongZhangMSFT-7548 commented

Need Script to read list of M365 Group sites from excel and get owners and members list as in csv OutPut

Hi All
Hope all are doing Good !
I am having list of M365 Group sites in excel sheet (nearly 150),where client is looking for members and owners details of those sites ,hence i need help in getting Script where it has to "read list of group sites from excel and get owners and members list as in csv Output"

THX

office-sharepoint-onlineoffice-exchange-server-administrationoffice-exchange-online-itprooffice-exchange-server-dev
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

TongZhangMSFT-7548 avatar image
0 Votes"
TongZhangMSFT-7548 answered TongZhangMSFT-7548 commented

Hi @Dhamley,
According to my testing and research, first You can create a list and import the excel sheet , then use PowerShell to get the site details from the list and get the site owner as .CSV output.

Here is a test example where you can implement this functionality using the following code:

First:
189053-image.png
189049-image.png
189067-image.png

Script:

 $SiteUrl = "https://xxxxxx.sharepoint.com/sites/xxx/"
 $ListName = "Site"
       
 #Get Credentials to connect
 $Cred = Get-Credential
     
 #Setup the context
 $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
 $Ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.UserName,$Cred.Password)
      
 #Get the list
 $List = $Ctx.Web.Lists.GetByTitle($ListName)
      
 #sharepoint online powershell read list items
 $ListItems = $List.GetItems([Microsoft.SharePoint.Client.CamlQuery]::CreateAllItemsQuery())
 $Ctx.Load($ListItems)
 $Ctx.ExecuteQuery()
 Write-host "Total Number of Items Found in the List:"$ListItems.Count
     
 #Iterate through List Items
 ForEach($Item in $ListItems)
 {
     $SiteURL=$ListItem["Title"]
     Connect-SPOService -url "https://xxxxx-admin.sharepoint.com"
     Get-SPOSite $SiteURL -limit ALL | Select URL, Owner| Select Owner| Export-csv -NoTypeInformation -Path "C:\test.csv"
    
 }

The output .csv file:
189111-image.png


If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.





image.png (4.6 KiB)
image.png (11.2 KiB)
image.png (5.5 KiB)
image.png (2.5 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @Dhamley,
I am checking to see if the problem has been resolved.
If there's anything you'd like to know, feel free to let me know.
Thanks for your understanding and support.

0 Votes 0 ·