Google Spreadsheet Reader jQuery Plugin

A jQuery plugin that allows you to convert a google spreadsheet into an array of JSON objects.

Getting Started

  1. You can download the full javascript file here
  2. You can reference the latest jquery file on this website by using the following script tag:
    <script src="https://gsr-jquery.netlify.com/gsr.jquery.js"></script>
  3. Requires jQuery proper (2.0+) to operate

Example

Given this Google Spreadsheet, we want to create a table. With this jQuery plugin it can be achieved in a few easy steps.

  1. Set the spreadsheet to be publically viewable via the share feature in Google Spreadsheets
  2. Copy the spreadsheet document ID which you can find in the link URL. In the above spreadsheet the id is 13Ouk8KlnEn954LSSxscQEnmqOkM9R3A4Iy2pof_RdJQ
  3. Add the GSR plugin script to your page.
  4. Define a call back function which will handle the results of the spreadsheet.
  5. Execute: $.gsr(documentID, callback); which passes the document ID and the callback function.

Callback Data Definition

The function returns a JSON object with two keys: knownHeaders and table.

{
 knownHeaders: ["first name", "last name", "gender", "age"],
 table: [
  {first name: "john", last name: "smith", gender: "male", age: "23"},
  {first name: "jane", last name: "smith", gender: "female", age: "24"},
  {first name: "pete", last name: "strong"},
  {first name: "johnny", last name: "appleseed", age: "19"}
}

Putting it all together

Now that we understand the general concept we can create the following table based on the above spreadsheet:

All that is needed is less than 50 lines of code:

// The Google Spreadsheet Reader jQuery Plugin
$.gsr("13Ouk8KlnEn954LSSxscQEnmqOkM9R3A4Iy2pof_RdJQ", loadTable);
// The callback function
function loadTable(data) {
let table = document.querySelector("table");
generateTableHead(table, data.knownHeaders);
generateTable(table, data.knownHeaders, data.table);
};
// Generate the Table Header
function generateTableHead(table, data) {
let thead = table.createTHead();
let row = thead.insertRow();
for (let key of data) {
let th = document.createElement("th");
let text = document.createTextNode(key);
th.appendChild(text);
row.appendChild(th);
}
}
// Generate the core table
function generateTable(table, header, data) {
for (let element of data) {
let row = table.insertRow();
var idx = 0;
for (key in element) {
while (key != header[idx]) {
let cell = row.insertCell();
let text = document.createTextNode("");
cell.appendChild(text);
idx += 1;
}
let cell = row.insertCell();
let text = document.createTextNode(element[key]);
cell.appendChild(text);
idx += 1;
}
while(document.querySelector("tr").children.length != idx) {
let cell = row.insertCell();
let text = document.createTextNode("");
cell.appendChild(text);
idx+=1;
}
}
}
view raw googleSpreadsheetReaderSnippet.js hosted with ❤ by GitHub