Google AppScript: Get Latitude Longitude & Distance between Places/City

This article shows how to get latitude and longitude of a particular place or city. We read the city/place name from a Google Spreadsheet, then get the latitude and longitude of that place using Google AppScript’s “Maps” class and then save the lat long in the spreadsheet.

We will also be reading two places name from a Google Spreadsheet and then get the distance between those places in Kilometer using AppScript’s Maps class. Then, we save the distance in the spreadsheet.

Get Latitude Longitude of any place

In the code below, you have to run the saveLatLong() function. In this function, a spreadsheet is opened by its id and then the data in it is read.

To get the spreadsheed id:

– Open Google Spreadsheet
– See the URL in your browser’s address bar
– The URL will be something like this: https://docs.google.com/spreadsheets/d/9tXNUNpM9tXNUNp0BydbYIIYLMxxxxxxxxxx/edit#gid=0
– So, in this case, the spreadsheet id is “9tXNUNpM9tXNUNp0BydbYIIYLMxxxxxxxxxx”

The data in the spreadsheet is in the following format:

• In the saveLatLong() function, we read the city name from 2nd column.
• Then we call the getLatLong(city) function which returns the formatted address, latitude and longitude of any particular city or place.
• Then, we save the address, latitude and longitude in 4th, 5th and 6th column of the SpreadSheet respectively.

/**
 * Maps Overview
 * https://developers.google.com/apps-script/reference/maps/
 *
 * Geocoder Class
 * https://developers.google.com/apps-script/reference/maps/geocoder
 */
function getLatLong(city) {
  //city = 'Kathmandu';
  var response = Maps.newGeocoder().geocode(city);
  var result = response.results[0];
  
  /*Logger.log('%s: %s, %s', 
             result.formatted_address, 
             result.geometry.location.lat, 
             result.geometry.location.lng);*/
    
  return [result.formatted_address, 
          result.geometry.location.lat, 
          result.geometry.location.lng];
}

function saveLatLong() {
  var sheetId = '9tXNUNpM9tXNUNp0BydbYIIYLMxxxxxxxxxx'; // ID of spreadsheet
  var ss = SpreadsheetApp.openById(sheetId);
  var sheet = ss.getActiveSheet(); 
  //var sheet = ss.getSheetByName('Sheet 2');
  var range = sheet.getDataRange();
  var values = range.getValues();
  
  /**
   * we start from i=1
   * i=0 is first row, we skip the first row as it contains heading info only
   */
  for (i=1; i < values.length; i++) {    
    var city = values[i][1]; // [i][1] means we read 2nd column data
    var latlong = getLatLong(city);
    
    var address = latlong[0];
    var lat = latlong[1];
    var long = latlong[2];
    
    sheet.getRange('D'+(i+1)).setValue(address); // D = 4th column
    sheet.getRange('E'+(i+1)).setValue(lat); // E = 5th column
    sheet.getRange('F'+(i+1)).setValue(long); // F = 6th column
  }
}

After we run the saveLatLong() function, we will get the following output in our SpreadSheet.

Get distance between any two places

In the code below, you have to run the saveDistance() function. In this function, a spreadsheet is opened by its id and then the data in it is read.

The data in the spreadsheet is in the following format:

• In the saveDistance() function, we read the first city/place name from 1st column and second city/place name from 2nd column.
• Then we call the getDistance(city1, city2) function which returns the distance between (in kilometer) the two cities/places.
• Then, we save distance value in the 3rd column of the Spreadsheet.

/**
 * Maps Overview
 * https://developers.google.com/apps-script/reference/maps/
 *
 * Geocoder Class
 * https://developers.google.com/apps-script/reference/maps/geocoder
 */
function getDistance(city1, city2) { 
  //var city1 = 'Kathmandu';
  //var city2 = 'Bhaktapur';
  
  var directions = Maps.newDirectionFinder()
    .setOrigin(city1).setDestination(city2)
    .setMode(Maps.DirectionFinder.Mode.DRIVING)
    .getDirections();
 
  var d = directions.routes[0].legs[0].distance.text; 
  // Logger.log(d); // sample output: 15 km
  var value = d.split(" ")[0]; 
  // Logger.log(value); // sample output: 15
  return value;
}

function saveDistance() {
  var sheetId = '9tXNUNpM9tXNUNp0BydbYIIYLMxxxxxxxxxx'; // ID of spreadsheet
  var ss = SpreadsheetApp.openById(sheetId);
  var sheet = ss.getActiveSheet();
  var range = sheet.getDataRange();
  var values = range.getValues();
  
  for (i=1; i < values.length; i++) {
    var city1 = values[i][0]; // first column data
    var city2 = values[i][1]; // second column data
    
    var distance = getDistance(city1, city2);    
    var distanceRange = sheet.getRange('C'+(i+1)); // save data in 3rd column
    distanceRange.setValue(distance);
  }  
}

After we run the saveDistance() function, we will get the following output in our Spreadsheet.

Hope this helps. Thanks.