Extract information of Communication sites to Excel

SharePoint sometimes can be a challenge to understand the structure and who is admin of the what. The article below allows you to extract all the SharePoint Communication Sites to an Excel that you can send to anyone to be analyzed without having to give Administrator rights.

With the next PowerShell script, you can extract the information of your SharePoint Site. Note to run this successfully, you need to be SharePoint admin and SharePoint admin of the site to guarantee that you can get the information of all the admins of the site.

$loginUrl   = "https://CONTOSO-admin.sharepoint.com" #SharePoint Admin Center
$username   = "CONTOSO@CONTOSO.onmicrosoft.com"
$password   = "password"

$encpassword = convertto-securestring -String $password -AsPlainText -Force
$cred = new-object -typename System.Management.Automation.PSCredential -argumentlist $username, $encpassword

$excel = New-Object -comobject Excel.Application
$excel.Visible = $True
Add-Type -AssemblyName Microsoft.Office.Interop.Excel
$xlFixedFormat = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlWorkbookDefault

$createExcel = $excel.Workbooks.Add()
$excel.ActiveSheet.Name = "Communication site"	
$excel.ActiveWorkbook.Title = "SharePoint"

$items = $excel.Worksheets.Item(1)

$items.Cells.Item(1,1) = "Site Title"
$items.Cells.Item(1,2) = "Site URL"
$items.Cells.Item(1,3) = "Site Owner"
$items.Cells.Item(1,4) = "Site Admins"
$items.Cells.Item(1,5) = "Quota in Use"
$items.Cells.Item(1,6) = "Quota Available"

$header = $items.UsedRange
$header.Interior.ColorIndex = 19
$header.Font.ColorIndex = 11
$header.Font.Bold = $True

$row = 2

Connect-SPOService -Url $loginUrl -Credential $cred
 $sites = Get-SPOSite
 
 foreach ($site in $sites){
  $siteAdmins = @();
  
  if($site.Template -eq "SITEPAGEPUBLISHING#0"){		
   $items.Cells.Item($row, 1) = $site.Title
   $items.Cells.Item($row, 2) = $site.Url
   $items.Cells.Item($row, 3) = $site.Owner
   $admins = Get-SPOUser -Site $site.Url -Limit all | Select-Object LoginName, IsSiteAdmin
   foreach ($admin in $admins){
    if($admin.IsSiteAdmin -eq $true){
     $siteAdmins += $admin.LoginName
    }	
   }      
   $items.Cells.Item($row, 4) = $siteAdmins -join "|"
   $items.Cells.Item($row, 5) = $site.StorageUsageCurrent
   $items.Cells.Item($row, 6) = $site.ResourceQuota
  
  $row = $row + 1
 }	
}

Conclusion

After you run this PowerShell script, you’ll have an Excel file with some information about your Communication Sites. You will be able to check your Title, URL, Administrators, Site Owner and Storage of the Site.