CtrlAltUpgrade
← All writing

Home Assistant · · Timothy Alexander

Letting Home Assistant fill in my mileage form

I drive between school sites most days, and the district reimburses mileage using an official chart of distances between sites. For years I logged trips in a paid mileage app, then retyped them into the district's expense form at the end of the month. Both halves of that were tedious, and the app's GPS miles never matched the chart anyway.

My car already reports its location to Home Assistant, and the form is just a spreadsheet. So I'm connecting them. The spreadsheet side and the link from Home Assistant are working; the last piece, the zone automation that spots each trip on its own, is what I'm finishing now.

What you need

  • Home Assistant, with a way to see where your car or phone is (I use the car maker's integration; the Companion app on your phone works too)
  • A Google Sheet copy of your expense form, with your organization's mileage chart on its own tab
  • Google Apps Script, published as a small web app, so Home Assistant has somewhere to send trips

The rules come first

Before writing any automation, I wrote down the rules in plain language, because that's where all the edge cases live:

  • A claimable leg is one work site to the next work site. Stopping at home or for lunch in between doesn't break the leg, and detours aren't claimed.
  • Driving from home to my regular office, and from my last stop back home, is commuting and isn't claimed.
  • If I drive from home straight to a different site, I claim it, using the shorter of home-to-site or office-to-site, which is what the form's policy says.
  • Miles always come from the official chart, not from GPS.
  • Anything odd gets marked Pending for me to approve: a work stop that isn't a school (a hardware store run), or a trip outside normal hours (evening games where I'm running audio and video).

Home Assistant: zones and one REST call

The plan for Home Assistant is simple: each school is a zone, and an automation watches the car arrive at a zone and remembers the last work site it left. When it arrives at a different site, it sends the leg to the spreadsheet. The sending part is already in place, a single rest_command:

# configuration.yaml
rest_command:
  mileage_log_trip:
    url: !secret mileage_url
    method: POST
    content_type: "application/json"
    payload: !secret mileage_payload

The web app's address and its shared token live in secrets.yaml, not in the automation, so the config can be shared or backed up without leaking them.

Google Sheets: the form does the math

The Apps Script web app checks the token, looks up the two sites on the mileage chart tab, and appends a row to a Trip Log tab: date, description, chart miles, status, and where the trip came from. The expense form tab never gets typed into directly. Its trip lines are formulas that pull approved trips for the pay period from the log, so the totals, rates and meal allowances calculate exactly as they did on the paper form.

That split matters. The log is messy and editable: I can fix a description, mark a pending trip approved, or add a meal. The form stays clean and always matches the official layout.

Closing out the month

At the end of the month, one menu item in the sheet does the paperwork:

  1. Makes a copy of the form and freezes the formulas into plain values, so it can't change later.
  2. Fills in the date and adds my signature image.
  3. Saves a PDF to a Drive folder and emails it to me.
  4. Includes a one-time link that sends it on to my supervisor once I've checked it.

There's also a "preview" option that only emails me, which I used a lot while getting the layout right.

What I'd tell you before you build one

  • Write the rules down first. Every bug I hit was a rule I hadn't stated, not a code problem.
  • Keep a human in the loop for the odd cases. Pending-for-approval is what makes it trustworthy enough to sign.
  • Never type into the official form. Feed it from a log with formulas, and it will always match the original.
  • Back up the sheet. It's now the record of money I'm owed.

Once the zones are in, the miles will match the chart without retyping, and month-end becomes one menu click and a quick check.