Search API Connector Documentation

Print

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

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
    coinmarketcap-img1
  2. Fill out your details and click Create My Account
    coinmarketcap-img2
  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.
    coinmarketcap-img3

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.
    coinmarketcap-img4  
  3. Leave authentication set to None. Under Headers enter two sets of key-value pairs like this:
    X-CMC_PRO_API_KEYYOUR_API_KEY
    Acceptapplication/json
    Replace YOUR_API_KEY with the value provided above in part 1 step 3.

    coinmarketcap-img5

  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:
    coinmarketcap-img6

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(@)
    coinmarketcap-img11
  • 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.
    coinmarketcap-img7
  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
      coinmarketcap-img8

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:
coinmarketcap-img10

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.

Previous Import Coinmap Data to Google Sheets
Next Import Constant Contact Data to Google Sheets

139 thoughts on “Import CoinMarketCap Data to Google Sheets”

  1. Hi,
    as of today, i get all my quotes on a single row. I checked and I noticed that the jmesPath is now pro-only (maybe they changed it today?).
    Is there any other way to get quotes on multiple rows?

    Reply
  2. Hello, thank you very much for the tutorial, and the (free) template. I changed it to the coins what hold in my portfolio, but I faced 2 problems:
    1. The price (data.XYCoinname.quote.USD.price) shows a “very” rounded number (for example: 0.56$ is 1$ according to this, altough if I look it up in the data_NoJMES sheet, it shows the correct price) Is there any way to make it show the full value?
    2. Because I mainly invest into new/startup coins, usually they have “self reported” marketcap, circulating supply, etc. Is there any chance to pull these values into this template?

    Thank you for your help, have a nice day Ana.

    Reply
    • Hey Bo!
      1. to see unrounded numbers, just click the “increase decimal places” button in the Google Sheets toolbar at the top of the page.
      2. We can pull whatever data CoinMarketCap provides, but I don’t know if those self-reported values are different from the “regular” marketcap, circulating supply etc values. I think for this it might be better to check out CoinMarketCap’s API documentation, because they’ll have the definitive answer on what’s available. If you find the metrics you want there, and don’t know how to pull them into your sheet, feel free to reach out and I’ll help you set up your request.

      Reply
  3. hi. i wanted to make a dynamic sheet where i will enter the symbol in cells ranges A3:A7 and it gives me respected prices in cell B3:B7
    But here I can see that I have to manually enter the crypto symbol. Is the above possible?

    Reply
  4. Hi there, I have added the API data but I am not sure how/if it will be updated regularly like every 15 mins like google sheets with stock prices, that is what I’m after, so could you confirm if it will work like that?

    Reply

Leave a Comment

Table of Contents