How to retrieve more than 5k records from a SharePoint list using javascript and camlquery? Need the js code

Question

How to retrieve more than 5k records from a SharePoint list using javascript and camlquery? Need the js code

Answer 1

To retrieve more than 5,000 records from a SharePoint list using JavaScript and CAML query, you'll need to implement pagination, as SharePoint imposes a threshold limit of 5,000 items per request. Here is a sample approach to achieve this:

1. Initialize Variables:
   • Set up the necessary variables and configurations.
   • Define the CAML query.
2. Create a Function to Fetch Items:
   • Use the REST API to fetch items.
   • Implement pagination by using the ListItemCollectionPositionNext property.
3. Combine Results:
   • Aggregate results from each paginated request.

Here’s a JavaScript code snippet to help you get started:

const siteUrl = "https://your-sharepoint-site-url";
const listName = "YourListName";
const camlQuery = `
  <View>
    <Query>
      <Where>
        <!-- Your CAML query conditions go here -->
      </Where>
    </Query>
  </View>`;

let allItems = [];
let listItemCollectionPosition = null;

function getListItems() {
  return new Promise((resolve, reject) => {
    const queryUrl = `${siteUrl}/_api/web/lists/getbytitle('${listName}')/getitems`;
    
    const requestBody = {
      query: {
        __metadata: { type: "SP.CamlQuery" },
        ViewXml: camlQuery,
        ListItemCollectionPosition: listItemCollectionPosition
      }
    };

    $.ajax({
      url: queryUrl,
      type: "POST",
      headers: {
        "accept": "application/json;odata=verbose",
        "content-type": "application/json;odata=verbose",
        "X-RequestDigest": $("#__REQUESTDIGEST").val()
      },
      data: JSON.stringify(requestBody),
      success: function (data) {
        const results = data.d.results;
        allItems = allItems.concat(results);

        listItemCollectionPosition = data.d.ListItemCollectionPositionNext;

        if (listItemCollectionPosition) {
          getListItems().then(resolve).catch(reject);
        } else {
          resolve(allItems);
        }
      },
      error: function (error) {
        reject(error);
      }
    });
  });
}

getListItems()
  .then((items) => {
    console.log("All items retrieved:", items);
  })
  .catch((error) => {
    console.error("Error retrieving items:", error);
  });

Explanation

1. Variables Initialization:
   • siteUrl: URL of your SharePoint site.
   • listName: Name of the SharePoint list you want to retrieve items from.
   • camlQuery: The CAML query to filter items.
2. Function getListItems:
   • Makes an AJAX POST request to fetch list items using the REST API.
   • Uses ListItemCollectionPositionNext to handle pagination.
   • Aggregates all items into the allItems array.
3. Recursion and Promise Handling:
   • The function calls itself recursively until all pages of items are fetched.
   • Uses Promises to handle asynchronous operations.

This script should be run in the context of a SharePoint page or a custom application page where jQuery is available. Adjust the CAML query to fit your specific filtering needs.

Answer 2

Use pagination, like this:

function getListItems() {
    var context = SP.ClientContext.get_current();
    var list = context.get_web().get_lists().getByTitle('NomeDaLista');
    var camlQuery = new SP.CamlQuery();
    camlQuery.set_viewXml('<View Scope="RecursiveAll"><RowLimit>5000</RowLimit></View>');
    var items = [];
    var position = null;
    function getNextBatch() {
        if (position) {
            camlQuery.set_listItemCollectionPosition(position);
        }
        var listItemCollection = list.getItems(camlQuery);
        context.load(listItemCollection);
        context.executeQueryAsync(
            function() {
                var listItemEnumerator = listItemCollection.getEnumerator();
                while (listItemEnumerator.moveNext()) {
                    var listItem = listItemEnumerator.get_current();
                    items.push(listItem);
                }
                position = listItemCollection.get_listItemCollectionPosition();
                if (position) {
                    getNextBatch();
                } else {
                    // Todos os itens foram recuperados
                    console.log('Total de itens recuperados: ' + items.length);
                }
            },
            function(sender, args) {
                console.log('Erro: ' + args.get_message());
            }
        );
    }
    getNextBatch();
}

If there are any misunderstandings, please let me know

Best regards，

Maycon Novaes

If the Answer is helpful, please click "Accept Answer" and upvote it.