How to import a Jira board into Excel using powerquery

Question

I would like to have a powerquery in my excel file which pulls all the issues from a jira board. I have:

1. created a filter in jira for the issues already.
2. captured the link to the csv export:

https://[mycompany].atlassian.net/sr/jira.issueviews:searchrequest-csv-with-bom-all-fields/12170/SearchRequest-12170.csv?atl_token=8e034a3-9a77-484b-9138-a0be45b9ff7b_1aaca7d5e98a798a9df94a3d032ab32e23de3433_lin&tempMax=1000

3. in "excel/powerquery/new source/other sources/web" I have keyed in the first part of the url:

https://[mycompany].atlassian.net/sr/jira.issueviews:searchrequest-csv-with-bom-all-fields/12170/SearchRequest-12170.csv

4. I've read that basic authentication is deprecated and I couldn't get anonymous,windows or organsational authentication to work, hence i chose webapi.

5. In jira, i created an api token, named it "excel".

6. in power query, i keyed in the api token string and specified the url for the jira csv download:

7. [Error Step] - when i try to connect, I receive the message: "a web api key can only be specified when a web api key name is provided"

-> What do I need to do? I hypothesize that some people have many api keys and I need to reference the one i named "excel"? How would I do this?

Any insights appreciated :)

Escurial

Did you try my approach using the Jira REST API? – Tangelatangelo

the web API auth didnt work for me.. I found that you need to use "Basic" option in Excel dialog, as username enter your JIRA acccount email address and as password the API token setup in Alassian (id.atlassian.com/manage-profile/security/api-tokens) – Fabiano

Answer 1

A different approach. Why don't you try to use the Add-in: Jira Cloud for Excel, it is an official product from Atlassian and it is free. You can keep your excel on sync with your Jira issues (only from one direction Jira -> Excel). Once you have extracted the issues (via Jira Filter, JQL or JIRA.JQL function, I prefer the last one option) you can use Power Query to transform the source, excel functions, etc.

This is the best way to approach it, in my opinion Jira-Excel integration. Unless you have have blocked Add-in in your Excel (usually some corporations do it), this is the simplest way to get both tools integrated. By the way they have a sibling product for Google Spreadsheet: Jira Cloud for Google Spreadsheet. I have been using both for several years.

As far as I remember they don't require API Token to authenticate. It can be easily installed from your Jira instance, go to Issues, put the query your want and then click the following icon:

Later, you can change the query from Excel, or use a Jira Filter or JIRA.JQL function. You can also specify the columns you want to bring in Excel and it allows also to get Jira custom fields your Jira instance has defined. No need to have admin privilege for doing it.

Oxblood

Thanks. The jira Addin is blocked for us and we need to do further data manipulations in PowerQuery with other datasets. I’m looking for a powerquery solution. – Escurial

Answer 2

I would go for @David Leal's solution if you want to follow the best practice. Otherwise, in the formula bar, modify the URL by appending the API key name as a query parameter. Add &os_username=excel to the end of the URL.

https://[mycompany].atlassian.net/sr/jira.issueviews:searchrequest-csv-with-bom-all-fields/12170/SearchRequest-12170.csv?atl_token=8e034a3-9a77-484b-9138-a0be45b9ff7b_1aaca7d5e98a798a9df94a3d032ab32e23de3433_lin&tempMax=1000&os_username=excel

When you include the os_username parameter with the value set to the API key name "excel," you specify which API key to use for authentication.

Bosporus

Hi! This I think it the closest I've gotten. I've updated query parameter with the syntax you shared but I am receiving a yellow prompt: "Please specify how to connect. Edit Credentials." I can then choose from the GUI: "Anonymous, Windows, Basic, Web API, Organisational Account. " Would you know what parameters I have to entered into one of the alternatives? With Web API, I can choose Key. I've tried the "atl_token=...&ios_username" but it doesn't work, do I need to specify email somehow as username? Is the Basic prompt simpler? I'm not sure what syntax is expected. – Escurial

Try the following: In the "Access Web Content" dialog that appears, URL: https://[mycompany].atlassian.net/sr/jira.issueviews:searchrequest-csv-with-bom-all-fields/12170/SearchRequest-12170.csv?atl_token=8e034a3-9a77-484b-9138-a0be45b9ff7b_1aaca7d5e98a798a9df94a3d032ab32e23de3433_lin&tempMax=1000&os_username=excel And leave these fields blank: API key name, API key,Headers. – Bosporus

Answer 3

I do not believe the download-issue-list-as-csv is available using a personal access token.

Using Power Query, you can do a REST API search that returns JSON and then use steps to unpack the values you need from the JSON response.

Here's a simple example that you can copy/paste in the Power Query advanced editor. When Power Query asks you for authentication for the connection, specify Anonymous authentication.

Modify the server URL, the jql query and the personal access token below to get started:

let
  Source = Json.Document(Web.Contents(
      "https://your.jira.server/rest/api/2/search", 
      [
          Query=[
            jql="project=SOMEPROJECT", 
            fields="summary,reporter,status", 
            maxResults="1000" /* default is 50 */
          ],
          Headers=[Authorization="Bearer YOURPERSONALACCESSTOKEN"]
      ]
  )),
  Navigation = Source[issues],
  AsTable = Table.FromList(Navigation, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
  Headers = Record.FieldNames(AsTable[Column1]{0}),
  Result = Table.ExpandRecordColumn(AsTable, "Column1", Headers, Headers),
  #"Removed columns" = Table.RemoveColumns(Result, {"self", "expand", "id"}),
  #"Expanded fields" = Table.ExpandRecordColumn(#"Removed columns", "fields", {"summary", "reporter", "status"}, {"summary", "reporter", "status"}),
  #"Expanded reporter" = Table.ExpandRecordColumn(#"Expanded fields", "reporter", {"name"}, {"reporter_name"}),
  #"Expanded status" = Table.ExpandRecordColumn(#"Expanded reporter", "status", {"name"}, {"status_name"})
in
  #"Expanded status"

Tangelatangelo