Google Sheets Commute Time Calculator for Apartment Hunting

When you're apartment or house hunting, commute time to work is often one of the most important factors in your decision. Instead of manually checking Google Maps for every potential home, I'll show you how to create a powerful custom function that calculates your commute time right inside Google Sheets.

This custom function will handle scenarios like morning school drop-offs, different travel modes, and flexible departure times. You can create a comprehensive comparison spreadsheet that evaluates multiple properties based on their commute convenience. The best part? Once you set it up, it works just like any other spreadsheet formula, making it easy to compare dozens of potential homes at once.

This tutorial will take approximately 20 minutes to complete, and by the end, you'll have a useful commute calculator that you can use in any Google Sheets spreadsheet.

Prerequisites

This tutorial assumes the following prerequisites:

• Basic understanding of Google Apps Script and how to create scripts

• Familiarity with custom functions for Google Sheets

• Basic knowledge of objects in Apps Script and arrays in Apps Script

4 steps to implement the apartment hunting commute calculator

• Step 1 — Create a new Google Sheets spreadsheet and bound script

• Step 2 — Create the custom function

• Step 3 — Test the function in Google Sheets

• Step 4 — Build Your Apartment Comparison Dashboard

Step 1 — Create a new Google Sheets spreadsheet and bound script

First, we must create a new Google Sheets spreadsheet and attach an Apps Script project to it.

Navigate to sheets.new to create a new Google Sheets spreadsheet or open your existing apartment hunting sheet if you've already created one.

Once your spreadsheet is open, go to Extensions → Apps Script to create a script project. This will open the Apps Script editor with a new project that's automatically bound to your spreadsheet. Delete the default myFunction() that appears in the editor. We'll replace it with our commute time calculator in Step 2 below.

Let's rename our project to something meaningful. Click on "Untitled project" at the top of the screen and rename it to "Apartment Commute Calculator".

Step 2 — Create the custom function

Now we'll create the COMMUTE_TIME custom function. This function will be the entry point that users call from their spreadsheets. It uses helper functions to validate inputs and to perform route calculations and estimate commute times.Copy paste the code below into the script editor and save the Apps Script project.

/**
* Returns commute time in minutes with improved error handling and flexibility.
* @param {string} am_pm AM or PM commute direction.
* @param {string} homeAddress Your home address.
* @param {string} workAddress Your work address (optional if schoolAddress provided).
* @param {boolean} hasSchoolStop Whether to include school as a waypoint.
* @param {string} schoolAddress The school's address (required if hasSchoolStop is true).
* @param {number} departureHour Optional: Hour for departure (0-23). Defaults: 8 for AM, 17 for PM.
* @param {string} travelMode Optional: DRIVING, WALKING, BICYCLING, TRANSIT. Default: DRIVING.
* @return {number} The commute time in minutes.
* @customfunction
*/
function COMMUTE_TIME(am_pm, homeAddress, workAddress, hasSchoolStop, schoolAddress, departureHour, travelMode) {
  try {
    const finalWorkAddress = isEmpty(workAddress)  ? null : workAddress;
    const finalHasSchoolStop = isEmpty(hasSchoolStop) ? false : hasSchoolStop;
    const finalSchoolAddress = isEmpty(schoolAddress) ? null : schoolAddress;
    // For departureHour, convert empty values to null to use the default logic later.
    const finalDepartureHour = isEmpty(departureHour) ? null : departureHour;
    // For travelMode, assign the default "DRIVING" if not provided.
    const finalTravelMode = isEmpty(travelMode) ? "DRIVING" : travelMode;

    // Validate the processed inputs
    validateInputs(am_pm, homeAddress, finalWorkAddress, finalHasSchoolStop, finalSchoolAddress, finalDepartureHour, finalTravelMode);
  
    // Normalize inputs
    const normalizedAmPm = am_pm.toUpperCase();
    const normalizedTravelMode = finalTravelMode.toUpperCase();
  
    // Determine route details
    const routeInfo = determineRoute(normalizedAmPm, homeAddress, finalWorkAddress, finalHasSchoolStop, finalSchoolAddress);
  
    // Set departure time
    const departTime = createDepartureTime(normalizedAmPm, finalDepartureHour);
  
    // Get directions
    const directions = getDirections(routeInfo, departTime, normalizedTravelMode);
  
    // Calculate total travel time
    return calculateTotalTime(directions);
  
  } catch (error) {
    throw new Error(`COMMUTE_TIME Error: ${error.message || error}`);
  }
}

