How to export Tempo Timesheet to Google Spreadsheets with Python and Gspread

In this tutorial I’m going to show how you can easily export worklogs from Tempo to a Google Spreadsheet.

Monthly, I do accounting checks on the status of the projects I’m following. We use Tempo Time Tracking plugin in Jira to log hours spent on user stories. For better manageability and transparency, the accounting operations are then carried out on a shared spreadsheet.

Extracting manually the hours logged on Jira during the month is a tedious and error prone activity. For this, Python with its gspread library and the API provided by Tempo.io comes to the rescue.

Google Spreadsheet

For this script we will need to setup a new or existing project in the Google API Console. You can follow gspread documentation to configure it.

You will automatically download a JSON file with credentials. It may look like this:

{
    "type": "service_account",
    "project_id": "api-project-XXX",
    "private_key_id": "2cd … ba4",
    "private_key": "-----BEGIN PRIVATE KEY-----\nNrDyLw … jINQh/9\n-----END PRIVATE KEY-----\n",
    "client_email": "473000000000-yoursisdifferent@developer.gserviceaccount.com",
    "client_id": "473 … hd.apps.googleusercontent.com",
    ...
}

Remember to create a spreadsheet where you will import data and share it with the client_email from the step above.

Gspread

Next we need to install and import gspread. You can use pip, poetry or any package installer you like. Personally, since I’m using JupyterLab on a Conda environment, I’ve installed it with this command in a notebook cell:

import sys
!conda install -c conda-forge — yes — prefix {sys.prefix} gspread

Others libraries needed are json and requests. In my case they were installed by default in the Anaconda root environment and I’ve just imported them.

Tempo API

Tempo API are documented here. We are going to query the billable time spent working with this endpoint.
To recall Tempo data we need to create a token used for authentication, you can create one in the API Integration (https://<YourJiraAddress>.atlassian.net/plugins/servlet/ac/io.tempo.jira/tempo-app#!/configuration/api-integration) section of Tempo Settings.

This private token will then be used to authenticate the request made by our script.

Extract data

First, we import our needed libraries:

import gspread
import json
import requests

Now that we have everything set up, we can start extract some juicy data for our report.

# create a function to extract values from the json response
def extract_values(obj, key):
    arr = []
def extract(obj, arr, key):
        if isinstance(obj, dict):
            for k, v in obj.items():
                if isinstance(v, (dict, list)):
                    extract(v, arr, key)
                elif k == key:
                    arr.append(v)
        elif isinstance(obj, list):
            for item in obj:
                extract(item, arr, key)
        return arr
results = extract(obj, arr, key)
    return results

# this function calls the Tempo worklog, defined by the project code and months of competence
def projectBillableHours(projectCode, startAccountingMonth, endAccountingMonth, limit, token):
    url = f"https://api.tempo.io/core/3/worklogs/project/{projectCode}"
    payload = {"from":f"2020-{startAccountingMonth}","to":f"2020-{endAccountingMonth}","limit":f"{limit}"}
    headers = {
        'Authorization': f'Bearer {token}',
        'Cookie': 'sessionId=""'
    }
    response = requests.request("GET", url, headers=headers, params = payload)
    jsonResponse = response.json()
    billableSeconds = extract_values(jsonResponse, 'billableSeconds')
    billableHours = sum(billableSeconds)/3600
    return billableHours

Configure Spreadsheet

Next we need to configure our spreadsheet to provide a list of project codes we want to extract the worklog data on.

I tend to organize in two different sheets Projects Codes from Data sheets where hours are persisted. This is because projects may change from month to month and I want a consistent sheet with data logged.

projectsWorksheet

dataWorksheet

Update data

With the Google Spreadsheet Key obtained from the URL we can:

• request the list of Projects Codes we want to extract data
• call Tempo API with a time frame and results limit
• create a dictionary of Projects Codes and Projects Hours
• update our Data sheets

# you can put the credentials downloaded from Google API Console into a service_account.json file
gc = gspread.service_account(filename='service_account.json')
sh = gc.open_by_key("<yourGoogleSpreadsheetKeyHere>")
# referenced sheets for Data and Projects Codes
dataWorksheet = sh.get_worksheet(0)
projectsWorksheet = sh.get_worksheet(1)
projectsList = projectsWorksheet.col_values(2)
hours = []
# Tempo limits the number of results to 50 by default. You can pass in limits a number between 1 and 1000
for i in projectsList:
    hours.append(projectBillableHours(i,"08","09",500, "<yourTokenHere>"))
projectsHours = dict(zip(projectsList, hours))
# Once the dictionary of Projects Codes and Projects Hours is created, we look for Projects Code value in the Data sheets and update the cell on the right with the Hours amount
for index, val in enumerate(projectsHours):
    cell = dataWorksheet.find(projectsList[index])
    dataWorksheet.update_cell(cell.row, cell.col+1, projectsHours.get(projectsList[index]))

The end results will look something like this:

final result of dataWorksheet filled

Here you can find the complete code for this tutorial, to execute it you can paste the content in a notebook cell and run it: