This is part of our series of articles (RSS) where we share some of our favorite technology tools. Like you, we’re pretty enthusiastic about new ways to geek out, and we hope to share some of that here. Let us know what you think.
A few days ago I was reading Paul Smalera’s Medium essay entitled ‘The Data Diet: How I lost 60 pounds using a Google Spreadsheet’. This interested me not only because of Paul’s astounding progress in his health habits, but also because I had tried this before. Tried and failed. In addition to requiring you to add your weight to a spreadsheet each day, Paul’s sheet template has a ten day rolling average, and I really liked that idea. But I still think it’s a friction point to manually type your weight into a Google Spreadsheet. Google Docs is atrocious on mobile, so that’s not really an attractive option for me. Yucky, yucky, yuck.
My plan of attack? After briefly considering an Ifttt recipe, I settled instead on the very handy Drafts app because of its ease of use. This uber-trendy text editor (which costs $2.99/¥250 in the App Store) could be used to input my weight in a snap on my iPod Touch and send it directly to a file in Dropbox, using the ‘Append to Dropbox’ action — or something similar anyway. But why Dropbox? We want our weight to go into our Google Spreadsheet, right? Yes, but I’ll get to that in a moment. Under
SETTINGS > CUSTOM ACTIONS > DROPBOX ACTIONS. I decided to make an entirely new action based on ‘Append to Dropbox’. I called it ‘Weight to Dropbox’ and I defined the path for this file to be saved under
/Public/Weight/. I made the file extension CSV, so that file can be easily imported later on from Google Docs. It’s a bit of a hack, but trust me, it works like a charm.
But the ‘Template’ section was a little tricky. I needed to add the date, plus a comma to complete the our daily line of input (because we’re creating a comma separated values file). Paul’s spreadsheet has a date field, so I want it to match up with his formal. To do that, we just need to enter the following:
The first part represents the date, and the second ‘draft’ portion will represent whatever we type in our Drafts entry, in this case our weight. If you’d like to get the action, it’s here. Clicking on it once you have Drafts installed should do the trick.
So now, inputting my weight on a daily basis creates a simple CSV file that looks a little like this (after three entries, anyway).
But by putting the file in my Dropbox public folder, that means my data is published to the web. And in turn, that means that by copying my Dropbox public link for that file , I can use Google Spreadsheet’s import function to connect it to Google Docs directly, and have the spreadsheet update whenever I add a new daily weigh-in.
To do that, I added a new sheet to Paul’s template called CSV. And in cell A1 I added the following data import formula :
…and BOOM! There’s your data. Now to get those entries into Paul’s spreadsheet template, you merely need to do use your ‘=’ sign in Paul’s sheet, and point it towards the corresponding cell data in your CSV sheet for that date (see below) . And then apply that formula to all cells for the entire year.
And now once you have that, you get do lots of fun stuff. I added a couple of charts and published the entire sheet to the web. Now I can add that to my iPod homescreen for example, and easily access my chart anytime I wish (pictured below). It doesn’t look entirely awful in landscape .
So far this system is working pretty well for me. I even bought a digital scales for better readings than my old analog scales. I’m only a few entries into my data recording, and I look forward to seeing if it makes an impact on my behavior and/or diet. I’ve been putting in more time on my desk-cycle after meals (did I not tell you about the desk-cycle?), so I hope I can myself keep in decent shape as a result. I’m considering another Dropbox action to log bike time, but that’s another discussion!
Update May 30th – I thought perhaps there might be a way to get my weight chart to display on my desktop. And indeed there is! Geektool for Mac does the trick, with its image display function. Just publish your Google Chart as an image, and grab the image URL for use in Geektool. I also used a fun Doctor Who backdrop, and threw in the DateLine app as a fancier X-axis. I still need to adjust my graph’s grid lines though. But I like how its coming along! (See below)