Sharepoint List - Threshold Exceeded when view is <5000

[dzvega]

Hi! I'm using the Sharepoint (SP) connector in Alteryx version 2018.3

I went through the guidelines posted onto Alteryx SP Connector; The bright text stating the error is not related to Alteryx wasn't missed 

I'm here to verify that the issue is indeed related to SP

The SP List I'm using is 180k + items. I created a View with a filter that cuts it down to '[today] -1' ~ 800 items, In SP I can see items in the view

The SP Connector guidelines post stated that I should only unfiltered and non sorted data, having a filter on the List View is the only way I can export any data from this. 

Is the SP connector limited to the whole List, or am I missing something else?

Thank you for your time!

-Dylan

[rafalolbert]

Hi @dzvega,

5K SharePoint limit is something i've been struggling for a while, the build in connector is great, but provided extended record set as well as lists with many fields this starts to create some problems.

I've decided to implement my own solution using Microsoft APIs, this is top level step by step:

1) create app for authentication, this is a great article which describes it in great detail: https://medium.com/@anoopt/accessing-sharepoint-data-using-postman-sharepoint-rest-api-76b70630bcbf

2) over to Alteryx - authentication steps are as below

3)  call https://site_address/_vti_bin/client.svc/ (replace site_address with your own site) - extract tenant_id

4) provided client_id and password, call: "https://accounts.accesscontrol.windows.net/" + [tenant_id] + "/tokens/OAuth/2" - obtain access_token

5) call https://site_address/_api/web/lists/getbytitle('list_name')/Items?$top=1&$orderby=ID desc (replace site_address and list_name with your values) - this will give you index of the latest record

6) call https://site_address/_api/web/Lists/GetByTitle('list_name')/items?$filter=ID gt min&$top=5000 for all 5K ranges to cover your recordset like:

7) at the end you get all your records into Alteryx - this works for lists and libraries, read and write (this is Microsoft's resource with all methods: https://docs.microsoft.com/en-us/sharepoint/dev/sp-add-ins/get-to-know-the-sharepoint-rest-service)

Example workflow is attached.

Hope this helps!

Thanks,

Rafal

#Excuse me, do you speak Alteryx?

[dzvega]

Thank you! I'll give this a try!

-Dylan