The post you’ve all been waiting for! After all the fun of the previous three posts (one, two and three) you are ready to take your training spreadsheet to unknown levels of awesomeness. It’s time to link in your garmin connect account. Yeeaaaah, I said it. That is the dream I hear you all say. And it is, it really is.

Note: You might have to do quite a bit of messing around to make this work. I have found that it’s really helpful and quick now it’s working and it was definitely worthwhile, but you might well not. Just saying.

Before we go on any further, this is not for the faint hearted. If you try this yourself and get it to work I will here on in call you a spreadsheet ninja. I’m going to try and hold your hand all the way through, but things are definitely going to get a little scary for a while…

 

What we are going to do

We’ll take your googledocs spreadsheet (I’m not going to pretend I know how to do this in Excel) and:

1. Configure your Garmin Connect account to make this work
2. Add in a custom function to parse json files
3. Add a sheet to store your garmin connect activities
4. Create some vlookups to pull in the data into your training plan
5. Create some formulas to mess around with that data

As always, I’m going to use this spreadsheet here:  http://goo.gl/Msd62O.

 

Configure your Garmin Connect

Garmin Connect has some API’s for pulling data from accounts. I got all of this information from this site: http://sergeykrasnov.ru/subsites/dev/garmin-connect-statisics/web_api.php. In order to make this work you need to have an open profile on Garmin Connect and all your activities need to be public. If you are uncomfortable with doing this then I’m afraid I can’t help you much. If you are happy with this:

1. Go here
2. Set both options for everyone to see

Right, let’s test if it works. Take this URL:

http://connect.garmin.com/proxy/activitylist-service/activities/username?start=1&limit=10

Change the username bit to your Garmin connect name and paste it into your favourite browser. What you should get back is a page that looks like this:

If you see any error messages it’s likely your privacy settings are wrong. If you see the above great! This is a JSON file showing your most recent activities. Cool, huh? No? Well it will be soon, just you wait.

 

Add in a custom function to parse json files

So now we have the data, we want to be able to load it into our spreadsheet. Googledocs have some flipping great import things (see here for a good overview if your interested) but sadly it doesn’t have JSON just yet. Thanks to some much brighter people than me, we can utilise a custom script to do this. This is much easier than it sounds. I got all of this from here: http://blog.fastfedora.com/projects/import-json, which is a brilliant overview of it. If you don’t want to read it, do this:

1. Go here and copy all of the script you see
2. In Googledocs go to ‘Tools’ then ‘Script Manager’
3. Click ‘New’
4. If you get a wizard menu, just click ok
5. Delete any text in the window like (myFunction … )
6. Paste that whole script in
7. Go to ‘File’ then ‘Save’
8. Call it ‘ImportJSON’

 

Add a sheet to store your garmin connect activities

Now add a new sheet in your spreadsheet and call it ‘Garmin’. Go to Cell B1 (it’s important not to use Column A, we’ll need it later) and paste the following in:

