How to Build a Zero-Touch, Bluetooth-Automated Mileage Tracker with Google Sheets
Commercial mileage apps love charging £5 to £15 every single month just to run background GPS and spit out a spreadsheet at tax time. If you run a UK limited company, you already know the stakes: HMRC lets you claim 45p per mile for the first 10,000 business miles (and 25p thereafter), plus an extra 5p per mile if you carry a fellow employee or director. Miss those logs, and you leave tax-free company money on the table.
Here is how to build a completely free, 100% automated mileage tracker using Android, your car’s Bluetooth, and Google Sheets.
How It Works
This setup turns your vehicle’s Bluetooth handshake into an automatic odometer:
[ Car Turns On ] ──► Phone connects to Car Bluetooth
│
▼
• MacroDroid captures starting GPS & timestamp
• Google Maps launches automatically on your dash
│
[ You Drive ] ────────► Zero interaction required
│
▼
[ Car Turns Off ] ──► Bluetooth disconnects
│
▼
• MacroDroid grabs destination GPS
• Prompt: "Carried a passenger?" (Checkbox)
• Prompt: "Trip Purpose?" (Client name / Personal)
│
▼
• MacroDroid sends JSON to Google Apps Script Webhook
│
▼
[ Google Sheets ] ──► • Calculates exact road driving distance via Google Maps API
• Reverse-geocodes starting & ending postcodes/addresses
• Ignores driveway jitter (< 0.5 miles)
• Calculates HMRC claim amount (£ @ 45p/50p)
• Adds a clickable Google Maps route link
Step 1: Set Up the Google Sheet Database & Dashboard
Start by building the destination spreadsheet.
-
Create a new Google Sheet named Mileage and Expenses.
-
Set up row 1 with these exact headers from column A to I:
-
A:
Date -
B:
Start Time -
C:
Start Address -
D:
End Address -
E:
Miles -
F:
Passenger -
G:
Purpose -
H:
Claim (£) -
I:
Map Link
-
-
Freeze the header rows: Click View $\rightarrow$ Freeze $\rightarrow$ 2 rows.
-
HMRC 10,000-Mile Counter: In an empty cell in row 2 (or a frozen dashboard block), add this formula to total your business miles for the tax year:
Excel
=SUMIF(G3:G, "<>Personal", E3:E) -
Conditional Formatting Alert: Set a conditional formatting rule on that total cell: Greater than or equal to
10000$\rightarrow$ fill color Red. This visually flags when your AMAP claim rate transitions from 45p down to 25p per mile.
Step 2: Deploy the Google Apps Script Webhook
Google Apps Script acts as the server backend. It calculates road distance using native Google Maps routing, converts meters to miles, resolves street addresses, and generates a visual route link.
-
In your sheet, click Extensions > Apps Script.
-
Replace all boilerplate code with the following production-ready script:
JavaScript
function doPost(e) {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var data = JSON.parse(e.postData.contents);
// 1. Query Google Maps Directions API natively
var directions = Maps.newDirectionFinder()
.setOrigin(data.startCoords)
.setDestination(data.endCoords)
.getDirections();
var distanceMiles = 0;
var startAddress = "Unknown";
var endAddress = "Unknown";
if (directions.routes && directions.routes.length > 0) {
var leg = directions.routes[0].legs[0];
distanceMiles = (leg.distance.value / 1609.34).toFixed(1); // Convert meters to miles
startAddress = leg.start_address;
endAddress = leg.end_address;
}
// 2. Driveway Guard: Ignore trips under 0.5 miles (moving car on drive / listening to radio)
if (distanceMiles < 0.5) {
return ContentService.createTextOutput("Ignored: Trip too short");
}
var claimAmount = "0.00";
// 3. HMRC Business Mileage Engine
var isPersonal = !data.purpose || data.purpose.toLowerCase() === "personal" || data.purpose.trim() === "";
if (!isPersonal) {
var baseRate = 0.45; // HMRC AMAP rate up to 10k miles
// Check for Boolean true or string "true" / "yes" from MacroDroid
var hadPassenger = (data.passenger === true || data.passenger === "true" || (typeof data.passenger === "string" && data.passenger.toLowerCase() === "yes"));
var passengerBonus = hadPassenger ? 0.05 : 0.00; // 5p HMRC passenger allowance
claimAmount = (distanceMiles * (baseRate + passengerBonus)).toFixed(2);
}
// 4. Clickable Route Link
var mapUrl = "https://www.google.com/maps/dir/?api=1&origin=" + data.startCoords + "&destination=" + data.endCoords;
// 5. Append row to spreadsheet
sheet.appendRow([
data.date,
data.startTime,
startAddress,
endAddress,
distanceMiles,
data.passenger,
data.purpose,
"£" + claimAmount,
mapUrl
]);
return ContentService.createTextOutput("Success");
}
-
Click Deploy > New deployment.
-
Click the gear icon > select Web app.
-
Configure the deployment settings:
-
Description:
Mileage Webhook -
Execute as:
Me -
Who has access:
Anyone
-
-
Click Deploy, authorise Google permissions, and copy the Web App URL.
Step 3: Configure MacroDroid on Android
Install MacroDroid (free from the Google Play Store). You will configure two lightweight macros: one for when you get in, and one for when you park.
Macro 1: “Start Drive”
-
Trigger:
-
Connectivity>Bluetooth Event>Device Connected> Select your car’s Bluetooth handsfree system.
-
-
Actions:
-
Location>Force Location Update. -
Variables>Set Variable> Create local string variableStartLat> set value to[location_lat]. -
Variables>Set Variable> Create local string variableStartLon> set value to[location_lon]. -
Variables>Set Variable> Create local string variableStartTime> set value to[hour24]:[minute]. -
Applications>Launch Application$\rightarrow$ select Maps > check Force New.
-
Save and enable the macro.
Macro 2: “End Drive”
-
Trigger:
-
Connectivity>Bluetooth Event>Device Disconnected> Select your car’s Bluetooth handsfree system.
-
-
Actions:
-
Location>Force Location Update. -
Variables>Set Variable> Create variable namedPassenger:-
Type: Boolean
-
Value: Choose User Prompt
-
Prompt text: “Carried a colleague/passenger?”
-
-
Variables>Set Variable> Create variable namedTripPurpose:-
Type: String
-
Value: Choose User Prompt (or
Selection Dialog) -
Prompt text: “Enter Client/Job purpose (or type ‘Personal’):”
-
-
Web Interactions>HTTP Request:-
Method:
POST -
URL: Paste your Google Web App URL
-
Content Type:
application/json -
Body: Paste this JSON block:
-
-
JSON
{
"date": "[year]-[month_digit]-[dayofmonth]",
"startTime": "[lv=StartTime]",
"startCoords": "[lv=StartLat],[lv=StartLon]",
"endCoords": "[location_lat],[location_lon]",
"purpose": "[lv=TripPurpose]",
"passenger": "[lv=Passenger]"
}
Crucial Tip on Variable Tags: Never manually type[lv=StartTime]if you run into syntax errors. Tap MacroDroid’s blue[...](Magic Text) button next to the input field, tap Variables, and select your variable. This ensures MacroDroid injects the exact internal token your device recognizes.
Testing & Edge Cases
Before relying on this for your annual tax filing, run through these common real-world driving conditions:
-
The Driveway Shuffle: Moving the car 20 feet to let someone out of the driveway triggers Bluetooth. The script’s 0.5-mile cutoff aborts the payload, keeping your sheet clean.
-
Round Trips Without Stopping: The Google Maps Directions engine measures distance between where Bluetooth connected and disconnected. If you do a 30-mile round trip without turning off the engine, your start and end coordinates match, resulting in 0 miles.
-
Habit fix: Always cut the ignition or disconnect Bluetooth briefly when reaching your halfway point to split outbound and return legs.
-
HMRC Compliance: Keep client names, job site locations, or specific errands in the purpose field. Ordinary commuting between home and a permanent base does not qualify for AMAP claims.
Once configured, the phone stays mounted or in your pocket. You drive, kill the engine, tap the confirmation prompts, and your limited company expenses are audited, calculated, and mapped in real time.