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

Knowing what you don’t know

Knowing what you don’t know is an extremely important part of being good at whatever you do. It’s easy to get lulled into feeling like you’re “good enough” and thus don’t need to keep striving. I have caught myself doing this lately. The only way that you can really get in touch with knowing what you don’t know is to push your boundaries. Try things you’ve never tried before. Stay in that unfamiliar feeling when you don’t know something. It’s uncomfortable, but necessary. Don’t think you know everything. Use that unfamiliar, uncomfortable feeling to hone in on what you need to work on.

I came across this unfamiliar feeling recently when I was trying to turn something I made into a jQuery plugin. I quickly realized I don’t know as much about JavaScript as I thought I did. I could have just pretended I didn’t feel that, but then I’d just be kidding myself. Instead, I looked at it as an opportunity to give myself a good foundation in JavaScript and that’s where I’m currently at.

Do it, then do it right, then do it better.
-Addy Osmani

This quote is perfect. I can do the first part (Do it), now I’m working on doing it right. It feels good to always be learning and bettering yourself.

2013 SUNY Oswego annual report

Each year, the annual report gives other SUNY presidents, faculty, and staff a glimpse into what the college has been up to in the previous year. It’s usually something that is placed in the same template as the rest of the site (see the annual report from 2010, 2011, or 2012) and has sections that highlight achievements of the college. The content (writing and photos) have always been very good, but the lack of depth in the web department usually means that the content gets put into an old template because of time constraints.

Since I joined SUNY Oswego’s public affairs office, there are now more resources and man power to make things stand out a little more. This year, we looked for a way to highlight the already awesome content in a modern way, using some cutting edge design patterns that looks simple, but has a decent technology stack going on under the hood. I think we achieved our goal and then some with this project.

A few screenshots

Landing page for "modern" browsers that can handle HTML5 video
Landing page for “modern” browsers that can handle HTML5 video
This is the basic template for a content page. Same nav at the top, large full width photo with a headline and introductory text.
This is the basic template for a content page. Same nav at the top, large full width photo with a headline and introductory text.
A little further down the page.
A little further down the page.

View the 2013 SUNY Oswego annual report

It was definitely the largest team project I’ve ever been involved in. Given how many people contributed to the project, I think it went smoothly.


Writing & organization: Julie Blissert
Project manager / UX decisions: Rick Buck
Art direction / designer / UX: Nick Malchoff
Photos: James Russell, Peter Finger
Video content: Jeff Newell
Photo & video support: Jennifer Osborne
Front end development: yours truly


  • Device agnostic
    • Used “graceful degradation” to ensure people with older browsers could at least access the content.
  • HTML5 video
  • Modern design patterns
    • “Hamburger” menu button
    • Large, full width photos with overlays
    • Custom typography
  • Performance optimizations
    • Concatenation and minification of site assets (CSS/JS)
    • Minified images
  • Development environment that supported rapid development/deployment
    • Git for version control
    • Grunt for task running; concatenation, minification
    • Rsync for easy deployment
  • Sprinkles
    • JavaScript based table of contents with scroll meter

Stay tuned, I’ll be writing more about some of the front-end development aspects of this project, since that was mostly what I worked on. Grunt, git, performance optimization, oh boy! Sounds like a lot of fun. 🙂