Two Seconds to Populate My Pomodoro Tracker by Google Sheets API
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.
Demo Files (Follow along!)
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.
2. Authenticate and Authorize
The first step is to enable Google Sheets API to access your spreadsheets. “Authentication and authorization are mechanisms used to verify identity and access to resources, respectively.” You can learn more about authentication and authorization here.
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.
3. Retrieve your sheets
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.
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.
res object includes an automatic response that tells what has been appended or updated, to confirm if we do the right thing.