topic Re: SharePoint REST API not returning all results in Power Query

SharePoint REST API not returning all results

mkubala — Thu, 07 Mar 2024 17:01:54 GMT

Hello,

I found the below code to use the SharePoint REST API to pull in large amounts of data from a SharePoint library. I have a document library with over 130,000 documents that I want to create a simple report from. Invoking the function seems to work, but when I do a descending sort on the Created date, it only has documents up to 9/12/2023 and there are a lot more recent documents than that. Am I getting throttled? Is there some way to see that in Power BI? Or maybe there is something off with the code, but I'm not familiar with Power Query so I can't tell. Any help would be appreciated.

let
Source = (tenant_name,site_name,list_name)=>
let
tenantname = tenant_name,
sitename = site_name, // if a subsite use "Site/SubSite"
listname = list_name,
baseurl = "https://" & tenantname & ".sharepoint.com/sites/" & sitename & "/_api/web/lists/GetByTitle('" & listname & "')/",
itemcount = Json.Document(Web.Contents(baseurl&"ItemCount", [Headers=[Accept="application/json"]]))[value],
skiplist = List.Numbers(0, Number.RoundUp(itemcount/5000), 5000),
#"Converted to Table" = Table.FromList(skiplist, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Skip"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Skip", type text}}),
fieldselect = "&$top=5000", // all fields with no expansion
#"Added Custom" = Table.AddColumn(#"Changed Type", "Items", each Json.Document(Web.Contents(baseurl& "items?$skipToken=Paged=TRUE%26p_ID=" & [Skip] & fieldselect, [Headers=[Accept="application/json"]]))),
#"Expanded Items" = Table.ExpandRecordColumn(#"Added Custom", "Items", {"value"}, {"value"}),
#"Expanded value" = Table.ExpandListColumn(#"Expanded Items", "value")

in
#"Expanded value"
in
Source

Re: SharePoint REST API not returning all results

lbendlin — Thu, 07 Mar 2024 21:29:41 GMT

Have you considered using the SharePoint Bulk API instead?

Re: SharePoint REST API not returning all results

mkubala — Fri, 08 Mar 2024 22:13:36 GMT

I haven't heard of that before, but I'll look it up and give it a shot.

Re: SharePoint REST API not returning all results

mkubala — Wed, 13 Mar 2024 15:15:06 GMT

I found this blog post which has an updated script that works: Updated – Get SharePoint List Data … Fast – Hoosier BI

let siteurl = "https://<your tenant url>/sites/<your site name>", // use ... /sites/<your site name>/<your subsite name> if applicable listname = "<your list name>", itemcount = Json.Document(Web.Contents(siteurl, [RelativePath = "_api/web/lists/GetByTitle('" & listname & "')/items?$select=ID&$orderby=ID%20desc&$top=1", Headers = [Accept = "application/json"]]))[value]{0}[ID], StartIDs = List.Numbers(0, Number.RoundUp(itemcount / 5000), 5000), ConvertToTable = Table.FromList(StartIDs, Splitter.SplitByNothing(), null, null, ExtraValues.Error), Add_EndIDs = Table.AddColumn(ConvertToTable, "Addition", each [Column1] + 4999, type number), RenamedColumns = Table.RenameColumns(Add_EndIDs, {{"Column1", "StartID"}, {"Addition", "EndID"}}), #"Changed Type" = Table.TransformColumnTypes(RenamedColumns, {{"StartID", type text}, {"EndID", type text}}), //Comment in only one of the fieldselect lines below, defining your select and expand columns using the example syntax shown fieldselect = "&$top=5000", // all fields with no expansion //fieldselect = "&$top=5000&$select=ID,Title,Date,PersonColumn,ChoiceColumn,LookupColumn", // list desired fields (no expansion) -No Spaces! //fieldselect = "&$top=5000&$select=ID,Title,Date,PersonColumn/LastName,PersonColumn/FirstName,ChoiceColumn,LookupColumn/Title,LookupColumn/Project,LookupColumn/ProjectStatus&$expand=PersonColumn,LookupColumn", //expand list fields - No Spaces! GetData = Table.AddColumn(#"Changed Type", "Items", each Json.Document(Web.Contents(siteurl, [RelativePath = "_api/web/lists/GetByTitle('" & listname & "')/items?$filter=(ID ge " & [StartID] & ") and (ID le " & [EndID] & ")" & fieldselect, Headers = [Accept = "application/json"]]))[value]), #"Removed Other Columns" = Table.SelectColumns(GetData, {"Items"}), #"Expanded Items" = Table.ExpandListColumn(#"Removed Other Columns", "Items") in #"Expanded Items"

or this one which accomplishes the same thing

let siteurl = "https://<your tenant url>/sites/<your site name>", // use ... /sites/<your site name>/<your subsite name> if applicable listname = "<your list name>", //Comment in only one of the fieldselect lines below, defining your select and expand columns using the example syntax shown fieldselect = "&$top=5000", // all fields with no expansion //fieldselect = "&$top=5000&$select=ID,Title,Date,PersonColumn,ChoiceColumn,LookupColumn", // list desired fields (no expansion) -No Spaces! //fieldselect = "&$top=5000&$select=ID,Title,Date,PersonColumn/LastName,PersonColumn/FirstName,ChoiceColumn,LookupColumn/Title,LookupColumn/Project,LookupColumn/ProjectStatus&$expand=PersonColumn,LookupColumn", //expand list fields - No Spaces! InitialWebCall = Json.Document(Web.Contents(siteurl, [RelativePath = "_api/web/lists/GetByTitle('" & listname & "')/items?$skipToken=Paged=TRUE" & fieldselect, Headers = [Accept = "application/json"]])), datalist = List.Generate(() => InitialWebCall, each List.Count([value]) > 0, each try Json.Document(Web.Contents(siteurl, [RelativePath = "_api" & Text.AfterDelimiter([odata.nextLink], "_api"), Headers = [Accept = "application/json"]])) otherwise [value = {}], each [value]), #"Converted to Table" = Table.FromList(datalist, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandListColumn(#"Converted to Table", "Column1") in #"Expanded Column1"