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

Question

Hi All
Hope all doing good and keeping safe!
Could you please help me with below query .....

I am having list of SPO-sites in excel sheet (nearly 900),where client is looking for members and owners details of those sites ,hence i need help in getting a Script, where it has to "read all SPO sites (one by one)from excel and get owners and members list as in csv Output".

THX

Answer 1

I actually looked into the code nessesary to solve your problem, but discovered a security issue that will halter the process, it's explained here:
https://learn.microsoft.com/en-us/answers/questions/588772/sharepoint-powershell-error-get-spouser-access-is.html

You need to have a Global Admin account that is also an owner on each site...

The code I written this far:
https://github.com/dkaaven/M365-Scripts/blob/main/SharePoint/Get-SPOSiteUsers.ps1
I might come back to this at a later time, but as far as I can tell what you wish for isn't possible.

Answer 2

@Dhamley

Note: The account to be used in the PowerShell should be the administrator of all these site collections.

1.Create a csv file as following picture shows.

2.Please run below PowerShell to get all members of all groups in the given site collections of csv file.
$Credentials=Get-Credential

$InputCSVPath = "C:\Temp\sitecollections.csv"  
$OutputCSVPath = "C:\Temp\GroupsReport.csv"  

$csv = Import-Csv $InputCSVPath  
$a = $csv.Url  

$GroupsData = @()  

ForEach($_ in $a) {  

    Connect-SPOService -url $_ -Credential $Credentials  

    Get-SPOSiteGroup -Site $_  

    $SiteGroups = Get-SPOSiteGroup -Site $_  

    ForEach($Group in $SiteGroups)  
    {  
        $GroupsData += New-Object PSObject -Property @{  
            'Site URL' = $_.URL  
            'Group Name' = $Group.Title  
            'Permissions' = $Group.Roles -join ","  
            'Users' =  $Group.Users -join ","  
        }  
    }  
}  

$GroupsData | Export-Csv $OutputCSVPath -NoTypeInformation  

---

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.