=importjson(“http://connect.garmin.com/proxy/activitylist-service/activities/username?start=1&limit=10″,””,””).

Change the username to yours again. You’ll get a ‘Thinking…’ message and then BOOOOOM! Your latest activities in the spreadsheet! Sweeeeeeeet!!!

You’ve got a lot of fields in there you don’t need and we only have the latest 10 activities. Let’s change that. Paste this in:

=importjson(“http://connect.garmin.com/proxy/activitylist-service/activities/username?start=1&limit=100″,”/activityList/startTimeGMT,/activityList/activityType/typeKey,/activityList/distance,/activityList/averageSpeed,/activityList/duration,/activityList/maxSpeed,/activityList/averageHR,/activityList/averageBikingCadenceInRevPerMinute,/activityList/calories”,””)

This is going to restrict the list to the fields I use for graphs and stuff, but mess around if you want more. You’ll need to figure out the structure of the JSON file but it’s pretty sensible. The ‘limit’ part is how many activities it brings back, now it’s 100. Bear in mind this is going to load every time we load the spreadsheet so it’s probably best to not make it too big, but you’re grown up, you decide (I’d really like to make it just pull in new activities, I should figure that out someday).

 

Create some vlookups to pull in the data into your training plan

Ok, we have the data now, but we want to add it into the training plan we created earlier right? We are going to use some vlookups to do that. Vlookups work by looking up a value in a range of cells and then returning a value from a column in that range. We could use date to do that, which would work if you only ever do one type of activity in a day, but this probably isn’t the case, especially if you have brick sessions from triathons. What we can do is use a combination of date and activity type (unless you do multiple activities of the same type in which case you’ll have to figure something else out) to do this.

In your garmin activities sheet, in cell A2, use the following formula:

=if(B2=””,””,CONCAT(DATEVALUE(B2),C2))

Copy it down to several hundred rows (at least as many activities you are planning on doing).

What this does is checks if there is a value in cell B2, if there isn’t it adds a text string with nothing in. If there is, it concatenates together the date value in B2 and the activity type in C2. I had some trouble with dates, so I had to convert the date into the DATEVALUE format too.

Ok, go to your training plan sheet now and after the complete column, add in these column names:

Distance(km), Distance(miles), Duration, Calories, Pace, Speed(mph), HR, Cadence

So here’s where the formula’s get a little silly, but let’s just make sure one works first. In your Distance(km) column, paste the following: =vlookup((DATEVALUE(A2),D2),Garmin!A:J,4,false).

This formula is looking up a value in your training plan based on combining the date and type, and looking for that value in the sheet called ‘Garmin’. If it finds it, it returns the value in the 4th column. Paste it down the rows. It should return some distance values if you have started your training. If it doesn’t it’s likely the date formats don’t match. Make your you change your training plan date formats to match the Garmin connect ones yyyy-mm-dd. If that doesn’t work, make sure your activity type names all match up (use the garmin name).

If you have got this far, bloody hell well done. Go make a brew you’ve done the hard part.

 

Create some formulas to mess around with that data

In case you didn’t spot it earlier let’s look at that garmin connect data. The duration figures are all seconds and the distance is all meters. You probably don’t measure your workouts in those so we’ll need to do something about it. Also, you probably have some activities you don’t use garmin connect for but still want to measure speed, distance and calories. Finally on that lookup we created there are ton’s of N/A values (when the vlookup can’t find a match it returns N/A. That makes our sheet pretty untidy doesn’t it?

To solve all of that we need some formulas. I can’t promise these are perfect, but they work for me.

[table]

Cell, Formula

Distance(km), “=(if(isna(vlookup(concatenate(DATEVALUE(A2),E2),Garmin!A:I,4,false)),,round(vlookup(concatenate(DATEVALUE(A2),E2),Garmin!A:I,4,false)/1000,2)))”

Distance(miles), “=if(K2=””,””,K2*0.621371192)”

Duration, “=(if(isna(vlookup(concatenate(DATEVALUE(A2),E2),Garmin!A:I,4,false)),,vlookup(concatenate(DATEVALUE(A2),E2),Garmin!A:I,5,false)))”

Calories,”=if(isna(vlookup(concatenate(DATEVALUE(A2),E2),Garmin!A:I,4,false)),,round(vlookup(concatenate(DATEVALUE(A2),E2),Garmin!A:I,round(8,0),false),0))”

Pace,”=if(M2=””,””,((M2/L2))/86400)”

Speed(mph),”=if(M2=””,””,round(L2/(M2/60/60),2))”

HR, “=if(isna(vlookup(concatenate(DATEVALUE(A2),E2),Garmin!A:I,4,false)),,round(vlookup(concatenate(DATEVALUE(A2),E2),Garmin!A:I,9,false),2))”

Cadence, “=if(isna(vlookup(concatenate(DATEVALUE(A2),E2),Garmin!A:J,4,false)),,vlookup(concatenate(DATEVALUE(A2),E2),Garmin!A:J,10,false))”

[/table]

 

It would take me ages to explain these but it’s a mixture of looking up values and doing some maths on those values. The isna test let’s me return an empty string (“”) if the vlookups fail to find anything. Copy them down the rest of your training plan. You’re done.

These work pretty well for me. You might need to mess around with field types for rounding and the pace one only works if you set the data type to ‘hours’. But this looks pretty cool:

I’d now suggest you go crazy with your pivot charts with your new data (average pace vs average HR anyone?). If you find this useful, interesting boring or whatever, let me know. Good luck with whatever you are training for 🙂