Import CoinMarketCap Data to Google Sheets
In this guide, we’ll walk through how to pull cryptocurrency data from the CoinMarketCap API directly into Google Sheets, using the API Connector add-on for Sheets.
We’ll first get an API key from CoinMarketCap, and then set up a request to pull in cryptocurrency exchange rate details to your spreadsheet.
- Before You Begin
- Part 1: Get your CoinMarketCap API Key
- Part 2: Create your API Request URL
- Part 3: Pull API data into Sheets
- Part 4: More Example API URLs
- Part 5: Handle Pagination
- Part 6: API Documentation
- Appendix: CoinMarketCap Crypto Portfolio Template
Before You Begin
Click here to install the API Connector add-on from the Google Marketplace.
Part 1: Get Your CoinMarketCap API Key
- If you haven’t already, navigate to https://coinmarketcap.com/api/ and click Get Your API Key Now
- Fill out your details and click Create My Account
- A moment later you’ll receive an email. Click on the email to verify your account, and you’ll be redirected into your new CoinMarketCap dashboard. Congrats! You now have access to the CoinMarketCap API. Hover over the API Key box in the top left and copy the key to your clipboard.
Part 2: Create Your API Request URL
We’ll first get the latest crypto quotes.
- API root: https://pro-api.coinmarketcap.com
- Endpoint: /v1/cryptocurrency/listings/latest
- Parameters: ?aux=cmc_rank (this will help limit the columns they send back. Leave it off if you want to see all available fields)
Putting it together, we get the full API Request URL:
Part 3: Pull CoinMarketCap API Data into Google Sheets
Now let’s get that data into Sheets.
- Open up Google Sheets and click Extensions > API Connector > Open.
- In the Create tab, enter the API URL we just created.
- Leave OAuth authentication set to None. Under Headers enter two sets of key-value pairs like this:
X-CMC_PRO_API_KEY YOUR_API_KEY Accept application/json
- 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 market data for all active cryptocurrencies in your Google Sheet:
- (Optional) CoinMarketCap returns a lot of fields, so click Edit Fields to open the visual field editor if you want to filter for specific data points.
Part 4: More Example API URLs
You can experiment with endpoints and query strings as described in the documentation to see other types of cryptocurrency data, but if you just want to jump in and get a feel for it, play around with the URLs you enter in the API URL path field.
You can use API Connector’s API Library to easily pull up most of these sample requests while in the add-on (just search for “CoinMarketCap”).
- latest quotes for specific cryptocurrencies
Due to the structure of the underlying JSON, by default the above request will return all selected symbols in a single row. To get each coin in its own row instead, add the following JMESPath expression:
- latest overall global market metrics
- metadata (logo, description, official website URL, social links, etc) for BTC and ETH
- Get the IDs for a set of symbols (useful when symbols are not unique)
- Fundamental Crypto Asset Scores (FCAS) for measuring cryptocurrency project health
Part 5: Handle Pagination
- CoinMarketCap limits the number of records returned in each request. By default, only 100 records will be returned unless you use the ‘start’ and ‘limit’ parameters as described in their documentation. The documentation says you can specify a limit of up to 5000, but in my own tests large limits seemed to produce an error, so I used 1000 in the examples below.
- With API Connector you can either run these paginated request URLs manually or loop through them automatically with offset-limit pagination handling (paid feature), like this:
- API URL: enter your request URL, including limit=1000
- Pagination type:
- Offset parameter:
- Limit parameter:
- Limit value:
- Number of pages: enter the number of pages you’d like to fetch
Part 6: API Documentation
Official API documentation: https://coinmarketcap.com/api/documentation/v1/
Appendix: CoinMarketCap Template
In this template, everything is configured for you to simply add your API key along with whatever currencies you’re interested in and get a dashboard like below:
Here is the template link.