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


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.


  1. Hello David,

    To not be dependent whether I have Excel installed on my pc, I’ve simplified your code and export it to CSV this way:

    $sites = $sites| where-object {$_.Template -eq “SITEPAGEPUBLISHING#0” } | Select Title,Url,Owner,StorageUsageCurrent,ResourceQuota |
    Export-Csv -delimiter “;” “C:\Dev\Sites.csv”


    Ricardo Calejo

    March 21, 2019
    • David Ramalho said:

      Hi Ricardo,

      Thank you for the trick. Improvements are always welcome.

      David Ramalho

      March 22, 2019
  2. Jean-Marc Dejean said:

    Your script is great. I can see where we connect to the tenancy-admin url. Where di I input the url for the site collection I ma really interested in?
    $loginUrl = “https://CONTOSO-admin.sharepoint.com”
    url I am interested in would something like

    June 23, 2020
    • David Ramalho said:

      Hi Jean,

      This script will extract all the site collections that you’ve on your tenant of the type Communication Sites.
      If you want to get the information from one site you can do multiple different ways. If you can connect to that site collection and get the information like this:

      $loginUrl = “SiteCollection”
      Connect-PnPOnline -Url $loginUrl -UseWebLogin

      June 25, 2020

Leave a Reply

Your email address will not be published. Required fields are marked *