Retrieve Google Spreadsheets Using JSON Feed & Output to HTML

JSON Google Sheets Tutorial

In this blog post I’ll be discussing how to access you Google Drive spreadsheets from your web application using jQuery and AJAX calls to the Google Data API.

To retrieve the content of a Google Sheet (as they are now called), it must first be set to be publicly reachable. The sharing permissions of the Google Sheet should be either “Public” or set to “Anyone with link can view.” This will allow the API to fetch the content of the Google Spreadsheet without needing authentication.

Another requirement will be to publish the Google Sheet to the web (File -> Publish to the web -> Publish).

The Google Data API allows you to pull data from two types of feeds, JSON and XML. URL formats are below.

JSON Format:
https://spreadsheets.google.com/feeds/list/SPREADSHEET/od6/public/basic?alt=json

XML Format:
https://spreadsheets.google.com/feeds/list/SPREADSHEET/od6/public/values

Below is a jQuery based example that pulls data from a public spreadsheet in Google Drive as JSON and prints as HTML.

Our Example

Comments in the code should walk you through how everything works. However, I still want to point a few interesting things out.

Don’t Manipulate The DOM Inside The For Loop

Manipulating the DOM inside the for loop is not an efficient way to write code. It’s slow. In our example, we build the full code into the HTML variable, then we output that variable at the end, outside the loop.

How would the wrong way look?

for (var i = entry.length - 1; i >= 0; i -= 1) {
   $('table tr').append('<td>' + data.feed.entry[i]['gsx$studentname']['$t'] + '<td>');
});

Get the Javascript For Loop Right

Here is the most common way that for loops are written for ( i = 0; i < object.length; i++ ){}

This is not optimal. Here’s a great blog post that describes the optimal way to write a for loop.

TL;DR

for (var i = 0, j = object.length; i < j; i += 1) {
	// do some stuff here
}

Include a Loading Animation

Sometimes it can take a while for all the data to be pulled through the API, particularly for larger spreadsheets. That’s why it’s a good idea to use a loading animation. In the particular example above we use a loading animation of Gangnam style 🙂

Gangnam-style Loading Animation
Mike Doubintchik

Author Mike Doubintchik

More posts by Mike Doubintchik

Join the discussion 11 Comments

  • Seems like od6 part of the link does not work any more? Replaced by default or list id.

  • Sjonnie says:

    Works like a charm!!

    Thanks a lot for this nice bit of code.

    Have one small question about it, it shows my sheet in opposite order, last row first?

    Is it possible to change this order so it shows row 1 first?

    • Hi Jonnie,

      I’m happy the code was helpful for you. To change the order so the first row comes first, we would change the loop to be the inverse of what it is now. It would look like this:

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

  • Sjonnie says:

    Mike, perfect, Muchas Gracias!!

    Another small question??

    Is it possible to alter the output style? Your code gives me an awesome table and what I´m looking for is if a value in a table column is minus (-) the row turns red?

    Found that I can style the output with css but can´t get the minus rows to turn red

    • Jonnie, this is definitely possible with a combination of jQuery and CSS.

      Do you have a codepen or jsfiddle that I can see with your code in action so I can write the correct code?

      In general, the idea is to find a cell with a negative value and give the parent row of that cell a particular class. Then with CSS style that class to have a red background (or you can continue using jquery to add the background styling. With meta programming:

      jQuery + CSS:

      $(document).ready( function() {
        // if the TD element has a '-', it will assign a 'red' class to the parent TR
        $("td:contains('-')").parent().addClass('row-red');
      }
      
      %MINIFYHTML9a8baf2f093affb59f836f05963f22e930%

      jQuery Only:

      $(document).ready( function() {
        // if the TD element has a '-', it will assign the parent TR a background color of red
        $("td:contains('-')").parent().css('background-color', 'red');
      }
      
  • Sjonnie says:

    Yes, have put it here: http://codepen.io/SjonnieVanAnita/pen/ORNbRL

    Have tried your solution but think i.m not good at jquery so must be how and where i´ve put the script. Have put it like this now:

    $(document).ready( function() {
    // if the TD element has a ‘-‘, it will assign a ‘red’ class to the parent TR
    $(“td:contains(‘-‘)”).parent().addClass(‘row-red’);
    }

    BTW, you rock, thanks for your help so far 😉

    • This should work (I tested in your codepen)

      jQuery (you can put this at the bottom of your javascript and I forgot a closing paren in my previous snippet):

      $(document).ready( function() {
        // if the TD element has a '-', it will assign a 'red' class to the parent TR
        $('td:contains("-")').parent().addClass('row-red');
      });
      

      CSS:

      .row-red > td {
        background: red;
      }
      
  • note that the /od6 folder does not work any more.
    To find out the right one go to:
    https://spreadsheets.google.com/feeds/worksheets/YOUR_SPREADSHEET_ID/private/full

    In the something like otjfgtty

    Substitute the /od6 for this new otjfgtty or whatever your doc has.

Leave a Reply