0

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.

0
0

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

1
  • I tried using your C# solution, but I was not able to get it to work in powershell. Thank you.
    – Geeroc
    May 26 '20 at 2:16
0

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)
1
  • This helped me loop through 10005 list items, but i can't get the value of the $ListItem. The progress bar code is broken though $List.ItemCount should be $listItems.Count, but this syntax correction doesn't fix it because something is not write with my code.I will post "half" working code later. There is progress at least, thank you Lee_MSFT
    – Geeroc
    May 26 '20 at 2:53
0

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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