Reverse ETL for your timesheets

This example shows how to create a timesheet from time tracking data generated by the command line tool Timewarrior and upload it to Google Sheets.

Content

1. notebook.ipynb explains the code step-by-step and allows you to interact and modify the dataflow.
2. upload_timesheet.py contains the same dataflow but as a script. It can be used as a Timewarrior extension directly, see below.

Set up

1. Create a virtual environment and activate it

   python -m venv venv && . venv/bin/active
   

2. Install requirements

   pip install -r requirements.txt
   

Timewarrior extension

Adding a Timewarrior extension means you can trigger arbitrary scripts with timew report SCRIPT_FILE_NAME. Even better, you can skip the report command and only type the SCRIPT_FILE_NAME partially if it's unambiguous. For example, you can trigger upload_timesheet.py with timew upload. See the (notebook)[./notebook.ipynb] for further context and explanations.

To enable this, follow these steps:

1. Identify your Timewarrior installation directory (we'll assume the default: ~/.timewarrior)

2. Copy upload_timesheet.py from this example to ~/.timewarrior/extensions

3. Create a credentials.json with credentials for the Google Sheets API (see step-by-step guide) under ~/.timewarrior/extensions. It should look like this:

   credentials = {
       "type": "service_account",
       "project_id": "...",
       "private_key_id": "...",
       "private_key": "-----BEGIN PRIVATE KEY----- ...",
       "client_email": "... .iam.gserviceaccount.com",
       "client_id": "...",
       "auth_uri": "https://accounts.google.com/o/oauth2/auth",
       "token_uri": "https://oauth2.googleapis.com/token",
       "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
       "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/ ...",
       "universe_domain": "googleapis.com"
   }
   

4. Enable execution permissions with chmod +x upload_timesheet.py

5. Verify the extension is enabled with timew extensions. You should see

   Extensions located in:
       ~/.timewarrior/extensions
   
   Extension        Status
   credentials.json No executable
   upload_timesheet.py Active
   

6. Create a new Google Sheets spreadsheet and get its spreadsheet_id from the Google Sheets URL https://docs.google.com/spreadsheets/d/$SPREADSHEET_ID/edit?gid=0#gid=0

7. Use the Share button to give access to the Google API service account that you created at Step 3

8. Add the spreadsheet_id as a Timewarrior config point via timew config spreadsheet_id $SPREADSHEET_ID

9. Use the command timew report upload_timesheet.py to test it out! You should be able to shorten it to timew upload

[!NOTE] If you run python upload_timesheet.py will hang because it will try to read values from sys.stdin which should be provided by the Timewarrior CLI. That's why you need to run it via timew report upload_timesheet.py