The solution is both obvious and simple. Use an existing proxy service -- that will do it for you. I will show you how to beat the SharePoint Online list restriction in 5 minutes or less.
About the problem
This issue is well known and documented as a restriction on the SharePoint Online platform: in order to protect itself from large queries and memory intensive operations, SharePoint Online prevents you from returning more than 5,000 items at a time. This isn’t a new concept; it is being used by almost all Platform-as-a-Service and Software-as-a-Service providers, including SalesForce, Azure Tables, and many more.
The Solution
Let’s go straight to the solution, shall we? The answer as hinted previously is simple: build (or use an existing) REST proxy layer that will do the hard work for you. If you are interested in building it yourself, I will point you to this
article written by Jayakumar that describes the approach; all you need to do it to wrap the logic inside a REST service that your client code can call. This approach will take some time, as the logic can get a bit tricky in certain situations; for example, you will need to pass in the CAML query to the REST call if you want to also filter the item list. This will certainly take more than 5 minutes.
However, you can also reuse an existing service that will do this for you: Enzo Online. It is very simple, and you can use it at no charge for a single SharePoint site. You can also pass a SQL-like WHERE clause, and Enzo will build the CAML query for you on the fly, so you don’t have to deal with the complexities of CAML. Last but not least, you can also limit the columns returned to improve speed (for example, you just want the ID and Title columns).
Try It
To try it out, you will need to sign up for the service (at no charge) and register a configuration setting for SharePoint. The configuration setting should be a user in SharePoint that has access to the list. Once you configure Enzo, executing the call is trivial. The following is a sample REST call (you can use Fiddler for example) that returns all list items from the Companies list; you will need the Enzo Authentication Token and the name of the configuration settings you created in Enzo which contains the SharePoint credentials to use,
GET https://daas001.enzounified.com/bsc/sharepoint/getlistitemsex HTTP/1.1
authToken: myEnzoAuthToken
config: myconfig
viewname: Companies
To filter the list of items returned and select the ID and Title columns, simply add a “where” header with a SQL-like where clause (remember that SharePoint’s columns are case sensitive) and the “columns” header for the list of columns,
GET https://daas001.enzounified.com/bsc/sharepoint/getlistitemsex HTTP/1.1
authToken: myEnzoAuthToken
config: myconfig
viewname: Companies
columns: ID,Title
where: State=’FL’
The call to Enzo Online returns a JSON document with as many items as requested. There are many other capabilities and options available. Please check out the overview of the service
here.
As an Azure MVP, I always look for ways to help the community, and this is one of my contributions. Enzo Online is itself a Platform as a Service, and as a result, you are also limited by certain restrictions; the free edition is limited to 1MB of data per call. Please contact me if these restrictions get in the way of your testing.