Hi all,
A lot of comments bout using CSV. I've actually followed very similar step but am using the APIs instead. MUCCCHHHH better. The data is much richer and you don't run into troubles when importing fields that are multi-select. In CSV, multi-select field come up in separate columns which you might not be able to predict so you transform logic become fragile.
Here's what I did... very similiar to the CSV version.
Importing into Excel Using Jira APIs
- Create a filter, or JQL you want to use to establish your data set, and make a note of it. Example: "project=DIG and issuetype=Bug" -> https://mycompany.atlassian.net/issues/?jql=project%3DDIG%20and%20issuetype%3DBug
- You then want to use the search API to retrieve all the issues and their meta data. I also advise you limit the query to the columns you want, not everything to limit the hit on the database, noting that the Cloud APIs will only return 100 records at a time - don't worry though you can appen them all in Power Query. Example API call: https://mycompany.atlassian.net/rest/api/3/search?jql=project%3DDIG%20and%20issuetype%3DBug&fields=issueType,summary,assignee,status,created&maxResults=100&startAt=0)
- Start Excel and the Power Query editor. This is different in different versions of Excel, but you need Excel 2016 or above I believe.
- You need to create a function in Power Query to load the first 100 rows and then call this function multiple times in order to get all your data. I haven't bothered figuring out how to set this up without hard coding, but I'm sure a techie could get onto that - I'm just a dumb project manager! ;) You'll need to add these using the Advanced Query editor initially. You can follow the wizards but this blog would be too long if I tried to explain that. :/
- Here's a copy of the function I set up: Query: LoadJiraIssuesFn
let LoadJiraIssuesFn = (startAt as number) =>
let
Source = Json.Document(Web.Contents("https://mycompany.atlassian.net/rest/api/3/search?jql=project%3DDIG%20and%20issuetype%3DBug&fields=issueType,summary,assignee,status,created&maxResults=100&"&Text.From(startAt))),
issues = Source[issues],
#"Converted to Table" = Table.FromList(issues, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"key", "fields"}, {"key", "fields"})
in
#"Expanded Column1"
in LoadJiraIssuesFn
- And here's a copy of the query to get all the data out: Query: LoadAllJiraIssues
let
// load all the records (this is will only load 200 records unless I make the call a few more times)
Source = Table.Combine({#"LoadJiraIssues"(0),#"LoadJiraIssues"(100)}),
#"Filtered Rows" = Table.SelectRows(Source, each true),
// Expand out the results to get the data
#"Expanded fields1" = Table.ExpandRecordColumn(#"Filtered Rows", "fields", {"summary", "assignee", "created", "status"}, {"fields.summary", "fields.assignee", "fields.created", "fields.status"}),
// Expand out some of the fields to get display names
#"Expanded fields.assignee" = Table.ExpandRecordColumn(#"Expanded fields1", "fields.assignee", {"displayName"}, {"fields.assignee.displayName"}),
#"Expanded fields.status" = Table.ExpandRecordColumn(#"Expanded fields.assignee", "fields.status", {"name"}, {"fields.status.name"}),
// Rename the column because I'm OCD
#"Renamed Columns" = Table.RenameColumns(#"Expanded fields.status",{{"fields.summary", "Summary"}, {"fields.assignee.displayName", "Assignee"}, {"fields.created", "Created"}, {"fields.status.name", "Status"}, {"key", "Jira Id"}})
in
#"Renamed Columns"
- When you run the query, if you haven't already, you'll be prompted with a permissions diaglogue (much like the CSV version shown in other people's responses). Select Basic authentication, enter your Jira username and password, and click Connect.
- After Excel connects, you will see a dialog with a preview of the table. Click Load to load it into the current worksheet. From this point forward, your Jira credentials will be cached, and you can update the query at any time by clicking Data/Refresh all.
(Thanks Dave Ahlers for the CSV tips above - I unashamedly based this on yours! Thanks mate - this really helped!)
Some other things to note:
- I've done this with the Cloud APIs, so am just assuming it works the same for Server version. I'm not a Jira guru... yet. :)
- If you've just switch over to Atlassian Access for SSO, then you'll need to realise that the APIs (and CSV) imports don't authenticate against the SSO. Not sure why, and I'm no way an expert in this area - like I said, a dumb project manager - so best to talk to your security gurus about that kind of thing. :) To authenticate, you'll need to use old passwords, as stored on the Atlassian servers. If the people using your spreadsheet can't remember the password then an admin will have to reset it for them.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.