Rest API _ Json _ several pages _ automatically call the next_page_URL

Hi Mark,

the solution for you is actually simpler than the one provided in this post. As we know that there are 175 pages for you to retrieve, the "only" thing there is to do is to create a list/table of those URLs and call them in an additionall column:

•  Check and copy the URLs in [links] and see how the page-number is integrated.
•  Then create a list {1..175} and convert it to a table
• Add a column where you paste one URL and replace its page number by "& Text.From([Column1]) &" so that the value from the column will replace the hardcoded number in the URL

When you execute that query, all your tables should be loaded into this new column. You can then expand that new column and receive all your data in one table.

If the URL is not constructed in that simple way, we would have to switch to the List.Generate-method instead and expand [links] in every step to retrieve the next URL.

ImkeF

I have posted the error image

Also i wanted to give you some context I am able to call Freshservice API but it only returns 30 records

let
Source = Json.Document(Web.Contents("https://swinerton.freshservice.com/helpdesk/tickets/filter/all_tickets.json ", [Headers=[Authorization="Basic xxxxxxx", #"Content_Type"="application/json"]])),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"cc_email", "created_at", "deleted", "department_id_value", "display_id", "due_by", "email_config_id", "frDueBy", "fr_escalated", "group_id", "id", "isescalated", "owner_id", "priority", "requester_id", "responder_id", "source", "spam", "status", "subject", "ticket_type", "to_email", "updated_at", "description", "description_html", "status_name", "requester_status_name", "priority_name", "source_name", "requester_name", "responder_name", "to_emails", "department_name", "assoc_problem_id", "assoc_change_id", "assoc_change_cause_id", "assoc_asset_id", "custom_field"}, {"Column1.cc_email", "Column1.created_at", "Column1.deleted", "Column1.department_id_value", "Column1.display_id", "Column1.due_by", "Column1.email_config_id", "Column1.frDueBy", "Column1.fr_escalated", "Column1.group_id", "Column1.id", "Column1.isescalated", "Column1.owner_id", "Column1.priority", "Column1.requester_id", "Column1.responder_id", "Column1.source", "Column1.spam", "Column1.status", "Column1.subject", "Column1.ticket_type", "Column1.to_email", "Column1.updated_at", "Column1.description", "Column1.description_html", "Column1.status_name", "Column1.requester_status_name", "Column1.priority_name", "Column1.source_name", "Column1.requester_name", "Column1.responder_name", "Column1.to_emails", "Column1.department_name", "Column1.assoc_problem_id", "Column1.assoc_change_id", "Column1.assoc_change_cause_id", "Column1.assoc_asset_id", "Column1.custom_field"}),
#"Expanded Column1.cc_email" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.cc_email", {"cc_emails", "fwd_emails", "reply_cc", "tkt_cc"}, {"Column1.cc_email.cc_emails", "Column1.cc_email.fwd_emails", "Column1.cc_email.reply_cc", "Column1.cc_email.tkt_cc"}),
#"Expanded Column1.custom_field" = Table.ExpandRecordColumn(#"Expanded Column1.cc_email", "Column1.custom_field", {"level_2_12133", "level_3_12133", "preferred_method_of_contact_12133", "best_time_to_contact_12133", "phone_number_12133", "level_1_12133", "office_location_12133", "job_number_12133"}, {"Column1.custom_field.level_2_12133", "Column1.custom_field.level_3_12133", "Column1.custom_field.preferred_method_of_contact_12133", "Column1.custom_field.best_time_to_contact_12133", "Column1.custom_field.phone_number_12133", "Column1.custom_field.level_1_12133", "Column1.custom_field.office_location_12133", "Column1.custom_field.job_number_12133"}),
#"Expanded Column1.cc_email.cc_emails" = Table.ExpandListColumn(#"Expanded Column1.custom_field", "Column1.cc_email.cc_emails"),
#"Expanded Column1.cc_email.fwd_emails" = Table.ExpandListColumn(#"Expanded Column1.cc_email.cc_emails", "Column1.cc_email.fwd_emails"),
#"Expanded Column1.cc_email.reply_cc" = Table.ExpandListColumn(#"Expanded Column1.cc_email.fwd_emails", "Column1.cc_email.reply_cc"),
#"Expanded Column1.cc_email.tkt_cc" = Table.ExpandListColumn(#"Expanded Column1.cc_email.reply_cc", "Column1.cc_email.tkt_cc")
in
#"Expanded Column1.cc_email.tkt_cc"

On Freshservice site, it says that to iterate through pages, we need to do this 

Thus if you see the new query i have appended page to the end of the url, 

("https://swinerton.freshservice.com/helpdesk/tickets/filter/all_tickets/?pages

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"