6

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.

4

2 Answers 2

5

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)
0
  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.

2
  • This is not feasible solution for column which depends on other column like calculated.. Jul 20, 2017 at 9:11
  • 1
    Calculation happens on item edit. That why its suggested to create test column and update random value using data sheet view. Edit event will trigger the calculation on the field. Hope that helps !!! Jul 20, 2017 at 15:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.