javascript 从 Google 电子表格解析 JSON
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9985798/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Parse JSON from Google Spreadsheet
提问by jrue
EDIT:entry.content.$tis the wrong field to access individual cells. entry.gsx$[cell column header]is the correct method. Apologies and thanks for helping to solve this.
编辑：entry.content.$t是访问单个单元格的错误字段。entry.gsx$[单元格列标题]是正确的方法。抱歉并感谢您帮助解决此问题。
Original question:
原问题：
I'm trying to parse JSON data from a Google Spreadsheet. The problem is, the entries field returns a string that is an entire row of the spreadsheet—but appears as a malformed object. How are other people parsing this data? Here is what the content node looks like:
我正在尝试从 Google Spreadsheet解析JSON 数据。问题是，条目字段返回一个字符串，该字符串是电子表格的整行，但显示为格式错误的对象。其他人如何解析这些数据？下面是内容节点的样子：
"content":
{
"type" :"text",
"$t" :"location: 780 Valencia St San Francisco, CA 94110,
phonenumber: (555) 555-5555,
website: http://www.780cafe.com,
latitude: 37.760505,
longitude: -122.421447"
},
Look carefully, the $tfield returns an entire string which is a row in the Google spreadsheet. So entry.content.$treturns a string: location: 780 Valencia St San Francisco, CA 94110, phonenumber: (555) 555-5555...
仔细看，该$t字段返回一个完整的字符串，它是 Google 电子表格中的一行。所以entry.content.$t返回一个字符串：location: 780 Valencia St San Francisco, CA 94110, phonenumber: (555) 555-5555...
Further exacerbating this issue is that some of the cells in the spreadsheet have commas (like addresses) which aren't escaped or quoted. Something like
进一步加剧了这个问题的是电子表格中的某些单元格包含未转义或引用的逗号（如地址）。就像是
jQuery.parseJSON(entry.content.$t)
or
或者
eval('('+ entry.content.$t + ')')
throws an error. I suppose regex is an option, but I'm hoping others may have solved this in a more elegant way. Thanks for the help!
引发错误。我认为正则表达式是一种选择，但我希望其他人可能以更优雅的方式解决了这个问题。谢谢您的帮助！
回答by Bergi
Have you found How can I access Google Sheet spreadsheets only with Javascript?? There one answer pointed to https://github.com/mikeymckay/google-spreadsheet-javascript.
您是否找到了如何仅使用 Javascript 访问 Google Sheet 电子表格？? 有一个答案指向https://github.com/mikeymckay/google-spreadsheet-javascript。
Also, https://developers.google.com/apps-script/service_spreadsheetseems to be promising.
此外，https://developers.google.com/apps-script/service_spreadsheet似乎很有希望。
回答by Eli Sand
The "text" inside the $tattribute is not JSON. According to the documentation, text nodes are transfomed in to $tattributes, so you cannot rely on anything in there being properly formatted JSON.
$t属性中的“文本”不是 JSON。根据文档，文本节点被转换为$t属性，因此您不能依赖正确格式化的 JSON 中的任何内容。
I would suggest using a regular expression instead, though I will warn you that to parse that output will require some fancy stuff. You'll end up using an assertion since you can't split on commas - you'll have to search for (\w+):but in order to find the next element, you'll have to take in everything up to another matching (\w+):, but also not gobble it up. It can be done.
我建议改用正则表达式，但我会警告您解析该输出需要一些花哨的东西。您最终将使用断言，因为您不能用逗号分隔 - 您必须搜索(\w+):但为了找到下一个元素，您必须将所有内容都包含在另一个匹配中(\w+):，但也不能狼吞虎咽起来。可以办到。
回答by Tadas ?ubonis
Just recently, I had the very same problem.
就在最近，我遇到了同样的问题。
To parse content of $t, you can use this RegEx:
要解析$t 的内容，您可以使用此正则表达式：
/(\w+): (.+?(?=(?:, \w+:|$)))/mgi
it will return pairs of key-value.
它将返回键值对。
JavaScript example:
JavaScript 示例：
var currentPropertiesText = jsonEntry['content']['$t'];
// var propsArray = currentPropertiesText.split(", ");
var re = /(\w+): (.+?(?=(?:, \w+:|$)))/mgi;
var propsArray = re.exec(currentPropertiesText)
var props = {};
while (propsArray != null) {
var propName = propsArray[1];
var propValue = propsArray[2];
props[propName] = propValue;
propsArray = re.exec(currentPropertiesText);
}
That should help :-)
那应该有帮助:-)
