Get All Lists details in a site collection Using Power shell in SharePoint Online

In this is post we will see how to get the  details of  all lists using CSOM power shell  using from  all the sub sites in a site collection in SharePoint online.

This post is the second post   in series of the  Power shell Scripts for Getting the data from site collection in SharePoint Online. you can access the first post Get all Sub site details in Site Collection in SharePoint online using Power shell .

Note:  please check the  first post before this post before to proceed further , since this is  continuation of the first post and we are using the excel generated in the above post as an input for the script below.

1)

Copy the below script to notepad and save the file with Get all List Info from all Sites.ps1 

#Add - PSSnapin Microsoft.SharePoint.PowerShell  

$host.Runspace.ThreadOptions = "ReuseThread" 

#Definition of the function that allows to create a new view in a SharePoint Online list 

function Get-ListInfo-BySite
{
    param ($sCSOMPath,$sSiteID,$sSiteName,$sSiteUrl,$sUserName,$sPassword,$sWeburl,$FileName)
    try
    {
        #Adding the Client OM Assemblies
        $sCSOMRuntimePath=$sCSOMPath +  "\Microsoft.SharePoint.Client.Runtime.dll"
        $sCSOMPathdll=$sCSOMPath +  "\Microsoft.SharePoint.Client.dll"
        Add-Type -Path $sCSOMPathdll
        Add-Type -Path $sCSOMRuntimePath        

        #SPO Client Object Model Context
        $spoCtx = New-Object Microsoft.SharePoint.Client.ClientContext($sSiteUrl)
        $spoCredentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($sUserName, $sPassword)
		#$spoCredentials = New-Object System.Net.NetworkCredential($sUserName, $sPassword)
        $spoCtx.Credentials = $spoCredentials       

		$web = $spoCtx.Web
		$spLists = $web.Lists 

		#$spoCtx.Load($web)
		$spoCtx.Load($spLists)
		$spoCtx.ExecuteQuery()
		#Write-Host $web.Title "-" $sSiteUrl -ForegroundColor Green
		$AllLists = @()

		foreach ($list in $spLists)
		{  

			$url =[string]$list.DefaultViewUrl

			if($url -like "*_catalogs*")
			{
			 #write-host "In If condition List url-  $($list.DefaultViewUrl.ToString()) "
			}
			else
			{
			 $AllLists += New-Object -TypeName PSObject -Property @{
								SiteID = $sSiteID
								SiteName = $sSiteName
								SiteUrl =$sSiteUrl
                                ListID = $list.ID
								ListTitle = $list.Title
                                ListViewUrl =($sWeburl+$list.DefaultViewUrl)
                                CreatedDate = $list.Created
                                LastModifiedDate = $list.LastItemModifiedDate
                                BaseTemplate= $list.BaseTemplate
								BaseType= $list.BaseType
                                ItemCount= $list.ItemCount
            					} | Select  SiteID,SiteName,SiteUrl,ListID,ListTitle,ListViewUrl,CreatedDate,LastModifiedDate,BaseTemplate,BaseType,ItemCount
			}
		}
		$AllLists| Export-CSV ($sCSOMPath+"\"+$FileName+".csv") -NoTypeInformation -Append #-Encoding UTF8

        $spoCtx.Dispose()
    }
    catch [System.Exception]
    {
		$FailedSite = @()
		$FailedSite += New-Object -TypeName PSObject -Property @{
								SiteID = $sSiteID
								SiteName = $sSiteName
								SiteUrl =$sSiteUrl
								Exception=$_.Exception.Message
            					} | Select  SiteID,SiteName,SiteUrl,Exception

		$FailedSite| Export-CSV ($sCSOMPath+"\"+$FileName+"_Failed.csv") -NoTypeInformation -Append #-Encoding UTF8

        Write-Host -ForegroundColor Red $_.Exception.ToString()
		#Read-Host -Prompt "Operation failed..! Press any key to close this and re run the script -Sub"
    }
} 

 function Get-ListInfo
{
    param ($sCSOMPath,$sSiteUrl,$sUserName,$sPassword,$sWeburl,$FileName,$ImportFile)
    try
    {
       	Write-Host "Getting All Lists in a Site Collection"
		Write-Host "--------------------------------------"

		$SiteInfo = import-csv ($sCSOMPath+"\"+$ImportFile+".csv")

		  for ($i = 0; $i -lt $SiteInfo.Count; $i++)
		 {
			$Site=$SiteInfo[$i]
			Write-Host "Getting List Information from $($i). $($Site.SiteUrl)"

			Get-ListInfo-BySite -sCSOMPath $sCSOMPath -sSiteID $Site.SiteID -sSiteName $Site.SiteName -sSiteUrl $Site.SiteUrl -sUserName $sUserName -sPassword $sPassword -sWeburl $sWeburl -FileName $FileName
		 }
		 <# 		 foreach ($Site in $SiteInfo)   		 { 			Get-ListInfo-BySite -sCSOMPath $sCSOMPath -sSiteID $Site.SiteID -sSiteName $Site.SiteName -sSiteUrl $Site.SiteUrl -sUserName $sUserName -sPassword $sPassword -sWeburl $sWeburl -FileName $FileName 		 } 		 #>

		Read-Host -Prompt "file created Successfull..! in the following path $sCSOMPath, Press any key to close this window"

    }
    catch [System.Exception]
    {
        Write-Host -ForegroundColor Red $_.Exception.ToString()
		Read-Host -Prompt "Operation failed..! Press any key to close this and re run the script -main"
    }
} 

$FileName = "Source_ListCollection"             #Read-Host -Prompt "Enter File Name to write (eg: XXXXX)"
$ImportFileName= "Source_SiteCollection"        #Read-Host -Prompt "Enter existing File Name to import file extension is csv (eg: YYYYY)"
$sWeburl = "https://tarun.sharepoint.com"        #Read-Host -Prompt "Enter Root Site URL (eg: http://Server:port/)"
$sUserName = "adminuser@tarun.onmicrosoft.com"            #Read-Host -Prompt "Enter User Name"
$sPassword = ConvertTo-SecureString "*****" -AsPlainText -Force                #Read-Host -Prompt "Enter your password" -AsSecureString
$sSiteUrl = "https://tarun.sharepoint.com/sites/sharepointmates"    #Read-Host -Prompt "Enter Site Collection URL (eg: http://Server:port/Sites/Dev)" 

$scriptpath = $MyInvocation.MyCommand.Path
$dir = Split-Path $scriptpath

Get-ListInfo -sCSOMPath $dir -sSiteUrl $sSiteUrl -sUserName $sUserName -sPassword $sPassword -sWeburl $sWeburl -FileName $FileName -ImportFile $ImportFileName

Note:

• Please update the  $sWeburl,$sUserName,$sPassword  and $sSiteUrl  variables with your SharePoint online  environment.
• In ImportFileName= “Source_SiteCollection”,   Source_SiteCollection is the name of the  excel which contains the details of the  sub sites details that was generated in the first post .  If you want to give the your custom file  as an input to the above script please create the excel with the  name  Source_SiteCollection and the excel sheet name  Source_SiteCollection and  structure as below
• Or else you can use the first post to generate the excel.

2) Now Right click on the Get all List Info from all Sites.ps1  Power shell script file and select Run with PowerShell

3) ) After Successful execution of the scripts we will get the below message

4)Now go to the folder, we will find the excel file.

5) Open the excel, We will find all the details of the sub sites in the excel file as below

On Premises:

For On premises we need to slightly change  $spoCredentials  and $sUserName    variables in the above script.

$spoCredentials = New-Object System.Net.NetworkCredential($sUserName, $sPassword)

We need to give the user name along with domain name as below

$sUserName = "2013dev\spadmin"

Please change the  on premises site  collection url , web url  and password as well.

Advertisement