Forum Discussion
Problems paging sharepoint rest api
- 1 year ago
GetSharePointData:
(StartRow as number, rl as number) => let Source = Json.Document(Web.Contents("https://companyz.sharepoint.com/_api/search/query", [Headers=[Accept="application/json", #"Content-Type"="application/json"], Query=[ querytext = "'(IsDocument:1)'", rowlimit = Text.From(rl), startrow = Text.From(StartRow) // Usare il parametro StartRow qui ]])), Rows = Table.FromRecords(Source[PrimaryQueryResult][RelevantResults][Table][Rows]), #"Added Custom" = Table.AddColumn(Rows, "Custom", each let #"Removed Columns" = Table.RemoveColumns(Table.FromRecords([Cells]),{"ValueType"}) in Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Key]), "Key", "Value")), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Cells"}), #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", Table.ColumnNames(#"Removed Columns"[Custom]{0})) in #"Expanded Custom"Query:
let PageSize = 1000, // Limite di risultati per ogni pagina MaxPages = 3, // Ad esempio, otteniamo 3 pagine (cambialo secondo necessità) Pages = List.Transform({0..MaxPages-1}, each GetSharePointData(_ * PageSize,PageSize)), // Ottieni tutte le pagine AllResults = Table.Combine(Pages) // Combina tutte le pagine in un'unica tabella in AllResults
ok, the table get this
= Json.Document(Web.Contents("https://sito.sharepoint.com/_api/search/query", [Headers=[Accept="application/json", #"Content-Type"="application/json"], Query=[
querytext = "'sharepoint'",
rowlimit = Text.From(50000),
startrow = Text.From(0) // Usare il parametro StartRow qui
]]))
but I'don't undestand the pagination
GetSharePointData:
(StartRow as number, rl as number) =>
let
Source = Json.Document(Web.Contents("https://companyz.sharepoint.com/_api/search/query", [Headers=[Accept="application/json", #"Content-Type"="application/json"], Query=[
querytext = "'(IsDocument:1)'",
rowlimit = Text.From(rl),
startrow = Text.From(StartRow) // Usare il parametro StartRow qui
]])),
Rows = Table.FromRecords(Source[PrimaryQueryResult][RelevantResults][Table][Rows]),
#"Added Custom" = Table.AddColumn(Rows, "Custom", each let #"Removed Columns" = Table.RemoveColumns(Table.FromRecords([Cells]),{"ValueType"})
in Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Key]), "Key", "Value")),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Cells"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", Table.ColumnNames(#"Removed Columns"[Custom]{0}))
in
#"Expanded Custom"
Query:
let
PageSize = 1000, // Limite di risultati per ogni pagina
MaxPages = 3, // Ad esempio, otteniamo 3 pagine (cambialo secondo necessità)
Pages = List.Transform({0..MaxPages-1}, each GetSharePointData(_ * PageSize,PageSize)), // Ottieni tutte le pagine
AllResults = Table.Combine(Pages) // Combina tutte le pagine in un'unica tabella
in
AllResults- yurop851 year ago
Helper I
Thanksssss!
- yurop851 year ago
Helper I
but I've some problems with the pagination.
for exampleHow intercept this exception ?
I don't get all the file in a tenant with this script - lbendlin1 year ago
Super User
Either choose "Remove Errors" as a transform or use "try ... otherwise ..."
- yurop851 year ago
Helper I
Works but with this call api rest I haven't all files in share point tenant.. why ?
I must add parameter in the call ? - lbendlin1 year ago
Super User
Are you a tenant admin?
- yurop851 year ago
Helper I
yes
- lbendlin1 year ago
Super User
how do you know you are not seeing all the files in the tenant? How many files do you have? (I know in our tenant we have hundreds of millions of files)
- yurop851 year ago
Helper I
Exists a microsoft dashboard the use the feed report.office.com that show the numer of file in the tenant.
This number not equas with the mine, also i play with this parameters and result is not the samelet PageSize = 1000, // Limite di risultati per ogni pagina MaxPages = 3, // Ad esempio, otteniamo 3 pagine (cambialo secondo necessità)