Get All Lists items 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 items using CSOM power shell  using from  all the sub sites in a site collection in SharePoint online.

This post is the third post   in series of the  Power shell Scripts for Getting the data from site collection in SharePoint Online.

Get all Sub site details in Site Collection in SharePoint online using Power shell .

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

Note:  please check the  above link 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 Items 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-ListItemInfo-ByList
{
    param ($sCSOMPath,$sSiteID,$sSiteName,$sSiteUrl,$ListID,$ListTitle,$ListViewUrl,$BaseTemplate,$BaseType,$ItemCount,$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
		$List = $web.Lists.GetByTitle($ListTitle);

		$caml="<View Scope='RecursiveAll'></View>"
		#$caml="<View Scope='RecursiveAll'><Query><Where><And><Or><Eq><FieldRef Name='Author' /><Value Type='User'>Nadakuditi, Dilip</Value></Eq><Eq><FieldRef Name='Editor' /><Value Type='User'>Nadakuditi, Dilip</Value></Eq></Or><Eq><FieldRef Name='FSObjType' /><Value Type='Integer'>0</Value></Eq></And></Where></Query></View>"
		$cquery = New-Object Microsoft.SharePoint.Client.CamlQuery
		$cquery.ViewXml=$caml
		$listItems = $List.GetItems($cquery)

		$spoCtx.Load($List)
		$spoCtx.Load($listItems)
		$spoCtx.ExecuteQuery() 

		$AllListItems = @()

		foreach ($listitem in $listItems)
		{
			$Title =""
			$DefaultDisplayFormUrl =""
			$CreatedBy =  "System Account"
			$ModifiedBy = "System Account"

			If([string]::IsNullOrEmpty($listitem.FieldValues["Title"]))
			{
				$Title = "No Title"
			}
			else
			{
				$Title =$listitem.FieldValues["Title"]
			}

			If([string]::IsNullOrEmpty($List.DefaultDisplayFormUrl))
			{
				#Write-host " file ref $($listitem.FieldValues["FileDirRef"])"
				$DefaultDisplayFormUrl = ($sWeburl + $listitem.FieldValues["FileDirRef"] + "/DispForm.aspx" + "?ID=" + $listitem.ID)
			}
			else
			{
				$DefaultDisplayFormUrl =($sWeburl + $List.DefaultDisplayFormUrl + "?ID=" + $listitem.ID)
			}

			$FieldCreatedBy = [Microsoft.SharePoint.Client.FieldUserValue]$listitem.FieldValues["Author"]
			$CreatedBy = $FieldCreatedBy.LookupValue
			$FieldModifiedBy = [Microsoft.SharePoint.Client.FieldUserValue]$listitem.FieldValues["Editor"]
			$ModifiedBy = $FieldModifiedBy.LookupValue

			$AllListItems += New-Object -TypeName PSObject -Property @{
								SiteID = $sSiteID
								SiteName = $sSiteName
								SiteUrl =$sSiteUrl
                                ListID = $ListID
								ListTitle = $ListTitle
                                ListViewUrl =$ListViewUrl
								BaseTemplate= $BaseTemplate
								BaseType=$BaseType
								ItemID=$listitem.ID
								ItemName=$Title
								ItemURL= $DefaultDisplayFormUrl
								CreatedBy = $CreatedBy
								ModifiedBy = $ModifiedBy
                                CreatedDate = $listitem.FieldValues["Created"]
                                ModifiedDate = $listitem.FieldValues["Modified"]

            					} | Select  SiteID,SiteName,SiteUrl,ListID,ListTitle,ListViewUrl,BaseTemplate,BaseType,ItemID,ItemName,ItemURL,CreatedBy,ModifiedBy,CreatedDate,ModifiedDate
		}
		$AllListItems| 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
                                ListID = $ListID
								ListTitle = $ListTitle
                                ListViewUrl =$ListViewUrl
								BaseTemplate= $BaseTemplate
								BaseType=$BaseType
								ItemCount=$ItemCount
								Exception=$_.Exception.Message
            					} | Select  SiteID,SiteName,SiteUrl,ListID,ListTitle,ListViewUrl,BaseTemplate,BaseType,ItemCount,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-ListItemInfo
{
    param ($sCSOMPath,$sSiteUrl,$sUserName,$sPassword,$sWeburl,$FileName,$ImportFile)
    try
    {
       	Write-Host "Getting All Lists Items from All Lists in a Site Collection"
		Write-Host "-----------------------------------------------------------"
        #$ListBaseTemplate =@("101","110","111","112","113","114","116","121","122","123")
		$ListBaseType =@("DocumentLibrary")

		$ListInfo = import-csv ($sCSOMPath+"\"+$ImportFile+".csv") | Where { $ListBaseType -notcontains $_.BaseType -And $_.ItemCount -ne 0}
		#  -And $_.ListViewUrl -notcontains $DefaultLists }

		write-host "List Info Cont -$($ListInfo.Count)"
		  for ($i = 0; $i -lt $ListInfo.Count; $i++)
		 {
			$List=$ListInfo[$i]
			Write-Host "$($i). List Title : $($List.ListTitle) :: Site: $($List.SiteUrl)"

			Get-ListItemInfo-ByList -sCSOMPath $sCSOMPath -sSiteID $List.SiteID -sSiteName $List.SiteName -sSiteUrl $List.SiteUrl -ListID $List.ListID -ListTitle $List.ListTitle -ListViewUrl $List.ListViewUrl -BaseTemplate $List.BaseTemplate -BaseType $List.BaseType -ItemCount $List.ItemCount -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_ListItem_Collection"        #Read-Host -Prompt "Enter File Name to write (eg: XXXXX)"
$ImportFileName= "Source_ListCollection"        #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/)"
$sSiteUrl = "https://tarun.sharepoint.com/sites/sharepointmates/"    #Read-Host -Prompt "Enter Site Collection URL (eg: http://Server:port/Sites/Dev)"
$sUserName = "admin@tarun.onmicrosoft.com"            #Read-Host -Prompt "Enter User Name"
$sPassword = ConvertTo-SecureString "xxxx" -AsPlainText -Force               #Read-Host -Prompt "Enter your password" -AsSecureString 

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

Get-ListItemInfo -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_ListCollection”,   Source_ListCollectionis the name of the  excel which contains the details of the  all list  details that was generated in the second 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_ListCollectionand the excel sheet name Source_ListCollectionstructure as below
• Or else you can use the second post to generate the excel

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

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 With name Source_ListItem_Collection  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.

 

In the Next post we will see how to get the Document Items using Power shell

Advertisement