Hello, colleagues!
I am starting to use the Monday.com API to extract data and use it at Power BI. I used the following algorithm:
-
A function that sends the API KEY, the board id, the limit, and the page number and receives only the itemId.
-
A function uses the first function and paginates the results.
-
A function that sends the itemId and receives all information from the column values.
PS.: the functions will be available at the end.
This algorithm takes around 20 minutes to extract a board with around 900 items with 40 columns. I am doing some actions to reduce time and would like your advice. My first attempt was to reduce the number of column values; however, this did not change the time consumption.
One point that I would like to understand is the limit logic. Besides my option, my first function is receiving all items. Does the API send all records if the complexity is under the limit?
First Function =>
(apikey as text, QuadroId as text, limite as number, pagina as number) =>
let
Fonte = Web.Contents(“https://api.monday.com/” & “v2”,
[
Headers=[
#“Method”=“POST”,
#“Content-Type”=“application/json”,
#“Authorization”=“Bearer " & apikey
],
Content=Text.ToBinary(”{““query””: “”{ boards (ids: " & QuadroId & “, limit:” & Number.ToText(limite) &“, page:” & Number.ToText(pagina) & “) { items { id name state relative_link } } }”“}”)
]
),
Dados = Json.Document(Fonte,65001)[data][boards]{0}[items]
in
Dados
Second Function
(apikey as text, QuadroId as text, limite as number) =>
let
Fonte = List.Generate(
() => [ offset = 1, data = fnConsultaItems( apikey, QuadroId, limite , 1)],
each not List.IsEmpty( [data] ),
each [ offset = [offset] + 1, data = fnConsultaItems( apikey, QuadroId, limite , [offset] + 1 )],
each [data]
){0}
in
Fonte
Third Function
(apikey, ItemId as text) =>
let
Fonte = Web.Contents(“https://api.monday.com/” & “v2”,
[
Headers=[
#“Method”=“POST”,
#“Content-Type”=“application/json”,
#“Authorization”=“Bearer " & apikey
],
Content=Text.ToBinary(”{““query””: “”{ items (ids: " & ItemId & “) { column_values { title type text } } }”“}”)
]
),
Dados = Json.Document(Fonte,65001)[data][items]
in
Dados