SharePoint Online: Get Attachments Report in a List using PowerShell

Requirement: Generate a Report to get an Inventory of all Attachments in a SharePoint Online List.

PowerShell to Get All Attachments in a SharePoint Online List

This blog post will show you how to get all of the attachments from a SharePoint Online list. This is a handy trick if you need to view or download files that have been uploaded to the list.

This PowerShell extracts data from all attachments, such as Number of Attachments, Size, Filename, URL, etc., to a CSV file.

Import-Module Microsoft.Online.SharePoint.PowerShell -DisableNameChecking
 
#Set Parameters
$SiteURL= "https://crescent.sharepoint.com/sites/marketing"
$ListName= "Proposals"
$CSVPath = "C:\Temp\AttachmentInventory.csv"
$AttachmentsData = @()
 
#Setup Credentials to connect
$Cred = Get-Credential
 
#Setup the context
$Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
$Ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.UserName,$Cred.Password)
 
#Get the List
$List = $Ctx.Web.Lists.GetByTitle($ListName)

#CAML Query to Get List Items with Attachments
$CAMLQuery = New-Object Microsoft.SharePoint.Client.CamlQuery
$CAMLQuery.ViewXml ="<View Scope='RecursiveAll'><Query><Where><Eq><FieldRef Name='Attachments' /><Value Type='Boolean'>1</Value></Eq></Where></Query></View>"    
$ListItems=$List.GetItems($CAMLQuery)
$Ctx.Load($ListItems)
$Ctx.ExecuteQuery()

ForEach($Item in $ListItems)
{
        Write-Host "Processing item ID:" $Item.ID
        
        #Get Attachments from List Item
        $Attachments = $Item.AttachmentFiles
        $Ctx.Load($Attachments)
        $Ctx.ExecuteQuery()            

        #Get the attachment details
        foreach($Attachment in $Attachments)
        {
            $File = $Ctx.Web.GetFileByServerRelativeUrl($Attachment.ServerRelativeUrl)
            $Ctx.Load($File)
            $Ctx.Load($File.Author)
            $Ctx.ExecuteQuery()
            
            $AttachmentsData += New-Object PSObject -Property ([ordered]@{
                ItemID  = $Item.Id
                TotalAttachments = $Attachments.Count
                FileName = $File.Name
                URL = $File.ServerRelativeUrl
                AddedBy =  $File.Author.Email
                "FileSize (KB)" = [Math]::Round(($File.Length/1KB),2)
            })
        }
}
#Export Attachments Inventory to CSV
$AttachmentsData | Export-Csv $CSVPath -NoTypeInformation
Write-host "Attachments Inventory has been Exported to CSV File!" -f Green

This script generates a CSV file as:

powershell to get list attachments in sharepoint online

PnP PowerShell to Get All Attachments Inventory from a Site

Let’s get attachments data from all lists of a given site to a CSV report using PnP PowerShell:

#Parameters
$SiteURL = "https://crescent.sharepoint.com/sites/PMO"
$CSVPath = "C:\Temp\Attachments.csv"

#Connect to SharePoint Online
Connect-PnPOnline -Url $SiteURL -Interactive

#Get All Custom Lists from the Web
$Lists = Get-PnPList | Where-Object {$_.BaseTemplate -Eq 100}

$Resultset = @()
#Loop through each list and collect attachment data
ForEach($List in $Lists)
{
    #Get All List Items with Attachments
    $SPQuery = "<View Scope='RecursiveAll'><Query><Where><Eq><FieldRef Name='Attachments' /><Value Type='Boolean'>1</Value></Eq></Where></Query></View>"
    $ListItems = Get-PnPListItem -List $List -PageSize 2000 -Query $SPQuery
    
    #Loop through each item in the list
    ForEach ($ListItem in $ListItems)
    {
         #Get All Attachments of the List Item
        $Attachments = Get-PnPProperty -ClientObject $ListItem -Property "AttachmentFiles"
        
        #Collect Attachment Properties
        ForEach($Attachment in $Attachments)
        {
            $Resultset += New-Object PSObject -Property ([ordered]@{
                AttachmentName = $Attachment.FileName
                ServerRelativeUrl = $Attachment.ServerRelativeUrl
                ListName = $List.Title
                ItemID = $ListItem.ID
                TotalAttachments = $Attachments.Count
                CreatedBy = $ListItem.FieldValues.Author.LookupValue                
            })
        }
    }
}
#Export Report to a CSV File
$Resultset
$Resultset | Export-Csv -Path $CSVPath -NoTypeInformation

If you want to download all attachments from a list, use: SharePoint Online: Download Attachments from List using PowerShell

Salaudeen Rajack

Salaudeen Rajack - SharePoint Expert with Two decades of SharePoint Experience. Love to Share my knowledge and experience with the SharePoint community, through real-time articles!

One thought on “SharePoint Online: Get Attachments Report in a List using PowerShell

  • I tried the first script but for some reason I get an unauthorized error after entering credentials at the prompt, even though I have the SPO Admin role. Is there a certain format the credentials need to be in?

    Reply

Leave a Reply

Your email address will not be published.