Issue Tracker Using Jira and Smartsheet

The main purpose of this project is to show how jira can be integrated with other project management tools through simple lines of codes and also to keep track of various issues raised in Jira and to store it in other databases such as Smartsheet, Google Sheet etc.,

In my issue tracker it is based on the daily issues which happen in a company usually related to the IT department which are tracked and maintained in jira.

Tools Required

• Jira
• Python
• Smartsheet
• Github Actions
• Jira API, Google Sheet API

Setup
Jira API and Google Sheet API is required to read data from Jira and write it to Google Sheets
APIs should be created in the secrets folder of Github actions.

Python Code
Jira Code

def push_data():
    email = '[e-mail]'
    api_token = os.getenv("JIRA_SECRET")  # Get Jira API token from secret
    server = 'https://nitish36.atlassian.net'
    jql = 'project = "IT" AND created >= -30d'
    url = f"{server}/rest/api/3/search"
    params = {
        "jql": jql,
        "maxResults": 100,
        "fields": "key,issuetype,customfield_10071,customfield_10073,statusCategory,created,priority,assignee,summary,labels"
    }
    headers = {
        "Accept": "application/json"
    }
    response = requests.get(
        url,
        headers=headers,
        params=params,
        auth=HTTPBasicAuth(email, api_token)
    )
    data = response.json()
    issues = []
    print("Status Code:", response.status_code)
    print("Response Text:", response.text)
    for issue in data["issues"]:
        fields = issue["fields"]
        print(json.dumps(issue["fields"], indent=5))
        issues.append({
            "Jira Issue Key": issue["key"],
            "Jira Ticket Type": fields["issuetype"]["name"],
            "Description": fields["summary"],
            "Client ID": fields["customfield_10071"],
            "City": fields["customfield_10073"]["value"] if fields["customfield_10073"] else "",
            "Status": fields["statusCategory"]["name"],
            "Issue Date": fields["created"],
            "Priority": fields["priority"]["name"],
            "Label": fields["labels"],
            "Assigned Person": fields["assignee"]["emailAddress"] if fields["assignee"] else "",
        })
    return issues

Google Sheet Code

def write_df():
    issues = push_data()
    df = pd.DataFrame(issues)
    GSHEET_NAME = 'Issue Tracker Jira'
    TAB_NAME = 'Dump'
    # Get Google credentials JSON string from env and parse
    gsheet_secret = os.getenv("GSHEET_SECRET")
    if not gsheet_secret:
        print("Google Sheets secret not found in environment.")
        return
    # Write the JSON to a temporary file
    credentialsPath = "temp_gsheet_credentials.json"
    with open(credentialsPath, "w") as f:
        f.write(gsheet_secret)
    try:
        gc = gspread.service_account(filename=credentialsPath)
        sh = gc.open(GSHEET_NAME)
        worksheet = sh.worksheet(TAB_NAME)
        set_with_dataframe(worksheet, df)
        print("Data loaded successfully!! Have fun!!")
        print(df)
    except Exception as e:
        print(f"Error: {e}")
    finally:
        if os.path.exists(credentialsPath):
            os.remove(credentialsPath)

Jira Process

A daily trigger is set at 9:00 AM with a form link to the concerned IT admin
Once the form is filled the issue is created in the backlog of Jira
Every 20 mins a trigger is set in GitHub Actions in such a way that new issues are tracked and copied to google sheets.
A data shuttle is used to pull the data from google sheet to Smartsheet
Smartsheet Process
Three sheets will be required: Intake, Rollup and Template.
Issues will flow into the intake sheet
Control Centre will be used to Roll out individual sheets based on the new rows which are added in intake sheet
Intake sheet will have basic details such as Ticket ID, Client ID, Name, When the Issue was raised, Sheet Link
Template will have intake sheet data along with these columns “Escalation Needed”, “Reasons”, “Issue Resolved (Yes/No)”, “Issue Resolved Date”.
Rollup will record all the data which can be used in reports, metrics and dashboards.

Github Links: https://github.com/Nitish36/Issue-Tracker-Jira