Sharepoint List - Threshold Exceeded when view is <5000

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

Best Practices

Sharepoint

Accepted answers

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?

workflow.yxmd

All comments

rafalolbert

Hi @dzvega,

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:

Example workflow is attached.

Hope this helps!

Thanks,

Rafal

#Excuse me, do you speak Alteryx?

workflow.yxmd

dzvega

Thank you! I'll give this a try!

-Dylan

billhand

The example you attached does not include the method that use to get the actual records from SharePoint. What are you using to get the list items after you get the increments?

Thanks

kumarsah

Hi rafalolbert,

Can you please explain what goes into the last macro tool? A sample of the macro will be helpful.

dzvega

Hi All,

A few steps to get the sharepoint connector to work around the threshold

1. Get Admin rights to the sharepoint site to be able to limit your sharepoint list view

Then pull from that list you created

This connector stopped worked for me after a while, I eventually had to move to power automate to pull from the list as a .csv

You can then pull the .csv with alteryx, format and then store as needed

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335