How to Optimize a PowerShell Script for SharePoint File Size Retrieval?
Asked by
TechyTurtle99
I'm working on a PowerShell script that pulls file information from a SharePoint document library to help a department reduce their storage usage. The script seems to hang when processing around 250,000 items. I'm wondering if this is due to a memory limit on the $Results variable. Here's the script I'm using:
```powershell
# Parameters
$SiteURL = "https://xxx.sharepoint.com/sites/FundingDocuments"
$ListName = "Funding Documents"
$ReportOutput = "C:TempFileSizeRpt.csv"
# Connect to SharePoint Online site
Install-Module PNP-powershell -scope CurrentUser
Connect-PnPOnline $SiteURL -Interactive
# Initialize output object
$Results = New-Object System.Collections.Generic.List[Object]
# Get all items from the document library
$List = Get-PnPList -Identity $ListName
$ListItems = Get-PnPListItem -List $ListName -PageSize 1000 | Where { $_.FileSystemObjectType -eq "File" }
Write-Host "Total Number of Items in the List: $($List.ItemCount)"
$ItemCounter = 0
# Iterate
foreach ($Item in $ListItems) {
$ItemCounter++
try {
$FileName = $Item.FieldValues.FileLeafRef
$RelativeURL = $Item.FieldValues.FileDirRef
$FileSize = $Item.FieldValues.'File_x0020_Size'
$Object = New-Object PSObject -Property ([ordered]@{
FileName = $FileName
RelativeURL = $RelativeURL
FileSize = $FileSize
})
$Results.Add($Object)
Write-Progress -PercentComplete (($ItemCounter / $List.ItemCount) * 100) -Activity "Processing Items $ItemCounter of $($List.ItemCount)" -Status "Getting data from item '$FileName'"
} catch {
Write-Warning "Error processing item $ItemCounter $Item.FieldValues.FileLeafRef"
}
}
```
Is there a limit on the number of items I can work with in $Results, and how can I improve this script to avoid hanging up?
Answers
Before stressing about limits, have you thought about why you're retrieving metadata for each item one by one? Instead of adding each file's data to $Results individually, you could reduce the number of calls significantly by querying the list view. If you have a ton of items, doing it 1000 at a time might make more sense than looping all 250,000. A bit of optimization can save you a lot of trouble!
Reply to this answer
It sounds like you might be maxing out your script's memory when you load all those items. Consider using a pipeline for better memory efficiency instead of loading everything into $Results at once. This can help reduce overhead. Also, check your memory usage while running the script – if it's pegged out, that's probably your bottleneck!
Reply to this answer
Add your answer
Answers are reviewed before publication.
TechyTurtle99: Good point! That could really streamline things. It’s just that the SharePoint setups we have are a bit chaotic, so I was hoping to list all the junk and then help the department clear it out. But I see the need for efficiency.