CODEWITHSUNDEEP
powershellsharepoint-onlinepower-automate
entryton
entryton

Reputation: 290

Delete all items from SharePoint Online list

I have a SPO site with a list that contains about 12000 items.

I need to refresh these items everyday from an Excel file.

I have setup a Flow to delete all the items, but I am facing some issues:

What would be the best way to delete all the items quickly?

Upvotes: 1

Views: 2503

Answers (2)

entryton
entryton

Reputation: 290

In my particular scenario, I had a column with an OrganizationName, with values starting with almost every alphabet.

What I did was as below:

  • I declared an array having all the alphabets.
  • Did an apply to each to the array.
  • Within the loop, I queried the list for items where OrganizationName starts with the particular alphabet
  • Added the retrieved items to an array
  • for each item in the array, deleted it from the list

With the alphabet array, I was able to work around the threshold limit of 5000 items. Each alphabet would have a maximum of 1500 names.

Upvotes: 0

LZ_MSFT
LZ_MSFT

Reputation: 4208

I suggest you use PowerShell to achieve it.

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"

$SiteUrl = "https://tenant.sharepoint.com/sites/team"
$UserName="[email protected]"
$Password ="xxx"
$ListTitle="CustomList";

#Setup Credentials to connect
$context = new-object Microsoft.SharePoint.Client.ClientContext($siteUrl)
$context.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($UserName,(ConvertTo-SecureString $Password -AsPlainText -Force))

$list = $context.Web.Lists.GetByTitle($ListTitle)
$context.Load($list)
$context.ExecuteQuery()

$continue = $true
while($continue)
{
    Write-Host -NoNewline "." -foregroundcolor black -backgroundcolor yellow
    $query = [Microsoft.SharePoint.Client.CamlQuery]::CreateAllItemsQuery(100, "ID")
    $listItems = $list.GetItems($query)
    $context.Load($listItems)
    $context.ExecuteQuery()       
    if ($listItems.Count -gt 0)
    {
        for ($i = $listItems.Count-1; $i -ge 0; $i--)
        {
            $listItems[$i].DeleteObject()
        }
        $context.ExecuteQuery()
    }
    else
    {
        $continue = $false;
    }
}
Write-Host "All listitems deleted from list." -foregroundcolor black -backgroundcolor green

If you want to run the code everyday, we can create a Windows Task Scheduler and run the PowerShell every day. Refer to: How to Automate PowerShell Scripts with Task Scheduler

Upvotes: 1

Related Questions