Sharepoint Online: Get List Items from Documents List with over 5000 Items using Powershell

Question

My goal is to create a powershell script that will change the created by value in my Sharepoint online documents list. Fyi this list contains 10005 items.

I'm at the very beggining and already stuck because of this error:

Exception calling "ExecuteQuery" with "0" argument(s): "The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator."

Here is my script:

##Variables for Processing
$SiteUrl = "https://xyz.sharepoint.com/sites/xyz"
$UserName ="user@xyz.com"
$Password ="xyz"
$ListName ="Documents"

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

#Setup the context
$Context = New-Object Microsoft.SharePoint.Client.ClientContext($SiteUrl)
$Context.Credentials = $Credentials

#Get the list (documents)
$Web = $context.Web
$documentsList = $Web.Lists.GetByTitle($ListName)

#Get All List items
$ListItems = $documentsList.GetItems([Microsoft.SharePoint.Client.CamlQuery]::CreateAllItemsQuery()) 
$context.Load($ListItems)
$context.ExecuteQuery()

Write-host "Total Number of List Items found:"$ListItems.count

I want to loop through the list items and change the created by value.

How do you get around the 5000 item limit in this case?

Thank you.

Answer 1

Please check the "ListItemCollectionPosition" property of the CamlQuery object and define the Rowlimit for example to 4500. Every query asks for 4500 items and querys again for the next 4500 items, as long as the query does not return 4500 items anymore.

        ClientContext clientContext = new ClientContext(siteUrl);
        SP.List oList = clientContext.Web.Lists.GetByTitle("Announcements");

        ListItemCollectionPosition itemPosition = null;

        while (true)
        {
            CamlQuery camlQuery = new CamlQuery();

            camlQuery.ListItemCollectionPosition = itemPosition;

            camlQuery.ViewXml = "<View><ViewFields><FieldRef Name='ID'/>" + 
                "<FieldRef Name='Title'/><FieldRef Name='Body'/>" + 
                "</ViewFields><RowLimit>5</RowLimit></View>";

            ListItemCollection collListItem = oList.GetItems(camlQuery);

            clientContext.Load(collListItem);

            clientContext.ExecuteQuery();

            itemPosition = collListItem.ListItemCollectionPosition;

            foreach (ListItem oListItem in collListItem)
            {
                Console.WriteLine("Title: {0}: \nBody: {1}", oListItem["Title"], oListItem["Body"]);
            }

            if (itemPosition == null)
            {
                break;
            }

            Console.WriteLine("\n" + itemPosition.PagingInfo + "\n");
        }

See this reference

Answer 2

You could try to use the batch logic in your PowerShell script.

$BatchSize = 500
$Query = New-Object Microsoft.SharePoint.Client.CamlQuery
$Query.ViewXml = "<View Scope='RecursiveAll'><Query><OrderBy><FieldRef Name='ID' Ascending='TRUE'/></OrderBy></Query><RowLimit Paged='TRUE'>$BatchSize</RowLimit></View>"

$Counter = 0
#Batch process list items - to mitigate list threshold issue on larger lists
Do { 
    #Get items from the list
    $ListItems = $List.GetItems($Query)
    $Ctx.Load($ListItems)
    $Ctx.ExecuteQuery()

    $Query.ListItemCollectionPosition = $ListItems.ListItemCollectionPosition

    #Loop through each List item
    ForEach($ListItem in $ListItems)
    {
        # to do
        $Counter++
        Write-Progress -PercentComplete ($Counter / ($List.ItemCount) * 100) -Activity "Processing Items $Counter of $($List.ItemCount)"
    }
} While ($Query.ListItemCollectionPosition -ne $null)

Answer 3

I'd like to thank Lee_MSFT for his help.
I used the code and inspiration from here.
I'm answering my own question for those who are looking for something complete.

Note that I was able to change the Created By value in my Sharepoint online documents list containing 10005 items, ONLY if I changed the Modified By value at the same time. If I only tried to do the Created By value, I would not be able to change anything. An explanation was found here

The solution that worked 100%, see code:

##Variables for Processing
$SiteUrl = "https://xyz.sharepoint.com/"
$UserName ="xyz@email.com"
$Password ="xyz"
$ListName ="Documents"

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

#Setup the context
$Context = New-Object Microsoft.SharePoint.Client.ClientContext($SiteUrl)
$Context.Credentials = $Credentials

#Get the list (documents)
$Web = $context.Web
#$Lists = $web.Lists;
$documentsList = $Web.Lists.GetByTitle($ListName)
$context.Load($documentsList)

# Prepare the query
$BatchSize = 500
$query = New-Object Microsoft.SharePoint.Client.CamlQuery
$Query.ViewXml = "<View Scope='RecursiveAll'><Query><OrderBy><FieldRef Name='ID' Ascending='TRUE'/></OrderBy></Query><RowLimit Paged='TRUE'>$BatchSize</RowLimit></View>"

#Get the User
$UserID="xyz@email.com"
$User = $Context.Web.EnsureUser($UserID)
$Context.Load($user)

#Get All List items
#change Created By and Modified By

    do
    {
    $ListItems = $documentsList.GetItems($query)
    $Context.Load($ListItems)
    $Context.ExecuteQuery()
    $query.ListItemCollectionPosition = $ListItems.ListItemCollectionPosition

    foreach($item in $listItems)
        {
            Try
            {
                # Add each item
                $item["Author"]=$User
                $item['Editor'] = $User
                $item.Update()
                $Context.ExecuteQuery()
            }
            Catch [System.Exception]
            {
                # This shouldn't happen, but just in case
                Write-Host $_.Exception.Message
            }
        }
    }
    While($query.ListItemCollectionPosition -ne $null)