PNP-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 any one 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 PNP-Script where it has to "read list of SPO sites from excel and get owners and members list as in csv Output".
However i don't have SPO admin right ,so i am looking for a kind of PNP Scripts.
As per my understanding i am posting this query ,please suggest me if any corrections is needed.

Hence expected CSV output should be like as follow

URL of the Site | Primary Owner Details | Secondary Owner Details | Members Details | etc Users with other permissions

Thanks in Advance

THX

Answer 1

Hello @Dhamley

PnP Powershell is a library is an open-source and community-provided library with an active community providing support for it. It is not a Microsoft-provided module so there's no SLA or direct support for this open-source component from Microsoft.

You can find more information about contacting this community at: https://learn.microsoft.com/en-us/powershell/sharepoint/sharepoint-pnp/sharepoint-pnp-cmdlets

Hope this helps with your query,

-----------

--If the reply is helpful, please Upvote and Accept as answer--

Answer 2

Hi @Dhamley ,
According to my research and testing, here is an example of using PowerShell to read data from excel, and an example of getting the site owner.
You can refer to it , hope it can help you:

$filePath ="C:\test.xlsx"    
    # Create an Object Excel. Application using Com interface    
    $excelObj = New-Object -ComObject Excel.Application    
    # Disable the 'visible' property so the document won't open in excel    
    $excelObj.Visible = $false    
    #open WorkBook    
    $workBook = $excelObj.Workbooks.Open($filePath)    
    #Select worksheet using Index    
    $workSheet = $workBook.sheets.Item(1)    
    #Select the range of rows should read    
    $range= 3    
    for($i=1;$i-le $range;$i++){    
       $workSheet.Columns.Item(1).Rows.Item($i).Text    
    }    


$AdminCenterURL = "https://xxxxxx-admin.sharepoint.com/"  
$SiteURL = "https://xxxxxx.sharepoint.com/sites"  
   
#Connect to SharePoint Online  
Connect-SPOService -url $AdminCenterURL -Credential (Get-Credential)  
   
#Get Site owners of all site collections  
Get-SPOSite $SiteURL | Select URL, Owner |Export-CSV -Path  

---

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.

---

Answer 3

However i don't have SPO admin right ,so i am looking for a kind of PNP Scripts.

for accessing SPO sites your rather should have admin rights (if you access it with user credentials - global admin or site collection admin in each site you want to check) or have client id/secret of AAD app to access these sites with app permission (this app in turn should have at least Sites.Read.All permission with admin consent granted). It is not possible to bypass this requirement with PnP