DEV Community

Habits + Google Sheets = Profit!

Hector de Isidro on February 22, 2019

Because good habits are worth it Update: after more than two years using the solution below, I finally decided to create my own nat...
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

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

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

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

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
Shanmugavel Arunachalam

excellent.