Look up an individual rate
Copy and paste this formula, and change BTC
to the coin of your choice:
=QUERY(IMPORTDATA("https://cryptorates.ai/files/standard.csv", ",", "en_US"), "SELECT Col3 WHERE Col1 = 'BTC'", 0)
Alternatively you can use a cell reference, for example to reference a symbol in cell A1
:
=QUERY(IMPORTDATA("https://cryptorates.ai/files/standard.csv", ",", "en_US"), "SELECT Col3 WHERE Col1='" & A1 & "'", 0)
You can see this in action by looking at the formula in cell C2
on this demo sheet:
Why import the full CSV file?
If you've looked at the API page, you might be wondering why you wouldn't want to just put this in a cell:
=IMPORTDATA("https://cryptorates.ai/v1/price/BTC")
It looks shorter and easier to understand, so what gives?
By importing the CSV file and then using the QUERY
function, we're leveraging Google Sheet's internal caching.
It only needs to make a single web request to fetch the CSV file, and then all of your subsequent requests can use that internally cached file, even if you're tracking thousands of prices.
If we did the method above, each cell needs its own web request and you end up back where you started - with lots of "Loading..." text.