Build a location-intelligence toolkit

Northwind’s operations team keeps answering the same location questions by hand: how far is a venue from the office, what cafes are near a client’s hotel, what is the postcode for a site they only have coordinates for. Each answer means a detour to Google Maps, and the results never make it back into the spreadsheet where the planning actually happens.

This toolkit collects those lookups into a small set of reusable helper functions. They wrap the built-in Maps service and the Google Places API so that distance, nearby-place, and reverse-geocoding queries can be called from any other script — or wired into custom spreadsheet functions — instead of being done one browser tab at a time.

What you’ll need

• A Google Cloud project with the Places API enabled, and an API key for it.
• That key stored in Script Properties as PLACES_KEY — see Store API keys and secrets securely.
• The built-in Maps service, which is available to every Apps Script project with no extra setup. distanceKm uses it and needs no key.
• A spreadsheet only if you want to call these helpers as custom functions; the helpers themselves work standalone.

The helpers

// Places API endpoint for "what's near this point" searches.
const NEARBY_API = 'https://maps.googleapis.com/maps/api/place/nearbysearch/json';

// Default search radius, in metres, for nearbyPlaces.
const NEARBY_RADIUS_M = 500;

/**
 * Returns the driving distance in kilometres between two locations.
 * Each location may be an address string or a "lat,lng" pair.
 * Returns null if no route can be found.
 */
function distanceKm(from, to) {
  // Ask the built-in Maps service for directions between the two points.
  const result = Maps.newDirectionFinder()
    .setOrigin(from)
    .setDestination(to)
    .getDirections();

  // No route means no answer — bail out cleanly.
  if (!result.routes.length) return null;

  // distance.value is in metres; convert to kilometres.
  return result.routes[0].legs[0].distance.value / 1000;
}

/**
 * Returns the names of places of a given type within NEARBY_RADIUS_M
 * of a coordinate. Defaults to cafes.
 */
function nearbyPlaces(lat, lng, type = 'cafe') {
  const key = PropertiesService.getScriptProperties().getProperty('PLACES_KEY');

  // Build the Places "nearby search" request URL.
  const url = NEARBY_API +
    '?location=' + lat + ',' + lng +
    '&radius=' + NEARBY_RADIUS_M +
    '&type=' + type +
    '&key=' + key;

  const res = UrlFetchApp.fetch(url, { muteHttpExceptions: true });
  const data = JSON.parse(res.getContentText());

  // OK or ZERO_RESULTS are both fine; anything else is an error worth logging.
  if (data.status !== 'OK' && data.status !== 'ZERO_RESULTS') {
    Logger.log('Places API returned: ' + data.status);
    return [];
  }

  // Pull just the place names out of the result list.
  return (data.results || []).map((place) => place.name);
}

/**
 * Reverse-geocodes a coordinate into a human-readable address using
 * the built-in Maps geocoder. Returns null if nothing matches.
 */
function addressForPoint(lat, lng) {
  const result = Maps.newGeocoder().reverseGeocode(lat, lng);
  if (!result.results.length) return null;
  return result.results[0].formatted_address;
}

How it works

1. distanceKm uses the built-in Maps direction finder. It sets an origin and destination, asks for directions, and reads distance.value — metres — off the first leg of the first route, converting to kilometres.
2. If the direction finder returns no routes (an unreachable or unrecognised location), distanceKm returns null rather than throwing.
3. nearbyPlaces reads the PLACES_KEY from Script Properties, builds a Places “nearby search” URL around the given coordinate, and fetches it.
4. It checks the API’s status field: OK and ZERO_RESULTS are both expected, anything else is logged and an empty list is returned.
5. It maps the result objects down to just their name fields, so callers get a plain list of place names.
6. addressForPoint wraps the Maps geocoder’s reverse-geocode call to turn a raw coordinate back into a formatted address, which is handy when a data feed gives you coordinates but no postal address.

Example run

Calling the helpers from a scratch function:

function tryToolkit() {
  Logger.log(distanceKm('Northwind Studio, London', 'Heathrow Airport'));
  // -> 28.6

  Logger.log(nearbyPlaces(51.5074, -0.1278, 'restaurant'));
  // -> ['The Ivy', 'Dishoom', 'Cafe Murano', ...]

  Logger.log(addressForPoint(51.5007, -0.1246));
  // -> 'Westminster, London SW1A 0AA, UK'
}

Wired into a sheet as custom functions, the same helpers fill cells directly — =distanceKm(A2, B2) next to a column of venues gives an instant distance column for the whole list.

Run it

These are library helpers, so there is nothing to schedule — call them from other scripts or expose them as custom functions:

1. Paste the helpers into a project, then add an @customfunction JSDoc tag above any helper you want to call from cells.
2. In the Apps Script editor, run tryToolkit once to approve the authorisation prompt and confirm the API key works.
3. Use the helpers from your sheets or from other automations as needed.

Watch out for

• distanceKm returns driving distance, not straight-line distance. For a walking or transit figure, set the travel mode on the direction finder with .setMode(Maps.DirectionFinder.Mode.WALKING).
• The Places API is billed per request once you pass the free monthly credit. Calling nearbyPlaces from hundreds of cells at once can run up a real bill — cache results or limit how often the sheet recalculates.
• Custom functions cannot read Script Properties in every context and run under a tight time limit. If nearbyPlaces is slow as a custom function, fetch the data with a normal function on a trigger instead and have the cell read the stored result.
• nearbyPlaces returns at most one page of results (around 20 places). Paging through more requires following the next_page_token field, which is not done here.
• Restrict the PLACES_KEY in the Cloud console to the Places API only. An unrestricted key that leaks can be used against any Google Maps service on your billing account.