Google Docs to log your rides

Posted by Neil on 08/09/2009 under Google Docs | 5 Comments to Read

Written by Neil Hickson and edited by Gillian Hook

The business I run specialises in helping other businesses use online media to market themselves. We make use of the many economical and free online tools and media that are now available. Two of my favourites are the WordPress blog/CMS web publishing platform and Google Docs. Put the two together and, with a little thought, you can design very complex and useful web applications.

As part of my cycling life, I want to keep a note of how many miles I cycle, partly because I think it helps psychologically to note your achievements (no matter how ‘paltry’ in my case!), and I also think it will be useful to have a record on how much cycling has had an impact on my life and how much money I have saved.

I bought myself a cheap Halfords cycling computer (an excellent product at £10 by the way) that collects the data I need on each ride (distance, time taken, average speed, maximum speed) together with a running total of all the mileage clocked up since it was first calibrated.

I started by entering this info in a spreadsheet, but eventually decided there must be a better way of collecting and making sense of this information. As I already keep a blog about my cycling life, I thought it would be a good exercise to use Google Docs to collect, process and display the data right through my website, making it easier to log the information and display the results.

I have started off in a simple way by just displaying my mileage and time on the bike until I have decided exactly what data it is that I want to make use of. So, just to give you an overview of the input process, I do the following:

1. Finish my ride
2. Go to my website and click on the ‘ride log’ page
3. Enter the password (no, I don’t want anyone else adding their own data or corrupting mine!)
4. Fill in the form data

From there, the data automatically gets logged onto a spreadsheet that acts as a database for the entries. The spreadsheet has a second page that works out any processing I want on the original data. The processed data is then published on a separate page of the website. The great strength about this is that it is totally flexible: you can collect whatever data you want, process it any way you want and display it in either numerical or graphical representations.

I strongly recommend that you get yourself a Google Mail/Docs account and a WordPress blog (either self-hosted or on WordPress.com) if you want to start with any web publishing project. Drop me an email if you want any advice.

I will start this tutorial from the assumption that you have Google Docs and a blog; if you don’t, please carry on reading because once you get a feel for the type of things you can achieve with these free tools, I am sure you will want them.

These techniques are ideal for creating enquiry forms, online polls, surveys etc. You will be emailed whenever someone completes a form and you will have a permanent record of the data collected all in a handy spreadsheet.

So let’s start with a new form in Google Docs.

You will end up with a blank form that you can start to fill in. The first thing to do is to name the form e.g. My Cycling Log, then save it. This will become the name of the data spreadsheet that you will later refer to.

Now start to formulate your questions: there are options for the format of the answer you want, ranging from simple text entries to paragraph text, ‘choose from a list’, multiple choice answers and tick boxes. These can be amended and edited as you see fit at any time.

You also have the option to make the questions mandatory so that the form will not be accepted unless the question has been answered. Here is a ‘checkbox’ question that I put together about ride weather conditions:

You can add themes to the form and then preview the results by going to the published version of the form via the URL at the bottom of the screen.

When you have built your form, close it down then go back to your document list. You may need to refresh your browser screen if the list hasn’t updated (F5 for PC, Command+r for Mac). The form will appear as a spreadsheet, which you can now open.

Here is my spreadsheet with some entries: the first column has a date stamp logging when the entry was made – a handy feature. Then, going left to right, there is a column for each of the questions on the form with the relevant data from that form. See below:

If you want to edit the form further, you can go to the form menu and enter the edit mode from there.

I named this sheet ‘Form Gathered Data’ in the bottom left hand corner of the spreadsheet.

When you are ready to publish your data collection form, select the ‘Embed Form’ item from the Form menu. See below:

You can copy the iframe HTML code necessary by holding and dragging your cursor over the embed code and then pasting the code into your new WordPress page.

If the WordPress page you are working is open in another tab on your browser, select that; if, however, you are working in two separate browsers like I do (more of this in the tip section), use the Alt+Tab keys to flip to the WordPress page you are going to paste the code into.

Make sure that the page you are pasting into is in HTML mode (see pic below) and paste in the code using control+v (command+v on the Mac).

