Table of Contents



Introduction


SharePoint lists can hold attachments which can grow considerably in size. This script will help you identify list attachments and estimate their size. It can be useful during re-org or migration.


Prerequisites


You need SharePoint Online SDK.


Steps

Step 1: Connect to SharePoint Online


Create ClientContext and add your credentials. ExecuteQuery() is not necessary here. I just like to use it early in order to test the connection:

$password = Read-Host "Password" -AsSecureString
$ctx=New-Object Microsoft.SharePoint.Client.ClientContext($Url)
$ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Username, $password)
$ctx.ExecuteQuery()


Step 2: Get all items

First you need to load your list:
$ll=$ctx.Web.Lists.GetByTitle($ListTitle)
$ctx.Load($ll)
$ctx.ExecuteQuery()

Then we can select only items with an attachment using CamlQuery:
$spqQuery = New-Object Microsoft.SharePoint.Client.CamlQuery
$spqQuery.ViewXml = "<Where><Eq><FieldRef Name='Attachments' /><Value Type='Boolean'>1</Value></Eq></Where>";

Load all your items:
$listItems=$ll.GetItems($spqQuery)
$ctx.Load($listItems)
$ctx.ExecuteQuery()


Step 3: Get item attachments


This step needs to be done per every item, so we start with a loop:
for($j=0;$j -lt $listItems.Count ;$j++)
  {       
 
  }

Now, for every item, we need to load its attachments:
for($j=0;$j -lt $listItems.Count ;$j++)
  {       
      $itemAttachments=$listItems[$j].AttachmentFiles
      $ctx.Load($itemAttachments)
      $ctx.ExecuteQuery()
  }

Each item can have one or more attachments. So what we actually loaded before is a collection of attachment files. We need to loop through each of them 
for($j=0;$j -lt $listItems.Count ;$j++)
  {       
      $itemAttachments=$listItems[$j].AttachmentFiles
      $ctx.Load($itemAttachments)
      $ctx.ExecuteQuery()
 
      foreach($itemAttachment in $itemAttachments)
      {
         #do something
      }
  }


Step 4: Export to csv 


We can either add each of our attachments to a predefined array:
$array=@()
$array+=$itemAttachment

or, since we only want to export the list of them to a csv, we can directly do that:
Export-CSV -InputObject $itemAttachment -Path $CSVPath -Append

If you are interested in only specific properties of this attachment, you can create a custom object and define its properties:
$obj = New-Object PSObject
$obj | Add-Member NoteProperty ItemID($listItems[$j].ID)
$obj | Add-Member NoteProperty ItemTitle($listItems[$j]["Title"])
$obj | Add-Member NoteProperty AttachmentName($file.Name)
$obj | Add-Member NoteProperty AttachmentVersions($file.Versions.Count)
$obj | Add-Member NoteProperty "AttachmentSize in KB"($fileSize)
Export-CSV -InputObject $obj -Path $CSVPath -Append
 

Sample results






Full script



  function Connect-SPOCSOM
{
    param (
        [Parameter(Mandatory=$true,Position=1)]
        [string]$Username,
        [Parameter(Mandatory=$true,Position=3)]
        [string]$Url
)
 
  $password = Read-Host "Password" -AsSecureString
  $ctx=New-Object Microsoft.SharePoint.Client.ClientContext($Url)
  $ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Username, $password)
  $ctx.ExecuteQuery() 
    $global:ctx=$ctx
}
 
 
function Get-SPOListItems
{
   param (
    [Parameter(Mandatory=$true,Position=1)]
        [string]$ListTitle,
    [Parameter(Mandatory=$false,Position=3)]
        [switch]$Recursive,
    [Parameter(Mandatory=$false,Position=4)]
        [string]$CSVPath
        )
   
   
  $ll=$ctx.Web.Lists.GetByTitle($ListTitle)
  $ctx.Load($ll)
  $ctx.Load($ll.Fields)
  $ctx.ExecuteQuery()
 
  $spqQuery = New-Object Microsoft.SharePoint.Client.CamlQuery
  $spqQuery.ViewXml = "<Where><Eq><FieldRef Name='Attachments' /><Value Type='Boolean'>1</Value></Eq></Where>";
  
  if($Recursive)
  {
     $spqQuery.ViewXml +="<View Scope='RecursiveAll' />";
  }
 
  $listItems=$ll.GetItems($spqQuery)
  $ctx.Load($listItems)
  $ctx.ExecuteQuery()
 
  for($j=0;$j -lt $listItems.Count ;$j++)
    {       
        $itemAttachments=$listItems[$j].AttachmentFiles
        $ctx.Load($itemAttachments)
        $ctx.ExecuteQuery()
 
        foreach($itemAttachment in $itemAttachments)
        {
           #Write-Output $att
            $file = $ctx.Web.GetFileByServerRelativeUrl($itemAttachment.ServerRelativeUrl);
            $ctx.Load($file)
            $ctx.ExecuteQuery()
            $fileSize = [Math]::Round(($file.Length/1KB),2)
            $obj = New-Object PSObject
            $obj | Add-Member NoteProperty ItemID($listItems[$j].ID)
            $obj | Add-Member NoteProperty ItemTitle($listItems[$j]["Title"])
            $obj | Add-Member NoteProperty AttachmentName($file.Name)
            $obj | Add-Member NoteProperty AttachmentVersions($file.Versions.Count)
            $obj | Add-Member NoteProperty "AttachmentSize in KB"($fileSize)
            Export-CSV -InputObject $obj -Path $CSVPath -Append
 
        }
    }
 
}
 
 
$global:ctx
 
 
# Paths to SDK. Please verify location on your computer.
Add-Type -Path "c:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "c:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
 
 
 
# Do not modify lines below
Connect-SPOCSOM -Username $Username -Url $Url
Get-SPOListItems -ListTitle $ListTitle -CSVPath $CSVPath -Recursive


Downloads

The script is available for download on Github:
List all attachments from SharePoint Online list to CSV file
Copy all SharePoint Online list item attachments to a SPO library

All suggestions, code changes, and improvements are welcome at my GitHub account:  https://github.com/PowershellScripts