When querying a SharePoint list that has more than 5,000 items, you’ll likely receive an error like this:
This view cannot be displayed because it exceeds the list view threshold (5000 items) enforced by the administrator. Microsoft.SharePoint.SPQueryThrottledException', 'The attempted operation is prohibited because it exceeds the list view threshold.', "500 Server Error: Internal Server Error for url
Or, your query will only return the default 100 items. To get around this, pagination can be used to query the list and return ALL of the items.
Example:all_items = list_to_export.items.paged(1000).get().execute_query()
Full script using VS Code:
from office365.runtime.auth.authentication_context import AuthenticationContext
from office365.sharepoint.client_context import ClientContext
app_settings = {
'url': 'https://taco.sharepoint.com/sites/queso/',
'client_id': 'ID here',
'client_secret': 'shhhh its a secret',
}
context_auth = AuthenticationContext(url=app_settings['url'])
context_auth.acquire_token_for_app(client_id=app_settings['client_id'], client_secret=app_settings['client_secret'])
#connect to the site
ctx = ClientContext(app_settings['url'], context_auth)
ctx.execute_query()
#get the target list
list_title = "List of Tacos"
list_to_export = ctx.web.lists.get_by_title(list_title)
#get all of the list items
all_items = list_to_export.items.paged(1000).get().execute_query()
list_items = [item for item in all_items]
print("Item count: {0}".format(len(list_items)))
The example above connects to a SharePoint site using a client ID and secret, then queries the list. Again, the key here is using pagination (paged). You can adjust the page size to better fit your needs, but be sure to leave it under 5,000, or you will be back to square one.
