Reference code to create JIRA issue using atlassian JIRA Python script - Give back to community

[Praveen Chitturi]

Dear Community,

I've learnt/benefited from the community with lot of answers for my challenges / other queries.  It's time for me also to give back for the community with the reference code I've created to create JIRA issues using python script. 

During our project work migration from Excel to JIRA, it's needed to create JIRA issues from Excel file. I'm pretty sure there might be some options available to upload but using pything scripting it gives more flexibility to read the data from excel file, update back the issue key to excel file and skip the rows based on certain conditions.

The process flow is like this

• Will check the excel file is already open or not.
• If not then will proceed for reading the excel file data from specific range specified by start row, end row, columns
• Once the data loaded into internal list then will call JIRA to create the JIRA issue inside for loop. 
• For every JIRA issue created will execute  one more JQL to read the craeted issue key(which by default JIRA is not returning). 
• The created issue# updating back to Excel file in 1st column for that corresponding row
• If there is any issue in creating the JIRA issue then will update the excel 1st column as "Error occured during JIRA issue creation"
• End of processing all the records, will close the excel file and exit.

I've used Eclipse IDE, PyDev, openpyxl, atlassian JIRA modules to create this. 

For reference of atlassian python APIs, please refer Welcome to Atlassian Python API’s documentation! — Atlassian Python API 3.14.0 documentation (atlassian-python-api.readthedocs.io) 

During pasting the code, the indent is missing. Please let me know if anyone is having the challenge to make the indenting exactly. 

#
# Script to read the excel data and create JIRA Issue with that
#

# Import Module library JIRA
# Refer https://atlassian-python-api.readthedocs.io/index.html for detailed possibilities with JIRA Python API
from atlassian import Jira

# import openpyxl package
# This package will support latest excel formats .XLSX
import openpyxl

from datetime import date
from datetime import time
from datetime import datetime
from atlassian.bitbucket.cloud.repositories import issues

# Give the location of the file
path = "C:\JIRA\JIRA Test Issues Creation.xlsx"

def read_exceldata(min_row, max_col, max_row, path, Exceldata, wb_obj , sheet_obj):


# Note: The first row or
# column integer is 1, not 0.

# Cell object is created by using
# sheet object's cell() method.
# cell_obj = sheet_obj.cell(row = 1, column = 1)
# cell_obj = sheet_obj['A2': 'G3'] 

# Loop over the rows
for row in sheet_obj.iter_rows(min_row=min_row, max_col=max_col, max_row=max_row):

for cell in row:
if cell.column == 1:
Excelrow = []
Excelrow.append(cell.row)
Excelrow.append(cell.value)
else:
Excelrow.append(cell.value)
if cell.column == max_col:
# Any additional checks to skip the current row?
# If the JIRA issue field is empty then only append the record to create JIRA issue
if(Excelrow[1] is None): 
# Create the list and store the excel values
Exceldata.append(Excelrow)
else:
continue
return Exceldata

def upd_excel_cell_value(wb_obj, sheet_obj, row, col,cell_value): 
mycell= sheet_obj.cell(row=row, column=col) 
mycell.value = cell_value

# Save the workbook
wb_obj.save(path)

def call_jira_and_Process(Exceldata, wb_obj, sheet_obj): 

#Set the JIRA connection
jira = Jira(
url='https://jira.mycompany.com/',
username= 'myusername',
password='mypassword')

# Create issue and add the required fields, 
# Hint:- For the structure you can test the rest API with postman first and use the body from the POSTMAN
for count, val in enumerate(Exceldata):

fields = {
"summary":val[2],
"issuetype": {
"id": "10001"
},
"project": {
"key": "myjiraproject"
},
"assignee": {
"name": val[3]
},
"components": [
{
"name": val[4]
}
],
# Convert to date format (planned due date
"customfield_10302": val[6].strftime("%Y-%m-%d"), 
#Effort estimation hours
"customfield_10301": val[5] , 
} 

# Create JIRA Story with the fields required
try:
jira.issue_create(fields)

except:
#Update excel with the created issue# (Pass the Cell reference and also the value to be updated
upd_excel_cell_value(wb_obj, sheet_obj, val[0], 1,"Error in creating JIRA Issue") 
continue
# Get the created issue key based on the JQL search
jql_request = 'project = myproject AND summary ~ ' + "'" + val[2] + "'" 
try:
issues = jira.jql(jql_request)

createdissue = issues["issues"][0]["key"]

# if created issue key is there then update the Excel again
Exceldata[count].append(createdissue)

#Update excel with the created issue# (Pass the Cell reference and also the value to be updated
upd_excel_cell_value(wb_obj, sheet_obj, val[0], 1,createdissue)
continue
except: 
#Update excel with the created issue# (Pass the Cell reference and also the value to be updated
upd_excel_cell_value(wb_obj, sheet_obj, val[0], 1,"JIRA Issue created but failed to get the issue key") 
continue

# Close the excel file 
wb_obj.close()

# Print the final result
print("\n JIRA Issue creation completed. Please check the excel file for results: \n" , path) 
return Exceldata

def main():

Exceldata = []

# Instantiate Excel instance and get the access for Workbook and Required sheet
# To open the workbook
# workbook object is created
wb_obj = openpyxl.load_workbook(path)

# Get workbook active sheet object
# from the active attribute
sheet_obj = wb_obj.active

# Check the workbook opened by outside by executing the save workbook function
try:
wb_obj.save(path)

wb_obj.close()

# Read the excel file data
read_exceldata(2, 6, 3, path, Exceldata, wb_obj, sheet_obj)

#Create JIRA issue with the excel data
call_jira_and_Process(Exceldata, wb_obj, sheet_obj)

except IOError:
print("Excel file is opened outside. Please close and retry the script again")

if (__name__ == "__main__"):
main()

The excel file looks like this

Regards,

Praveen

[Pramodh M]

Wow Great Write Up @Praveen Chitturi 

Thank You

[Sumit Somani]

@Praveen Chitturi Can you please explain how you created fields.
Also  I was having problem with indentation with this block

try:

            jira.issue_create(fields)

        except:

                #Update excel with the created issue# (Pass the Cell reference and also the value to be updated

                upd_excel_cell_value(wb_obj, sheet_obj, val[0], 1,"Error in creating JIRA Issue") 

                continue

                # Get the created issue key based on the JQL search

                jql_request = 'project = myproject AND summary ~ ' + "'" + val[2] + "'"