Geocoding with Google Sheets + ImportJSON

Even as I grow wary Google’s monopoly power and the amount of data they have about literally EVERYTHING I do, I can’t deny my love for Google Sheets. Google sheets are one of the easiest ways to work with csvs and table data. You can do almost everything you can do in Excel in them (albeit with fewer rows), write or import custom functions to do everything you can’t do with the defaults, leverage add-ons to do things like removing duplicates, leave comments in cells for your collaborators, and export the data in JSON format via the google sheets api (covered here!).

One of the things I haven’t seen covered well in the dev blogging space is how to use Google sheets to call APIs. I generally use simple for loops to call APIs but sometimes I want a more visual way of playing with them. You can call JSON APIs directly within google spreadsheet cells. This allows you all of the visual and intuitive power of spreadsheets (e.g. formulas + FILL DOWN) but with the lovely addition of external data. As an example, this blog will cover how to geocode location data using Google Sheets + the Google geocoding API.

What you’ll need to do this tut:

• Google Sheets
• Google geocoding API key
• The ImportJSON script

In this blog post I will geocode this list of the world’s tallest mountains I found on Wikipedia ( sheet demo here ):

Mount Everest K2 Kangchenjunga Lhotse Makalu Cho Oyu

…

My sheet looks something like this:

Add ImportJSON to your sheet

ImportJSON is amazing project/set of scripts that give google sheets users several custom functions for importing JSON data into their spreadsheets. You first need to copy the raw IMPORTJSON script into your google sheet to be able to use it.

Within your spreadsheet, go to Tools > Script editor. Copy the ImportJSON.gs into the file and save it. After closing the script editor, you can now call the importjson function within sheets.

Build your Geocoding formula

Warning: long formula (which I will explain) ahead:

=transpose(importjson(concatenate("https://maps.googleapis.com/maps/api/geocode/json?address=",B@,"mountain","&key=AIzaSyCLZ_ryjgLjIe1waefaewfaTebmyDH71D9E"),"/results/geometry/location/lat","noHeaders"))

Alright, there’s a couple things going on here.

First off, concatenate is building my url for the geocoding API. In this case, it’s bringing together:

1. https://maps.googleapis.com/maps/api/geocode/json?address=
2. B2, which is ‘Mount Everest’
3. ‘Mountain’ (to make it clearer to the API, what we are searching for)
4. &key=AIzaSyCLZ_ryjgLjIe1waeffaewffaTebmyDH71D9E (the API key)

2. Importjson is the function that calls our custom function script. I’m hoping this was fairly obvious. The less obvious part is the “/results/geometry/location/lat”,”noHeaders” parts.

/results/geometry/location/lat is the specific path in the JSON for the latitude. Let’s take a look at the actual JSON in the browser to make sure this is clear to you:

noHeaders is a parameter that importJSON accepts and means simply ‘just give me the body of the response!’. You read more about this parameter here (the original project) and here (the fork that is now maintained).

Finally, transpose is just rotating the result sideways, thus allowing me to drag down more easily. If you don’t do this with some functions, they won’t work (as the default is not to override existing adjacent data).

Input your Geocode formula and Fill Down

First we’re going to get the latitude values. Put this formula in C2 and fill down.

Next, we change the formula slightly to “/results/geometry/location/lng” to get the values do the same thing in column D. After filling down, we should now see this:

You now have a nice tool for geocoding any location data you might have in the future. And, of course, you can call a variety of other APIs to bring that data into a spreadsheet for easier manipulation.

Kyle Pennell

Kyle Pennell is a marketer and writer with a variety of technical chops (JavaScript, Google Sheets/Excel, WordPress, SEO). He loves clear and helpful copy and likes automating things and making web apps (mostly maps). Found at kyleapennall.com and many other digital spaces