Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
How to build a pretty Excel timesheet using Toggl API and Python. See https://www.guguweb.com/2014/03/13/build-excel-timesheet-toggl-api-python/
#!/usr/bin/python
import requests
from xlsxwriter import Workbook
import sys
from datetime import date, timedelta
import argparse
from math import floor, ceil
def toExcelTime(time, round_minutes=0, round_func=round):
# time is in milliseconds
# divide by 1000 to obtain seconds
seconds = float(time) / 1000.
# arrotondo ai minuti richiesti, se devo
if round_minutes:
minutes_slots = round_func(seconds / (round_minutes * 60.))
seconds = minutes_slots * round_minutes * 60
# divide by 86400 (seconds in a day) and obtain the "fraction of day" used in Excel times
return seconds / 86400.
_api_token = 'api_token_taken_from_your_toggl_profile'
_workspace_id = 12345 # your workspace id
# to get your workspace_id you can use a code like this
#r = requests.get('https://www.toggl.com/api/v8/workspaces', auth=(_api_token, 'api_token'))
#print r.json()
today = date.today()
firstof_month = date(today.year, today.month, 1)
lastof_prevmonth = firstof_month - timedelta(days=1)
firstof_prevmonth = date(lastof_prevmonth.year, lastof_prevmonth.month, 1)
parser = argparse.ArgumentParser(description='Download timesheet from Toggl.')
parser.add_argument('client', help='Client name on Toggl (required)')
parser.add_argument('--since', metavar='YYYY-MM-DD', help='Date from', default=firstof_prevmonth.strftime('%Y-%m-%d'))
parser.add_argument('--until', metavar='YYYY-MM-DD', help='Date to', default=lastof_prevmonth.strftime('%Y-%m-%d'))
parser.add_argument('--round', help='Minutes to round to', type=int, default=5)
parser.add_argument('--round-type', help='Round type: ceil (default), floor, round', type=str, default='ceil')
args = parser.parse_args()
client = args.client
since = args.since
until = args.until
round_minutes = args.round
round_type_map = {
'round': round,
'floor': floor,
'ceil': ceil,
}
round_func = round_type_map[args.round_type]
output = '%s_%s_%s.xlsx' % (client, since, until)
workbook = Workbook(output)
worksheet = workbook.add_worksheet()
worksheet.set_column('A:A', 80)
bold = workbook.add_format({'bold': True})
time_format = workbook.add_format({'num_format': '[HH]:mm'})
time_format_bold = workbook.add_format({'num_format': '[HH]:mm', 'bold': True})
r = requests.get('https://toggl.com/reports/api/v2/summary', auth=(_api_token, 'api_token'), params={
'workspace_id': _workspace_id,
'since': since,
'until': until,
'user_agent': 'api_test'
})
data = r.json()
row = 0
for project in data['data']:
title = project['title']
if title['client'] == client:
row += 1
project_title_row = row
worksheet.write(project_title_row, 0, title['project'], bold)
row += 1
for i in project['items']:
worksheet.write(row, 0, i['title']['time_entry'])
worksheet.write(row, 1, toExcelTime(i['time'], round_minutes, round_func))
row += 1
worksheet.write_formula(project_title_row, 1, '=SUM(B%d:B%d)' % (project_title_row + 2, row), time_format_bold)
worksheet.set_column('B:B', None, time_format)
workbook.close()
print "client: %s" % client
print "date range: %s - %s" % (since, until)
if round_minutes:
print "round minutes: %d" % round_minutes
print "round type: %s" % args.round_type
print "output: %s" % output
print "rows written: %d" % row
@pateldiv68

This comment has been minimized.

Copy link

commented Apr 9, 2017

this code leaves an empty time sheet and overwrites every time I run it, I can't figure out the mistake, please suggest.

@pateldiv68

This comment has been minimized.

Copy link

commented Apr 9, 2017

my code:

#!/usr/bin/python
import requests
from xlsxwriter import Workbook
import sys

def toExcelTime(time):
# time is in milliseconds
# divide by 1000 to obtain seconds
# divide by 86400 (seconds in a day) and obtain the "fraction of day" used in Excel times
return float(time) / 1000. / 86400.

_api_token = 'bb1a52a2006ce158bbf962ec13c1bd6f'
_workspace_id = 1969193 # your workspace id

to get your workspace_id you can use a code like this

#r = requests.get('https://www.toggl.com/api/v8/workspaces', auth=(_api_token, 'api_token'))
#print r.json()

if len(sys.argv) < 4:
print ("Usage: %s client since until" % sys.argv[0])
sys.exit(1)

client = sys.argv[1]
since = sys.argv[2]
until = sys.argv[3]

workbook = Workbook('%s_%s_%s.xlsx' % (client, since, until))
worksheet = workbook.add_worksheet()
worksheet.set_column('A:A', 80)

bold = workbook.add_format({'bold': True})
time_format = workbook.add_format({'num_format': '[HH]:mm'})
time_format_bold = workbook.add_format({'num_format': '[HH]:mm', 'bold': True})

r = requests.get('https://toggl.com/reports/api/v2/summary', auth=(_api_token, 'api_token'), params={
'workspace_id': _workspace_id,
'since': since,
'until': until,
'user_agent': 'api_test'
})
data = r.json()
row = 0
for project in data['data']:
title = project['title']
if title['client'] == client:
row += 1
project_title_row = row
worksheet.write(project_title_row, 0, title['project'], bold)
row += 1
for i in project['items']:
worksheet.write(row, 0, i['title']['time_entry'])
worksheet.write(row, 1, toExcelTime(i['time']))
row += 1
worksheet.write_formula(project_title_row, 1, '=SUM(B%d:B%d)' % (project_title_row + 2, row), time_format_bold, toExcelTime(project['time']))

worksheet.set_column('B:B', None, time_format)
workbook.close()

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.