Getting JSON data from Google spreadsheets and using it in Google maps

Map screen cap

The problem:

This is a screen shot of the map I am working with. Notice the 4 bench markers on the map. Each of these bench markers show an InfoWindow when clicked. The InfoWindow contains a picture of the view at the bench and maybe some caption text. The problem I needed to solve was to allow a non-technical to user to update these InfoWindows and add/remove markers as needed.

The solution:

The solution involved using Google spreadsheets to publish a spreadsheet to the web, then grabbing that data as JSON and feeding it into a function that creates Markers and InfoWindows. This post got me started, then I extended it to Google maps.

Here’s how the spreadsheet is laid out:

Spreadsheet Example

Google will truncate your latitude and longitude coordinates a bit. It didn’t even make a noticeable difference in the map I was working on but it’s worth noting if you’re looking for extreme precision.

First, publish your spread sheet

Publish the spread sheet

A dialog window will pop up and you’ll have some options to set. For the most part you can leave the defaults. The most important thing for this to work is getting the key from the link Google provides. Be sure to check “Automatically republish when changes are made” so if you make changes it’ll auto-update your app.

You should get a URL like this from that dialog:<YOUR-KEY-HERE>&output=html

The part in bold is what we’re interested in.

Getting the JSON with jQuery

This is the piece of code that goes within your map’s initialize() function. The actual useful data we want is nested pretty deep within the JSON object that is returned from Google. Also, Google will take your row headings and make them lowercase and remove special characters.

For example: If the JSON object is named data, then you’d find the first cell of the “Bench ID” row at:

Here’s how I looped through the results to extract the data:

$.getJSON('<YOUR-KEY-HERE>/od6/public/values?alt=json', function(data) {

for (var i = 0; i < data.feed.entry.length; i++) {

  var id = data.feed.entry[i].gsx$benchid.$t;
  var photo = data.feed.entry[i].gsx$photourl.$t;
  var caption = data.feed.entry[i].gsx$captionoptional.$t;
  var string = '<img src="' + photo + '"><p>' + caption + '</p>';

  var lat = data.feed.entry[i].gsx$lat.$t;
  var long = data.feed.entry[i].gsx$long.$t;

  createMarker(lat, long);
  createInfoWindow(id, string);



Now, I just needed a simple function to make the markers and InfoWindows. I put this outside the scope of initialize();.

/* Initialize a blank array to store all the markers
 * that we're going to place on the map
var markers = [];

/* Function takes in a latitude and longitude coordinate
 * and constructs a marker object, then pushes it on
 * the marker array we just created.
function createMarker(lat, long) {

  var lol = new google.maps.LatLng(lat, long);

  var marker = new google.maps.Marker({
    position: lol,
    icon: "overlays/bench-icon.png",
    map: map,
    visible: true



/* Function takes in the Bench ID and caption we constructed
 * in $.getJSON's callback function, then creates an InfoWindow
 * object and displays it on the map.
function createInfoWindow(id, contentString) {

  var infowindow = new google.maps.InfoWindow({
    content: contentString

  google.maps.event.addListener(markers[id], 'click', function() {,markers[id]);


The Result

The data has been pulled in and the InfoWindows have been populated. Time to party.

The Result

9 thoughts on “Getting JSON data from Google spreadsheets and using it in Google maps”

  1. Nice, elegant solution. Sometimes I find it hard to make sense of he Google API documentation, Thank you for sharing your work process!

What do you think?