Forum Discussion

Florian89's avatar
Florian89
New Member
6 years ago

REST API JIRA : worklog

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 

 

 

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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 🙂
     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi.

      Can you explain idea abot this first funkcion?

      Whi this is neeade and how I call it later?

       

      regards

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        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.