get site, web and list information with PowerShell for SharePoint Online
We often get the question to list the site structure for SharePoint on premise and this can be done quite easily with get-spsite, get-spweb and get-splist but only get-sposite is available for SharePoint Online. This script will do the following:
– get all root sites in SharePoint Online and show its template
– get all webs and subwebs from root sites in SharePoint Online and show Its template
– get all lists from sites and webs in SharePoint Online and show its item count
– collect this information and store this in an HTML table
The script will generate the following output:
You will need to install the SharePoint Online Management Shell from https://www.microsoft.com/en-us/download/details.aspx?id=35588 and all of its pre-requisites. The below script needs to be copied to a .ps1 file on the workstation you are planning to run this script. It will create a logfile on the same location.
function connectToO365{
# Let the user fill in their admin url, username and password
$adminUrl = Read-Host "Enter the Admin URL of 0365 (eg. https://<Tenant Name>-admin.sharepoint.com)"
$userName = Read-Host "Enter the username of 0365 (eg. admin@<tenantName>.onmicrosoft.com)"
$password = Read-Host "Please enter the password for $($userName)" -AsSecureString
# set credentials
$credentials = New-Object -TypeName System.Management.Automation.PSCredential -argumentlist $userName, $password
$SPOCredentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($userName, $password)
#connect to to Office 365
try{
Connect-SPOService -Url $adminUrl -Credential $credentials
write-host "Info: Connected succesfully to Office 365" -foregroundcolor green
}
catch{
write-host "Error: Could not connect to Office 365" -foregroundcolor red
Break connectToO365
}
#create HTML file
$filePath = create-outputfile
#start getting site collections
get-siteCollections
add-content -value "
</body>
</html>
" -path $filePath
}
function create-outputfile(){
#Create unique string from the date
$date = get-date -format dMMyyyyhhmm
#set the full path
$filePath = "$($PSScriptRoot)\Output$($date).html"
#test path
if (!(Test-Path -path $filePath)){
#create file
New-Item $filePath -type file | out-null
#print info
write-host “File created: $($filePath)” -foregroundcolor green
#add start HTML information to file
add-content -value "
<html>
<body>
<h1>Sites information Office 365</h1>
<table border='1' style='font-family: Calibri, sans-serif'>
<tr>
<th style='background-color:blue; color:white'>URL</th>
<th style='background-color:blue; color:white'>Type</th>
<th style='background-color:blue; color:white'>Template</th>
<th style='background-color:blue; color:white'>Item Count</th>
</tr>
" -path $filePath
}
else{
#break so there won't be duplicate files
write-host "Output file already exists, wait 1 minute" -foregroundcolor yellow
Break create-outputfile
}
return $filePath
}
function get-siteCollections{
#Get all site collections
$siteCollections = Get-SPOSite
#loop through all site collections
foreach ($siteCollection in $siteCollections)
{
#set variable for a tab in the table
$pixelsweb = 0
$pixelslist = 0
#add info to HTML document
add-content -value "<tr style='background-color:cyan'><td>$($siteCollection.url)</td><td>TopSite</td><td>$($sitecollection.template)</td><td></td></tr>" -path $filePath
write-host "Info: Found $($siteCollection.url)" -foregroundcolor green
#search for webs
$AllWebs = Get-SPOWebs($siteCollection.url)
}
}
function Get-SPOWebs($url){
#fill metadata information to the client context variable
$context = New-Object Microsoft.SharePoint.Client.ClientContext($url)
$context.Credentials = $SPOcredentials
$web = $context.Web
$context.Load($web)
$context.Load($web.Webs)
$context.load($web.lists)
try{
$context.ExecuteQuery()
#loop through all lists in the web
foreach($list in $web.lists){
add-content -value "<tr><td><span style='margin-left:$($pixelslist)px'>$($list.title)</td><td>List/library</td><td></td><td>$($list.itemcount)</td></tr>" -path $filePath
}
#loop through all webs in the web and start again to find more subwebs
$pixelsweb = $pixelsweb + 15
$pixelslist = $pixelslist + 15
foreach($web in $web.Webs) {
add-content -value "<tr style='background-color:yellow'><td><span style='margin-left:$($pixelsweb)px'>$($web.url)</td><td>Web</td><td>$($web.webtemplate)</td><td></td></tr>" -path $filePath
write-host "Info: Found $($web.url)" -foregroundcolor green
Get-SPOWebs($web.url)
}
}
catch{
write-host "Could not find web" -foregroundcolor red
}
}
connectToO365
Manual
This script has been run on a workstation with Windows 8.1. First run the SharePoint Online Management Shell as administrator
.
Then run the following command
& “script location”
And answer the following three questions
And press enter
All sites have been successfully crawled and its contents is listed in the HTML document located next to the .ps1 file
Information
You can list more information if you want and you can find the properties on the following locations:
– Web properties: https://msdn.microsoft.com/EN-US/library/office/microsoft.sharepoint.client.web_properties.aspx
– List properties: https://msdn.microsoft.com/EN-US/library/office/microsoft.sharepoint.client.list_properties.aspx
Let me know if you have any remarks or updates
Thanks for sharing, works perfect. Is it possible to enter a specific Sitecollection?
Hi jeroen,
Thanks for feedback 🙂
You can change $siteCollections = Get-SPOSite to $sitecollections = “site collection url”. The script will only use that site.
Kind regards,
Maarten
Pingback: Get information of sites (List/Library, Template, Item Count) from SharePoint Online using Powershell | SharePoint Components
Thank you, this is easy and awesome! Is there a way I can export this data to csv?
Hi jadus,
It is possible to export this data to csv but you will have to change the script. You can copy the table to excel but this will be a manual action. You can perhaps also loop through the html table and save it to an csv file.
Kind regards,
Maarten
Pingback: Get information of sites (List/Library,Template, Item Count) from SharePoint OnlineSharePoint Components
This is good stuff, right mix of PowerShell + Powershell CSOM
Thanks for the script! For single site collection I would suggest to use: $siteCollections = Get-SPOSite -Identity “”
Cheers,
JeroenS
How would I add to check if versioning is enabled for the list and library?
Hello Mgmjtech,
You will need to edit the HTML tags but you can find if versioning is enabled by adding $list.enableversioning to the add-content in the foreach loop.
Kind regards,
Maarten
Just stumbled across this and it does exactly what I need! I want to keep an eye on document libraries that are getting near to the 5,000 item limit and take action if they breach that! Is there a way to colour code those ones that have greater than 4,000 items so that they would appear as red say?
Many many thanks,
Mark
Hello Mark,
You can create an If statement that if itemcount less then 4000 it shows the current HTML code to add a new line and else to add the same line but apply CSS to color:red.
Please let me know if this answer is sufficient,
Kind regards,
Maarten
Thanks, works very nicely, 🙂