Written by Matthew Bilyeu
on July 25, 2017

Using the Google Places API in Google Sheets

My girlfriend and I were making a list of places to visit while on vacation in a new city. We decided to put this data in a spreadsheet so that we could easily see and keep track of the different types of places we were considering and other data like their cost, rating, etc.

It seemed annoying to have to copy data straight from Google Maps/Places into a spreadsheet, so I used this as an excuse to play with the Google Places API.

I wanted to create a custom function in sheets that would accept as input the URL to a Google Maps place, and would populate some cells with data about that place. This way we could discover places in Google Maps, and then quickly get info about those places into our tracking sheet.

Google Maps URLs look like this:

https://www.google.com/maps/place/Dirty+Franks/@39.9453658,-75.1628075,15z/data=!4m5!3m4!1s0x0:0x26f65f8548e1f772!8m2!3d39.9453658!4d-75.1628075

It’s straightforward to parse from this URL the place name and the latitude/longitude. Those pieces of info can be fed to the Places Text Search Service to get structured info about the place in question. E.g.

$ curl "https://maps.googleapis.com/maps/api/place/textsearch/json?query=Dirty+Franks&location=39.9453658,-75.1628075&radius=500&key=$API_KEY"
{
   "html_attributions" : [],
   "results" : [
      {
         "formatted_address" : "347 S 13th St, Philadelphia, PA 19107, United States",
         "geometry" : {
            "location" : {
               "lat" : 39.9453658,
               "lng" : -75.1628075
            },
            "viewport" : {
               "northeast" : {
                  "lat" : 39.9467659302915,
                  "lng" : -75.1615061697085
               },
               "southwest" : {
                  "lat" : 39.9440679697085,
                  "lng" : -75.16420413029151
               }
            }
         },
         "icon" : "https://maps.gstatic.com/mapfiles/place_api/icons/bar-71.png",
         "id" : "30371f87239f7f5259d9b24a62d8ec7c32861097",
         "name" : "Dirty Franks",
         "opening_hours" : {
            "open_now" : true,
            "weekday_text" : []
         },
         "photos" : [
            {
               "height" : 608,
               "html_attributions" : [
                  "\u003ca href=\"https://maps.google.com/maps/contrib/114919395575905373294/photos\"\u003eDirty Franks\u003c/a\u003e"
               ],
               "photo_reference" : "CmRaAAAAY-2fs6cFG21uVFP33Aguxwy4q_cCx8Z46lOGazGyNNlRhn6ar90Drb8Z4gZnuVdyQZsvwPXfmOl8efqfiJrfMf01QgLN9KKZh5-eRfTcZFkIQ5kO08xTOH5nUjiy0G-NEhCgLdOf6afTjgF7sC9V_JOyGhQBxnxXYmtQe-kXF8dIk-mSEhFgJQ",
               "width" : 1080
            }
         ],
         "place_id" : "ChIJAxzOXSTGxokRcvfhSIVf9iY",
         "price_level" : 2,
         "rating" : 4.3,
         "reference" : "CmRRAAAAIhlMRQZtM9JbwJYXeGPWWkP70ujjPj6NlK_1ZXQSefVk5oNa22vqseV1ySiti3zXMyZuzSn5DIQEBQoqTOmmFLH7iHp6Lr1XGZ5x0zVaUZFjvD2EYDHxbICvMNRaBWOIEhCAHJaIxUcjP5kw6FJqhhzTGhSJsWZQ09kuYNFpk9-xAM4EyQWRNQ",
         "types" : [ "bar", "point_of_interest", "establishment" ]
      }
   ],
   "status" : "OK"
}

That JSON data can be ingested by the sheet. Custom functions in Google Sheets, I found out, can return nested arrays of data to fill surrounding cells, like this:

return [ [ "this cell", "one cell to the right", "two cells to the right" ],
         [ "one cell down", "one down and to the right", "one down and two to the right" ] ];

Here’s the resultant code to populate my sheet with Places data:

function locUrlToQueryUrl(locationUrl) {
  var API_KEY = 'AIz********************';
  var matches = locationUrl.match(/maps\/place\/(.*)\/@(.*),/);
  var name = matches[1];
  var latLon = matches[2];
  var baseUrl = 'https://maps.googleapis.com/maps/api/place/textsearch/json';
  var queryUrl = baseUrl + '?query=' + name + '&location=' +  latLon + '&radius=500&key=' + API_KEY;
  return queryUrl;
}

function GET_LOC(locationUrl) {
  if (locationUrl == '') {
    return 'Give me a Google Places URL...';
  }
  var queryUrl = locUrlToQueryUrl(locationUrl);
  var response = UrlFetchApp.fetch(queryUrl);
  var json = response.getContentText();
  var place = JSON.parse(json).results[0];
  var place_types = place.types.join(", ");
  var price_level = [];
  for (var i = 0; i < place.price_level; i++) { price_level.push('$'); }
  price_level = price_level.join('')
  
  return [[ place.name,
            place.formatted_address,
            place_types,
            place.rating,
            price_level ]];
}

The function can be used like any of the built-in Sheets functions by entering a formula into a cell like =GET_LOC(A1). And voila:

← → Top