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:
My sheet looks something like this:
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.
Warning: long formula (which I will explain) ahead:
Alright, there’s a couple things going on here.
concatenate is building my url for the geocoding API. In this case, it’s bringing together:
- B2, which is ‘Mount Everest’
- ‘Mountain’ (to make it clearer to the API, what we are searching for)
- &key=AIzaSyCLZ_ryjgLjIe1waeffaewffaTebmyDH71D9E (the API key)
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).
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).
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.