Forum Discussion
Rest API _ Json _ several pages _ automatically call the next_page_URL
This video will show you: https://www.youtube.com/watch?v=vhr4w5G8bRA&t=6s
Hi ImkeF,
Thanks a lot for your reply. It helps a lot.
I generate a script that do pretty much the same than the video.
It looks like this:let Source = Json.Document(Web.Contents(url, [Headers=[Authorization="your token"]])), iterations = Source[total_pages], // get the information within the response url = "you URL", // here goes your URL FnGetOnePage = (url) as record => let Source = Json.Document(Web.Contents(url, [Headers=[Authorization="yourtoken"]])), data = try Source[connections] otherwise null, //get the data of the first page next = try Source[next_page_url] otherwise null, // the script ask if there is another page res = [Data=data, Next=next] in res, GeneratedList = List.Generate( ()=>[i=0, res = FnGetOnePage(url)], each [i]<iterations and [res][Data]<>null, each [i=[i]+1, res = FnGetOnePage([res][Next])], each [res][Data]), #"Converti en table" = Table.FromList(GeneratedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error) in #"Converti en table"It works perfectly.
Have a good day,Paul
Helper III
I am trying to call APIs from Freshservice. After many weeks, I finally was able to call the site which returned only 30 records. I need to be able to paginate. I have visited every single link mentioned on the blogs but I am not able to follow...can any of you please see what I am doing wrong here
Source = Json.Document(Web.Contents("https://swinerton.freshservice.com/helpdesk/tickets/filter/all_tickets/?pages",[Headers=[Authorization="Basic xxxxxxxxx", #"Content_Type"="application/json"]])),
iterations = Source[total_pages], // get the information within the response
url = "https://swinerton.freshservice.com/helpdesk/tickets/filter/all_tickets/?pages", // here goes your URL
FnGetOnePage =
(url) as record =>
let
Source = Json.Document(Web.Contents("https://swinerton.freshservice.com/helpdesk/tickets/filter/all_tickets/?pages", [Headers=[Authorization="xxxx", #"Content_Type"="application/json"]])),
Page = try Source[connections] otherwise null, //get the data of the first page
next = try Source[next_page_url] otherwise null, // the script ask if there is another page
res = [Page=page, Next=next]
in
res,
GeneratedList =
List.Generate(
()=>[i=0, res = FnGetOnePage(url)],
each [i]<iterations and [res][Page]<>null,
each [i=[i]+1, res = FnGetOnePage([res][Next])],
each [Page][Data]),
#"Converti en table" = Table.FromList(GeneratedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Converti en table"
Helper III- ImkeF
Community Champion
You're missing the let-keyword at the beginning:
let Source = Json.Document(Web.Contents("https://swinerton.freshservice.com/helpdesk/tickets/filter/all_tickets/?pages",[Headers=[Authorizati... xxxxxxxxx", #"Content_Type"="application/json"]])), iterations = Source[total_pages], // get the information within the response url = "https://swinerton.freshservice.com/helpdesk/tickets/filter/all_tickets/?pages", // here goes your URL FnGetOnePage = (url) as record => let Source = Json.Document(Web.Contents("https://swinerton.freshservice.com/helpdesk/tickets/filter/all_tickets/?pages", [Headers=[Authorization="xxxx", #"Content_Type"="application/json"]])), Page = try Source[connections] otherwise null, //get the data of the first page next = try Source[next_page_url] otherwise null, // the script ask if there is another page res = [Page=page, Next=next] in res, GeneratedList = List.Generate( ()=>[i=0, res = FnGetOnePage(url)], each [i]<iterations and [res][Page]<>null, each [i=[i]+1, res = FnGetOnePage([res][Next])], each [Page][Data]), #"Converti en table" = Table.FromList(GeneratedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error) in #"Converti en table"- svishwanathan
- ImkeF
Hi svishwanathan,
sorry, but I cannot help you any further here.
This is an advanced topic and there is no standard solution for it currently. I you don't have a basic understanding of the M function, it is very difficult for me to tell you what you have to do. Both functions that might have to be adjusted depend on the actual data that comes from your source and this would mean that I would have to guide you through it step-by-step and you would have to share your screen after each step. This is consulting service in my eyes and I don't do it in the forums anymore.
So you might consider opening a new thread on this to raise the chance that someone else picks this up.