Geocoding & Reverse Geocoding Functions In Google Sheets

Introduction

In the previous post, we learned the basics of developing and using a custom function in Google Sheets. It is time now to switch to something more advanced compared to our first example. We have seen in the past how to use Google’s geocoding services from VBA. Here we will see how to call these services using Google Apps Script (GAS). The main advantage when developing a GAS function is that, unlike VBA, you don’t need to explicitly get an API key. The overall approach is slightly different than in the case of VBA since there is a Geocoder class available in GAS.

GAS code 

Below you will find the code for five different GAS functions:

• getCoordinates
• getCoordinatesArray
• getLatitude
• getLongitude
• getAddress

I think that the function names are self-explanatory. It should be noted that the getCoordinatesArray is identical to getCoordinates, but it returns the latitude and longitude as an array, not as a single string. In addition, the getAddress function is performing actually reverse geocoding. The idea behind these functions is similar to VBA, so after reading the request status, then if it is “OK”, the corresponding node is read and returned.

/* Start of the GAS code. */
 
/*
  -------------------------------------------------------------------------------
  The next set of functions are used to perform geocoding and reverse geocoding.
  
  Written By:    Christos Samaras
  Date:          18/08/2018
  E-mail:        [email protected]
  Site:          https://myengineeringworld.net
  -------------------------------------------------------------------------------
*/
  
/**
 * This function returns the latitude and longitude of a given address using the Geocoder class. 
 *
 * @param {A2} address A cell that contains an address.
 * @return The latitude and the longitude of the given address.
 * @customfunction
 */
function getCoordinates(address)
{  
  // Initialize the geocoder object.
  var geocoder = Maps.newGeocoder().geocode(address);
  
  // Initialize the latitue/longitude variables.
  var lat = 0;
  var long = 0
  
  // Check if the response returned without a problem.
  if (geocoder.status == 'OK') 
  {
      // Retrieve the latitue/longitude information.
      lat = geocoder["results"][0]["geometry"]["location"]["lat"];
      long = geocoder["results"][0]["geometry"]["location"]["lng"];
  }
  
  // Return the latitue/longitude information as string.  
  return lat + ", " + long;
}
  
// --------------------------------------------------------------------------------------------------------------
  
/**
 * This function returns the latitude and longitude of a given address as an array using the Geocoder class. 
 *
 * @param {A2} address A cell that contains an address.
 * @return The latitude and the longitude of the given address.
 * @customfunction
 */
function getCoordinatesArray(address)
{  
  // Initialize the geocoder object.
  var geocoder = Maps.newGeocoder().geocode(address);
  
  // Initialize the latitue/longitude variables.
  var lat = 0;
  var long = 0
  
  // Check if the response returned without a problem.
  if (geocoder.status == 'OK') 
  {
      // Retrieve the latitue/longitude information.
      lat = geocoder["results"][0]["geometry"]["location"]["lat"];
      long = geocoder["results"][0]["geometry"]["location"]["lng"];
  }
  
  // Return the latitue/longitude information as an array.  
  return [lat, long];
}
  
// --------------------------------------------------------------------------------------------------------------
  
/**
 * This function returns the latitude of the given address using the Geocoder class.
 *
 * @param {A2} address A cell that contains an address.
 * @return The latitude of the given address.
 * @customfunction
 */
function getLatitude(address)
{        
  // Initialize the geocoder object.
  var geocoder = Maps.newGeocoder().geocode(address);
  
  // Initialize the latitude variable.
  var lat = 0;
  
  // Get the latitude if the response returned without a problem.
  if (geocoder.status == 'OK')   
      lat = geocoder["results"][0]["geometry"]["location"]["lat"];      
  
  // Return the latitude as double.
  return lat;
}
  
// --------------------------------------------------------------------------------------------------------------
  
/**
 * This function returns the longitude of the given address using the Geocoder class.
 *
 * @param {A2} address A cell that contains an address.
 * @return The longitude of the given address.
 * @customfunction
 */
function getLongitude(address)
{    
  // Initialize the geocoder object.
  var geocoder = Maps.newGeocoder().geocode(address);
  
  // Initialize the longitude variable.
  var long = 0;
  
  // Get the latitude if the response returned without a problem.
  if (geocoder.status == 'OK')   
      long = geocoder["results"][0]["geometry"]["location"]["lng"];
  
  // Return the longitude as double.  
  return long;
}
  
// --------------------------------------------------------------------------------------------------------------
  
/**
 * This function returns the address from a given pair of latitude and longitude using the Geocoder class.
 * In other words, it performs reverse geocoding.
 *
 * @param {A2} lat A cell that contains a latitude value between -90 and +90 degrees.
 * @param {B2} long A cell that contains a longitude value between -180 and +180 degrees.
 * @return The address from a given pair of latitude and longitude.
 * @customfunction
 */
function getAddress(lat, long)
{
  
  // Checking the input variables:
  // The valid range of latitude in degrees is -90 and +90 for the Southern and Northern hemisphere respectively.   
  if(lat < -90 || lat > 90)
    return "Invalid Latitude";
 
  // Longitude is in the range -180 and +180 specifying coordinates West and East of the Prime Meridian, respectively.
  if(long < -180 || long > 180)
    return "Invalid Longitude";
    
  var response = Maps.newGeocoder().reverseGeocode(lat, long);
  var address = "";
  
  if (response.status == 'OK')
    address = response["results"][0]["formatted_address"];
  
  return address;
}
 
/* End of the GAS code. */

All functions containing descriptions following the approach that was analyzed here. The getCoordinatesArray function, though, needs “special treatment” to get its results. Here is how you can call it:
a. Select two adjacent cells.
b. In one of the two cells, enter the formula:
=TRANSPOSE(ARRAYFORMULA(getCoordinatesArray(A5)))
Where A5 is the cell containing the address you need to geocode.

NOTE: In the case of multiple results (for example two cities sharing the same name), the above functions return the first occurrence, so be careful with your input.

Online geocoding and reverse geocoding examples 

You can find a few examples online in this Google Sheets file.

Note that the file is on the View only mode. To enable editing, go to File → Make a copy. Once the file is copied on your Drive, you can use whenever you want.

Read also

How To Create & Use A Custom Function In Google Sheets

Page last updated: 17/06/2019