Skip to main content

Retrieve Google Spreadsheets Using JSON Feed & Output to HTML

By May 7, 2016AJAX, API, Blog, Google, Javascript, jQuery
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:

XML Format:

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

Our Example

See the Pen JSON Feed & Output from Google Drive by Mike Doubintchik (@allurewebsolutions) on CodePen.

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.


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

Author allure

More posts by allure

Join the discussion 19 Comments

  • Dušan Vrban says:

    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
      .row-red { background: red; }

      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:

    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

    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


      .row-red > td {
        background: red;
  • Danie Torres says:

    note that the /od6 folder does not work any more.
    To find out the right one go to:

    In the something like otjfgtty

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

  • Nawa Dasa Satsata says:

    A little different from the Sjonnie case. I want to display one particular line, for example the row to 2 or 7 or 4, and/or just a few rows that I choose like row of 1, 3, and 5 only. How do I modify the javascript? Thank you for your help.

    • Mike Doubintchik says:

      You can make a check for the line by seeing if i = LINE_NUMBER. For example, to get line 5 (remember, i = 0 is line 1, i = 1 is line 2, etc.):

        for (var i = 0; i < entry.length; i++) {
          if (i === 4) {
              html += "whatever information you're retrieving";
      • Nawa Dasa Satsata says:

        Okay Thanks.

        However, can I get a specific data, from rows and cells?

        We take the example, from cell “Major”, there are values {English, Math, English, Art, English, Art, English, Math, Math, English, Physics, Art, Physics, Math, English, Math, English, Physics, Art , Physics, Physics, Art, Physics, Math, Art, English, Physics, Math, Art, Math}.

        Can I get specific rows, which contain cell “Major” with the value {“English”} only?

        In essence, I want to call the data “Student Name”, “Gender”, “Class Level”, “Home State”, “Extracurricular Activity” with the same “Major”, namely {“English”}.


        • Mike Doubintchik says:

          Just make your loop look like this:

            for (var i = 0; i < entry.length; i++) {
              if (entry[i]["gsx$major"]["$t"] === "English") {
                html += "";
                html += "" + entry[i]["gsx$studentname"]["$t"] + "";
                html += "" + entry[i]["gsx$gender"]["$t"] + "";
                html += "" + entry[i]["gsx$classlevel"]["$t"] + "";
                html += "" + entry[i]["gsx$homestate"]["$t"] + "";
                html += '' + entry[i]["gsx$major"]["$t"] + "";
                html += "" + entry[i]["gsx$extracurricularactivity"]["$t"] + "";
                html += "";
          • Nawa Dasa Satsata says:

            Thank you for your valuable help above. I will try experimenting again with the script that you created to manipulate JSON Spreadsheets. Maybe if I have trouble later I will come back to this post to trouble you. I hope you don’t mind my question …

          • Nawa Dasa Satsata says:

            Hi, Doubintchik …

            Srcipt from you managed to help me sort JSON from {“Major”: “English”}.

            To get the total number of rows, how to write the script so that when JSON data is taken and rendered it will say “Total: 30 Students (taken from the number of rows)”.

            And for specific total data, for example, “Total Major English: 8 Students (also taken from the number of rows containing {” Major “:” English “}).

            Thank you for taking the trouble to reply, Mike.

  • Abhishek says:

    I didn’t understand what is studentname in gsx$studentname is. Is it identifier or column name ?
    If I have a column called priority, to access the data in that column should I write gsx$priority ?

    • Mike Doubintchik says:

      gsx$studentname is the column name as retrieved from the API. If you console.log the data.entry.feed which is returned from the API, you will see the structure of the data. The feed is an array of rows from the spreadsheet.

      For example, the first item in the array is:

      category: [{…}]
      content: {type: "text", $t: "gender: Female, classlevel: 4. Senior, homestate: …jor: English, extracurricularactivity: Drama Club"}
      gsx$classlevel: {$t: "4. Senior"}
      gsx$extracurricularactivity: {$t: "Drama Club"}
      gsx$gender: {$t: "Female"}
      gsx$homestate: {$t: "CA"}
      gsx$major: {$t: "English"}
      gsx$studentname: {$t: "Alexandra"}
      id: {$t: "…hn6HIvwb4_LXR-bKH_R8fXWkY/od6/public/values/cokwr"}
      link: [{…}]
      title: {type: "text", $t: "Alexandra"}
      updated: {$t: "2021-02-23T00:25:43.569Z"}

Leave a Reply

Designed by

best down free | web phu nu so | toc dep 2017