Mileage rates change annually. Keep the rate in Config!B1 and recompute amounts in script or after rate edits.
mileageLogCalculation reads the rate once, multiplies each miles value, and writes column F amounts.
Rounding to cents keeps reimbursement exports payroll-friendly.
Combine with Maps Distance Matrix if you want miles filled from from/to addresses (separate pattern).
Need this built? Hire a Google Apps Script developer →
Sheet / project setup
| Sheet | Column / Cell | Purpose |
|---|---|---|
| Config | B1 | Rate per mile |
| MilesLog | A date | Trip date |
| MilesLog | B employee | Worker |
| MilesLog | C from | Origin |
| MilesLog | D to | Destination |
| MilesLog | E miles | Distance |
| MilesLog | F amount | Output |
What this script does
Rate × miles batch update for reimbursement amounts.
Prerequisites
Config!B1 rate; MilesLog miles populated.
- Numeric miles
- Rate as decimal dollars
- Run after log imports
Walkthrough
Rate 0.67, miles 10 → amount 6.70.
Edge cases
Blank rate yields 0 amounts — validate Config.
- Cent rounding
- Does not overwrite miles
How to test
Change rate and re-run; amounts update.
Hardening for production
Snapshot rate onto each row for audit when rates change mid-period.
Variations
Different rates by vehicle type column.
Full code: mileageLogCalculation()
Set Config!B1 to the current IRS/corporate rate, then run mileageLogCalculation().
/**
* Calculate mileage reimbursement from MilesLog using a rate cell.
*/
const LOG = "MilesLog"; // A date, B employee, C from, D to, E miles, F amount
const RATE_CELL = "Config!B1";
function mileageLogCalculation() {
const ss = SpreadsheetApp.getActive();
const rate = Number(ss.getRange(RATE_CELL).getValue()) || 0;
const sheet = ss.getSheetByName(LOG);
const values = sheet.getDataRange().getValues();
for (let i = 1; i < values.length; i++) {
const miles = Number(values[i][4]) || 0;
values[i][5] = Math.round(miles * rate * 100) / 100;
}
sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
Logger.log("Applied rate %s to %s rows", rate, values.length - 1);
}- Line 5: A1 notation for the rate including sheet name.
- Line 9: Reads the reimbursement rate once.
- Line 14: Per-row amount.
- Line 14: Amount column.
Deploy this example
- 01
Open Apps Script
In the bound spreadsheet: Extensions → Apps Script. For standalone projects, create one at script.google.com and link your Sheet by ID.
- 02
Paste and save
Add a .gs file, paste the code below, rename constants at the top (sheet names, column letters, API property keys), then save.
- 03
Authorize once
Run the main function from the editor. Accept OAuth scopes when prompted — triggers cannot run until authorization succeeds once.
- 04
Add the trigger
Triggers → Add trigger → choose the handler function and event (time-driven, on edit, or on form submit). Delete test triggers before production.
Before you run: mileage amounts
- 1Config!B1 has current rate
- 2Miles column numeric
- 3Policy matches personal vs company vehicle
- 4Run before AP export
- 5Consider freezing rate per row for audits
- 6Spot-check a few trips
Frequently asked questions
Manual entry, odometer import, or a Distance Matrix script writing column E.
Split logs by date and run with different rates, or store rateUsed per row.
Format column F as currency in the UI; values remain numbers.
Yes — PropertiesService.getProperty('MILE_RATE').
Store miles as total distance; do not double in the script.
Finance decides — script only multiplies.
Fine for live sheets; scripts help snapshot before export.