Powershell SharePoint Get all item data from document libraries – CSOM SharePoint Online

The powershell script below shows how to iterate through all libraries in a web on SharePoint Online and extract filename, version and checked out to name.

#Credentials to connect to office 365 site collection url 
$username = "me@isharepoint.onmicrosoft.com"
$password = "mypass"
$url = "https://isharepoint.sharepoint.com/"
$csvfile = "c:\export.csv"


$securePassword = $password |ConvertTo-SecureString -AsPlainText -force

<#
Write-Host "Load CSOM libraries" -foregroundcolor black -backgroundcolor yellow
Set-Location $PSScriptRoot
Add-Type -Path (Resolve-Path "Microsoft.SharePoint.Client.dll")
Add-Type -Path (Resolve-Path "Microsoft.SharePoint.Client.Runtime.dll")
Write-Host "CSOM libraries loaded successfully" -foregroundcolor black -backgroundcolor Green 
#>

Write-Host "authenticate to SharePoint Online Tenant site $url and get ClientContext object" -foregroundcolor black -backgroundcolor yellow  
$Context = New-Object Microsoft.SharePoint.Client.ClientContext($url) 
$credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($username, $securePassword) 
$Context.Credentials = $credentials 
$context.RequestTimeOut = 5000 * 60 * 10;
$web = $context.Web
$site = $context.Site 
$context.Load($web)
$context.Load($site)
try
{
  $context.ExecuteQuery()
  Write-Host "authenticateed to SharePoint Online Tenant site $url and get ClientContext object succeefully" -foregroundcolor black -backgroundcolor Green
}
catch
{
  Write-Host "Not able to authenticateed to SharePoint Online $_.Exception.Message" -foregroundcolor black -backgroundcolor Red
  return
}

#P.S. You can get Lists with single ExecuteQuery, but keeping the above code to connect to the site so that will be common for all samples
#You can try placing below 37 and 38 lines of below 21st line, it will still get the Lists by making single request to the server
#Get all lists Info
Write-Host "Getting all the list info" -foregroundcolor black -backgroundcolor yellow  
$Lists = $web.Lists
$Context.Load($Lists)
try
{
$Context.ExecuteQuery()
Write-Host "Successfully retrived all the list info" -foregroundcolor black -backgroundcolor green  

}
catch
{
Write-Host "Error while getting the list info" -foregroundcolor black -backgroundcolor Red  
}

#this bit creates the CSV if it does not already exist
$headers = "List", "Name", "Type", "Path", "Version", "Status"
$psObject = New-Object psobject
foreach($header in $headers)
{
 Add-Member -InputObject $psobject -MemberType noteproperty -Name $header -Value ""
}
$psObject | Export-Csv $csvfile -NoTypeInformation



Write-Host "Displaying Lists with in the site started....." -foregroundcolor black -backgroundcolor yellow  
#Get List Title
foreach($list in $Lists)
{
	#if($list.BaseTemplate -eq "101" -and $list.Title -eq "Test1")   
	if($list.BaseTemplate -eq "101")   	
	{
		Write-Host "Started on library: " $list.Title -foregroundcolor black -backgroundcolor yellow

	
		$items = $list.GetItems([Microsoft.SharePoint.Client.CamlQuery]::CreateAllItemsQuery())
		
		$Context.Load($items)
		$Context.ExecuteQuery()
		Write-Host "... Found " $items.Count " items" -foregroundcolor black -backgroundcolor yellow
		
		$totalItemCount = $items.Count
		$count = 0
		foreach($item in $items)
		{
			$status = ""
			$version = ""
			$count = $count+1
			
			Write-Host "... Processing file " $count " of " $totalItemCount
			
			if($item.FileSystemObjectType -eq "File")
			{
				$checkedout = $item["CheckoutUser"]
				
				if($checkedout)
				{
					$checkoutUser = $web.GetUserById($checkedout.LookupId);
					$Context.Load($checkoutUser)
					$Context.ExecuteQuery()
					$status = $checkoutUser.LoginName
				}
				
				$versionData = $item.File.Versions
				$Context.Load($versionData)
				$Context.ExecuteQuery()
				$versionData = $versionData[$versionData.Count-1]
				$version = $versionData.VersionLabel
			}
			
			$hash = @{
				 "List" =  $list.Title
				 "Name" = $item["FileLeafRef"]
				 "Type" = $item.FileSystemObjectType
				 "Path" = $item["FileRef"]
				 "Version" =$version
				 "Status" = $status
				  }
				  
			$newRow = New-Object PsObject -Property $hash
			Export-Csv $csvfile -inputobject $newrow -append -Force
		}
	}
}
Write-Host "Displaying Lists Completed" -foregroundcolor black -backgroundcolor Green