Get latitude/longitude for any location through Google Sheets and plot these in Looker

_In this post we’ll explore an easy way to enhance your geodata using Apps script and Google Maps service, which makes it easier to plot values on a map in Looker, without having to create a topoJSON file.

Getting latitude & longitude for any address

Google Apps Script is a powerful way of adding additional functionality to Google Sheets, enabling you to add custom menus and functions, as well as integrations with other Google services. Here, we’ll add two functions that can call Google Maps service and Yandex Maps Service, and use that service to get the location coordinates. Note that both services are subject to quota.

Open up a new Google Spreadsheet and go to the script editor, under the Tools menu and paste in the Apps Script code below:

/**
 * Returns latitude and longitude values for given address using the Google Maps Geocoder.
 *
 * @param {string} address - The address you get the latitude and longitude for.
 * @customfunction
 */
function GEOCODE_GOOGLE(address) {
    if (address.map) {
        return address.map(GEOCODE_GOOGLE)
    } else {
        var r = Maps.newGeocoder().geocode(address)
        for (var i = 0; i < r.results.length; i++) {
            var res = r.results
            return res.geometry.location.lat + ", " + res.geometry.location.lng
        }
    }
}

/**
 * Returns latitude and longitude values for given address using the Yandex Geocoder.
 *
 * @param {string} address - The address you get the latitude and longitude for.
 * @customfunction
 */
function GEOCODE_YANDEX(address) {
    if (address.map) {
        return address.map(GEOCODE_YANDEX)
    } else {
        input = encodeURI(address)
        var r = UrlFetchApp.fetch(
            "https://geocode-maps.yandex.ru/1.x/?format=json&geocode=" +
            input + "&results=1&lang=en-US", {
                "method": "get"
            })
        var res = JSON.parse(r)
        try {
            res = res.response.GeoObjectCollection.featureMember[0].GeoObject.Point.pos
            res = res.split(" ")[1] + ", " + res.split(" ")[0]
            return res
        } catch (e) {
            return ""
        }
    }
}

As you can see, we’re defining two functions that takes an address as input. The metadata that is added on top function gives more context on how it can be used. Hit save and return back to the spreadsheet.

On the spreadsheet, add the addresses you want to return the geo coordinates for. Both geocoders are quite accurate, but it is still important to be specific about addresses. For example, “Rialto”, would most probably bring you to the famous Rialto Bridge in Venice, Italy, even though you really meant “The Bird Flanagan Pub, Rialto, Dublin”. Try appending a city, state or country name if you only have a short address.

Now we can use either one of the two custom formulas to get the coordinates:

=GEOCODE_GOOGLE(A2) // OR =GEOCODE_GOOGLE(“The Bird Flanagan Pub, Rialto, Dublin”)

or to use Yandex:

=GEOCODE_YANDEX(A2) // OR =GEOCODE_YANDEX(“The Bird Flanagan Pub, Rialto, Dublin”)

You’ll get 53.336599, -6.2992162 as the result, meaning you found your spot (and it’s a good spot). Subsequently you can split the latitude and longitude with a (=SPLIT(B2, ", ")) into two separate columns.

Bringing this data into Looker

We can now look at getting this data into looker. If you’re using BigQuery, you can use the BQ functionality to query data in Drive, described here. If you need a more database agnostic way to get your data into Looker, you can use a JDBC connection straight from Apps script and write your data to your database.

After bringing the data into Looker, I can create a view called location from this table and join this view on any table that contains Dublin area codes, which allows me to map these areas easily:

7 16 43.5K
16 REPLIES 16

Using the same process as above you can also achieve approximate mapping of IP addresses.
The function below uses the free IP geolocation API from http://freegeoip.net/

function ip_geolocation(cell_reference) {

  # cell_reference is the Google Sheet value (e.g '8.8.8.8' or 'github.com')
  var response = UrlFetchApp.fetch("freegeoip.net/json/" + cell_reference);
  var result = [];
  var values = JSON.parse(response.getContentText());
  result.push([values.latitude , values.longitude, values.city, values.country_name, values.country_code]);
    
  return result
}

This will work for IP addresses and domain names.

alenseb
Participant I

Hey @rducarrouge,
looks like freegeoip has been deprecated, is there any other link?

Hey @alenseb,

Per

 wget http://freegeoip.net/
--2018-07-10 18:01:21--  http://freegeoip.net/
Resolving freegeoip.net (freegeoip.net)... 104.25.148.25, 104.25.149.25, 2400:cb00:2048:1::6819:9519, ...
Connecting to freegeoip.net (freegeoip.net)|104.25.148.25|:80... connected.
HTTP request sent, awaiting response... 301 Moved Permanently
Location: https://ipstack.com [following]
--2018-07-10 18:01:21--  https://ipstack.com/
Resolving ipstack.com (ipstack.com)... 23.246.243.31
Connecting to ipstack.com (ipstack.com)|23.246.243.31|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: unspecified [text/html]
Saving to: ‘index.html.1’

