Get Latitude and Longitude using Google Apps script

In this we will see how we can get latitude and longitude of a location using Google Apps Script.

I was working on a use case where i was trying to link Charts created in Google sheet with Google slide such that the charts gets updated automatically when the data in the sheet changed.

But with Geo chart i found that when the underlying data gets changed, the charts in google slides becomes blank when updated. So i figured it out that this process becomes more smooth when the charts are created with latitude and longitude instead of the location name directly as seen in below image (column 2).

Below is the data used :

 

 

In below code, we are looping over the data (as shown above), from each row picking the location name (in column 2) and then using the MAPS class in apps script, getting the latitude and longitude and writing to the column 3 and column 4 respectively.

Code to bring Latitude and Longitude using Google Apps script : 

function Lat_Long() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  //Replace "Data" below with your sheet name!
  var sheet = ss.getSheetByName("Data")
  //getting range of the data
  var rangeData = sheet.getDataRange();
  //getting the last column
  var lastColumn = rangeData.getLastColumn();
  Logger.log(lastColumn)
  //getting the last row
  var lastRow = rangeData.getLastRow();
  //Getting last row in logger to verify
  Logger.log(lastRow)
  //Looping over the data
  for (var i=2;i<lastRow+1; i++){
    //Below in getRange i means all rows and 2 means the 2nd column 
    //where we have the location name e.g US (refer the image)
    var someCell= sheet.getRange(i,2).getValues()
    Logger.log(someCell)
    //Below calling the built in Maps class and its method (.newGeocoder()) to get the 
    //geo info. 
    var response = Maps.newGeocoder().geocode(someCell);
    for (var j = 0; j < response.results.length; j++){
      var result = response.results[j];
      //Below will get the latitude info of the location
      var Lat = result.geometry.location.lat
      //Below will get the longitude info of the location
      var Long = result.geometry.location.lng
      Logger.log(Lat)
      Logger.log(Long)
      //We are updating the latitude in column 3 so in range below we have given 3
      sheet.getRange(i,3).setValue(Lat)
      //We are updating the latitude in column 4 so in range below we have given 4
      sheet.getRange(i,4).setValue(Long)
  }
}
}

If you want to know more about the Maps class and also want to try other functionalities available in the class, you can refer the original documentation from here. Also if you want to check other Google Apps Script which I shared in my previous posts, please check here.

 

If you have any questions or if you need any help please get in touch with me using the comment section below.