jQuery: Google Drive Spreadsheets JSON

Update for January 21, 2015:
The new Google Drive has new URL pattern for the objects (spreadsheet, especially), I think. And I haven't tinkered that.
You might want to read the Google Developers documentation about the new Drive features and probably discuss the API techniques on Stack Overflow.

This earlier demo still works.

This is very interesting, using Google Drive Spreadsheet as our data list "bank", and displaying it on our website.
And, instead of using standard iframe as the publishing-on-another-place way, I fetch the provided JSON, parse the object, continued by injecting the entries that represent the sheet cells values onto the HTML element. In this case, I use table element as the wrapper.

This demo displays the list of 50 states in United States of America, with each capital city, abbreviation, and the motto.

---

This is the default iframe

---

This is the user interface I made
With additional front-end "search" function. I mostly use jQuery here.

Anywho, to sum up, this is a demo of reading and displaying the public (published) spreadsheet without iframe.

No.	State	State Capital	Abbr	Motto

---

The JSON format of the spreadsheet feed
Let's take a look briefly. The public feed of your Google Drive Spreadsheet is : https://spreadsheets.google.com/feeds/list/[public-spreadsheet-key]/od6/public/values?alt=json

It can use either list or cells. I used the list URL there.

The [public-spreadsheet-key] can be found on the URL of your published spreadsheet.

For instance, the spreadsheet URL of this demo is :

https://docs.google.com/spreadsheets/d/1l3NStR0vghdRjxr_vmynirtDrSxIRpqZ9ln7Vuf0F9o/pubhtml

The marked characters above is the spreadsheet key number.
You can open a new tab and see the JSON format of the feed using this :

https://spreadsheets.google.com/feeds/list/1l3NStR0vghdRjxr_vmynirtDrSxIRpqZ9ln7Vuf0F9o/od6/public/values?alt=json

---

Then what?
This is pretty much long, so I'll share the basic concept of this :

• First, get the JSON using jQuery, I use $.get(...)
• Then find the object element thingy that represents the value of each cell. You can look at the format using JSON.stringify() or just open the URL, then organize the format using online/offline JavaScript code beautifier.
• Inject those entries as the table contents (using JavaScript for loop or jQuery each() then combined with JavaScript innerHTML or jQuery html() and append()).
• The first stage completed, those are : fetching, parsing, and basic injecting. Boy, the first stage huh?
  Next stage, make the table of values interactive to user. As in this demo, I put some CSS for shaping (for a bit of aesthetic) and mouse cursor hovering event (for highlighting) plus search function (for finding).
  The front-end search function is heavy on JavaScript array tinkering.
  Anyway, about the search, I didn't pull another request from the server, just finding the matching injected values here.

The main reason why I didn't put my front-end coding here because it's a "trial and error" one. I'm a trial and error hobbyist. But, you can always look at the source of the independent page (a little different version) below.

---

Anyway, Google Drive Spreadsheet is simple to update, it already has super sweet document editing UI there, unlike, well, common database-management stuff. I can easily update the spreadsheet values which will also make the feed being updated.
Neat

Check out the independent page (and view the source) at Thor

---

Method reference and Google Drive :

• Google Developers demo page
• Google Drive : drive.google.com

---

How to do it properly and extending the Google Sheets developers.google.com/apps-script/guides/sheets

---

About The CSS (styling)
This demo is using box-shadow, border-radius, pseudo-selectors and table styling properties.

About The Script
This demo consists of get method, array "manipulation", basic RegExp, match(...), and all the iterations plus appending.