CODEWITHSUNDEEP
powershellsharepointcsom
IAmKale
IAmKale

Reputation: 3426

How do I retrieve all items from a Sharepoint list using CSOM if I can only retrieve N items as a time?

I'm writing a Powershell script to retrieve all items from a list. I've been told by my superiors that I'm limited to pulling 200 rows at a time, so I wrote the following code with that in mind:

function getLookupValues($_ctx, $_listName, $_colToMatch)
{
    $lookupList = $_ctx.Web.Lists.GetByTitle($_listName)
    $query = [Microsoft.SharePoint.Client.CamlQuery]::CreateAllItemsQuery(200, 'ID', $_colToMatch)
    $vals = $lookupList.getItems($query)
    $_ctx.Load($lookupList)
    $_ctx.Load($vals)
    $_ctx.ExecuteQuery()

    return $vals
}

The list I'm testing has over 200 items in it. When I run this code, I retrieve only the first 200 items. I suppose that's as expected, but I figured since the query is called an "All Items" query, it might know to repeatedly query for 200 items until it reaches the end of the list. However, as I found out through testing, that isn't the case.

What's the proper way to retrieve every item in a list if I'm given a limit of N rows per query? Do I need to perform some kind of loop that repeatedly queries and dumps the results into a holding array until all of the items have been retrieved?

Upvotes: 3

Views: 28168

Answers (2)

IAmKale
IAmKale

Reputation: 3426

Madhur's answer was mostly correct, but I needed something that works with Sharepoint's Client library. Here's how I tweaked the code to achieve the desired result:

$mQueryRowLimit = 200
function getAllListItems($_ctx, $_listName, $_rowLimit = $mQueryRowLimit)
{
    # Load the up list
    $lookupList = $_ctx.Web.Lists.GetByTitle($_listName)
    $_ctx.Load($lookupList)

    # Prepare the query
    $query = New-Object Microsoft.SharePoint.Client.CamlQuery
    $query.ViewXml = "<View>
        <RowLimit>$_rowLimit</RowLimit>
    </View>"

    # An array to hold all of the ListItems
    $items = @()

    # Get Items from the List until we reach the end
    do
    {
        $listItems = $lookupList.getItems($query)
        $_ctx.Load($listItems)
        $_ctx.ExecuteQuery()
        $query.ListItemCollectionPosition = $listItems.ListItemCollectionPosition

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

    return $items
}

Upvotes: 4

Madhur Ahuja
Madhur Ahuja

Reputation: 22699

Technically, there is no one stopping you from retrieving all the rows at one go. If you see, the CreateAllItemsQuery documentation, there is an overload which will get you all the rows

http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.client.camlquery.createallitemsquery%28v=office.15%29.aspx

If you are given a limit of N rows per query, you will have to retrieve it via loop.

Have look at this article : http://blogs.msdn.com/b/kaevans/archive/2012/02/13/iterating-large-sharepoint-lists-with-powershell.aspx

I am replicating the code here:

$web = Get-SPWeb http://portal.sharepoint.com
$list = $web.Lists["LargeList"]

$spQuery = New-Object Microsoft.SharePoint.SPQuery
$spQuery.ViewAttributes = "Scope='Recursive'";
$spQuery.RowLimit = 2000
$caml = '<OrderBy Override="TRUE"><FieldRef Name="ID"/></OrderBy>' 
$spQuery.Query = $caml 

do
{
    $listItems = $list.GetItems($spQuery)
    $spQuery.ListItemCollectionPosition = $listItems.ListItemCollectionPosition
    foreach($item in $listItems)
    {
        Write-Host $item.Title
    }
}
while ($spQuery.ListItemCollectionPosition -ne $null)

Upvotes: 3

Related Questions