#PowerPlatformTip 123: ‘Get Lowest / Highest SharePoint ID & TotalCount’

TL;DR: Get the lowest ID, highest ID and total count of a SharePoint list in Power Automate with three simple $orderby/ItemCount REST calls.

💡 Challenge

You need the highest, lowest, and total count of item IDs in a SharePoint list, to build reports, paginate, or manage data more efficiently, without scanning the whole list.

✅ Solution

Use three small HTTP requests against the SharePoint REST API to fetch the lowest ID, the highest ID and the total item count directly.

🔧 How It’s Done

1. Get the lowest ID

🔸 Send an HTTP GET to the SharePoint API: /_api/Web/Lists/GetByTitle('YourListName')/Items?$select=ID&$filter=ID gt 0&$top=1&$orderby=ID asc

🔸 Set the method to GET and the header accept: application/json; odata=nometadata.

2. Get the highest ID

🔸 Same request, ordered descending: /_api/Web/Lists/GetByTitle('YourListName')/Items?$select=ID&$filter=ID gt 0&$top=1&$orderby=ID desc

🔸 Use the same method and headers.

3. Get the total item count

🔸 Call the ItemCount endpoint: /_api/Web/Lists/GetByTitle('YourListName')/ItemCount

🔸 Again use GET with the same headers.

🎉 Result

You get the lowest, highest, and total count of IDs in your SharePoint list in seconds, perfect for reports and data analysis.

🌟 Key Advantages

🔸 Automated and fast data retrieval

🔸 Simplifies management of large lists

🔸 No manual searching required

---

🎥 Video Tutorial

---

🛠️ FAQ

1. Do I need any special permissions to run these HTTP requests? You need at least read permissions on the SharePoint list to retrieve IDs and item counts via the API. Ensure your connection in Power Automate has the appropriate access.

2. Can this approach handle large lists with more than 5,000 items? Yes. Retrieving the lowest or highest ID with $top=1 is efficient, but be aware of list view thresholds when filtering larger datasets. For total count, the ItemCount endpoint stays performant.

3. How can I adapt this to retrieve other columns or values? Adjust the $select and $orderby query parameters to include the desired fields (e.g., Title) and order by those columns as needed.

🔗 Related Tips

• #PowerPlatformTip 97: Enhanced Pagination, handle large SharePoint result sets efficiently.
• #PowerPlatformTip 95: Optimized SharePoint Queries, build fast, delegable list queries.