Importing issues from JIRA to Excel using API

Sherwin R February 14, 2022

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 ?

2 answers

2 accepted

1 vote
Answer accepted
Levente Szabo _Midori_
Marketplace Partner
Marketplace Partners provide apps and integrations available on the Atlassian Marketplace that extend the power of Atlassian products.
February 14, 2022

@Sherwin R 

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.

Sherwin R February 14, 2022

Yes, but it would be better in my case if i can use REST API directly to achieve that.

Like Levente Szabo _Midori_ likes this
0 votes
Answer accepted
Pramodh M
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
February 14, 2022

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

https://community.atlassian.com/t5/Jira-discussions/Reference-code-to-create-JIRA-issue-using-atlassian-JIRA-Python/td-p/1942861#U1942872 

Looping Praveen Chitturi for any help

Thanks,
Pramodh

Sherwin R February 14, 2022

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

Pramodh M
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
February 14, 2022

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

Sherwin R February 14, 2022

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

Pramodh M
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
February 14, 2022

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

Issue.png

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

Sherwin R February 14, 2022

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

Pramodh M
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
February 14, 2022

@Sherwin R 

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

Sherwin R February 15, 2022

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 

JIRA.PNG

 

 

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

Suggest an answer

Log in or Sign up to answer