Import CoinMarketCap Data to Google Sheets

Last Updated On
byAna
< Knowledge Base

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. 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.

For a quick start, get your API key and then skip ahead to the pre-built CoinMarketCap reporting template.

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

We can now enter all our values into API Connector and import CoinMarketCap API data into Google 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. 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 step 3.

    coinmarketcap-img5

  4. Leave authentication set to None. 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: HANDLE PAGINATION

Note that 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.
coinmarketcap-img7

To retrieve paginated records, add these ‘start’ and ‘limit’ parameters like this:

page 1: https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest?limit=5000
page 2: https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest?start=5001&limit=5000

With API Connector, you can either set these up as separate requests or loop through them automatically with pagination (paid feature) for added efficiency, like this:
coinmarketcap-img8

PART 5: GET MORE COINMARKETCAP API DATA

  1. Experiment with endpoints and query strings as described in the documentation to see other types of cryptocurrency data. For example, this URL will retrieve the latest global cryptocurrency market metrics:
    https://pro-api.coinmarketcap.com/v1/global-metrics/quotes/latest
  2. The free version of CoinMarketCap limits the number of accessible endpoints and number of call credits. Check the pricing page for details.
If you’re looking for a more dynamic, ticker-like experience in Sheets, check out the API Connector custom function IMPORTAPI() for faster refresh rates.

APPENDIX: GET THE COINMARKETCAP FCAS TEMPLATE

CoinMarketCap offers the ability to get tokens’ current aggregated market status by either security or utility types based on the following GET request:

https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest?cryptocurrency_type={CRYPTO_TYPE_HERE}

In this template, data from the latest listings along with the FCAS score for the top 3 crypto projects are listed. All of the requests are configured for you to simply add your API key along with the desired token type and get a dashboard like below:

coinmarketcap-img9

If you want to skip right to the dashboard, here is the link.

IMPORTANT SECURITY NOTE

Anyone with Owner or Edit access to your Google Sheet can view all the information you’ve saved within API Connector, including API keys and other credentials. Treat these keys as passwords and limit access to your sheet accordingly.

25 thoughts on “Import CoinMarketCap Data to Google Sheets”

  1. When I load the data into google sheets it only loads 100 coins. is there a way to export all coins in order to filter on market cap, circulating supply, volume?

    Reply

    • Good question! You can add the parameter limit=5000 to get 5000 rows of data. If that’s still not enough, you can combine it with the start parameter.
      I’ll update the article to reflect this.

      Reply

  2. Thakns so muc! I successfully did it. However, there are many critical coins that are not being reported. For example; Aion, WAX and Peerplays are all not there, even after I had imported over 1000 coins.

    Woat could be the issue?

    Reply

    • I just tested a request to https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest?limit=5000 and found all 3 of those coins on the list. Which URL are you using?

      Reply

  5. hi Ana, I just downloaded the add on and followed this guide, but in the header seccion there is no place where to enter the data. What Is wrong with this? Thanks

    Reply

    • That’s strange, could you please send a screenshot of what you’re seeing? Which browser are you using?

      In general, if there is some usage or display issue with an addon, it means there’s some conflict with a browser extension or setting. So please also try disabling other extensions (especially anti-virus extensions) or reloading the addon in incognito mode.

      Reply

  6. Hello Ana,
    I am using the API based on your useful Article put here.
    When I run the request the timestamp shown is 2020-08-29T06:34:36.879Z .But the Actual time I run the API was 1:38 PM JST.Due to this current Price is not shown(The Binance Exchane price was 17 USDT but my Google sheet is showing 15 USDT).Could you help to fix this issue?

    Reply

    • The endpoint returns 3 different timestamps: 1) Timestamp (ISO 8601) of the last time this cryptocurrency’s market data was updated, 2) Timestamp (ISO 8601) of when the conversion currency’s current value was referenced, and 3) Current timestamp (ISO 8601) on the server. Are all of them looking off? In my own tests the timestamps don’t differ from API Connector’s own timestamps by more than about 30 seconds. Can you please add a timestamp (located at Output Options > Add timestamp) and compare the API Connector timestamp to the server timestamp?
      Keep in mind that the API Connector timestamp will reflect the time zone of your sheet, which can be viewed by clicking File > Spreadsheet settings.

      Reply

      • Hello Ana,
        I had selected “Append” in the Output mode and due to that nothing gets updated(Date,Price),Once I changed to “Overwrite” then everything gets updated properly.
        But I have some formulas put in the Page 1(limit 5000) and these formuals are lost when I select Overwrite.Could you let me know how to fix the update issue by having Append option as I don’t want to loose my Customer formulas added in page1.

      • Hi Ragu, append mode should return all data except the headers from the first row, to avoid repeating headers within the sheet. Are you seeing something different?

        Since overwrite mode overwrites everything in the sheet, I suggest using a formula like =query({Sheet1!A1:Z10000}) to pull all your data into a second sheet. Then you can apply your formulas to that second sheet, and won’t lose your formulas when the data refreshes. Let me know if that makes sense, and feel free to email a link to your sheet to support if you’d like us to take a look and help out with more specific advice.

      • Hello Ana

        How can I build an endpoint to call, for example the BTC and ETH, symbols and logos at the same time?

        I made this but I get errors:

        api.coinmarketcap.com/v1/cryptocurrency/info?symbols=”BTC,ETH”?logo=”BTC,ETH”

        It is based on what I’ve seen here: https://imgur.com/a/jMws1fV

      • That looks fine to me, I think you just need to remove the quotation marks, e.g. https://pro-api.coinmarketcap.com/v1/cryptocurrency/info?symbol=BTC,ETH. Also it should be ?symbol=, not ?symbols=.

      • Yes, Ana, it works well! Thanks a lot

        (BTW I made a mistake with “symbols” parameter because it’s singular, not plural as I wrote earlier”)

        My last question is how to get the logo of each one of these cryptos. Should I need to create a new endpoint and run it from another spreadsheet or can I run it in the same one?

        I have this URL but due to 8 calls limits of the free API plan I’m not able to test today:

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

        But I’m not sure, since “logo” belongs to the “aux” parameter https://imgur.com/a/jMws1fV

      • You don’t need to do anything extra, as logo is already included as one of the fields from your original request URL. You should see links to the logos returned in the fields data » BTC » logo and data » BTC » logo.
        Also, if you want to actually see the images in your sheet, you can add a cell containing the IMAGE() function, e.g. =IMAGE(M2), and it will automatically display the image from the link.

  8. Hi Ana and thanks a lot for your article!
    What if I just want USD price, 24h % and 7jd % for a list of coins defined by their ID (because symbol is sometimes the same for 2 coins)?

    Reply

    • Hey David, you’d make a request like https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?id=1,1027,825, where you include whatever IDs you need in the id parameter. Btw, I recommend using compact report style (under Output Options) to make the output easier to read.

      Reply

      • Thanks, it works fine but how can i filter columns?
        I just need coin symbol, USD price, 24h % and 7d %
        I think it’s in JMESpath but i don’t know the syntax…

      • Hi there, you can try this: data.*[].{symbol:symbol,quote:quote}. For more info, please check my JMESPath guide.

        Alternatively, you can just query the columns you want into a new sheet using Sheets functions. For example a function like this would get columns A, C, and E:
        =QUERY(Data!A:Z, “select A, C, E”)

        Or, easiest of all, you can just hide the columns you don’t want to see. Hope that helps!

Leave a Comment