GoogleAPI/JSON:是否从电子表格中检索数据? [英] GoogleAPI/JSON: Retrieving data from spreadsheet?
本文介绍了GoogleAPI/JSON:是否从电子表格中检索数据?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
事实证明，我想出了自己的解决方案:
It turns out that I came up with my own solution:
var data = {"version":"1.0","encoding":"UTF-8","feed":{"xmlns":"http://www.w3.org/2005/Atom","xmlns$openSearch":"http://a9.com/-/spec/opensearchrss/1.0/","xmlns$gs":"http://schemas.google.com/spreadsheets/2006","xmlns$batch":"http://schemas.google.com/gdata/batch","id":{"$t":"https://spreadsheets.google.com/feeds/cells/0ArzGbN1Jn061dFdJZ29VcWttZExoRXQ5TnZVX29xUlE/od6/public/basic"},"updated":{"$t":"2012-10-11T21:56:33.189Z"},"category":[{"scheme":"http://schemas.google.com/spreadsheets/2006","term":"http://schemas.google.com/spreadsheets/2006#cell"}],"title":{"type":"text","$t":"Sheet1"},"link":[{"rel":"alternate","type":"text/html","href":"https://spreadsheets.google.com/pub?key\u003d0ArzGbN1Jn061dFdJZ29VcWttZExoRXQ5TnZVX29xUlE"},{"rel":"http://schemas.google.com/g/2005#feed","type":"application/atom+xml","href":"http://spreadsheets.google.com/feeds/cells/0ArzGbN1Jn061dFdJZ29VcWttZExoRXQ5TnZVX29xUlE/od6/public/basic"},{"rel":"http://schemas.google.com/g/2005#batch","type":"application/atom+xml","href":"http://spreadsheets.google.com/feeds/cells/0ArzGbN1Jn061dFdJZ29VcWttZExoRXQ5TnZVX29xUlE/od6/public/basic/batch"},{"rel":"self","type":"application/atom+xml","href":"http://spreadsheets.google.com/feeds/cells/0ArzGbN1Jn061dFdJZ29VcWttZExoRXQ5TnZVX29xUlE/od6/public/basic?alt\u003djson-in-script"}],"author":[{"name":{"$t":"dhuanco"},"email":{"$t":"dhuanco@gmail.com"}}],"openSearch$totalResults":{"$t":"9"},"openSearch$startIndex":{"$t":"1"},"entry":[{"id":{"$t":"https://spreadsheets.google.com/feeds/cells/0ArzGbN1Jn061dFdJZ29VcWttZExoRXQ5TnZVX29xUlE/od6/public/basic/R1C1"},"updated":{"$t":"2012-10-11T21:56:33.189Z"},"category":[{"scheme":"http://schemas.google.com/spreadsheets/2006","term":"http://schemas.google.com/spreadsheets/2006#cell"}],"title":{"type":"text","$t":"A1"},"content":{"type":"text","$t":"Maria"},"link":[{"rel":"self","type":"application/atom+xml","href":"http://spreadsheets.google.com/feeds/cells/0ArzGbN1Jn061dFdJZ29VcWttZExoRXQ5TnZVX29xUlE/od6/public/basic/R1C1"}]},{"id":{"$t":"https://spreadsheets.google.com/feeds/cells/0ArzGbN1Jn061dFdJZ29VcWttZExoRXQ5TnZVX29xUlE/od6/public/basic/R1C2"},"updated":{"$t":"2012-10-11T21:56:33.189Z"},"category":[{"scheme":"http://schemas.google.com/spreadsheets/2006","term":"http://schemas.google.com/spreadsheets/2006#cell"}],"title":{"type":"text","$t":"B1"},"content":{"type":"text","$t":"30"},"link":[{"rel":"self","type":"application/atom+xml","href":"http://spreadsheets.google.com/feeds/cells/0ArzGbN1Jn061dFdJZ29VcWttZExoRXQ5TnZVX29xUlE/od6/public/basic/R1C2"}]},{"id":{"$t":"https://spreadsheets.google.com/feeds/cells/0ArzGbN1Jn061dFdJZ29VcWttZExoRXQ5TnZVX29xUlE/od6/public/basic/R1C3"},"updated":{"$t":"2012-10-11T21:56:33.189Z"},"category":[{"scheme":"http://schemas.google.com/spreadsheets/2006","term":"http://schemas.google.com/spreadsheets/2006#cell"}],"title":{"type":"text","$t":"C1"},"content":{"type":"text","$t":"USA"},"link":[{"rel":"self","type":"application/atom+xml","href":"http://spreadsheets.google.com/feeds/cells/0ArzGbN1Jn061dFdJZ29VcWttZExoRXQ5TnZVX29xUlE/od6/public/basic/R1C3"}]},{"id":{"$t":"https://spreadsheets.google.com/feeds/cells/0ArzGbN1Jn061dFdJZ29VcWttZExoRXQ5TnZVX29xUlE/od6/public/basic/R2C1"},"updated":{"$t":"2012-10-11T21:56:33.189Z"},"category":[{"scheme":"http://schemas.google.com/spreadsheets/2006","term":"http://schemas.google.com/spreadsheets/2006#cell"}],"title":{"type":"text","$t":"A2"},"content":{"type":"text","$t":"John"},"link":[{"rel":"self","type":"application/atom+xml","href":"http://spreadsheets.google.com/feeds/cells/0ArzGbN1Jn061dFdJZ29VcWttZExoRXQ5TnZVX29xUlE/od6/public/basic/R2C1"}]},{"id":{"$t":"https://spreadsheets.google.com/feeds/cells/0ArzGbN1Jn061dFdJZ29VcWttZExoRXQ5TnZVX29xUlE/od6/public/basic/R2C2"},"updated":{"$t":"2012-10-11T21:56:33.189Z"},"category":[{"scheme":"http://schemas.google.com/spreadsheets/2006","term":"http://schemas.google.com/spreadsheets/2006#cell"}],"title":{"type":"text","$t":"B2"},"content":{"type":"text","$t":"32"},"link":[{"rel":"self","type":"application/atom+xml","href":"http://spreadsheets.google.com/feeds/cells/0ArzGbN1Jn061dFdJZ29VcWttZExoRXQ5TnZVX29xUlE/od6/public/basic/R2C2"}]},{"id":{"$t":"https://spreadsheets.google.com/feeds/cells/0ArzGbN1Jn061dFdJZ29VcWttZExoRXQ5TnZVX29xUlE/od6/public/basic/R2C3"},"updated":{"$t":"2012-10-11T21:56:33.189Z"},"category":[{"scheme":"http://schemas.google.com/spreadsheets/2006","term":"http://schemas.google.com/spreadsheets/2006#cell"}],"title":{"type":"text","$t":"C2"},"content":{"type":"text","$t":"ENG"},"link":[{"rel":"self","type":"application/atom+xml","href":"http://spreadsheets.google.com/feeds/cells/0ArzGbN1Jn061dFdJZ29VcWttZExoRXQ5TnZVX29xUlE/od6/public/basic/R2C3"}]},{"id":{"$t":"https://spreadsheets.google.com/feeds/cells/0ArzGbN1Jn061dFdJZ29VcWttZExoRXQ5TnZVX29xUlE/od6/public/basic/R3C1"},"updated":{"$t":"2012-10-11T21:56:33.189Z"},"category":[{"scheme":"http://schemas.google.com/spreadsheets/2006","term":"http://schemas.google.com/spreadsheets/2006#cell"}],"title":{"type":"text","$t":"A3"},"content":{"type":"text","$t":"Susan"},"link":[{"rel":"self","type":"application/atom+xml","href":"http://spreadsheets.google.com/feeds/cells/0ArzGbN1Jn061dFdJZ29VcWttZExoRXQ5TnZVX29xUlE/od6/public/basic/R3C1"}]},{"id":{"$t":"https://spreadsheets.google.com/feeds/cells/0ArzGbN1Jn061dFdJZ29VcWttZExoRXQ5TnZVX29xUlE/od6/public/basic/R3C2"},"updated":{"$t":"2012-10-11T21:56:33.189Z"},"category":[{"scheme":"http://schemas.google.com/spreadsheets/2006","term":"http://schemas.google.com/spreadsheets/2006#cell"}],"title":{"type":"text","$t":"B3"},"content":{"type":"text","$t":"25"},"link":[{"rel":"self","type":"application/atom+xml","href":"http://spreadsheets.google.com/feeds/cells/0ArzGbN1Jn061dFdJZ29VcWttZExoRXQ5TnZVX29xUlE/od6/public/basic/R3C2"}]},{"id":{"$t":"https://spreadsheets.google.com/feeds/cells/0ArzGbN1Jn061dFdJZ29VcWttZExoRXQ5TnZVX29xUlE/od6/public/basic/R3C3"},"updated":{"$t":"2012-10-11T21:56:33.189Z"},"category":[{"scheme":"http://schemas.google.com/spreadsheets/2006","term":"http://schemas.google.com/spreadsheets/2006#cell"}],"title":{"type":"text","$t":"C3"},"content":{"type":"text","$t":"GER"},"link":[{"rel":"self","type":"application/atom+xml","href":"http://spreadsheets.google.com/feeds/cells/0ArzGbN1Jn061dFdJZ29VcWttZExoRXQ5TnZVX29xUlE/od6/public/basic/R3C3"}]}]}};
function format_ss(data){ // format spreadsheet
var table = new Array();
for(var k in data.feed.entry){
var id = data.feed.entry[k].id.$t;
var row = id.match(/R([0-9]*)C/)[1]-1;
var col = id.match(/C([0-9]*)/)[1]-1;
if(Object.prototype.toString.call(table[row]) != '[object Array]')
table[row] = new Array();
table[row][col] = data.feed.entry[k].content.$t;
}
return table;
}
var ss = format_ss(data);
它就像一种魅力.
原始问题:
这是我使用Google文档创建的公开电子表格:
This is a public spreadsheet I created using google docs:
My problem is, how can I properly get this data with JSON?
我尝试了
这太糟糕了. json数据的格式不正确，例如，我无法确切判断哪个单元格属于哪个列/字段.
This is awful to work with. The json data doesn't come properly formatted, for example I can't exactly tell what cell belongs to which column/field.
有人知道更好的方法来获取此电子表格数据吗?
Does anyone know a better way to fetch this spreadsheet data?
推荐答案
如果您在条目"部分下查看，则会看到:
If you look under the "Entry" section you'll see:
"id": { "$t": "https://spreadsheets.google.com/feeds/cells/0ArzGbN1Jn061dFdJZ29VcWttZExoRXQ5TnZVX29xUlE/od6/public/basic/R1C1" }
"id": { "$t": "https://spreadsheets.google.com/feeds/cells/0ArzGbN1Jn061dFdJZ29VcWttZExoRXQ5TnZVX29xUlE/od6/public/basic/R1C1" }该ID基本上是第1行第1列的URL，然后在该URL下找到:
"content": { "type": "text", "$t": "Maria" }The ID is basically the URL of Row 1, Column 1. Then under that you'll find the:
"content": { "type": "text", "$t": "Maria" }那里的$ t值应该可以为您提供数据.我必须同意这有点冗长，但这就是具有许多属性的事物的本质.
The $t value there should give you your data. I have to agree it's a bit verbose but that's the nature of something that has so many properties.
这篇关于GoogleAPI/JSON:是否从电子表格中检索数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
