Update/Delete All List-item in SharePoint online list with more than 30,000 records

Question

I have SharePoint Online List which contains more than 30,000 records.

There are two use-cases I need to work as below:

1. I have added a new column in that list, I want to update that column based on other columns in that list. here for adding a new record, this is not an issue but here I am talking about existing data. and I am not able to update all record due to list threshold limit.

2. Delete all records for that list, for this, I have used PowerShell script with CSOM to delete each record one by one. but again I got the list threshold limit issue, hence I have a limited bench of record to 5000. and it is time-consuming one record took one second to delete.

Is there any other way to do this stuff?

Note: I have migrated that data from other SQL database.

Answer 1

Querying data from SharePoint using CSOM works in batches. Every time you call ExecuteQuery() all operations are batched to one transaction and sent to the SharePoint. The key is to use ExecuteQuery() only when needed.

You can use CamlQuery and limit number of retrieving items.

Delete all items

$cc = New-Object Microsoft.SharePoint.Client.ClientContext("https://tenant.sharepoint.com/sites/some-site")
$cc.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials("[email protected]", (ConvertTo-SecureString "password" -AsPlainText -Force))

$list = $cc.Web.Lists.GetByTitle("Some list")
$query = New-Object Microsoft.SharePoint.Client.CamlQuery
$query.ViewXml = "<View><RowLimit>200</RowLimit></View>"

do
{
    $started = Get-Date
    $items = $list.GetItems($query)
    $cc.Load($items)
    $cc.ExecuteQuery()

    if ($items.Count -eq 0) { break }

    for ($i = 0; $i -lt $items.Count; $i++)
    {
        # Delete row 
        # Because DeleteObject() effectively change the collection the index 0 is not a mistake
        $items[0].DeleteObject()
    }

    $cc.ExecuteQuery()
    Write-Host "Time elapsed: $((Get-Date) - $started)"
} while ($true)

Update all items

$cc = New-Object Microsoft.SharePoint.Client.ClientContext("https://tenant.sharepoint.com/sites/some-site")
$cc.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials("[email protected]", (ConvertTo-SecureString "password" -AsPlainText -Force))

$list = $cc.Web.Lists.GetByTitle("Some list")
$query = New-Object Microsoft.SharePoint.Client.CamlQuery
$query.ViewXml = "<View><RowLimit>200</RowLimit></View>"

do
{
    $started = Get-Date
    $items = $list.GetItems($query)
    $cc.Load($items)
    $cc.ExecuteQuery()

    # It is important to update ListItemCollectionPosition of object $query with current position
    $query.ListItemCollectionPosition = $items.ListItemCollectionPosition

    if ($items.Count -eq 0) { break }

    for ($i = 0; $i -lt $items.Count; $i++)
    {
        # update columns as needed
        $items[$i].Update()
    }

    $cc.ExecuteQuery()
    Write-Host "Time elapsed: $((Get-Date) - $started)"
    # ListItemCollectionPosition is null if there is no other page
} while ($query.ListItemCollectionPosition -ne $null)

Answer 2

1. Create a test column - lets say "TestA".
2. Create a view with item limit 100 and filter condition - "TestA not equal to 1".
3. edit the view in datasheet view, in TestA column enter value "1", and drag it to next 100 items seen in the view.
4. This way your new column will get updated / required functionality will work.

For quickly updating the item, you can open view in multiple tabs and using paging update multiple items at once.