So now you have your data collection form as a page on your website. Mine looks like the image below because I added at theme – there are many available – or you can just have it plain:

You shouldn’t be able to see this when you go to my web site because the page is protected and needs a password to gain access to the form.

So, how do we process the data and get it to display on the site? More ’spreadsheetery magic’ needed.

Go back to your spreadsheet and click onto the ‘Add Sheet’ button in the bottom left hand corner. This will add a sheet to the current spreadsheet file, which we will use to process the raw data of our spreadsheet and produce information that we can publish.

I called this sheet ’summary sheet’ and its output appears on my mileage page. You will recognise this sheet if you visit my page.

Just to illustrate some of the things that are possible, I decided to collect and display my total time on my bike plus my total distance travelled in both miles and kilometres, which seem to be the international ‘currency’ of bicycling distance.

I first put my titles down the left hand side of the sheet in the A column. Then, for the first calculation, in B2 I entered the start of the formula:

=sum(

I then clicked onto the spreadsheet link for my data spreadsheet in the bottom left-hand corner of the browser window. I then clicked onto the ‘C’ column header in this spreadsheet – this column holds the ‘time riding’ information that is deposited into the data gathering form. The formula auto completes with the data

Just enter the closing bracket ‘)’ at the end and press enter. That’s it – you should be back in your summary sheet at the cell where you started to enter the formula with the following:

=SUM(‘Form Gathered Data’!C:C)

This formula ’sums’ the column ‘C’ from the ‘Form Gathered Data’ spreadsheet. It will continue to add on every cycling time that I enter into the ‘time’ entry of my spreadsheet.

There is a finite limit to how many entries you can have in a spreadsheet – Google currently limit it to this: Spreadsheets: Each can be up to 256 columns, 200,000 cells, or 100 sheets–whichever is reached first. There’s no limit on rows. In short, more rides than I have planned for this year, and several more to come!

I went through a similar procedure for distances. For the kilometre conversion I put in cell B6 the following formula:

=B4*1.61

Displaying Processed Data on your web site

You have now created your spreadsheet and are ready to get its contents to show up in a page on your WordPress web site.

First, go to the Share menu and select publish as a web page.

The ‘Publish to the web’ dialogue box appears. The part you want to change is the ‘Get a link to the published data’ in the lower section of the box. However, this remains ‘greyed out’ until you click onto the ‘Start publishing’ button in the ‘Sheets to publish’ panel.

The bottom box then becomes ‘live’ and you need to select the ‘HTML to embed in a page’ option, which is below the default ‘Web page’ option. (See pic below).

Type in the cell range of the spreadsheet: in my example this is the range A2 (top left hand cell) to B6 (bottom right hand cell). The two cells are separated by a colon e.g. A2:B6.

When you press the enter key, the box below the cell entry box changes to an iframe HTML code (see pic below). This is the code you need to copy and put into your WordPress page.

Highlight the entire iframe code between the <> brackets by dragging your mouse over the code, holding down the mouse button (I usually go backwards from end to start to make sure I get all the code or it won’t work). To copy, you can use ‘control+c’ (command+c on Mac).

Note that the iframe dimensions are dictated by the width and height dimensions noted in pixels (see pic below). If you want to avoid using sliders in your page, you can change these dimensions to something more suitable such as 600 pixels by 600 pixels.

Some experimentation may be necessary and the size of the text you use in your spreadsheet will also have an influence on the dimensions.

Likewise, the width available is dictated by the template of your blog; most are between 500 and 600 pixels as this is an ideal size for readability and for putting reasonable-sized images on the page.

Hit publish and away you go.

One tip I have is this: as WordPress doesn’t always have the ability to display the text in the way you would expect to see it, have two browser tabs, or even two separate browsers open: one with the WordPress page on which you are working (ie the ‘back end’) and one with how it will appear to the rest of the world.

I usually work in Safari, the Mac default browser, and have Firefox opened displaying (to the outside world) the page I am actually editing – Firefox has a handy web tool called Firebug for assessing CSS etc.

I then use Alt+Tab to flick between the two.

Don’t forget to press F5 on the PC (command+r on the mac) each time you make a change to refresh the browser screen, otherwise you won’t see the last changes you made to the page.