Please find my humble reply that i could find via my limited knowledge in combination with Gen AI and google search.
Let me know if it helps.
To efficiently retrieve only selected columns for items created within a specific date range from a SharePoint list with over 10,000 items, using the REST API, follow best practices for threshold handling and pagination. Your query structure is correct for selection and filtering, but you'll need to enhance it for large-volume retrieval due to SharePoint's 5,000-item view threshold.
Query Validation
Your provided REST API query correctly:
- Selects specific fields via $select
- Expands multi-person columns using $expand
- Filters by the Created date with $filter
- Limits results per call with $top=500
However, SharePoint REST API has a 5,000-item threshold. For lists above this size, you must use pagination with the ListItemCollectionPosition (server-driven paging) returned in response. $top=500 is appropriate for each page, but to get all items, you need to continue querying using the paging token.
Best Practices for Large Lists
- Use server-driven paging: After each request, check for an @odata.nextLink property in the response. Use its URL as the next request to retrieve further pages until no more pages remain.
- Avoid using $skip for big lists: It is inefficient and not recommended.
- Do not request all items at once: Stick to $top=500 or $top=1000 for optimal performance.
- Use index columns: Ensure the Created column is indexed, especially for large lists to prevent errors.
Pagination Example
- Run your query as posted.
- Inspect the response for @odata.nextLink
- If present, make the next REST call to that URL.
- Repeat until all items are retrieved.
Query Optimization (Sample)
_api/web/lists/GetByTitle('Tickets')/items?
$select=Id,Title,IssueTitle,Description,Category,Priority,Status,ExpectedDueDate,ResolutionNotes,SupportComments,ResolvedDate,ClosedDate,PhoneNumber,Created,Modified,LastUpdatedTime,MarkAsDelete,MirrorID,OnBehalfOf,AssignedTo/Id,AssignedTo/Title,AssignedTo/EMail,RequestedBy/Id,RequestedBy/Title,RequestedBy/EMail,OnBehalfName/Id,OnBehalfName/Title,OnBehalfName/EMail
&$expand=AssignedTo,RequestedBy,OnBehalfName
&$filter=Created ge datetime'2025-09-01T00:00:00Z' and Created le datetime'2025-09-12T23:59:59Z'
&$top=500
Use pagination as described above.
Additional Recommendations
- Index your Created column for better performance if not already done.
- If using Power Automate, consider the "Get items" action with Pagination enabled, though REST remains most flexible.
- For extremely large exports, consider using SharePoint Graph API or export to CSV via Power Automate or custom scripting for further automation.
References:
| Aspect |
Recommendation |
Reason |
Source |
| Select columns |
Use $select |
Limits data, faster |
|
| Date filter |
Index Created |
Avoid threshold errors |
|
| Pagination |
Use @odata.nextLink |
Efficient paging |
|
| Item count |
$top=500 or $top=1000 |
Stay under 5,000 threshold |
|
The query is correct for selection and date filtering. Add server-driven paging logic to handle more than 5,000 items, and index the Created column for threshold compliance.
![]()
![]()
Sources:
