在Google表格中解析JSON [英] Parsing JSON in Google Sheets
问题描述
我第一次使用JSON,所以请原谅我缺乏知识.
I'm working with JSON for the first time, so please excuse my lack of knowledge.
我正在尝试使用JSON文件填充Google表格中的数据.我只是不知道正确的语法.如何格式化JSON函数以正确访问数据并停止返回错误?
I'm trying to use a JSON file to populate data in a Google Sheet. I just don't know the right syntax. How can I format a JSON function to properly access the data and stop returning an error?
我正在尝试从此处提取数据:
I'm trying to pull data from here:
https://eddb.io/archive/v6/bodies_recently.jsonl
进入Google表格.
into a Google Sheets.
我已经加载了ImportJSON脚本,并使用一个非常小的JSON文件对其进行了测试( http://date.jsontest.com/),并使用此功能按广告进行操作:
I've got the ImportJSON script loaded and I've tested it with a really small JSON file (http://date.jsontest.com/) and it works as advertised, using this function:
=ImportJSON("http://date.jsontest.com", "/date")
但是,当我尝试对上述eddb.io中的JSON使用相同的功能时,我无法使其正常工作.
However, when I try to use the same function with the JSON from eddb.io above, I can't get it to work.
我想做的是将名称"拉入A1,然后将一些属性拉入列中,例如:
What I would like to do is pull the "name" into A1 and then a few of the attributes into columns, like so:
name id type_name rotational_period, etc.
Here's a link to my tests: https://docs.google.com/spreadsheets/d/1gCKpLcf-ytbPNcuQIIzxp1RMy7N5K8pD02hCLnL27qQ/edit?usp=sharing
推荐答案
此解决方法如何?
当我看到https://eddb.io/archive/v6/bodies_recently.jsonl
的URL时,我注意到文件的扩展名是jsonl
.因此,当我检查从https://eddb.io/archive/v6/bodies_recently.jsonl
检索的值时,发现这些值是JSON行. Dimu Designs的评论已经提到了这一点.我也可以确认官方文件说bodies_recently.jsonl
是行分隔的JSON.
When I saw the URL of https://eddb.io/archive/v6/bodies_recently.jsonl
, I noticed that the extension of the file is jsonl
. So when I checked the values retrieved from https://eddb.io/archive/v6/bodies_recently.jsonl
, it was found that the values were JSON Lines. This has already been mentioned by Dimu Designs's comment. Also I could confirm that the official document says bodies_recently.jsonl
is Line-delimited JSON.
不幸的是,ImportJSON无法直接解析JSON Lines的值.因此,需要将脚本修改为一种解决方法.在共享的电子表格中,ImportJSON的脚本作为容器绑定的脚本放置.在此修改中,我修改了脚本.请进行如下修改.
Unfortunately, ImportJSON cannot directly parse the values of JSON Lines. So it is required to modify the script as a workaround. In your shared Spreadsheet, the script of ImportJSON is put as the container-bound script. In this modification, I modified the script. Please modify as follows.
在脚本编辑器中的130-135行可以看到以下功能.
The following function can be seen at the line of 130 - 135 in your script editor.
function ImportJSONAdvanced(url, query, options, includeFunc, transformFunc) {
var jsondata = UrlFetchApp.fetch(url);
var object = JSON.parse(jsondata.getContentText());
return parseJSONObject_(object, query, options, includeFunc, transformFunc);
}
收件人:
请将以上功能替换为以下脚本,然后保存该脚本.然后,请再次将=ImportJSON("https://eddb.io/archive/v6/bodies_recently.jsonl", "/id")
放置到单元格中.
To:
Please replace the above function to the following script, and save the script. Then, please put =ImportJSON("https://eddb.io/archive/v6/bodies_recently.jsonl", "/id")
to a cell, again.
function ImportJSONAdvanced(url, query, options, includeFunc, transformFunc) {
var jsondata = UrlFetchApp.fetch(url);
var object = jsondata.getContentText().match(/{[\w\s\S].+}/g).map(function(e) {return JSON.parse(e)}); // Modified
return parseJSONObject_(object, query, options, includeFunc, transformFunc);
}
结果:
- 尽管此修改后的脚本适用于
https://eddb.io/archive/v6/bodies_recently.jsonl
中的值,但我不确定此修改后的脚本是否适用于所有JSON行值.我为此表示歉意.
- Although this modified script works for the values from
https://eddb.io/archive/v6/bodies_recently.jsonl
, I'm not sure whether this modified script works for all JSON lines values. I apologize for this.
- eddb.io/api
- JSON Lines
如果我误解了您的问题,而这不是您想要的结果,我深表歉意.
If I misunderstood your question and this was not the result you want, I apologize.
这篇关于在Google表格中解析JSON的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!