JSONP Google Spreadsheets

by Ryan Brackett

HTML

<div id="results" data-id="1_-3Hq9kdHXSebYvBNKKPU1T109Z7UV9hanCs0wB2ZBs"></div>

CSS

body {
  padding: 20px;
}

table {
  width: 100%;
}

td {
  padding: 3px;
}

tr {
  border-bottom: 1px solid #dedede;
}

JavaScript

var spreadsheetID = $("#results").attr("data-id");
// SPREADSHEET MUST BE PUBLISHED TO WEB
var url = 'https://spreadsheets.google.com/feeds/cells/' + spreadsheetID + '/od6/public/values?alt=json-in-script&callback=?';

var onSuccess = function(data) {
  var html = "";
  var entries = data.feed.entry;

  for (var i = 0, len = entries.length; i < len; ++i) {
    html += entries[i].content.$t + ' ';
    if ((i + 1) % 3 == 0) {
      html += '<br/>';
    }
  }

  $('#results').html(html);
}

$.getJSON(url, cellEntries);

function cellEntries(json) {

  var table = document.createElement('table');
  table.setAttribute('id', 'output');
  var tbody = document.createElement('tbody');

  var tr;

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

    var entry = json.feed.entry[i];
    if (entry.gs$cell.col == '1') {
      if (tr != null) {
        tbody.appendChild(tr);
      }

      tr = document.createElement('tr');
    }

    var td = document.createElement('td');
    td.appendChild(document.createTextNode(entry.content.$t));
    tr.appendChild(td);
  }

  tbody.appendChild(tr);
  table.appendChild(tbody);
  document.getElementById('results').appendChild(table);
}