Using IFTTT, Google Calendar, Google Sheets, Excel, and Python to Track Activity

Steps

1. Set up IFTTT between Google Calendar and Google Sheets

2. Edit Headers of Google Sheet

3. Process CSV with python and create new spreadsheet

Python code can be found here.

Detailed walkthrough after the jump.

Since college, I have been intrigued by time management and productivity. During my time in undergrad, I actually created a program called Tommies Unplugged. Tommies Unplugged provided information and resources to assist students in using their technology in an efficient and productive manner. Tommies Unplugged further spun off into a blog that allowed me to further practice and develop my writing skills. Most of the blog is now archived but I have reposted some of my favorite pieces to my Medium profile. I stopped writing as much after college and perhaps ironically dove headfirst into computer programming during my spare time. This project helped me meld my interest in productivity into an exercise to practice my Python skills. I hope the data can provide me useful insights into how I use my time and how I can be more effective in my work and leisure time.

This project will track how much time you spent in meetings, classes, etc. using data from your Google Calendar. It creates an Excel spreadsheet with the Date, Title, Description, Location, Start time, End time, Duration, and Day of the week of each event in your calendar so you can do further data analysis with your method of choice.

1. Set up IFTTT between Google Calendar and Google Sheets

IFTTT is a website that offers a platform to link products and services in an Internet of Things (IOT) format. For our purposes, it basically lets your Google Calendar talk to and work with your Google Sheets. If you do not have one yet, you will need to create a free account on IFTT. Some interfaces for products and services are already built out on IFTTT but this particular project will require you to create your own applet.

Go to the Explore page in the top right of the browser window. Select “Create” after “Make your own from scratch”

On Create your own page, select the “+” sign next to “This” under “If”

Search for and select Google calendar. IFTTT will ask you for your credentials.

Next, choose “Any event starts” as your trigger.

Then, choose which calendar you would like to use and click “Create trigger”. This will bring you back to the “If This Then That Page”.

Select “That” to open up options for what to do once a Google calendar event start.

This time, search and select Google Sheets.

On the next page, select “Add row to spreadsheet”.

The next page will allow you to configure what data gets added to the growing spreadsheet. Edit as you would like and then select “Create Action”. I did not include the event URL in my example.

2. Edit Headers of Google Sheet

The Google sheet you generate will not originally have the header names included. You will want to add those in as to match the Python code you will eventually use to process the data.

Navigate to your new sheet, insert a new row, and add the following headers: Title, Description, Location, Start, End, Date, Duration_minutes, and Day_week. It should look like this when you are done (sans data if you just got things up and running). Notice how Date, Duration_minutes, and Day_week do not have data under them yet. The start/end dates and times are given but they are not in a useful format for manipulation. We will use Python to fix this.

At the point you have data you are ready to analyze, select File > Download > Comma-separated-values, and save it to where Python can read it.

3. Process initial sheet with Python and create a new spreadsheet

Check to make sure the CSV file exported properly. Here is what mine looks like.

I renamed it to testdata.csv. Next, crank up MedCalendarTrack.py (can be found here in GitHub) to add in the missing data for the fields Date, Duration_Minutes, and Day_week. Type in the file name of the initial CSV (in my case testdata.csv) and the name of the new file (I named mine new_testdata.csv).

Now if you open new_testdata.csv, it will show the new data values.

After that, you are all set to use the data however you would like. I would love to hear your questions, comments, or any way I could write more efficient code for future projects.

Find me

LinkedIn: https://www.linkedin.com/in/chris-hornung/

Twitter: https://twitter.com/ChrisHornung

Github: https://github.com/ChristyHorn14