Dash to Google Sheets (IFTTT)

This is my current solution for logging journeys to a Google spreadsheet. In parallel, another IFTTT recipe is writing the same information to an Evernote notebook.

Arguably, a database would be a more appropriate solution. And, if this sort of thing appeals to you, you'd probably enjoy this Personal Web Apps YouTube presentation by Nathan Vander Wilt, which gave me some additional inspiration. It's an interesting 30 minute talk.

Bluetooth OBD-2 Device

In summary, my current set up consists of an Elm327 OBD-2 device, around £7.50, kindly donated by Steven Horner. This plugs into the engine management port (under the steering wheel in the case of my Volvo) and communicates with Dash running on my Nexus-5 smartphone. A recipe on IFTTT periodically checks for completed journeys, and sends them to Evernote and Google Sheets. In addition, the Android app provides real-time stats while you're driving; such as current and average fuel consumption, etc.

Formatting the Output

Modifying the IFTTT Recipe

Conveniently enough, Dash links directly with your IFTTT account, so sending the data to a Google spreadsheet is a simple matter of creating a new recipe. I made a couple of minor tweaks to the default recipe, skipping some fields and fine tuning a couple more with a formula.

Specifically, I wanted a "clickable" link to the map, not simply a URL. The two formulae below take care of that, substituting clickable "Start" and "End" links in place of the URL.

=hyperlink("{{StartAddressMapURL}}", "Start")
=hyperlink("{{EndAddressMapURL}}", "End")

The code snippet below shows the modified recipe.

{{StartedAt}} ||| {{StartAddress}} ||| =hyperlink("{{StartAddressMapURL}}", "Start") ||| {{DrivingScore}} ||| {{DistanceDriven}} ||| {{FuelConsumed}} ||| {{AvgDistancePerHourWithLabel}} ||| {{AvgFuelConsumptionWithLabel}} ||| {{EndedAt}} ||| {{EndAddress}} ||| =hyperlink("{{EndAddressMapURL}}", "End") ||| =(indirect("F"&row())*4.54609)*(index(Fuel!A1:A,count(Fuel!A1:A)))

The Google Sheet

The resulting spreadsheet is not perfect, but provides a convenient view of all journeys. The cost calculation is rather crude, using a second tab in the spreadsheet, labelled "Fuel". It's a single column, operating on the principle that every time I refuel, I add a cell to the bottom of the list with the price per litre. The formula below makes this calculation, using the most recently added fuel cost.

=(indirect("F"&row())*4.54609)*(index(Fuel!A1:A,count(Fuel!A1:A)))

Whilst this works OK, the problem is probably fairly obvious, given that every time you log a new price per litre on the "Fuel" tab, the entire "Cost of Journey" column is recalculated on the main tab. The unintended consequence being that the historic journeys' cost is shown using the latest fuel cost.

Fuel Costs on Google Sheet

But, despite that limitation, the following information is logged, and for my purposes it still represents a useful record of each journey.

Start Time: August 4, 2014 at 6:44PM  
Start Address: Dundas Mews, Middlesbrough TS1  
Map: Start  
Driving Score: 99  
Distance: 7.4832816  
Gallons: 0.21807793  
Average Speed: 29 mph  
Average Fuel: 29 mpg  
End Time: August 4, 2014 at 7:00PM  
End Address: Durham Rd, Stockton-on-Tees TS19 9PB  
Map: End  
Cost of Journey: 1.24916639