I am trying to automate exporting issues from JIRA to excel workbook periodically. Is it possible to achieve this using Rest API. I read that it is posssible to export the issues to JSON format using the API, but is there a way to directly export the issues to excel or is it possible to convert the JSON to Exce, as if were exported to excel manually ?
Is the direct usage of the REST API a requirement?
If you are on Jira Server or Data Center, you can easily do this by automating Excel exporting (and saving, emailing, or attaching to issues) with the combination of Better Excel Exporter and Better Excel Automation for Jira.
More on how to set up the necessary apps here in the Documentation of Better Excel Automation.
Hi @Sherwin R
Welcome to the Community!!
Here's an article that may help you for creating issues from Excel via API using Python Script
Looping Praveen Chitturi for any help
Thanks,
Pramodh
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi @Pramodh M
Thanks for the answer. Actually I want to export issues from JIRA as a excel worksheet and not create an issue.
Thanks,
Sherwin
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi @Sherwin R
You could export a maximum of 1000 issues at a time using Jira UI itself.
Using API if you need to get the issues, the response will be in JSON. Using Pagination, you can get all the issues from instance using API
Tools like JQ will help in converting the issues from JSON to CSV.
Here's a guide on how to do that
https://stackoverflow.com/questions/32960857/how-to-convert-arbitrary-simple-json-to-csv-using-jq
Let me know which fields you need to track in Excel I will send out a command for that
Thanks,
Pramodh
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi @Pramodh M ,
Thanks again for your quick reply.
So the fields I want to export for each issue are:
Issue type, Key, Summary, Assignee, Reporter, Status, Due Date, Hourly rate 1, Hourly rate 2, Checklist, Resolution, Components and Initiator.
Some of them are custom fields.
And I also want to export only Issue Type == Activity.
Thanks,
Sherwin
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi @Sherwin R
Here's a reference on how you can export
This is an example:-
curl --request GET \
--url 'https://your-domain.atlassian.net/rest/api/3/search?jql=issuetype%20%3D%20Feature' \
--user 'email@domain.com:token' \
--header 'Accept: application/json' | jq -r '.issues[] | [.key, .fields.summary] | @csv'
So in here
[.key, .fields.summary]
Go on adding your custom field to get the list like this
You will need export this to CSV by adding
>> issues.csv at the end of the command
Let me know if you have any queries
Please mark the answer as accepted
Thanks,
Pramodh
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi @Pramodh M ,
I am using python requests library to make these API calls. Could you please give me an example of how
curl --request GET \
--url 'https://your-domain.atlassian.net/rest/api/3/search?jql=issuetype%20%3D%20Feature' \
--user 'email@domain.com:token' \
--header 'Accept: application/json' | jq -r '.issues[] | [.key, .fields.summary] | @csv'
could be written there. And I am using Basic Auth.
Thanks,
Sherwin
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Here's a Python Wrapper you can use
https://atlassian-python-api.readthedocs.io
Do a pip install atlassian-python-api
from atlassian import Jira
jira = Jira(
url='https://your-site.atlassian.net',
username='email@domain.com',
password='token',
cloud=True)
jql_request = 'issuetype = feature'
issues = jira.jql(jql_request)
print(issues)
Save the output to a file - outputfile
And use the command to get the data in CSV format
cat outputfile | jq -r '.issues[] | [.key, .fields.summary] | @csv'
Let me know if you have any queries
Thanks,
Pramodh
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi @Pramodh M
So the first code snippet is working if i plainly give the url as https://sample.domain.com/jira/ which I guess fetches me issues from all projects.
But if I entered the url as https://sample.domain.com/jira/projects/TESTPROJECT, I get an error
And secondly, where should I enter this command ? Is it in the command prompt ?
cat outputfile | jq -r '.issues[] | [.key, .fields.summary] | @csv'
Thanks,
Sherwin
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.