Tag Archives: jira

Power Query and Jira Search API Return Everything Project Related

Posted on by
Reply

From Power BI, how do you retrieve all the issues related to a Jira project?

To follow along with this post, you’ll need the following:
Power Query – Excel or Power BI
Jira API key
Powershell, Python, or a tool to base64 encode a string

To get a Jira API key, navigate to your Jira homepage, click your account icon (top right), and select Manage account. The shortcut to the account page is here: https://id.atlassian.com/manage-profile/security
From the account page, click on the Security header link, then click on Create and manage API tokens.


From the API Tokens page, click on the Create API token button. When the window opens, enter a value in the Label field and click the Create button. From your new API token window, copy the API token value. YOU WILL NEED this later in this demo.

When accessing the Jira API, basic auth is used, and for this example, we need to base64 encode the login credentials. You can use your favorite programming/scripting language or a website like Base64Encode.org to encode the credentials. In a production scenario, I do NOT suggest using a public website to encode/decode anything. This is just a demo; my API key will be revoked once I finish writing this. 

Example string of what needs to be encoded:
 Your Jira login email address + colon + API token
 youEmail@example:APItoken

Example:
 ian@example.com:ETETT3xFfGF009ETR_3bbA7Gk_ZLzPCAocvKcAvSzKe5-ysU_8fGwBxuRSsyx7efUcvaTACOSh3sgJWGtictvqGBF0yCy3ZzzKb39_gHBgYxnxjBURRITO3ofEWea_Wf4P9XWWmiNACHOWxUUA7O9cwFhH9WO6hq4-yAwEnbQUESOc=AEAA3875

Encoded value:
aWFuQGV4YW1wbGUuY29tOkVURVRUM3hGZkdGMDA5RVRSXzNiYkE3R2tfWkx6UENBb2N2S2NBdlN6S2U1LXlzVV84Zkd3Qnh1UlNzeXg3ZWZVY3ZhVEFDT1NoM3NnSldHdGljdHZxR0JGMHlDeTNaenpLYjM5X2dIQmdZeG54akJVUlJJVE8zb2ZFV2VhX1dmNFA5WFdXbWlOQUNIT1d4VVVBN085Y3dGaEg5V082aHE0LXlBd0VuYlFVRVNPYz1BRUFBMzg3NQ==

Next, we need to open Power Query to capture the Jira data.
Excel: Data tab –> Get Data –> Launch Power Query editor
Power BI: Home tab –>Transform data –> Transform data

In Power Query, select New Source, then select Blank Query. From the toolbar, click Advanced Editor. Delete everything in the Query window. Below is the M code for connecting to Jira and parsing the API response. You will want to update the code to replace the Url value, the Authorization value, and jql=”project = YourProject” values! You can get the URL and YourProject value from your Jira project homepage. 

