Hello,
I have a sharepoint online list with 14k+ rows and 250+columns, 35+ of them being lookups. We have reached the maximum number of fields that can be indexed.
It's a legacy list and it's used a source of data entry, not for documents/files.
I am trying to retrieve via REST api all the rows (and all its columns), however, I am facing an issue while doing so. Here is all that I've tried:
I used the Items endpoint, I get all columns but the lookup fields contain Id and not the actual LookUp value itself. I cannot use $expand because it crosses the threshold limit for number of lookups being expanded
I used the RenderListDataAsStream api, and configured the body of the request with a ViewXML property to define the CAML query and omitted the "ViewFields" defintion while specifying pagination and RowLimit. This works like a charm except not all fields are returned, some lookup fields are left out. It also leaves out some of the Indexed lookup fields. I cannot specify all the columns in "ViewFIelds" because then it throws a threshold limit error
I tried using Items/(ITEM_ID)/FieldValuesAsText and this works fine except the date fields have values that are not what we want/expect.
I then tried using Items/(ITEM_ID)/Versions?$top=1 (AND) Items/(ITEM_ID)/Versions?$filter=IsCurrentVersion eq true and this seems to solve the problem but again, some date field values are confusing
Most of the date fields are stored as DateOnly but when we retrieve via normal Items they return in UTC format which is fine.
Can someone help/point me in the right direction please?
Thanks
