Issue Tracker Using Jira and Smartsheet

Nitish k — Tue, 13 May 2025 08:48:40 +0000

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 = 'nitish.pkv@gmail.com'
    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

Airbnb ETL

Nitish k — Tue, 12 Dec 2023 03:57:58 +0000

Airbnb is a popular online marketplace that enables individuals to rent out their properties, whether it's a spare room, an entire home, or unique accommodations, to travelers seeking short-term lodging. Founded in 2008, Airbnb has revolutionized the hospitality industry by providing a platform for hosts to list their properties and travelers to book accommodations that suit their preferences and budgets. The platform offers a wide range of options, from apartments and houses to treehouses and yurts, in various locations worldwide. Users can browse listings, read reviews, communicate with hosts, and make reservations directly through the Airbnb website or mobile app. This innovative concept has not only changed the way people travel but also opened up new income opportunities for property owners and hosts.

Real-Time Data Generation for Airbnb Analytics: Python Script Development: Created customized Python scripts tailored to the specific needs of the Airbnb project. These scripts were designed to fetch, process, and generate real-time data on crucial aspects, such as occupancy rates, pricing trends, and guest reviews. Data Pipelines: Established data pipelines to ensure the continuous and automated collection of data from various sources. These pipelines not only facilitated real-time data updates but also standardized the incoming data for consistency and accuracy.

Automated Data Integration with Google Sheets: Scheduling and Automation: Set up scheduled tasks that automatically transferred the Airbnb project data to Google Sheets at specified intervals. This automation eliminated manual data entry, reducing the risk of errors and saving valuable time. Google Sheets API Integration: Leveraged the Google Sheets API to seamlessly connect the data generation processes with Google Sheets. This integration allowed for efficient data synchronization, ensuring that the most recent information was readily available to project stakeholders.

Data Visualization and Reporting using Google Looker Studio: Interactive Dashboards: Utilized the capabilities of Google Looker Studio to craft interactive and user-friendly dashboards. These dashboards presented key Airbnb project metrics in an intuitive manner, enabling team members to explore data trends and insights effortlessly. Tailored Data Visualizations: Designed custom data visualizations and reports that aligned with the specific needs of the project. These visualizations served as valuable tools for data-driven decision-making, aiding in project management and strategic planning.

Link for my Project: Airbnb

Forem: Nitish k

Issue Tracker Using Jira and Smartsheet

Airbnb ETL