Two Seconds to Populate My Pomodoro Tracker by Google Sheets API

Contents

Stuggling with time management for the past semester, I came across the Pomodoro Technique developed by Francesco Cirillo. Although it was something I’ve heard of a long time ago, I still decided to give it a try. This time I used Google Sheets as my Pomodoro tracker.

I have divided the tracker to 3 sheets, they are (1) Today’s To-Do, (2) Daily Log, and (3) To-Dos (See below images). I would use To-Dos to record my general goals I wanna accomplish either in short run or long run. Then, at the beginning of each day, I pick some tasks I would like to focus on and put those to Today’s To-Do; at the end of each day, I manually enter today’s finished tasks and organize them into Daily Log, which is an extensive table that recorded what I did each date.

After manually transferring information from Today’s To-Do to Daily Log for a while, I got tired of the tedious “copy and paste” process. Thinking it might be a good chance to pick up some new skills, I looked up how to read and write Google Sheets using the elegant gspread API and it turned out to save so much time by just running a short script.

If you want to get a hands-on experience, you can use the demo files to code along with me. You are welcome to adapt and improve to make your own fit.

1. Imports

 1 2 3 4 5 6 7 8  import pandas as pd from google.colab import auth import gspread from oauth2client.client import GoogleCredentials from datetime import date, datetime import pytz import os import json 

2. Authenticate and Authorize

 1 2  auth.authenticate_user() gc = gspread.authorize(GoogleCredentials.get_application_default()) 

But essentially, once you run these two lines of code, an URL will pop up and opens a new tab in your browser which requires you to grant access. After that, copy the url and paste that to the box says verification.

 1 2 3 4  url = "https://docs.google.com/spreadsheets/d/1oogWtA5vSlcZTYzYL1CDrv4FIytao2MBBq6UmCKHLK4/edit#gid=0" wb = gc.open_by_url(url) todosheet = wb.worksheet("Today's To-Do") logsheet = wb.worksheet("Daily Log") 

Next we’re very much prepared to open our spreadsheets. Remember the gc variable above, which stores a client_class instance which you don’t really need to know. gc.open_by_url(url) opens a spreadhsheet by url and returns a Spreadsheet instance wb. By changing the name parameter of wb.worksheet(???), we can retrieve our respective sheets.

4. Manipulate retrieved sheets data

In my case, because I want to read the cell data into list of lists (which represent rows) and write it to a new sheet, I have manipulated it in ways I see fit. The returned newList will be used to populate the “Daily Log” sheet.

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44  data = todosheet.get_all_values() df_todo = pd.DataFrame(data) df_todo.columns = df_todo.iloc[0] df_todo = df_todo.drop(0) df_todo = df_todo.reset_index(drop=True) header = df_todo.columns.tolist() rowsList = df_todo.to_numpy().tolist() ''' return a list of lists to populate the log sheet will only add tasks that have already been done ''' def createPopulateData(rowsList): newList = [] for row in rowsList: rowList = [] task = row[header.index('task')] status = row[header.index('status')] if task == "" or status == "": break start_time = row[header.index('start time')] stepType = row[header.index('step/type')] pomos = row[header.index('pomodoro(s)')] pomoNum, intDistract, extDistract = 0, 0, 0 for sign in pomos: if sign == "x": pomoNum += 1 elif sign == "-": extDistract += 1 elif sign == "'": intDistract += 1 else: continue if pomoNum == 0: continue tz = pytz.timezone("US/Central") todayDate = datetime.now(tz).strftime("%m/%d/%y") # assumes the author runs the script before local 11:59 pm notes = row[header.index('notes')] rowList.extend([todayDate, start_time, stepType, task, pomoNum, intDistract, extDistract, notes]) newList.append(rowList) return newList newList = createPopulateData(rowsList) 

5. Streamline the update process

What if I wish to rewrite because I have made some other modifications to my “Today’s To-Do” sheet since my last write? Because the sheet.append_rows() function by default appends after the last filled row of the table and I don’t want that, I create a marks.json file when I run the script initially, which records the starting index of the range and whether this is an update action. If it’s an update action, I will switch to using sheet.update() as it will update and reflect the new changes I’ve made to sheet.

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37  path = "/content/marks.json" if not os.path.exists(path): start = len(logsheet.col_values(1)) + 1 marks = {} marks["start"] = start marks["update"] = False with open(path, "w") as f: json.dump(marks, f) else: with open(path) as f1: marks = json.load(f1) marks["update"] = True with open(path, "w") as f2: json.dump(marks, f2) with open(path) as f: marks = json.load(f) start, update = marks["start"], marks["update"] ''' do not delete Today's To-Do sheet until satisfied with Daily log editing because starting index of rows range to append is fixed ''' append = len(newList) - 1 end = marks["start"] + append if not update: print(f"append {len(newList)} rows to Daily Log") res = logsheet.append_rows(newList, table_range=f"A{start}:H{end}") else: print(f"update {len(newList)} rows to Daily Log") res = logsheet.update(f"A{start}:H{end}", newList) if len(newList) == 0: print("no finished task to append or update yet :)") res 

Lastly, the res object includes an automatic response that tells what has been appended or updated, to confirm if we do the right thing.