Need help migrating to API 2023-10 changes - using PowerQuery

Platform discussions
, , ,
3

Getting the max page number to create a fail safe stopping point:

() =>

let

//need a dynamic pageNum so it grows with the data volume

itemsCount = Web.Contents("https://api.monday.com/v2",

    \[   

        Headers=\[

            #"Method"="GET",

            #"Content-Type"="application/json",

            #"API-Version"=#"UseApiVersion",

            #"Authorization"="Bearer " & #"AuthKey"

        \],

    Content=Text.ToBinary("{""query"": ""query { boards(ids: " & BoardNbr_SalesPipeline & ") { items_count } }""}")

    \]),

itemsCountData = Json.Document(itemsCount)\[data\]\[boards\]{0}\[items_count\],

maxPageNum = Int64.From(((itemsCountData / 100) + 1))

in

maxPageNum

The function for generating the results of the API calls:

(pageNbr as number, optional continuationCursor as text, optional data as list, optional maxPage as number) =>

let

maxPageNum = 

    if pageNbr = 1

    then GetSalesPipelineMaxPageNbr()

    else maxPage,



firstPage = Text.ToBinary("{""query"": ""query { boards(ids: " & BoardNbr_SalesPipeline & ") { items_page (limit: 100) { cursor, items { id, name, updated_at, group { title }, column_values { column { title }, text, ... on BoardRelationValue { display_value }} } } } }""}"),

nextPage = Text.ToBinary("{""query"": ""query { next_items_page (limit: 100, cursor: \\""" & continuationCursor & "\\"") { cursor, items { id, name, updated_at, group { title }, column_values { column { title }, text, ... on BoardRelationValue { display_value }} } } }""}"),



Source = Json.Document(Web.Contents("https://api.monday.com/v2",

\[   

    Headers=\[

        #"Method"="POST",

        #"Content-Type"="application/json",

        #"API-Version"=#"UseApiVersion",

        #"Authorization"="Bearer " & #"AuthKey"

    \],

Content=

    if pageNbr = 1

    then firstPage

    else nextPage

\])),



nextCursor = 

    if pageNbr = 1

    then (Source)\[data\]\[boards\]{0}\[items_page\]\[cursor\]

    else (Source)\[data\]\[next_items_page\]\[cursor\],



currentData =

    if pageNbr = 1                                      //these both reach the same final state, the file list of records are then identical and can be appended without an issue

    then (Source)\[data\]\[boards\]{0}\[items_page\]\[items\]   //this only works on page 1 which has \[boards\] as part of the results response

    else (Source)\[data\]\[next_items_page\]\[items\],        //this only works on followup pages using the cursor and the \[next_items_page\]



appendedData =

    if currentData is null and data is null then {}

    else if data is null then List.Combine({{}, currentData})

    else if data is null then List.Combine({data, {}})

    else List.Combine({data, currentData}),



nextPageNbr = pageNbr + 1,



output =

    if nextCursor is null or nextPageNbr > maxPageNum   

    then appendedData

    else @GetSalesPipelineCursorPagination(nextPageNbr,nextCursor,appendedData,maxPageNum)

in

output