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.

4 Comments

  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”

    Abraço,

    Ricardo Calejo

    March 21, 2019
    Reply
    • David Ramalho said:

      Hi Ricardo,

      Thank you for the trick. Improvements are always welcome.

      Abraço,
      David Ramalho

      March 22, 2019
      Reply

Leave a Reply

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