Getting JSON data from Google Spreadsheets - Uncaught TypeError

Question

I have tried a couple of methods, including the one from this post : JSON data from google spreadsheet

However, none of it works. I created a simple test table in google docs, and published it. You can see the published version of the spreadsheet here for your reference.

Here is the direct link to the JSON data, for those who cannot access the link see below for the posted JSON data.

This is my current jQuery JSON code : (Note: the url I am using is valid, but I have not included it below because of its length)

$.getJSON('<json-data-url>', function(data) {
    var string = "<table>";       
    for (var i = 0; i < data.feed.entry.length; i++) {

      var itemNum = data.feed.entry[i].gsx$itemnum.$t;
      var cost = data.feed.entry[i].gsx$cost.$t;
      var msrp = data.feed.entry[i].gsx$msrp.$t;
      string += '<tr><td>' + itemNum + '</td><td>' + cost + '</td><td>' + msrp + '</td></tr>';

    }

    string += "</table>";

    $(string).appendTo('body');
});

The current error that this returns is : Uncaught TypeError: Cannot read property '$t' of underfined

Here is a Demo Fiddle for your testing

I believe this error stems mostly from my inability to accurately read the raw JSON file, so my selector is wrong.

Raw JSON

{"version":"1.0","encoding":"UTF-8","feed":{"xmlns":"http://www.w3.org/2005/Atom","xmlns$openSearch":"http://a9.com/-/spec/opensearchrss/1.0/","xmlns$gsx":"http://schemas.google.com/spreadsheets/2006/extended","id":{"$t":"https://spreadsheets.google.com/feeds/list/17UFRY5IHNNA08M3C6J7KLl_mtZxYNtiZNJ3aPXhfEaM/od6/public/basic"},"updated":{"$t":"2014-06-18T17:26:03.848Z"},"category":[{"scheme":"http://schemas.google.com/spreadsheets/2006","term":"http://schemas.google.com/spreadsheets/2006#list"}],"title":{"type":"text","$t":"Sheet1"},"link":[{"rel":"alternate","type":"application/atom+xml","href":"https://docs.google.com/spreadsheets/d/17UFRY5IHNNA08M3C6J7KLl_mtZxYNtiZNJ3aPXhfEaM/pubhtml?hl\u003den_US"},{"rel":"http://schemas.google.com/g/2005#feed","type":"application/atom+xml","href":"https://spreadsheets.google.com/feeds/list/17UFRY5IHNNA08M3C6J7KLl_mtZxYNtiZNJ3aPXhfEaM/od6/public/basic"},{"rel":"http://schemas.google.com/g/2005#post","type":"application/atom+xml","href":"https://spreadsheets.google.com/feeds/list/17UFRY5IHNNA08M3C6J7KLl_mtZxYNtiZNJ3aPXhfEaM/od6/public/basic"},{"rel":"self","type":"application/atom+xml","href":"https://spreadsheets.google.com/feeds/list/17UFRY5IHNNA08M3C6J7KLl_mtZxYNtiZNJ3aPXhfEaM/od6/public/basic?alt\u003djson"}],"author":[{"name":{"$t":"MetSales1"},"email":{"$t":"metsales1@gmail.com"}}],"openSearch$totalResults":{"$t":"4"},"openSearch$startIndex":{"$t":"1"},"entry":[{"id":{"$t":"https://spreadsheets.google.com/feeds/list/17UFRY5IHNNA08M3C6J7KLl_mtZxYNtiZNJ3aPXhfEaM/od6/public/basic/cokwr"},"updated":{"$t":"2014-06-18T17:26:03.848Z"},"category":[{"scheme":"http://schemas.google.com/spreadsheets/2006","term":"http://schemas.google.com/spreadsheets/2006#list"}],"title":{"type":"text","$t":"Linea Pro"},"content":{"type":"text","$t":"cost: $590, msrp: $690"},"link":[{"rel":"self","type":"application/atom+xml","href":"https://spreadsheets.google.com/feeds/list/17UFRY5IHNNA08M3C6J7KLl_mtZxYNtiZNJ3aPXhfEaM/od6/public/basic/cokwr"}]},{"id":{"$t":"https://spreadsheets.google.com/feeds/list/17UFRY5IHNNA08M3C6J7KLl_mtZxYNtiZNJ3aPXhfEaM/od6/public/basic/cpzh4"},"updated":{"$t":"2014-06-18T17:26:03.848Z"},"category":[{"scheme":"http://schemas.google.com/spreadsheets/2006","term":"http://schemas.google.com/spreadsheets/2006#list"}],"title":{"type":"text","$t":"Infinea Tab"},"content":{"type":"text","$t":"cost: $690, msrp: $790"},"link":[{"rel":"self","type":"application/atom+xml","href":"https://spreadsheets.google.com/feeds/list/17UFRY5IHNNA08M3C6J7KLl_mtZxYNtiZNJ3aPXhfEaM/od6/public/basic/cpzh4"}]},{"id":{"$t":"https://spreadsheets.google.com/feeds/list/17UFRY5IHNNA08M3C6J7KLl_mtZxYNtiZNJ3aPXhfEaM/od6/public/basic/cre1l"},"updated":{"$t":"2014-06-18T17:26:03.848Z"},"category":[{"scheme":"http://schemas.google.com/spreadsheets/2006","term":"http://schemas.google.com/spreadsheets/2006#list"}],"title":{"type":"text","$t":"Touch Screen"},"content":{"type":"text","$t":"cost: $108, msrp: $208"},"link":[{"rel":"self","type":"application/atom+xml","href":"https://spreadsheets.google.com/feeds/list/17UFRY5IHNNA08M3C6J7KLl_mtZxYNtiZNJ3aPXhfEaM/od6/public/basic/cre1l"}]},{"id":{"$t":"https://spreadsheets.google.com/feeds/list/17UFRY5IHNNA08M3C6J7KLl_mtZxYNtiZNJ3aPXhfEaM/od6/public/basic/chk2m"},"updated":{"$t":"2014-06-18T17:26:03.848Z"},"category":[{"scheme":"http://schemas.google.com/spreadsheets/2006","term":"http://schemas.google.com/spreadsheets/2006#list"}],"title":{"type":"text","$t":"AIO Terminal"},"content":{"type":"text","$t":"cost: $790, msrp: $890"},"link":[{"rel":"self","type":"application/atom+xml","href":"https://spreadsheets.google.com/feeds/list/17UFRY5IHNNA08M3C6J7KLl_mtZxYNtiZNJ3aPXhfEaM/od6/public/basic/chk2m"}]}]}}

