Supercharge Your Google Sheets with Google Maps Integration
Ever needed to quickly get coordinates, distances, or travel times in your spreadsheets? I've created a simple Google Apps Script that interfaces with Google Maps API to do exactly that.
Three Powerful Functions
GET_COORDINATES: Type =GET_COORDINATES("123 Main St, Anytown") to get the latitude and longitude as a comma-separated value.
GET_DISTANCE: Calculate driving distance with =GET_DISTANCE("origin address", "destination address"). Returns the distance in miles.
GET_TRAVEL_TIME: Find out how long a trip takes with =GET_TRAVEL_TIME("origin address", "destination address"). Returns minutes.
Both distance and travel time functions accept an optional third parameter for travel mode: "driving" (default), "walking", "bicycling", or "transit".
How It Works
Behind the scenes, the script uses Google's Maps Service to geocode addresses and calculate routes. The geocode converts addresses into coordinates, while the direction finder calculates optimal routes, distances, and travel times.
To implement this in your spreadsheet, simply copy the script into the Apps Script editor (Extensions > Apps Script), save, and start using the functions right away!
Working with Large Data Sets
For large data sets, you may exceed Google Maps API limits, which can result in errors or cost implications. For projects that only require estimating distances (like creating mileage radius bands), we developed an alternative formula that doesn't use the Maps API:
=6371*ACOS(COS(RADIANS(90-A2))*COS(RADIANS(90-C$2))+SIN(RADIANS(90-A2))*SIN(RADIANS(90-C$2))*COS(RADIANS(B2-D$2)))*0.621371
This formula calculates the "as-the-crow-flies" distance between coordinates. In this example, it measures the distance between the coordinates in cells A2 (latitude) and B2 (longitude) relative to a fixed point at C2 (latitude) and D2 (longitude). The result is in miles (0.621371 converts kilometers to miles).
While not accounting for actual road routes, this approach is perfect for quickly generating distance calculations across thousands of data points without API constraints.
Full Script
/**
* Google Sheets script for Google Maps integration
* This script provides three custom functions:
* 1. GET_COORDINATES - Returns latitude and longitude for an address
* 2. GET_DISTANCE - Returns distance between two addresses in miles
* 3. GET_TRAVEL_TIME - Returns travel time between two addresses in minutes
*/
function GET_COORDINATES(address) {
if (!address) {
return "Error: No address provided";
}
try {
// Use the Maps service to get the coordinates
const geocoder = Maps.newGeocoder();
const response = geocoder.geocode(address);
// Check if we have a valid response
if (response.status !== "OK" || !response.results[0]) {
return "Error: Address not found";
}
// Extract coordinates
const location = response.results[0].geometry.location;
return location.lat + "," + location.lng;
} catch (error) {
return "Error: " + error.toString();
}
}
function GET_DISTANCE(origin, destination, mode) {
if (!origin || !destination) {
return "Error: Both origin and destination addresses required";
}
// Default mode is driving
mode = mode || "driving";
try {
// Use the Maps service to get directions
const directions = Maps.newDirectionFinder()
.setOrigin(origin)
.setDestination(destination)
.setMode(mode)
.getDirections();
// Check if we have a valid response
if (!directions.routes || !directions.routes[0]) {
return "Error: No route found";
}
// Extract distance in meters
const distanceMeters = directions.routes[0].legs[0].distance.value;
// Convert to miles (1 meter = 0.000621371 miles)
const distanceMiles = distanceMeters * 0.000621371;
// Round to 2 decimal places
return Math.round(distanceMiles * 100) / 100;
} catch (error) {
return "Error: " + error.toString();
}
}
function GET_TRAVEL_TIME(origin, destination, mode) {
if (!origin || !destination) {
return "Error: Both origin and destination addresses required";
}
// Default mode is driving
mode = mode || "driving";
try {
// Use the Maps service to get directions
const directions = Maps.newDirectionFinder()
.setOrigin(origin)
.setDestination(destination)
.setMode(mode)
.getDirections();
// Check if we have a valid response
if (!directions.routes || !directions.routes[0]) {
return "Error: No route found";
}
// Extract duration in seconds
const durationSeconds = directions.routes[0].legs[0].duration.value;
// Convert to minutes and round to nearest minute
return Math.round(durationSeconds / 60);
} catch (error) {
return "Error: " + error.toString();
}
}