PowerShell Delete Large SharePoint List item 14 days old

user_15002837 0
Viewed 271

The script below works for a small sharepoint list but I cannot seem to get it to work with a large list over 700,000 rows.

Any suggestions would be greatly appreciated.

$list = "Audit Only Test"
$items = Get-PnPListItem -List $listName -Fields "Title","Created","ID","ShiftDate"

#Connect to PnP Online
Connect-PnPOnline -Url $SiteURL -UseWebLogin
   
#Define Query to get list items
$Query= "<View Scope='RecursiveAll'>
          <Query>
                <Where>
                    <And>
                        <Lt>
                            <FieldRef Name='ShiftDate' Type='DateTime'/>
                            <Value Type='DateTime' IncludeTimeValue='TRUE'>
                                <Today OffsetDays='-7'/>
                            </Value>
                        </Lt>
                        <Eq>
                            <FieldRef Name='FSObjType' /><Value Type='Integer'>0</Value>
                        </Eq>
                    </And>
                </Where>
            </Query>
         <RowLimit>500</RowLimit>
        </View>"
 
#Get All Items from the List in batches
$ListItems = Get-PnPListItem -List $ListName -Query $Query -PageSize 500

#Write-host "Total Number of Items Found:"$ListItems.count
foreach($item in $listItems)
{
 Write-Host "Deleting Item - $($item.Id)"
 #$item.deleteobject()
 Remove-PnPListItem -List $listName -Identity $item.Id -Force -ErrorAction Stop
 }
 Write-Host -f Green "`nAll List Items Deleted Successfully!"   ```
sharepoint
1 Answers

You could use the below command to delete the items:

Get-PnPListItem -List $ListName -Query $Query -PageSize 500 -ScriptBlock { Param($items)} | % { Remove-PnPListItem -List $ListName -Identity $_.Id -Force}

Update:

You don't have to use for each loop through the items, just use the code like the following would work for you:

Connect-PnPOnline -Url $SiteURL -UseWebLogin   
#Define Query to get list items
$Query= "<View Scope='RecursiveAll'>
          <Query>
                <Where>
                    <And>
                        <Lt>
                            <FieldRef Name='ShiftDate' Type='DateTime'/>
                            <Value Type='DateTime' IncludeTimeValue='TRUE'>
                                <Today OffsetDays='-7'/>
                            </Value>
                        </Lt>
                        <Eq>
                            <FieldRef Name='FSObjType' /><Value Type='Integer'>0</Value>
                        </Eq>
                    </And>
                </Where>
            </Query>
         <RowLimit>500</RowLimit>
        </View>"
$ListName="Yourlist name"
Get-PnPListItem -List $ListName -Query $Query -PageSize 10 -ScriptBlock { Param($items)} | % {
Write-Host "Deleting Item - $($_.Id)"
 Remove-PnPListItem -List $ListName -Identity $_.Id -Force}   
 Write-Host -f Green "`nAll List Items Deleted Successfully!"   ```
user_116630710
Related