index.html.1                      [ <=>                                            ]  17.00K  --.-KB/s    in 0.08s   

2018-07-10 18:01:21 (224 KB/s) - ‘index.html.1’ saved [17406]

… it looks like the dns name has simply changed, and http://freegeoip.net/ redirects to https://ipstack.com/. Therefore, the api endpoints should still be accessible here with the changed domain.

Thanks,
Philip

Hey Alen, yeah the service name changed as Philip mentioned.
Another available option for personal dev work is http://ip-api.com/
their doc on using the API is here

Call: http://ip-api.com/json/208.80.152.201

response:

{
  "status": "success",
  "country": "United States",
  "countryCode": "US",
  "region": "CA",
  "regionName": "California",
  "city": "San Francisco",
  "zip": "94105",
  "lat": "37.7898",
  "lon": "-122.3942",
  "timezone": "America\/Los_Angeles",
  "isp": "Wikimedia Foundation",
  "org": "Wikimedia Foundation",
  "as": "AS14907 Wikimedia US network",
  "query": "208.80.152.201"
}

This has a limit rate of 150 requests per minute.

For those who frequently run into quota issues with the Google Maps geocoder, Yandex has a pretty good geocoder as well with a daily limit of 25k requests. It doesn’t seem to enforce per-minute quota either.

You can easily get a geocoder custom function in Sheets as below. One caveat, Yandex returns the location as longitude, latitude e.g. 4.478452 50.915114 (the other way around).

/**
 * Returns latitude and longitude values for given address
 *
 * @param   {"address"}  address Address type.
 * @customfunction
 */
function GEOCODE(input) {
    input = encodeURI(input)
    var options = {"method": "get"};
    var response = UrlFetchApp.fetch("https://geocode-maps.yandex.ru/1.x/?format=json&geocode=" + input + "&results=1&lang=en-US", options);
    var result = JSON.parse(response);
  try {
    result = result.response.GeoObjectCollection.featureMember[0].GeoObject.Point.pos
    return result;
  } catch(err) {
    return "" // in case we get nothing back
  }
}

Exactly what I was looking for, thank you!

Is there a way to use this as an array formula? I keep getting an error when I try.

To split into latitude and longitude (with B2 being the address cell; change as appropriate for your spreadsheet.)

Latitude:
=QUERY(SPLIT(GEOCODE_GOOGLE(B2), “,”), “SELECT Col1”)

Longitude:
=QUERY(SPLIT(GEOCODE_GOOGLE(B2), “,”), “SELECT Col2”)

Are there a similar function for get the elevation based in the coordenates?

Hey Tony!

I was curious so I took a look at what Google has on offer-- Turns out they’ve got an elevation API: https://developers.google.com/maps/documentation/elevation/start

It wouldn’t be as plug & play as the script Brecht wrote, but you could adapt the Yandex example to hit the API endpoints defined in that doc and get the elevation back.

Can somebody help me out please.  Whenever I try to run the Yandex code an error pops up, the error is 

Exception: Request failed for https://geocode-maps.yandex.ru returned code 403. Truncated server response: {"statusCode":403,"error":"Forbidden","message":"Key is required"} (use muteHttpExceptions option to examine full response) (line 23).

It would be a great help if somebody can sort this out for me.

Hi, I’m running into a TypeError for the map method. Any ideas as to how to resolve this? 

3383bbff-9f3f-4440-acf2-5eec798f318b.png

*Updated. I’ve resolved this. It was my mistake, I was running the function in the editor. Didn’t realize it just needed to be saved to work in Sheets.

Hello, I have tried to use the script for geocoding, following your instructions, but I keep getting an error result saying "TypeError: Cannot read properties of undefined (reading 'map')". Have I done something wrong?

Hi, We have to make a small change - change "res." , to "res[0]." 
For example: 

return res[0].geometry.location.lat + ", " + res[0].geometry.location.lng

When I run the script within the spreadsheet, I receive these errors.

For =GEOCODE_GOOGLE(Q1)

TypeError: Cannot read properties of undefined (reading 'location') (line 14).

For =GEOCODE_YANDEX(Q1)

Exception: Request failed for https://geocode-maps.yandex.ru returned code 403. Truncated server response: {"statusCode":403,"error":"Forbidden","message":"Key is required"} (use muteHttpExceptions option to examine full response) (line 30).

It seems that Yandex requires inclusion of an API key now.

Change one line in GEOCODE_GOOGLE to:
return res[0].geometry.location.lat + ", " + res[0].geometry.location.lng

Top Labels in this Space
Top Solution Authors