Solved: API REST Tracket Cursor Based Pagination

VBLOT · ‎03-03-2023

Hello all,

The API i'm trying to get info from is : https://avisi-apps.gitbook.io/tracket/api/worklog-api

Which basically returns in a JSON format worklogs that employees did.

First I have to get a Bearer Token to authenticate, this part is done.

But I have another issue for paginate other this until the end.

I have an error that blocks me, I think I did the most of it but still missing a point here.

For the moment I have only 2 pages which I get like :

I do this that way because we have a cursor id in all pages except the last one which next-cursor is equal to null.

I basically want a way to fetch all pages until he gets no next-cursor or a next-cursor equals to null which means that's the last page.

Here is my code :

 Source = Json.Document(Web.Contents("https://v1-gateway-9e6mvodx.uk.gateway.dev/api/1.0/worklogs",[Headers = [#"Content-Type"="application/json", #"Authorization"=token]])),

    #"Converted to Table" = Record.ToTable(Source),
    #"Transposed to Table" = Table.Transpose(#"Converted to Table"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed to Table", [PromoteAllScalars=true]),

data = let
    Pagination = List.Generate( ()=> Source,
    each [worklogs] <> null,
    each 
        if Record.HasFields(_, "next-cursor")
        then Json.Document(Web.Contents("https://v1-gateway-9e6mvodx.uk.gateway.dev/api/1.0/worklogs?next=" & Text.From([#"next-cursor"]), [Headers=[Authorization=token]] )) 
        else 
            if [#"next-cursor"] = null
            then Json.Document(Web.Contents("https://v1-gateway-9e6mvodx.uk.gateway.dev/api/1.0/worklogs", [Headers=[Authorization=token]] ))
            else Record.FromList({null, null}, {"worklogs", "next-cursor"} ))
    in 
Pagination,

    #"Converted to Table1" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Ignore),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"worklogs", "next-cursor"}, {"worklogs", "next-cursor"})

in

    #"Expanded Column1"

Let me know if it's unclear or you need more information,

VB