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 at Technet Gallery:
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