/**
* Checks if a value is empty
*/
function isEmpty(value) {
  return (value === "#NAME?" || value === "null" || value === null || value === undefined || value === "");
}

/**
* Validates all input parameters
*/
function validateInputs(am_pm, homeAddress, workAddress, hasSchoolStop, schoolAddress, departureHour, travelMode) {
  // Validate am_pm
  if (!am_pm || (typeof am_pm !== 'string') || !["AM", "PM"].includes(am_pm.toUpperCase())) {
    throw new Error("The am_pm parameter must be 'AM' or 'PM'");
  }
  // Validate home address
  if (!homeAddress || typeof homeAddress !== 'string' || homeAddress.trim() === '') {
    throw new Error("Home address is required and must be a non-empty string");
  }
  // Validate destination addresses
  if (!workAddress && !schoolAddress) {
    throw new Error("At least one destination (work or school address) must be provided");
  }
  // Validate school stop logic
  if (hasSchoolStop === true && (!schoolAddress || schoolAddress.trim() === '')) {
    throw new Error("School address is required when hasSchoolStop is true");
  }
  // Validate departure hour
  if (departureHour !== null && (typeof departureHour !== 'number' || isNaN(departureHour) || departureHour < 0 || departureHour > 23)) {
    throw new Error("Departure hour must be a number between 0 and 23");
  }
  // Validate travel mode
  const validModes = ["DRIVING", "WALKING", "BICYCLING", "TRANSIT"];
  if (typeof travelMode !== 'string' || !validModes.includes(travelMode.toUpperCase())) {
    throw new Error(`Travel mode must be one of: ${validModes.join(", ")}`);
  }
}

/**
* Determines the optimal route based on commute direction and stops
*/
function determineRoute(am_pm, homeAddress, workAddress, hasSchoolStop, schoolAddress) {
  let origin, destination, waypoints = [];
  if (am_pm === "AM") {
    // Morning: Home → (School) → Work
    origin = homeAddress;
    destination = workAddress || schoolAddress;
  
    if (hasSchoolStop && workAddress && schoolAddress) {
      waypoints.push(schoolAddress);
    }
  } else {
    // Evening: Work → (School) → Home
    origin = workAddress || schoolAddress;
    destination = homeAddress;
  
    if (hasSchoolStop && workAddress && schoolAddress) {
      waypoints.push(schoolAddress);
    }
  }
  return { origin, destination, waypoints };
}

/**
* Creates a departure time for the next weekday
*/
function createDepartureTime(am_pm, departureHour) {
  const departTime = new Date();
  // Set time components
  const defaultHour = am_pm === "AM" ? 8 : 17;
  departTime.setHours(departureHour !== null ? departureHour : defaultHour);
  departTime.setMinutes(0);
  departTime.setSeconds(0);
  departTime.setMilliseconds(0);
  // Set to next weekday (Monday-Friday)
  const today = new Date();
  const daysUntilNextWeekday = getNextWeekdayOffset(today);
  departTime.setDate(today.getDate() + daysUntilNextWeekday);
  return departTime;
}

/**
* Calculates days until the next weekday
*/
function getNextWeekdayOffset(date) {
  const dayOfWeek = date.getDay(); // 0 = Sunday, 6 = Saturday
  if (dayOfWeek === 0) return 1; // Sunday → Monday
  if (dayOfWeek === 6) return 2; // Saturday → Monday
  if (dayOfWeek < 5) return 1;   // Mon-Thu → Next day
  return 3; // Friday → Monday
}

/**
* Gets directions using Google Maps API
*/
function getDirections(routeInfo, departTime, travelMode) {
  const { origin, destination, waypoints } = routeInfo;
  let directions = Maps.newDirectionFinder()
    .setOrigin(origin)
    .setDestination(destination)
    .setDepart(departTime)
    .setMode(Maps.DirectionFinder.Mode[travelMode])
    .setOptimizeWaypoints(true); // Optimize waypoint order
  // Add waypoints
  waypoints.forEach(waypoint => {
    directions.addWaypoint(waypoint);
  });
  const result = directions.getDirections();
  if (!result.routes || result.routes.length === 0) {
    throw new Error("No routes found for the specified addresses");
  }
  return result;
}

/**
* Calculates total travel time from directions
*/
function calculateTotalTime(directions) {
  const legs = directions.routes[0].legs;
  if (!legs || legs.length === 0) {
    throw new Error("No route legs found in directions");
  }
  const totalSeconds = legs.reduce((total, leg) => {
    return total + (leg.duration?.value || 0);
  }, 0);
  return Math.round(totalSeconds / 60);
}

Understanding the COMMUTE_TIME Function Parameters

