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: Pull Data from CryptoCompare to Sheets
- Part 3: Create a Custom Request
- Part 4: Handle Timestamps
- Part 5: 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: Pull Data from CryptoCompare to Sheets
The easiest way to get started with the CryptoCompare API is through API Connector’s built-in integration.
- In Sheets, open API Connector and create a new request (Extensions > API Connector > Open > Create request)
- Select CryptoCompare from the drop-down list of applications
- Under Authorization, enter your API key preceded by the word
Apikey
. - Choose an endpoint. We’ll start with
/data/top/mktcapfull
, which is the endpoint for fetching top coins by market cap. - This endpoint requires the
tsym
parameter, which is the currency symbol to convert into. We'll choose USD for this example. - Choose a destination sheet, name your request, and hit Run to see the response data in your sheet.
Part 3: Create a Custom Request
Alternatively, you can run your own custom requests instead of using API Connector’s pre-built integration, using any of the parameters and endpoints shown in their API documentation. Here's how to set it up:
- Application:
Custom
- Method:
GET
- Request URL:
https://min-api.cryptocompare.com/data/top/mktcapfull?tsym=USD
- Headers:
Authorization
:Apikey
your_api_key
Part 4: Handle CryptoCompare Timestamps
For most endpoints, CryptoCompare returns dates as UNIX timestamps . Here's how to convert those into standard dates:
- Open the field editor by clicking Edit fields
- Find the timestamp fields and click the code icons
In the dropdown formula menus, select the "UNIX SECONDS TO DATE" option. It will automatically populate with Google'sEPOCHTODATE
formula - Click OK and then Save fields
- Run the request and the output will automatically show the timestamps as human-readable dates.
Part 5: 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
any example to get historical eth prices for the last 5 years or more? (all period)
Check out the
/data/v2/histoday
endpoint from the dropdown endpoint menu. Just set the from symbol to ETH, set end date of now, limit to 2000, etc, and you'll get a daily report of ETH prices for the last 2000 days. Set the end date back to get another 2000 days.Hi, thanks for the tutorial. I'd like to build a simple table with daily closing prices (rows) across multiple token tickers (columns). I've been trying to write a single formula that concatenates each column label (ticker) into the ImportJSON request but keep getting REF errors. Could you please help me Ana. Best, Dan
Sorry I'm not really familiar with IMPORTJSON as it's a different tool from API Connector, but if you just want to run an API request that returns your symbols across the top and prices underneath, you can run a request like this:
https://min-api.cryptocompare.com/data/pricemulti?fsyms=BTC,ETH,BNB&tsyms=USD&api_key=11111
. Just enter in the symbols of interest into thefsyms
parameter. Please check if that gets you what you're looking for.