New ImportJSON Implementation

[qeet]

Hi,

Just thought I would let people here know about a new JSON Import function I have created. I thought it would be OK to post here as development of this project seems to have stalled and my implementation solves many long outstanding issues,

The project is called IMPORTJSONAPI and can be found at https://github.com/qeet/importjsonapi.
The custom function allows importing of JSON using JSONPath query expressions and the syntax is very different from ImportJSON so it might require some relearning.

Here are some of the issues that my implementation solves:

Add option to return scalar arrays as arrays #1
Figure out how to handle multiple objects in Facebook API using id fields #2
Ability to import JSONPath queries #6
Option to print empty values on unset requested paths #42
Take only first value from list of returned values #67
JSON structure containing unique query identifiers #71
Some random number returns if data value is null #77
Parsing is incomplete when trailing object keys have null values #79
Can't limit import to N items from array #98
Import fields duplicate #104
Need help Google Sheets truncating data #107
ImportJSON with request Header #114
Problems with similar keys #119
Logical true isn't recognized #143
Not parsing NULL values correctly #147
How do you parse retrieve index/row from an array? #150
Duplicate Lines on Import #151

PS. Feel free to close/delete this post if it is not considered appropriate.

[qeet]

Glad it worked out for you!. Please could you create an issue for your request in my repositiory and I will take a look - Thanks.

[dwolff22]

I just wanted to let you know that this worked great for the Duplicate Lines issue. Thank you so much for taking this initiative!

[Iruwen]

Can this be used in a similar way as ImportJSON so that all returned data is simply returned as a two-dimensional sheet without having to manually specify tons of queries?

[qeet]

My implementation does require more manual configuration but is pretty straight forward once you learn the query language. I would say that if ImportJSON is working for you then great. However if you have an issue that does not work then IMPORTJSONAPI will probably be a solution.

[Iruwen]

Wow, I just started typing something that looked like it could work and surprisingly it did, lol.
Dunno if it could be done more elegant, but from what I see it looks fine (I had that annoying null value issue with ImportJSON):

=IMPORTJSONAPI("https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest?CMC_PRO_API_KEY=<key>&limit=5000&aux=platform,max_supply,circulating_supply,total_supply,volume_7d", "$.data.*", "id, name, symbol, slug, max_supply, circulating_supply, total_supply, platform.symbol, platform.token_address, quote.USD.price, quote.USD.volume_24h, quote.USD.volume_7d, quote.USD.percent_change_24h, quote.USD.percent_change_7d, quote.USD.last_updated")

Do you have a donation address? I owe you a beer!

[qeet]

Great! Glad it worked out for you. Also your query is totally fine.

I don't have a donation address I'm afraid but I totally appreciate the gesture - Thank you!

[fghber]

@qeet Good job. However, the main reason I use bradjaspers' is the BasicAuth option his implementation offers.

[Iruwen]

@fghber importjsonapi can do that too, see https://github.com/qeet/importjsonapi#parameter-examples

[fghber]

@Iruwen Indeed, thank you. It was not working for me first since I was not aware you have to provide the credentials in a different form: The credentials must be constructed like this:

The username and the password are combined with a colon (aladdin:opensesame).
The resulting string is base64 encoded (YWxhZGRpbjpvcGVuc2VzYW1l).

https://developer.mozilla.org/en-US/docs/Web/HTTP/Headers/Authorization

I can confirm this implementation works very well. Amazing job!

[zenminimalist]

@qeet I'm trying to switch from importJSON to this more powerful JSONAPI, but I'm only getting partial results back:

{"result":[{"netSharePurchaseActivity":{"maxAge":1...

like it's cut off...

I used:
=IMPORTJSONAPI("https://query2.finance.yahoo.com/v10/finance/quoteSummary/AAPL?modules=netSharePurchaseActivity"; "$";"quoteSummary")

If I want everything back, is that the correct syntax (not a coder, unfortunately)?

[qeet]

Try something like this:

=IMPORTJSONAPI("https://query2.finance.yahoo.com/v10/finance/quoteSummary/AAPL?modules=netSharePurchaseActivity", "$.quoteSummary.result[*].netSharePurchaseActivity","period, buyInfoCount.raw, totalInsiderShares.raw")

In the last parameter you specify the columns you want returned. If you want everything then unfortunately you will have to explicitly list each item - there is no automated way to list all data.

[zenminimalist]

Thanks a lot. That's extremely helpful! So from my understanding, there's no way to tell it to pull everything below root? What string to I have to add and where for it to give me the headers?

Last question: I know there's always more feature requests than time, but it there a plan to include a feature "pull everything starting from the root down"?

[qeet]

No you cannot pull everything with one command. You need to explicitly specify each item you want to retrieve. Also there are no headers since you are specifying each column yourself and can therefore manually set the header.

I'm afraid there is currently no plan to add a pull everything feature - sorry.

[zenminimalist]

@qeet Is there a way to have JSONAPI give back the headers/title column? I didn't see anything related to this in the doc. But maybe I oversaw that.

[Iruwen]

Also there are no headers since you are specifying each column yourself and can therefore manually set the header.

[khinebo]

It looks like this script has reproduced rather than fix the first ImportJSON issue listed:

Add option to return scalar arrays as arrays #1