JSON data into Numbers

Solution 1:

While it is possible to do this in AppleScript, with or without JSON Helper, nonetheless, due to the number of records retrieved (8888) and the time it takes to iterate over them compared to a shell script solution, AppleScript is not the best way to retrieve and parse the data.

As a test I did use AppleScript to create a CSV file from the retrieved data and it took 19+ minutes compared to 5 seconds using a shell script to do the same thing.

That said, I'm posting the example shell script code I used to retrieve the data and create a CSV file using the third-party utility: jq

Example shell script code:

#!/bin/bash

cd /private/tmp || exit
curl 'https://api.coingecko.com/api/v3/coins/list' -o coins.json
printf '%s\n' "ID,SYMBOL,NAME" > coins.csv
jq -r '.[] | [.id, .symbol, .name] | @csv' coins.json >> coins.csv
open -a "Numbers" coins.csv

• ^{The example shell script code is just that, an example. It can be written to accommodate command line arguments, such as the URL and filenames, etc.}

Notes:

The order in [.id, .symbol, .name] was used just because that's the way it is in the raw data at the website and also how it's retrieved using curl, while JSON Helper changed the order to id, name, symbol. You can change the order in the printf statement and the jq query as you'd like.

If you don't have or use Homebrew, you do not need it in order the get jq as a precompiled binary is available. It's not packaged and once downloaded as e.g. jq-osx-amd64, I did the following in Terminal:

cd Downloads
xattr -d com.apple.quarantine jq-osx-amd64
mv jq-osx-amd64 jq
chmod +x jq
mkdir -p /usr/local/bin
sudo mv -nv jq /usr/local/bin/

To use the example shell script code, in Terminal, run the following compound command:

f="fetchcoins"; touch "$f"; open -e "$f"; chmod +x "$f"

In the opened fetchcoins document, copy an paste the example shell script code into it and then save and close it.

Back in Terminal:

sudo mv -nv fetchcoins /usr/local/bin/

That's it, you're all set to use it in Terminal:

fetchcoins 

_{About using AppleScript, whether one parses the data to a CSV file or writes the code to populate the cells in a Numbers spreadsheet as the data is iterated over, because of the extreme time differential I'm not going to provide the example AppleScript code I used unless requested. Although to be blunt, please don't ask and use jq as it is the proper tool to use in this use case.}