Power Automate SharePoint Lookup at Scale (Large lists)

When planning your SharePoint automations there are a few things to consider if your list grows very large. SharePoint can handle lists that are in the millions of records, but you will need a plan to mitigate issues that will occur.

When using the SharePoint connectors Get Items or Send an HTTP request to SharePoint I ran into issues when using very large lists. Even with a solid retry policy I would get a timeout error or just no results.

I haven't seen this documented but here is how I solved my issue.

Make sure your target query is a text column. Make sure you index the SharePoint columns. You must do this early on before your list grows past 20k items.

Index your column: In your SharePoint list, go to settings and select Indexed columns - Create a new index - Create primary column - Choose your column.

I've had the best results with text columns.

(Beginner) Now you can use get items and use a standard Odata query. I haven't used this method in a while so YMMV.

(Expert) Myself I always use Send HTTP request to SharePoint. This is the gold standard in my option for SharePoint actions. Check out the SharePoint API for the complete list.

I will caveat that using a list over 100k items brings limitations such as security trimming so plan what you want to do.

Summary - If you are using the free tier of automate, with some planning you can build a robust automation or application.