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
See the Pen JSON Feed & Output from Google Drive by Mike Doubintchik (@allurewebsolutions) on CodePen.
Credits for inspiration go to: https://ctrlq.org/code/20004-google-spreadsheets-json
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 🙂
Seems like od6 part of the link does not work any more? Replaced by default or list id.
It is working for me: https://spreadsheets.google.com/feeds/list/1ZZbancDuEW3N2cKgWjhn6HIvwb4_LXR-bKH_R8fXWkY/od6/public/basic?alt=json
Interesting. Do you have this spreadsheet inside a domain (Google Apps) or on google (gmail) account?
It’s just a gmail account. Did you set the spreadsheet to “Publish to the web”?
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++) {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'); }.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'); }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.
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.
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"; } }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”}.
I imitate your spreadsheet on RETRIEVE GOOGLE SPREADSHEETS USING JSON FEED & OUTPUT TO HTML
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 += " ";
}
}
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 …
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.
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 ?
gsx$studentnameis 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:
0: 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: "https://spreadsheets.google.com/feeds/list/1ZZbanc…hn6HIvwb4_LXR-bKH_R8fXWkY/od6/public/values/cokwr"} link: [{…}] title: {type: "text", $t: "Alexandra"} updated: {$t: "2021-02-23T00:25:43.569Z"}