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

Question

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?

Answer 1

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
}