let
    Source = (startAt as number) as table =>
    let
        Url = "https://YOUR-COMPANY.atlassian.net/rest/api/3/search",
        WebContents = Web.Contents(Url,
            [
                Headers = [#"Authorization"="Basic aWFuQGV4YW1wbGUuY29tOkVURVRUM3hGZkdGMDA5RVRSXzNiYkE3R2tfWkx6UENBb2N2S2NBdlN6S2U1LXlzVV84Zkd3Qnh1UlNzeXg3ZWZVY3ZhVEFDT1NoM3NnSldHdGljdHZxR0JGMHlDeTNaenpLYjM5X2dIQmdZeG54akJVUlJJVE8zb2ZFV2VhX1dmNFA5WFdXbWlOQUNIT1d4VVVBN085Y3dGaEg5V082aHE0LXlBd0VuYlFVRVNPYz1BRUFBMzg3NQ==", #"Content-Type"="application/json"],
                Query=[startAt=Text.From(startAt), jql="project = YourProject"]
            ]
        ),
        ParsedJson = Json.Document(WebContents),
        Issues = ParsedJson[issues],
        TableFromJson = Table.FromList(Issues, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        Total = ParsedJson[total],
        NextStartAt = startAt + List.Count(Issues)
    in
        if NextStartAt < Total then
            Table.Combine({TableFromJson, @Source(NextStartAt)})
        else
            TableFromJson,

    InitialCall = Source(0),
    #"Expanded Column1" = Table.ExpandRecordColumn(InitialCall, "Column1", {"expand", "id", "self", "key", "fields"}, {"Column1.expand", "Column1.id", "Column1.self", "Column1.key", "Column1.fields"}),
    #"Expanded Column1.fields" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.fields", {"statuscategorychangedate", "customfield_10990", "fixVersions", "customfield_10991", "customfield_10992", "resolution", "customfield_10751", "customfield_10104", "customfield_10984", "customfield_10985", "customfield_10986", "customfield_10987", "customfield_10989", "lastViewed", "customfield_10462", "priority", "customfield_10980", "customfield_10100", "customfield_10463", "customfield_10101", "customfield_10465", "customfield_10102", "customfield_10103", "labels", "customfield_10973", "customfield_10974", "customfield_10732", "customfield_10733", "customfield_10975", "customfield_10976", "aggregatetimeoriginalestimate", "customfield_10977", "timeestimate", "customfield_10978", "versions", "customfield_10979", "issuelinks", "assignee", "status", "components", "customfield_10450", "customfield_10451", "customfield_10452", "customfield_10453", "customfield_10454", "customfield_10841", "customfield_10445", "customfield_10842", "customfield_10843", "customfield_10447", "customfield_10448", "customfield_10844", "customfield_10569", "customfield_10449", "aggregatetimeestimate", "customfield_10728", "creator", "subtasks", "reporter", "aggregateprogress", "customfield_10200", "customfield_10676", "customfield_10830", "customfield_10831", "customfield_10710", "customfield_10438", "progress", "votes", "issuetype", "timespent", "project", "aggregatetimespent", "customfield_11003", "customfield_10423", "customfield_10544", "customfield_10545", "customfield_10424", "customfield_10942", "customfield_10546", "customfield_10821", "customfield_10822", "customfield_10943", "customfield_10427", "customfield_10823", "customfield_10944", "customfield_10945", "customfield_10428", "resolutiondate", "customfield_10946", "customfield_10827", "customfield_10948", "workratio", "customfield_10949", "customfield_10828", "customfield_10829", "watches", "created", "customfield_10541", "customfield_10543", "customfield_10533", "customfield_10654", "customfield_10655", "customfield_10930", "customfield_10931", "customfield_10414", "customfield_10656", "customfield_10657", "customfield_10415", "customfield_10811", "customfield_10933", "customfield_10537", "customfield_10812", "customfield_10416", "customfield_10658", "customfield_10659", "customfield_10538", "customfield_10417", "customfield_10935", "customfield_10418", "customfield_10419", "customfield_10818", "customfield_10819", "updated", "timeoriginalestimate", "customfield_10492", "description", "customfield_10893", "customfield_10531", "customfield_10895", "customfield_10653", "customfield_10006", "customfield_10007", "security", "customfield_10801", "customfield_10802", "customfield_10805", "customfield_10806", "customfield_10807", "customfield_10928", "customfield_10809", "summary", "customfield_10000", "customfield_10001", "customfield_10002", "customfield_10640", "customfield_10641", "customfield_10642", "customfield_10753", "customfield_10478", "customfield_10633", "customfield_10513", "environment", "customfield_10637", "customfield_10516", "customfield_10879", "customfield_10517", "duedate", "customfield_10639"}, {"Column1.fields.statuscategorychangedate", "Column1.fields.customfield_10990", "Column1.fields.fixVersions", "Column1.fields.customfield_10991", "Column1.fields.customfield_10992", "Column1.fields.resolution", "Column1.fields.customfield_10751", "Column1.fields.customfield_10104", "Column1.fields.customfield_10984", "Column1.fields.customfield_10985", "Column1.fields.customfield_10986", "Column1.fields.customfield_10987", "Column1.fields.customfield_10989", "Column1.fields.lastViewed", "Column1.fields.customfield_10462", "Column1.fields.priority", "Column1.fields.customfield_10980", "Column1.fields.customfield_10100", "Column1.fields.customfield_10463", "Column1.fields.customfield_10101", "Column1.fields.customfield_10465", "Column1.fields.customfield_10102", "Column1.fields.customfield_10103", "Column1.fields.labels", "Column1.fields.customfield_10973", "Column1.fields.customfield_10974", "Column1.fields.customfield_10732", "Column1.fields.customfield_10733", "Column1.fields.customfield_10975", "Column1.fields.customfield_10976", "Column1.fields.aggregatetimeoriginalestimate", "Column1.fields.customfield_10977", "Column1.fields.timeestimate", "Column1.fields.customfield_10978", "Column1.fields.versions", "Column1.fields.customfield_10979", "Column1.fields.issuelinks", "Column1.fields.assignee", "Column1.fields.status", "Column1.fields.components", "Column1.fields.customfield_10450", "Column1.fields.customfield_10451", "Column1.fields.customfield_10452", "Column1.fields.customfield_10453", "Column1.fields.customfield_10454", "Column1.fields.customfield_10841", "Column1.fields.customfield_10445", "Column1.fields.customfield_10842", "Column1.fields.customfield_10843", "Column1.fields.customfield_10447", "Column1.fields.customfield_10448", "Column1.fields.customfield_10844", "Column1.fields.customfield_10569", "Column1.fields.customfield_10449", "Column1.fields.aggregatetimeestimate", "Column1.fields.customfield_10728", "Column1.fields.creator", "Column1.fields.subtasks", "Column1.fields.reporter", "Column1.fields.aggregateprogress", "Column1.fields.customfield_10200", "Column1.fields.customfield_10676", "Column1.fields.customfield_10830", "Column1.fields.customfield_10831", "Column1.fields.customfield_10710", "Column1.fields.customfield_10438", "Column1.fields.progress", "Column1.fields.votes", "Column1.fields.issuetype", "Column1.fields.timespent", "Column1.fields.project", "Column1.fields.aggregatetimespent", "Column1.fields.customfield_11003", "Column1.fields.customfield_10423", "Column1.fields.customfield_10544", "Column1.fields.customfield_10545", "Column1.fields.customfield_10424", "Column1.fields.customfield_10942", "Column1.fields.customfield_10546", "Column1.fields.customfield_10821", "Column1.fields.customfield_10822", "Column1.fields.customfield_10943", "Column1.fields.customfield_10427", "Column1.fields.customfield_10823", "Column1.fields.customfield_10944", "Column1.fields.customfield_10945", "Column1.fields.customfield_10428", "Column1.fields.resolutiondate", "Column1.fields.customfield_10946", "Column1.fields.customfield_10827", "Column1.fields.customfield_10948", "Column1.fields.workratio", "Column1.fields.customfield_10949", "Column1.fields.customfield_10828", "Column1.fields.customfield_10829", "Column1.fields.watches", "Column1.fields.created", "Column1.fields.customfield_10541", "Column1.fields.customfield_10543", "Column1.fields.customfield_10533", "Column1.fields.customfield_10654", "Column1.fields.customfield_10655", "Column1.fields.customfield_10930", "Column1.fields.customfield_10931", "Column1.fields.customfield_10414", "Column1.fields.customfield_10656", "Column1.fields.customfield_10657", "Column1.fields.customfield_10415", "Column1.fields.customfield_10811", "Column1.fields.customfield_10933", "Column1.fields.customfield_10537", "Column1.fields.customfield_10812", "Column1.fields.customfield_10416", "Column1.fields.customfield_10658", "Column1.fields.customfield_10659", "Column1.fields.customfield_10538", "Column1.fields.customfield_10417", "Column1.fields.customfield_10935", "Column1.fields.customfield_10418", "Column1.fields.customfield_10419", "Column1.fields.customfield_10818", "Column1.fields.customfield_10819", "Column1.fields.updated", "Column1.fields.timeoriginalestimate", "Column1.fields.customfield_10492", "Column1.fields.description", "Column1.fields.customfield_10893", "Column1.fields.customfield_10531", "Column1.fields.customfield_10895", "Column1.fields.customfield_10653", "Column1.fields.customfield_10006", "Column1.fields.customfield_10007", "Column1.fields.security", "Column1.fields.customfield_10801", "Column1.fields.customfield_10802", "Column1.fields.customfield_10805", "Column1.fields.customfield_10806", "Column1.fields.customfield_10807", "Column1.fields.customfield_10928", "Column1.fields.customfield_10809", "Column1.fields.summary", "Column1.fields.customfield_10000", "Column1.fields.customfield_10001", "Column1.fields.customfield_10002", "Column1.fields.customfield_10640", "Column1.fields.customfield_10641", "Column1.fields.customfield_10642", "Column1.fields.customfield_10753", "Column1.fields.customfield_10478", "Column1.fields.customfield_10633", "Column1.fields.customfield_10513", "Column1.fields.environment", "Column1.fields.customfield_10637", "Column1.fields.customfield_10516", "Column1.fields.customfield_10879", "Column1.fields.customfield_10517", "Column1.fields.duedate", "Column1.fields.customfield_10639"}),
    #"Expanded Column1.fields.status" = Table.ExpandRecordColumn(#"Expanded Column1.fields", "Column1.fields.status", {"name", "id"}, {"Column1.fields.status.name", "Column1.fields.status.id"}),
    #"Expanded Column1.fields.assignee" = Table.ExpandRecordColumn(#"Expanded Column1.fields.status", "Column1.fields.assignee", {"accountId", "emailAddress", "displayName"}, {"Column1.fields.assignee.accountId", "Column1.fields.assignee.emailAddress", "Column1.fields.assignee.displayName"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1.fields.assignee",{{"Column1.fields.updated", type datetimezone}}),
    #"Expanded Column1.fields.issuetype" = Table.ExpandRecordColumn(#"Changed Type", "Column1.fields.issuetype", {"id", "name"}, {"Column1.fields.issuetype.id", "Column1.fields.issuetype.name"}),
    #"Expanded Column1.fields.reporter" = Table.ExpandRecordColumn(#"Expanded Column1.fields.issuetype", "Column1.fields.reporter", {"accountId", "emailAddress", "displayName"}, {"Column1.fields.reporter.accountId", "Column1.fields.reporter.emailAddress", "Column1.fields.reporter.displayName"}),
    #"Expanded Column1.fields.priority" = Table.ExpandRecordColumn(#"Expanded Column1.fields.reporter", "Column1.fields.priority", {"name", "id"}, {"Column1.fields.priority.name", "Column1.fields.priority.id"})
in
    #"Expanded Column1.fields.priority"

After pasting the M code in the Query window, click Done. When the Access Web content window opens, select Anonymous!

Once connected, Power Query will hit the Jira API and loop through the response, building a rich dataset. In the right column, my M code added a few extra Applied Steps. You can remove these or add steps to clean up the returned dataset.

When you are ready, click the Close & Apply button. The next couple of screenshots are focused on Power BI, but the same data will be available in Excel. Here, you can see the total number of items returned by the calls to the Jira API from Power Query.

One big thing to note: if you don’t need to pull the entire dataset down from Jira, try creating sample JQL queries and copying the URL value to Power Query. I grabbed this screenshot from the Issues page of my project. Note that adding more filters will change the URL to reflect the update. The URL contains the JQL query string.

You’d want to modify This section of Power Query M code.

Use Power Automate to Create Jira Tasks

Posted on by
9

I’m working on a Power Pages project that requires a Jira service desk task to be created for each portal submission. Out of the box, Jira provides a simple connector to create tasks and requests, but the connect falls short of handling field types other than simple text. This means choice, checkbox, and dropdown fields are not available. This only leaves a couple of options, and I opted to use a simple HTTP action to create the tasks.

Basic overview of what I’ll be creating:
Flow that’s triggered by a dataverse row creation
Create a Jira task and populate metadata
Attach a file to the Jira task

Jira fields and types:
Issue Type – Choice
Request Type – Choice
Tortilla – Choice
Meat – Choice
Veggies – Checkbox multi-select
Number of Tacos – Number
Pickup Date Time – Date and Time
Summary – Text
Attachment – Attachment

Interfacing with the Jira API requires knowing a little about the fields you’ll be updating and the project and issue type you want to use. If you haven’t created one already, you need a Jira API token to work with the API.


Request type:
Go to Project Settings, then look at the URL and copy the value after pid=
https://taco.atlassian.net/secure/project/EditProject!default.jspa?pid=10001

With the ID, you can query the service desk request-types endpoint
https://taco.atlassian.net/rest/servicedesk/1/servicedesk/request/10001/request-types
In the returned payload, note the portal key and key values; combine the two, and you have the request type value tr/9f7c4029-6d23-4cb1-bb8a-02d0050d944b

Project key:
The project key is available on the project settings page, listed under the name field.
Example: TACOS

Issue type:
For simplicity, I’m only dealing with one issue type, and I captured the issueType value using the request-types endpoint noted above.
Example: “issueType”: 10015

For the remaining field values, you can get them in one of two ways.
Create a new issue in the browser, then use the browser developer tools (F12 or Ctrl + Shift + I) to inspect each field’s HTML value.

The other option is to click the gear icon (top right), select Issues, click on Custom Fields, search for a field, click on it, click Edit detail, and then grab the ID value from the URL. Once the ID is captured, join it with customfield_, resulting in customfield_10073, which is the field’s internal value.

In this example, the summary issue type fields are the only ones that do not have a customfield_X naming convention. It might be possible that some system-generated fields have a different naming convention, but I’ll dig into that another day.

Column Display NameColumn Internal NameColumn Type
Issue Typeissuetypesystem
Request Typecustomfield_10010system
Tortillacustomfield_10073Select List (single)
Meatcustomfield_10074Select List (single)
Veggiescustomfield_10075Checkboxes
Number of Tacoscustomfield_10076Number Field
Pickup Date Timecustomfield_10077Date Time Picker
Summarysummarysystem

Endpoint URL:
https://taco.atlassian.net/rest/api/3/issue/
Headers: {“Content-Type”: “application/json”}
Authentication: Raw
Key: Basic aWhddsfadfafa..NOT…A…REAL…KEY..dafdfdafd=
Example payload:

{
  "fields": {
    "project": {
      "key": "TACOS"
    },
    "customfield_10010": "tr/9f7c4029-6d23-4cb1-bb8a-02d0050d944b",
    "summary": "Taco order summary",
    "issuetype": {
      "id": "10015"
    },
    "customfield_10073": {"value": "Flour"},
    "customfield_10074": {"value": "Chicken"},
    "customfield_10075": [{"value": "Pico"},{"value": "Grilled Veggies"}],
    "customfield_10076": 2,
    "customfield_10077":"2022-11-05T11:05:00.000+0000"
  }
}

View of the task in Jira

How do you attach a file to a Jira task using Power Automate?
Attaching a file to a Jira task requires one more API call, and it’s simple!

Endpoint URL:
https://taco.atlassian.net/rest/api/3/issue/Key/attachments
Headers: {“X-Atlassian-Token”: “no-check”}
Authentication: Raw
Key: Basic aWhddsfadfafa..NOT…A…REAL…KEY..dafdfdafd=

Example payload:

{
  "$content-type": "multipart/form-data",
  "$multipart": [
    {
      "headers": {
        "Content-Disposition": "form-data; name=\"file\"; filename=@{outputs('Get_file_properties')?['body/{FilenameWithExtension}']}"
      },
      "body": @{body('Get_file_content')}
    }
  ]
}

Attachment
I’m getting a file from SharePoint and passing its contents to the API call for the attachment. The same thing works with Azure blob storage or grabbing a file from the dataverse. If you want to attach more than one file, create additional HTTP attachment calls.

Here’s a simple overview of the Flow:

Parse JSON schema:

{
    "type": "object",
    "properties": {
        "id": {
            "type": "string"
        },
        "key": {
            "type": "string"
        },
        "self": {
            "type": "string"
        }
    }
}

The Jira documentation is great, and the forums are active and helpful.