Zip Codes to City, Region & Geocode in Google Sheets

ZIP codes are a very useful and common way to gather location data. But zip codes alone are often less useful than the city, state, latitude and longitude that correspond to the zip code. City and state/region, allow you to group the data with a pivot table while

City and state/region, allow you to group and aggregate numerical data with pivot tables, while latitude and longitude offer sensible ways to determine areas and distance.

Here is a simple custom function that provides easy access to the zippopotom.us free ZIP code converter API.

ZIP Code Custom Function

ZIPLOC( zip code [, country abbrv] )

To translate ZIP codes to geographic info, just supply the ZIPLOC function with a ZIP code. The corresponding two-letter country code is optional but necessary for countries other than the US.

For example, if you want to get geographic data about the 1000 ZIP code of Bulgaria, the function would look like this:

ZIPLOC( 1000, BG)

The function will return the following horizontal array of data:

Country, Region, City, Latitude, Longitude

Bulgaria, София (столица) / Sofija (stolica), София / Sofija, 42.6833, 23.3167

Google Apps Script Code

The code makes use of the UrlFetchApp function to make a call to the zippopotam.us RESTful APi and parses the requested JSON data and converts it to an array. To see what the JSON data looks like, check out this URL:

http://api.zippopotam.us/bg/1000

To use the code, just copy and paste it into the Google Apps Script Code editor and click Save. The ZIPLOC function will be available on the corresponding Google Spreadsheet.

function ziploc(zip, country) {
  
  // default country is US
  country = country === undefined ? 'US' : country
  
  // to learn more about the zippopotam.us API, visit
  var response = UrlFetchApp.fetch("http://api.zippopotam.us/" + country + "/" + zip, {muteHttpExceptions: true});
  
  if (String(response.getResponseCode())[0] === '4'){
    return "Zip code not found"
  }
  
  var z = JSON.parse(response.getContentText());
  
  return [ [z["country"], z.places[0]["state"], z.places[0]["place name"], z.places[0].latitude, z.places[0].longitude] ]
  
}