DEV Community

loading...

Habits + Google Sheets = Profit!

hector6872 profile image Hector de Isidro ・3 min read

Because good habits are worth it

What is a New Year Resolution?

A New Year’s resolution is a tradition in which we make a bunch of promises¹ -on the last days of the year or so- to ourselves to do something in order to improve our behaviour or lifestyle in a good way during the year ahead (i.e. quit smoking, eat less junk food, lose weight, do some form of exercise and/or stop saying JS is a real programming language²).

Even though some they could be just a personal goal or challenge (i.e. travel solo), almost every promise we make of this kind usually is -to a greater or lesser degree- a habit (to either make or break it).

What is a habit?

A habit is the behaviour pattern, acquired through frequent repetition³, we do often unconsciously -unintentionally and uncontrollably- in response to a known cue⁴.

What does Google have to do with all this?

Most of us have ever promised to break some bad habit as a New Year’s resolution and then realise after summer holidays -at best- that we hadn’t even started. And then, once again, another year is gone…

Tracking our habits⁵ every day — or at least once a week — is the best way to keep us motivated and gives us a glimpse of how well (or bad) we are doing it.

I’ve tried a bunch of applications and websites to track habits but for whatever reason (pricing, lack of export options, uncertain future, etc…) I haven’t felt comfortable with any of them. Then, I talked to two huge organisation experts Jesus Cerviño & David.S -whom I’m lucky enough to have as co-workers- and I was told that I could do everything I wanted (and even more) with a powerful tool⁶ widely used: Google Sheets.

First we make our habits, then our habits make us

After doing some research I’ve created an improved Habit Tracker template based on the awesome work of Harold Kim.

Log in to your Google Account, make a copy of my Habit Tracker template (File>Make a copy…) and fill down Activity column with your smart goals and habits.

Then, clear the sample data and you will be able to track your own habits putting a X (or 😄/🙁 depending of your mood) on every cell you want to mark it as Done.

Don’t break the chain!

Features

  • Streak and Max Streak

  • Progression based on the value you have put in Expected column (which has to be in days⁷)

  • Highlight current day

  • Alternate colours every month

  • Autohide columns based on date

Tip: you could even use the Habit Tracker as a habits related journal through the built-in notes feature.

Don’t forget to set up a recurring reminder (you can use also Google Calendar btw) so you don’t forget to keep track of your habits. Then make a habit of this too!

Conclusion

Above all, don’t be a slave of your (bad) habits… and be like Bill!

This article was originally published on Medium


[1] The most common New Year’s Resolutions in UK (December 2015) and US (December 2017)
[2] Just trolling, don’t feed me 🐟
[3] Developing a new habit takes between 18 and 254 days (66 days on average). Unfortunately, there are no magic numbers.
[4] A great tip about habits and willpower: we should not focus on the behaviour but rather focus on the cue (i.e. location, time of day, emotional state, belief, other people, etc…) to develop new habits (or to break them)
[5] What gets measured gets done
[6] It’s free, multi-platform, allow us to export the data and works offline 💪
[7] Remember that a common year has 52 weeks (i.e. if you want to workout 3 times a week you just have to put in that cell =3*52) 👨‍🎓


External links 👀

Discussion (13)

pic
Editor guide
Collapse
corinneiskorean profile image
☃️cirrussly c❄ld☃️

Hi I have a quick question on the streak/max columns. They're currently showing errors on my end in both the empty and original template, and I'm unsure what's going on (not a coder, was just looking for a habit tracking template for google sheets!) The error says the row is not an array? thanks!

Collapse
jeremywalter profile image
Jeremy Walter

Figured this out ... kinda through dumb luck. The date format in row 1 is set for 1/1/2019 (displays as 1/1, have to see/change it in the formula). Change it to 1/1/2020 and drag across all the rows - and the streak and max column formulas work beautifully again.

Collapse
corinneiskorean profile image
☃️cirrussly c❄ld☃️

thank you so much! this worked!

Collapse
hector6872 profile image
Hector de Isidro Author

Thanks for catching that!

Collapse
jeremywalter profile image
Jeremy Walter

Yeah - something broke in the code yesterday. I used the template, copied to my own and customized it, and everything worked beautifully for about a week. Then at some point yesterday the streak/max columns errored out in my copied sheet. I thought I broke something, so went and grabbed the original one as well - and same error message. Not sure what happened.

Collapse
nonoesp profile image
Nono Martínez Alonso · Nono.ma 👨🏻‍🎨 • Edited

Thanks for the tip, Jeremy.

It seems the code relies on the TODAY() function, and there's no TODAY in the old 2019 sheet anymore, so the current script is not backwards compatible. I've been using the habit tracker in 2019 and now it doesn't render the current and max streak values.

Anyway, setting to 1/1/2020 and dragging worked for me. (I had to add one more row for 12/31/2020 as February has 29 days in 2020.)

Cheers,
Nono from Getting Simple

Thread Thread
hector6872 profile image
Hector de Isidro Author

Ooops! Thank you for pointing that out, I'll look into it!

Thread Thread
nonoesp profile image
Nono Martínez Alonso · Nono.ma 👨🏻‍🎨

Just figured out a solution: replace TODAY() with $ND$1 in your 2019 habit tracker sheet and everything works. (This will make it stay frozen at 31/12/2019.)

Thread Thread
nonoesp profile image
Nono Martínez Alonso · Nono.ma 👨🏻‍🎨

And, actually, the best way I've found to continue with the new year (and carry out with the existing streak and max counters) is to add columns to the right of the 2019 sheet and expand the dates by dragging 31/12/2019 and having the same sheet for 2019 and 2020. That way, everything works as it used to and the counters stay the same.

Hope that helps!

Cheers,
Nono from Getting Simple

Collapse
eoinmurphy profile image
Eoin Murphy

This is great, thanks for posting! Just letting you know that the Progression column is currently returning the error: Unknown function: 'GET_PROGRESSION', so you may want to check that out. :)

Collapse
hector6872 profile image
Hector de Isidro Author

I don't know why it says that because as you can see in the sheet's functions: it actually exists.
As workaround you can use the empty Template (which shares the same code tho 🤷)

Collapse
finallynero profile image
Nero Adaware • Edited

This is awesome, I am currently tracking my habits with a different google spreadsheet but I am having problem with calculating the streak for habits, I think i will migrate to yours.

Collapse
shan profile image