SharePoint Online: Get all list attachments using Powershell and CSOM

Article
1/17/2024

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

Share via