As always... any and all help is greatly appreciated!

I'm looking in the JSON feed, and there is no `data.feed.entry[i].gsx$itemnum`. What is `gsx$itemnum`? This is nowhere in the JSON, and this is why you are getting undefined errors. — tomaroo, Jun 18 '14 at 19:14
@tomaroo first time I'm attempting to use JSON. I have also tried `content$itemnum.$t` that hasn't worked. — Adjit, Jun 18 '14 at 19:19

tomaroo · Accepted Answer · 2014-06-18T19:50:48.163

2

Use this site to help you map out your JSON data and see what you have to work with:

http://json.parser.online.fr/

From what I can tell, this may be what you're aiming for:

  var id = data.feed.entry[i].id.$t;
  var title = data.feed.entry[i].title.$t;
  var content = data.feed.entry[i].content.$t;

There doesn't seem to be a property for cost or msrp in the JSON data.

Edit: I see that the content property stores the cost and msrp info in a string. You can't really do much with it unless you split up/parse the string:

  var x = content.split(', '); // ["cost: $590", "msrp: $690"]
  var cost = x[0]; // "cost: $590"
  var msrp = x[1]; // "msrp: $690"

  var $cost = cost.split(' ')[1]; // "$590"
  var $msrp = msrp.split(' ')[1]; // "$690"

edited Jun 18 '14 at 19:50

answered Jun 18 '14 at 19:34

tomaroo

2,524
1
19
22

Ahh, very cool tool. Is there a way to go deeper into the `$t` from content? like `$t.cost` or are the contents of `$t` just a string? so I would have to parse the string on my own? – Adjit Jun 18 '14 at 19:40
Awesome, that helps a lot. I guess google docs does not use JSON to populate their tables, because it doesn't have cell(1,1). It has columns `cost` and `msrp` in JSON, but not the column `Item Num` – Adjit Jun 18 '14 at 19:54

adhikari18 · Answer 2 · 2014-06-19T13:03:22.597

function displayContent(json) {
var string = "<table>";      
var len = json.feed.entry.length;
for (var i=0; i<len; i++) 
{
    var itemNum = json.feed.entry[i].gsx$itemnum.$t;
    var cost = json.feed.entry[i].gsx$cost.$t;
    var msrp = json.feed.entry[i].gsx$msrp.$t;
string += '<tr><td>'+itemNum+'</td><td>'+cost+'</td><td>'+msrp+'</td></tr>';
}
string += "</table>";
$(string).appendTo('body');
}

After this put callback script src="http://spreadsheets.google.com/feeds/list/17UFRY5IHNNA08M3C6J7KLl_mtZxYNtiZNJ3aPXhfEaM/od6/public/values?alt=json-in-script&callback=displayContent" type="text/javascript"

Here is the working fiddle: http://jsfiddle.net/adhikari18/J98LD/

Getting JSON data from Google Spreadsheets - Uncaught TypeError

Raw JSON

2 Answers2