CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgeocodinggoogle-geocoder
user8642569
user8642569

Reputation: 11

getting lat/long from address with geocode in google apps script

I've got this working code that gets the lat/long combo from an address.

function testgeocode(){ 
    var responses = SpreadsheetApp.openById('###').getSheetByName('Form Responses');
    var range = responses.getRange('AD2:AD');
    var addresses = range.getValues();

    var row = range.getRow();
    var column = range.getColumn();

    var destination = new Array();
    destination[0] = 31; // column + 1;
    destination[1] = 32; // column + 2;

    var geocoder = Maps.newGeocoder();
    var count = range.getHeight();
  
    for(i in addresses) {
        var location = geocoder.geocode(addresses[i]).results[0].geometry.location;
        responses.getRange(row, destination[0]).setValue(location.lat);
        responses.getRange(row++, destination[1]).setValue(location.lng);
        Utilities.sleep(200);
    }
}

I'm having difficulty modifying it so that the lat/long combo is outputted into one column with the format 'lat,long'.

Upvotes: 0

Views: 2850

Answers (4)

ganoro
ganoro

Reputation: 518

This is another snippet the does the work:

function getLatLng(address) {
  // Geocode the address to get the location
  var geocoder = Maps.newGeocoder().setRegion('us');
  var response = geocoder.geocode(address);
  
  // Check if the geocode operation was successful
  if (response.status === 'OK') {
    var result = response.results[0];
    var lat = result.geometry.location.lat;
    var lng = result.geometry.location.lng;
    
    // Log the latitude and longitude for debugging
    Logger.log('Latitude: ' + lat + ', Longitude: ' + lng);
    
    return `${lat},${lng}`;
  } else {
    Logger.log('Geocode was not successful for the following reason: ' + response.status);
    return null;
  }
}

Then you can use it in your sheets like this =getLatLng(A2)

Upvotes: 0

vstepaniuk
vstepaniuk

Reputation: 868

Another way is to create a custom function:

function a2c(address){
  var geo = Maps.newGeocoder().geocode(address);
  var loc = geo.results[0].geometry.location;
  return (loc.lat + ',' + loc.lng);
}

Upvotes: 0

Marios
Marios

Reputation: 27390

Explanation / Issues:

  • First of all, your code is quite inefficient. The reason for that is because you are using getRange and setValue inside a for loop but also two times per iteration. As it is explained in the best practices, the proper way to achieve your goal is to store the values into an empty array and then use setValues outside the for loop to set them to your sheet.

  • The other modification is already mentioned in the other answer. You want to concatenate location.lat, location.lng separated by a comma.

Solution:

function testgeocode(){ 
    var responses = SpreadsheetApp.openById('###').getSheetByName('Form Responses');
    var range = responses.getRange('AD2:AD');
    var addresses = range.getValues();

    var row = range.getRow();
    var column = range.getColumn();

    var destination = new Array();
    destination[0] = 31; // column + 1;
    destination[1] = 32; // column + 2;

    var geocoder = Maps.newGeocoder();
    var count = range.getHeight();
    
    var data = [];
  
    for(i in addresses) {
        var location = geocoder.geocode(addresses[i]).results[0].geometry.location;
        data.push([location.lat + ',' + location.lng]) // new code
        Utilities.sleep(200);
    }
    
    responses.getRange(2,destination[0],data.length,1).setValues(data); // new code
    
}

Upvotes: 1

mshcruz
mshcruz

Reputation: 1987

From what I understood, currently you're getting the latitude and longitude values and writing them to different cells, but you want to have them both in a single cell, separated by a comma. If that's the case, then you can make the following changes.

From:

var location = geocoder.geocode(addresses[i]).results[0].geometry.location;
responses.getRange(row, destination[0]).setValue(location.lat);
responses.getRange(row++, destination[1]).setValue(location.lng);

To:

var location = geocoder.geocode(addresses[i]).results[0].geometry.location;
responses.getRange(row, destination[0]).setValue(location.lat + ',' + location.lng);

You can adjust the parameters of getRange() if you want to write it to a different cell.

Upvotes: 0

Related Questions