Parsing JSON in Google Sheets
How about this workaround?
Reason of issue:
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.
Workaround:
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.
From:
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);
}
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);
}
Result:
Note:
- 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.
References:
If I misunderstood your question and this was not the result you want, I apologize.
Related videos on Youtube
Andrew
Updated on June 04, 2022Comments
-
Andrew almost 2 years
I'm working with JSON for the first time, so please excuse my lack of knowledge.
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
into a Google Sheets.
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")
However, when I try to use the same function with the JSON from eddb.io above, I can't get it to work.
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
-
baruchiro about 5 yearsMy guess is the second json contain lists, and can't converted to a sheet
-
toddmo over 4 yearsYou're referencing github.com/bradjasper/ImportJSON/blob/master/ImportJSON.gs
-
-
Andrew about 5 yearsThanks. The json may also be malformed. I've tried to put the file in this :jsoneditoronline.org and it returns errors.
-
player0 about 5 yearsyeah, typo, sry... fixed
-
TheAddonDepot about 5 yearsThe data payload is in JSON Lines format, which is distinct from pure JSON.