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.

Feel free to also check out our YouTube tutorial here.

Contents

• 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
• Appendix [PRO]: CoinMarketCap Crypto Portfolio Template
• Appendix [FREE] 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

1. If you haven’t already, navigate to https://coinmarketcap.com/api/ and click Get Your API Key Now
   
2. Fill out your details and click Create My Account
   
3. 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 follow the CoinMarketCap documentation to access the latest crypto quotes.

• API root: https://pro-api.coinmarketcap.com
• Endpoint: /v1/cryptocurrency/listings/latest

Putting it together, we get the full API Request URL:

https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest

Part 3: Pull CoinMarketCap API Data into Google Sheets

Now let’s get that data into Sheets.

1. Open up Google Sheets and click Add-ons > API Connector > Open.
2. In the Create tab, enter the API URL we just created.
    
3. Leave 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

   Replace YOUR_API_KEY with the value provided above in part 1 step 3.

   

4. Create a new tab and click ‘Set current’ to use that tab as your data destination.
5. Name your request and click Run. A moment later you’ll see the latest market data for all active cryptocurrencies in your Google Sheet:
   

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. Try the following (one at a time).

• latest global cryptocurrency market metrics

  https://pro-api.coinmarketcap.com/v1/global-metrics/quotes/latest

• all active cryptocurrencies with latest market data, sorted by 24 hr volume

  https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest?sort=volume_24h

• metadata (logo, description, official website URL, social links, etc) for BTC and ETH

  https://pro-api.coinmarketcap.com/v1/cryptocurrency/info?symbol=BTC,ETH

• latest quotes for BTC and ETH, converted to euros

  https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?symbol=BTC,ETH&convert=EUR

  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: data.values(@)
  
  
• Fundamental Crypto Asset Scores (FCAS) for measuring cryptocurrency project health

  https://pro-api.coinmarketcap.com/v1/partners/flipside-crypto/fcas/listings/latest

Note that the free version of CoinMarketCap limits the number of accessible endpoints and number of call credits. Check their pricing page for details.

Part 5: Handle Pagination

1. 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. YMMV.
   
2. 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, making sure to include limit=1000
   • Pagination type: offset-limit
   • Offset parameter: start
   • Limit parameter: limit
   • Limit value: 1000
   • Number of pages: enter the number of pages you’d like to fetch
     

Appendix [PRO]: 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:

Please note that the request template uses a JMESPath query to reformat the API data, so this report requires a pro account with API Connector (if you’ve just installed API Connector, you’ll have a free trial). Here is the link.

Appendix [FREE]: CoinMarketCap Template

If you are using the free version of API Connector, you can use this template instead. Instead of JMESPath, it uses some Google Sheets functions like TRANSPOSE to restructure the data.

It is exactly the same as the report shown above, but because the horizontal data consumes so many columns, it is limited to 10 coins instead of 100.