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:

From	To	Distance (km)	Address	Latitude	Longitude
Kathmandu	Bhaktapur
Kathmandu	Janakpur
Kathmandu	Nagarkot
Kathmandu	Dhulikhel
Kathmandu	Biratnagar
Kathmandu	Dhangadi

• 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.

From	To	Distance (km)	Address	Latitude	Longitude
Kathmandu	Bhaktapur		Bhaktapur, Nepal	27.6710221	85.4298197
Kathmandu	Janakpur		Janakpur, Nepal	26.7271466	85.9406745
Kathmandu	Nagarkot		Nagarkot, 44812, Nepal	27.7173579	85.5045711
Kathmandu	Dhulikhel		Dhulikhel, Nepal	27.6253491	85.5560635
Kathmandu	Biratnagar		Biratnagar 56613, Nepal	26.4524746	87.271781
Kathmandu	Dhangadi		Dhangadhi 10900, Nepal	28.685244	80.621591

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:

From	To	Distance (km)	Address	Latitude	Longitude
Kathmandu	Bhaktapur
Kathmandu	Janakpur
Kathmandu	Nagarkot
Kathmandu	Dhulikhel
Kathmandu	Biratnagar
Kathmandu	Dhangadi

• 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.

From	To	Distance (km)	Address	Latitude	Longitude
Kathmandu	Bhaktapur	16
Kathmandu	Janakpur	223
Kathmandu	Nagarkot	27.2
Kathmandu	Dhulikhel	31.1
Kathmandu	Biratnagar	375
Kathmandu	Dhangadi	671

Hope this helps. Thanks.