API Pagination with scroll ID

[Rabi]

Hi Everyone, 

I am trying to retrieve entire set of enrollments via API. The API supports pagination with scroll ID. However I am not able to retrieve entire set of data.

Below is the attached query:

let
  TokenResponse =
        Json.Document(
            Web.Contents(
                "https://auth.go1.com",
                [
                    RelativePath = "/oauth/token",
                    Headers = [
                        #"Content-Type" = "application/x-www-form-urlencoded",
                        Accept = "application/json"
                    ],
                    Content =
                        Text.ToBinary(
                            Uri.BuildQueryString(
                                [
                                    client_id = "",
                                    client_secret = "",
                                    grant_type = "client_credentials"
                                ]
                            )
                        )
                ]
            )
        ),

    AccessToken = TokenResponse[access_token],

    BaseUrl = "https://api.go1.com/v2/enrollments",

    FirstResponse =
        Json.Document(
            Web.Contents(
                BaseUrl,
                [
                    Query = [scroll = "true"],
                    Headers = [
                        #"api-version" = "2022-07-01",
                        Authorization = AccessToken
                    ]
                ]
            )
        ),

    ScrollId = FirstResponse[_scroll_id],
    FirstHits = FirstResponse[hits],

    GetScroll = (sid as text) =>
        Json.Document(
            Web.Contents(
                BaseUrl,
                [
                    Query = [
                        scroll = "true",
                        _scroll_id = sid
                    ],
                    Headers = [
                        #"api-version" = "2022-07-01",
                        Authorization = AccessToken
                    ]
                ]
            )
        ),

    Pages =
        List.Generate(
            () => [Sid = ScrollId, Result = FirstHits, More = true],

            each [More] = true and [Result] <> null,

            each
                let
                    next = GetScroll([Sid]),
                    hits = try next[hits] otherwise {},
                    newSid = try next[_scroll_id] otherwise null
                in
                    [
                        Sid = newSid,
                        Result = hits,
                        More = List.Count(hits) > 0 and newSid <> null
                    ],

            each [Result]
        ),

    Combined =
        List.Combine(Pages),

    ResultTable =
        Table.FromRecords(Combined)

in
    ResultTable

[v-prasare]

Hi @Rabi ,

We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.

@Ritaf1983 ,Thanks for your prompt response

Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community support

[Ritaf1983]

Hi @Rabi 

The issue is likely caused by the scroll ID parameter name used in the follow-up requests.

The API response returns `_scroll_id`, but for the next requests you should pass that value as `scrollId`, not as `_scroll_id`.

You may also want to include the Bearer prefix in the Authorization header.

Try this pattern:

let
TokenResponse =
Json.Document(
Web.Contents(
"https://auth.go1.com",
[
RelativePath = "oauth/token",
Headers = [
#"Content-Type" = "application/x-www-form-urlencoded",
Accept = "application/json"
],
Content =
Text.ToBinary(
Uri.BuildQueryString(
[
client_id = "",
client_secret = "",
grant_type = "client_credentials"
]
)
)
]
)
),

AccessToken = TokenResponse[access_token],

GetPage = (optional ScrollId as nullable text) as record =>
let
QueryParams =
if ScrollId = null then
[scroll = "true"]
else
[scroll = "true", scrollId = ScrollId],

Response =
Json.Document(
Web.Contents(
"https://api.go1.com",
[
RelativePath = "v2/enrollments",
Query = QueryParams,
Headers = [
#"api-version" = "2022-07-01",
Authorization = "Bearer " & AccessToken,
Accept = "application/json"
]
]
)
)
in
Response,

FirstPage = GetPage(null),

Pages =
List.Generate(
() =>
[
Page = FirstPage,
Hits = try FirstPage[hits] otherwise {},
ScrollId = try FirstPage[_scroll_id] otherwise null
],

each List.Count([Hits]) > 0,

each
if [ScrollId] = null then
[
Page = null,
Hits = {},
ScrollId = null
]
else
let
NextPage = GetPage([ScrollId]),
NextHits = try NextPage[hits] otherwise {},
NextScrollId = try NextPage[_scroll_id] otherwise null
in
[
Page = NextPage,
Hits = NextHits,
ScrollId = NextScrollId
],

each [Hits]
),

Combined = List.Combine(Pages),

ResultTable =
if List.Count(Combined) = 0 then
#table({}, {})
else
Table.FromRecords(Combined)
in
ResultTable

The important change is here:

[scroll = "true", scrollId = ScrollId]

instead of:

[scroll = "true", _scroll_id = sid]

I would also recommend temporarily returning the number of rows per page while testing, so you can confirm whether each scroll request is returning a new batch or only the first page again.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly