Search API Connector Documentation
Import CryptoCompare Data to Google Sheets
In this guide, we’ll walk through how to pull cryptocurrency data from the CryptoCompare API directly into Google Sheets, using the API Connector add-on for Sheets.
Personally, this is my favorite crypto API due to their generous limits, wide range of available data, and excellent documentation (including an API request builder tool).
We’ll first get an API key from CryptoCompare, and then set up a request to pull in cryptocurrency exchange rate details to your spreadsheet.
Contents
- Before You Begin
- Part 1: Get your CryptoCompare API Key
- Part 2: Create your API Request URL
- Part 3: Pull Trading Pair API Data into Sheets
- Part 4: Get More CryptoCompare Data
- Part 5: Handle Timestamps
- Part 6: API Documentation
Before You Begin
Click here to install the API Connector add-on from the Google Marketplace.
Part 1: Get Your CryptoCompare API Key
- If you haven’t already, navigate to https://min-api.cryptocompare.com/pricing and click Get your free key.
- Fill out your details and click Sign Up
- A moment later you’ll receive an email. Click on the email to verify your account, and you’ll land on your new CryptoCurrency dashboard. Click https://www.cryptocompare.com/cryptopian/api-keys to access the API key section of your account.
- Click Create an API key
- Give your API key a name, it can be anything but here we’ll call it ‘Crypto for Google Sheets’. Select the permissions you’d like, and click Add.
- You’ll now see your API key. Congrats, you now have access to the CryptoCompare API! Copy this and keep it handy, we’ll need it in a moment.
Part 2: Create Your CryptoCompare API Request URL
We’ll first access the current price of BTC (Bitcoin) across several fiat trading pairs.
- API root: https://min-api.cryptocompare.com
- Endpoint: /data/price
- Query Strings: ?fsym=BTC&tsyms=USD,JPY,EUR
Putting it together, we get the full API Request URL:
https://min-api.cryptocompare.com/data/price?fsym=BTC&tsyms=USD,JPY,EUR
Part 3: Pull CryptoCompare API Data into Sheets
We can now enter all our values into API Connector.
- Open up Google Sheets and click Extensions > API Connector > Open.
- In the Create tab, enter the API URL we just created.
- We don’t need any OAuth authentication so just leave that set to None.
- Under Headers enter a set of key-value pairs like this:
Replace YOUR_API_KEY with the API key you received above in step 6.authorization Apikey YOUR_API_KEY - Create a new tab and click Set current to use that tab as your data destination.
- Name your request and click Run. A moment later you’ll see the latest exchange rates for Bitcoin in your Google Sheet:
Part 4: Get More CryptoCompare API Data
Experiment with endpoints and query strings as described in the documentation to see other types of cryptocurrency data, or try the following requests:
- Top coins by total volume
https://min-api.cryptocompare.com/data/top/totalvolfull?limit=10&tsym=USD
- Bitcoin prices and volume for the past 30 days
https://min-api.cryptocompare.com/data/v2/histoday?fsym=BTC&tsym=USD&limit=30
- Get social data for a coin (Reddit posts, Twitter subscribers etc)
https://min-api.cryptocompare.com/data/social/coin/latest?coinId=4432
- Get exchange data
https://min-api.cryptocompare.com/data/exchanges/general
Part 5: Handle CryptoCompare Timestamps
For most endpoints, CryptoCompare returns dates as UNIX timestamps .
To convert these timestamps to a human readable date, set your data destination to cell B1, and add the following formula to cell A2:=arrayformula(if(I2:I<>"",(I2:I/86400)+DATE(1970,1,1),""))
I2:I reflects the column in which the timestamp is located, so just modify it to match the column in which you want to convert timestamps.
Part 6: API Documentation
Official API documentation: https://min-api.cryptocompare.com/documentation
Hey MA!
I just tried to run your add-on on this link:
https://min-api.cryptocompare.com/data/exchanges/general
I don’t want to use any queries because I am trying to collect the entire set of exchange reocrds. I tested this url in python and it seemed to work fine, but the API Connector Add-On gives two “Failed to run request” errors in the add-on
Hey Doug! Sorry you’re having trouble. You can see full information on error messages here, but basically this error is usually associated with large requests timing out, since Google Sheets has a 6 minute / execution request limit. I checked this file and the issue is not that it’s huge, but that it has a deeply nested structure, which takes a long time to flatten out to cells and potentially runs into that execution limit. In my own test it worked when I switched to compact mode, since that runs a bit faster, can you please try that?
Thanks Ana!
Took a while to run but it ended up working. Thanks for the rapid reply!
any example to get historical prices for BTC for the past 30 days?
Sure, you can use this request URL:
https://min-api.cryptocompare.com/data/v2/histoday?fsym=BTC&tsym=USD&limit=30
They send back dates as UNIX timestamps, so I’d set a destination cell of B1, and then in cell A2 enter the following function:
=arrayformula(if(I2:I<>"",(I2:I/86400)+DATE(1970,1,1),""))
That will convert all the timestamps to human-readable time dates. I just added info on this to the article as well.