0

I can't use Office365-REST-Python-Client to retrieve Sharepoint files because it's only works on Windows not on Linux - Docker (it throws error:

File "/usr/local/lib/python3.8/site-packages/office365/runtime/auth/providers/saml_token_provider.py", line 102, in get_authentication_cookie logger.error(e.response.text) AttributeError: 'NoneType' object has no attribute 'text')

The type of authentication (either app client or user credentials) doesn't have parameter for me to add token manually.

I'm trying to retrieve the items via standard REST API but I don't know how to retrieve all rows (150+k rows). Is it possible to do it without JScript? I only want to retrieve files that have properties "Approved". But I don't know how to integrate CAML Query with REST API. I tried to use filter, but because of the 5000 max threshold, it didn't give me the result at all.

Any help is much appreciated.

1
  • Follow SharePoint's recommendations for REST GET requests for large lists. When you use the $filter= expression, make sure all the columns you use in constructing the filter are indexed. For some large lists/libs I have had which trigger the list view threshold error, I omitted the filter, made sure I had a very good $select= expression, was careful about using $expand=, and I did the filtering at the browser rather than at the server. It's not ideal, and especially if you have a request-response interim going into minutes rather than a few seconds. Figure what's best for you.
    – Steve Penn
    Commented Feb 5, 2022 at 18:50

1 Answer 1

1

This answer uses javascript code in the examples. But it should give you a reference for what is needed in another language: Functions, Vars, and Promises

It is always faster to use IDs for grabbing the contents of a list that is big.

This article describes a way to do this using the nextPage option.

The way to get speed out of it is to break up the total count of items into groups of 5000.

List is 26042 items long with give you six runs

  1. 1-5000
  2. 5001-10000
  3. 10001-15000
  4. 15001-20000
  5. 20001-25000
  6. 25001-26042

Store the smaller number in a var called low and the larger number in high.

Select only the columns or fields you really need to help speed things up, if possible.

Using $top will do two things, force SharePoint to get what ever number is specified (can't go over 5000 regardless of list throttle settings on farm) and remove the nextPage data from being added to your returned object. That will make joining the data together some what easier later on.

What it looks like with the numbers:

/_api/web/lists/getbytitle('ListName')/items?$top=5000&$filter=(Id gt 0) and (Id lt 5001)&$select=columnnames

es6 string interpolation:

`/_api/web/lists/getbytitle('ListName')/items?$top=5000&$filter=(Id gt ${low}) and (Id lt 
${high})&$select=columnnames`

OR

a regular string format: "/_api/web/lists/getbytitle('ListName')/items?$top=5000&$filter=(Id gt "+low+") and (Id lt "+high+")&$select=columnnames

You can build a function that will look at what the last Id (largest Id number) is and then created the batch url strings for you in an array.

Using a Promise.all() you can async call all the urls and let the Promise.all keep them in order. And if for some reason they aren't in order when you join the results, just do a sort on the result by Id.

There are two things to watch out for. First, a list with the highest Id of 26042 will likely not contain 26,042 items in it. So using the itemCount endpoint of SharePoint will not help you with creating the array of Id batches. If there are missing Id values (for instance, 230 and 564 were deleted, meaning the list is actually only 26040 items long) SharePoint will just ignore them rather than returning null results in their place.

Second, you will need to clean up the results before merging them together to remove the extra 'id' field SharePoint adds. All lists have ID and Id. JSON functions will error out if both are still present when you try to convert from string to an object. A simple find and replace with regex can remove the 'duplicate' heading and value.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.