Spreadsheets for everything

December 28th, 2019

Recently I've started using Google Sheets as my main tool for organizing lists, tracking time, and taking notes. I've tried many different things in the past:

  • Google Keep: Definitely more user-friendly on mobile. Great for shopping lists. I tried using Google Sheets for a shopping list and the mobile interface was awful. I am, however, prone to using Google Keep as a dumping ground for random links and not tagging appropriately, so I end up burying notes under a pile of random links. Also, I need a to-do list where I can track time, and Keep won't help with that.
  • Google Docs: Again, I can't track time as easily using Google Docs, also, I end up using Google Docs a lot for, well, writing documents. Not the best way to organize to-do lists and other notes.
  • SuperProductivity: This is an app that tracks time and syncs with Jira, but I was having a lot of problems with it bogging down my computer.
  • Markdown and text files: I end up forgetting to update/forgetting where the file is on my hard drive/am unable to access files from another computer.

I've tried using Google Spreadsheets for a few things in the past. I had a budget spreadsheet set up for 2019 that I maintained until April. I'm not terribly bad at following a budget, but I am bad at updating a budget spreadsheet. I've also tried using spreadsheets for tracking tickets at work, but really, that's what Jira is for, right?

However, after learning a few shortcuts for Google Sheets, I've found a great way to make a to-do list and time tracker that I'm actually using at work. While it doesn't have any fancy Jira integrations, I'm able to more accurately calculate how much time I'm spending on tasks each day.

To-Do spreadsheet

This is my personal to-do spreadsheet. The work sheet is set up exactly the same way. Here's how I have it set up:

To-do spreadsheet

Each task has its own line, followed by the date that it's added to the spreadsheet. That way, recurring items can be sorted by date if needed. Then, a column for the date that I finished the item. There's a handy shortcut for adding the current date to a Google Sheet. Simply press Ctrl + ;.

When I have completed items, I can filter by whether or not the "Finish Date" column is empty, hiding completed items but keeping them in the spreadsheet in case I need to refer to them in the future.

Spreadsheet filtering

The next column is the total elapsed time that a project took. It's not really necessary for a personal to-do list, but I use it all the time at work to track progress on Jira projects. I've put the total elapsed time here instead of after all of the timestamps because I wanted the important information as close together as possible. This column uses the following algorithm to calculate the elapsed time:

=MAX(G2-F2,0)+MAX(I2-H2,0)+MAX(K2-J2,0)+MAX(M2-L2,0)+MAX(O2-N2,0)+MAX(Q2-P2,0)

It takes the first "Time End" column and subtracts the first "Time Start" column from it, then does the same for the subsequent columns, then adds them all together. That way, if I'm starting and stopping a project frequently, I can get the day's total elapsed time. With Jira tickets, I log the elapsed time in Jira each day, then delete the timestamps for the next day. I'm using MAX() so that if a pair of cells returns a negative elapsed time when it's in progress, it returns 0 instead.

One of the most useful keyboard shortcuts for this to-do list is the shortcut for adding the current time. It's nearly the same as adding the current date, but with the Shift key added, Ctrl + Shift + ;.

Finally, I added a bash script into ~/.bash_profile so I can easily open my to-do list in a new window from the command line. I like controlling my computer from the command line as much as possible, and using keyboard shortcuts as much as possible.

alias todo='open -n \/Applications\/Google\ Chrome.app --args --app="{URL to spreadsheet goes here}"'

Line-A-Day worklog

Another of my new spreadsheets is a place to keep brief notes. I try to keep daily notes about the major events of the day at work, so I have something to refer to at the end of the year. However, I haven't been able to stick with anything. I've tried Markdown files and text files, free writing and bullet journaling, but I can't seem to form the habit of writing notes daily.

One Line A Day notebook page

For Christmas this year, Jason got me a new journal, the One Line A Day book. Each page is dedicated to one day, but divided into five sections. The idea is to write a tiny bit about the major things that happened on each day for the next five years. Journaling an entire page each day can be daunting, but dedicating a few minutes to write a line or two is much easier. Because of this, I was inspired to create the Line-A-Day worklog spreadsheet.

Line-A-Day spreadsheet

I've created a spreadsheet with the years across the top and the days in the first column. Each month gets its own tab. I have just enough space to write a few sentences in each cell.

Honestly, I'm not sure how well this is going to work or if I'll stick with it. I'm planning on sticking with the paper journal for personal notes, but whether or not the spreadsheet version will continue to keep my interest remains to be seen.

Wants list

The last new personal spreadsheet I've made to try to keep myself organized is a "wants list". This is a simple list of non-food items that I want. (Non-food because I have that separate grocery list in Google Keep.)

Wants list

The list ranges from things that I probably wouldn't get myself but I'd like to have (the Disney H2O+ bath set is $100+ and frivolous, but I like the way it smells), to things that I need (the casserole dish was something we've needed in this house for a long time). I'm making this list for a few reasons. First, to keep impulse buying in check. If it's not already on the list, is it really something I want? Maybe the item should go on the list for a while to see if I really want it, and if so, I can buy it later. Second, it helps to keep a list for birthdays and Christmas, so I have something to give to family who ask.

These are just a few ways that I'm using spreadsheets to try to be a more organized person. I'm currently working on another spreadsheet to keep track of recipes. I received 5(!) cookbooks for Christmas this year, on top of the two that I had from last year. I plan on going through each book, inputting the recipe, book, page number, and ingredients into the spreadsheet, and keeping track of when I tried the recipe and if we liked it. Hopefully, by the end of the year I'll have a record of all the new dishes I've prepared in the past year!