topic Re: REST API JIRA : worklog in Developer

REST API JIRA : worklog

Florian89 — Fri, 27 Mar 2020 09:28:46 GMT

Hi everyone,

I am working for the first time with the JIRA API and PowerBI.

My goal : I want to extract all detailed worklogs from JIRA to build a dashboard for the team.

My issue : the query I am currently using just extract the consolidated time on a ticket. However I would need the detail by user.

The query I am currently using :

Json.Document(Web.Contents("https://XXX.atlassian.net/rest/api/2/searchstartAt=0&maxResults=100&jql=project=""YYYY""&expand=,names")),

Anyone knows which parameters should I insert to extract the complete worklog table?

Moreover, do you know how to extract more than 100 rows at the same time? For now I am using a main query which combined sereal query with hundred results rows.

Thank you

Re: REST API JIRA : worklog

Anonymous — Wed, 16 Nov 2022 05:59:05 GMT

hi,

define a function named getJIRADCPage:

(_jql as text, _fields as text, _startAt as text) => let Source = Json.Document( Web.Contents( "you company jira url", [ RelativePath="/rest/api/2/search", Query=[ maxResults="1000", jql=_jql, fields=_fields, startAt=_startAt ] ] )), issues = Source[issues], #"Converted to Table" = Table.FromList(issues, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"expand", "id", "self", "key", "fields"}, {"Column1.expand", "Column1.id", "Column1.self", "Column1.key", "Column1.fields"}) in #"Expanded Column1"

query body:

let Source = Json.Document(Web.Contents( "your company jira url", [ RelativePath="/rest/api/2/search", Query=[ maxResults="1000", jql="author=xxx", fields="key", startAt="0" ] ])), total1 = Number.Round(Source[total]/1000 + 0.5-1, 0, RoundingMode.Up), List = List.Transform({0..total1}, each _ * 1000), #"Converted to Table" = Table.FromList(List, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}}), #"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "Data", each GetJIRADCPage("author=xxx", "field1, field2", [Column1])), #till here, all issues belong to autor:xxx are gotten #"Expanded Data" = Table.ExpandTableColumn(#"Invoked Custom Function", "Data", {"Column1.key"}, {"Column1.key"}), #"Add worklog url" = Table.AddColumn(#"Expanded Data", "worklog url", each "https://your company jira url/rest/api/latest/issue/"&[Column1.key]&"/worklog"), #"Invoked Custom Function1" = Table.AddColumn(#"Add worklog url", "worklog", each Json.Document(Web.Contents([worklog url]))) in #"Invoked Custom Function1"

then shape data as you want 🙂

Re: REST API JIRA : worklog

Anonymous — Fri, 28 Oct 2022 19:05:06 GMT

Hi.

Can you explain idea abot this first funkcion?

Whi this is neeade and how I call it later?

regards

Re: REST API JIRA : worklog

Anonymous — Wed, 16 Nov 2022 06:30:11 GMT

purpose of this first function is we can tell JIRA get data starting at any index which specifed by _startAt as third parameter of this function.

total1 = Number.Round(Source[total]/1000 + 0.5-1, 0, RoundingMode.Up), //source[total] is the numbe of rows gottent from JIRA. so this line make for example total1=0 when total==600, total1=1 when total==1001

List = List.Transform({0..total1}, each _ * 1000), //we will have list{0,1} for example when total=1001.

#"Converted to Table" = Table.FromList(List, Splitter.SplitByNothing(), null, null, ExtraValues.Error), //convert list{0, 1} to table with only one column but 2 rows with row0=0, row1=1.

#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}}), //change int to type since we define _startAt as text in function.

#"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "Data", each GetJIRADCPage("author=xxx", "field1, field2", [Column1])), //then we call function we defined for each row of table we generated and specify _startAt by value of Column1 of this table.