Before using the function in our spreadsheet, let's break down the different parameters it accepts. This will help you customize your commute calculations for various scenarios.

The function accepts several parameters:

=COMMUTE_TIME(am_pm, homeAddress, workAddress, hasSchoolStop, schoolAddress, departureHour, travelMode)

Here’s a detailed explanation of each parameter:

• am_pm (Required): This text parameter specifies whether you are calculating the morning or evening commute.

• Accepted values: "AM" or "PM".

• How it works: If you enter "AM", the function calculates the route from your homeAddress to your workAddress (potentially via the schoolAddress). If you enter "PM", it calculates the return trip.

• homeAddress (Required): The starting point for your "AM" commute and the final destination for your "PM" commute.

• Accepted values: A valid street address as a text string (e.g., "1600 Amphitheatre Parkway, Mountain View, CA").

• workAddress (Optional): Your primary workplace address.

• Accepted values: A valid street address as a text string.

• Note: This is optional only if a schoolAddress is provided, as the function needs at least one destination.

• hasSchoolStop (Optional): A true/false value that tells the function whether to include a stop at a school.

• Accepted values: TRUE or FALSE.

• Default value: FALSE.

• How it works: If set to TRUE, you must also provide a schoolAddress. The function will then include this stop as a waypoint in its calculation.

• schoolAddress (Optional): The address of the school.

• Accepted values: A valid street address as a text string.

• Note: This parameter is only used if hasSchoolStop is set to TRUE.

• departureHour (Optional): Allows you to specify the hour of departure for the commute calculation.

• Accepted values: A number between 0 (midnight) and 23 (11 PM).

• Default value: 8 (for 8:00 AM) if am_pm is "AM", and 17 (for 5:00 PM) if am_pm is "PM".

• How it works: The function calculates the commute for the next upcoming weekday based on this hour.

• travelMode (Optional): The mode of transportation for the commute.

• Accepted values: "DRIVING", "WALKING", "BICYCLING", or "TRANSIT".

• Default value: "DRIVING".

Step 3 — Test the function in Google Sheets

In a cell, try using the COMMUTE_TIME custom function.

You can test with different scenarios typical for apartment hunting:

For evaluating a potential apartment:

=COMMUTE_TIME("AM", "{{Potential Apartment Address}}", "{{Your Work Address}}")

For families with school-age children:

=COMMUTE_TIME("AM", "{{Potential House Address}}", "{{Your Work Address}}", TRUE, "{{School Address}}")

For checking public transit options:

=COMMUTE_TIME("AM", "{{Potential Apartment Address}}", "{{Your Work Address}}", false, "", 8, "TRANSIT")

Step 4 — Build Your Apartment Comparison Dashboard

Now that you have the COMMUTE_TIME function, you can use it to build a spreadsheet to compare apartments. The screenshot below shows the spreadsheet that I used while apartment hunting a few years ago, with addresses and other information obscured for privacy reasons. I used conditional formatting to help me quickly visualize tradeoffs between features, total cost and commute time.

Pro Tip

Create a separate sheet to store constants like addresses and fuel cost.

Also, consider creating named ranges for these values.

This makes your formulas cleaner and easier to update. For example:

=COMMUTE_TIME("AM", B3, momWorkAddress, false)

Conclusion

You've successfully created a commute time calculator that brings Google Maps functionality directly into your apartment hunting spreadsheets. This custom function handles routing scenarios and provides rough time estimates.

The function's flexibility makes it perfect for comparing multiple properties side-by-side. You can now calculate commute times for dozens of potential homes and even factor in school locations for families, to make the best housing decision.

Thank you for following along with this tutorial. I hope this custom function saves you time and helps you find the perfect home with a commute that fits your lifestyle.

Future work

Here are some enhancements you can try to expand your apartment hunting spreadsheet:

• Add traffic-aware calculations: Google's Maps API offers paid traffic data that can significantly improve commute time accuracy during peak hours. Consider upgrading to a paid Maps API plan to access real-time traffic information for more precise estimates during your typical work hours.

• Create a comprehensive property comparison dashboard: Build a spreadsheet that includes rent/mortgage costs, square footage, commute times, and proximity to amenities like grocery stores and gyms. You could even add a weighted scoring system to rank properties.

• Add multiple workplace scenarios: If you or your partner have multiple work locations or might change jobs, create columns that calculate commute times to different potential workplaces from each property.

• Include weekend and leisure commutes: Modify the function to also calculate travel times to frequently visited places like family, friends, or recreational areas to get a complete picture of how a location fits your lifestyle.