API Connector Documentation

Print

Import CoinMarketCap Data to Google Sheets

Last Updated OnMarch 19, 2024
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 for Sheets.

We'll first get an API key from CoinMarketCap, and then set up a request to pull in crypto data to your spreadsheet.

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: Pull Data from CoinMarketCap into Sheets

The easiest way to get started with the CoinMarketCap API is through API Connectorā€™s built-in integration.

  1. In Sheets, open API Connector and create a new request (Extensions > API Connector > Open > Create request)
  2. Select CoinMarketCap from the drop-down list of applications
    coinmarketcap-application
  3. Under Authorization, enter your API key.
    coinmarketcap-authorization
  4. Choose an endpoint. Weā€™ll start with /v1/cryptocurrency/listings/latest, which is the endpoint for fetching the latest market data.
    coinmarketcap-endpoints
  5. In the parameters section, select auxiliary fields to retrieve.
    coinmarketcap-parameters
  6. Optionally select other parameters, e.g. limit to get more than 100 records.
  7. Choose a destination sheet, name your request, and hit Run to see the response data in your sheet.
    coinmarketcap-response

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 API URLs shown in the API documentation. Here's an example request setup:

  • Application: Custom
  • Method: GET
  • Request URL: https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest?aux=cmc_rank
  • OAuth: None
  • Headers:
    • X-CMC_PRO_API_KEY:  your_key

Part 4: Handle Pagination

  1. CoinMarketCap limits the number of records returned in each request. By default, only 100 records will be returned unless you set the limit parameter to 1000. (The documentation says you can specify a limit of up to 5000, but in my own tests limits > 1000 may produce an error).
    coinmarketcap-img7
  2. With API Connector you can loop through multiple pages of 1000 with offset-limit pagination handling, like this:
    • Pagination type: offset-limit
    • Offset parameter: start
    • Limit parameter: limit
    • Limit value: 1000
    • Run until: choose when to stop running the request
      coinmarketcap-pagination-offset-limit

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

Here is the template link.

230 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

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

  6. Hi!

    I'm trying to load all of the metadata for all of the coins and im having problems with setting correct request.
    https://pro-api.coinmarketcap.com/v1/cryptocurrency/info?id=300
    returns meta on 1 coin only.
    How do I return URLS only for all of the crypto currencies ?
    Thanks

    Reply

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

  7. 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ā€)

        Update: you can now use API Connector's visual field editor to simply select the columns you want to see.

  8. Hi Ana,

    your article helped me a lot. Thank you.
    I want to create a spreadsheet where i have the overview on my coins and their current values.
    Therefore i need to build connections from the prices of the coins to other fields.
    What about if the rank of the coins change, will the connection be wrong or invalid afterwards?
    What is the best way to connect the fields?
    sorry my technical english isn't so good...

    Reply

    • Hey Florian! If I understand correctly, you have a list of coins in one sheet, and want to keep all the data lined up?
      If so, you can just use Google Sheets functions for this, for example put all your coins in one column and then use VLOOKUP to pull associated data into associated columns. There are lots of tutorials online about how to use VLOOKUP, here's one: https://exceljet.net/excel-functions/excel-vlookup-function. With VLOOKUP, it doesn't matter if your coin data changes position, since you'll be matching on the coin name or ID.
      Let me know if that answers your question, if not, feel free to message back with more detail about what you're looking for.

      Reply

      • Hi Ana, could you maybe elaborate on this solution. I have tried the Multiple VLOOKUP Matches but could not get it to work.
        I had difficulties as well since the data is ordered in a different way after importing. I have my personal data (coins) on one tab, and the data from CMC on the other.
        I am not able to match on the coin name or ID and get the values in the second tab. Is there a wau to do that? Thank you very much for all the time and effort you put into this. Cheers, EC

      • I think I need to see your sheet to help troubleshoot your issue. Could you please contact support so I can take a look? Otherwise, how about using the preset template in this article?

  9. Hi Ana,

    one more question. My data does not update. What do i have to configure for automatic updates?
    Unfortunately I don't understand your quote:
    "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."

    Thanks for your help šŸ™‚

    Reply

  10. Hello Ana, thank you for the wonderful detailed article! I was able to set this up in my spreadsheet in no time at all. šŸ™‚

    Right now all price quotes are in USD. Is there a way to modify the request URL so it displays prices in BTC? Thank you very much!

    Reply

    • Thank you for the nice comment, I'm glad the article was useful!
      CoinMarketCap's documentation says that you can convert currency in the quote object from USD to BTC by adding ?convert=BTC to the end of your URL. Can you please try that and see how it goes?

      Reply

  11. Hello! so i have done everything perfectly but i wanna add an extra something. I want to add convert for the usd prices that i receive from coinmarketcap via that method. Then i want a real time converter to change the prices to euro as well.

    Is that possible via coinmarkecap's api? and if yes what's the exact code i should type in the app?

    Reply

    • CoinMarketCap lets you convert currencies by adding the convert parameter to the end of your API request URL, e.g. https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest?convert=EUR.

      Sheets itself also has currency conversion functions, e.g. you can enter a function like =GOOGLEFINANCE("CURRENCY:USDEUR") directly into your cell to see a conversion rate.

      Reply

      • ahhh thanks a lot, i made a little bit of digging myself and found the googlefinance method!

        I will need your help on another one though, it's the last thing missing to completely set up my excel. So i have one sheet in which all the data for the top 500 coins come and another one in which i have my portfolio

        On each of the coins that i am interested in in the price cell i have an ="name of the other sheet"AG2 for example and ofc when i click refresh the price change. Here is the problem though. The coin ranking is constantly change so basically except the top 2 coins all the other prices change as well and i have to manually change it each time.

        The "best" thing i thought was to take the api for only certain coins but still i would have that problem in a smaller scale and if i wanna add new coins then here we go again.

        Do you have a suggestion? btw sorry for the trouble

  12. Hello, I'm trying to retrieve a quote for The Graph which has symbol GRT from Coinmarketcap. However, when I request that token, I get a quote for Global Rental Token which apparently has the same symbol. Is there a way to specify exactly which token I desire? Thank you.

    Reply

    • I see, in that case I think you need to use the slug instead of the symbol. So instead of https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?symbol=GRT you can use https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?slug=the-graph.

      Reply

    • By default you need to click Run for these requests to refresh, but API Connector provides two methods to refresh automatically: scheduling and the IMPORTAPI function. Neither method is exactly realtime though (scheduling is limited to 1x an hour, and importapi to once every couple minutes).

      Reply

  15. Hello, first of all, congratulations on your website.
    to get the quotations of cryptocurrencies in euros? would the following formula be correct? https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest?start=1&limit=1000&EUR=CONVERT&CMC_PRO_API_KEY= (MI API)

    Reply

    • Please enter it like this: https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest?limit=1000&convert=EUR&CMC_PRO_API_KEY=yourapikey
      Alternately you can run it like shown in the article, with the API key entered into the Headers section.

      Reply

  17. I'm getting the following error when I try this:

    { "status": { "timestamp": "2021-03-03T14:06:52.064Z", "error_code": 1002, "error_message": "API key missing.", "elapsed": 0, "credit_count": 0 } }

    Reply

    • Please make sure you've included your API key in the headers section, with Key = X-CMC_PRO_API_KEY, Value = your API key

      Reply

      • Hi,

        I'm also getting an error 'Attribute provided with invalid value:'

        In the header box I have this:
        Key = X-CMC_PRO_API_KEY, Value = my API key

        Just putting in my key returns the error Brad mentioned above.

        In the value box next to it I have:
        application/json

        Do you think it's spacing? Sorry, I'm completely new to this!

      • That sounds like it's correct but I can't really say without seeing it. Please check the screenshots in the article to see exactly what it should look like, or feel free to send a screenshot via support if you'd like me to take a look.

  20. Hi,
    Great work! Can you please tell me why it does not recognize and fetch data for all these coins? I have put all these into Inputs tab. I "refresh all now" but on Summary tab all those below are empty from columns C to N. Thanks
    LTO,DATA,AMKR,NULS,CELR,CTXC,ONE,MLT,WIN,DUSK,BLZ,AION,CTSI,COTI,WRX,GXC,NKN,ARPA,DOCK,STPT,COS,KEY,MITH,PERL,MDT,GTO,MBL,CHR,TCT,DREP,TROY,OGN,AKRO,BEAM,FIO,IRIS,JST,NBS,OXT,REEF,RIF,SKL

    Reply

    • I tried your set of symbols and saw the following error returned in the status bar: "Invalid value for "symbol": "MLT").
      So it looks like MLT isn't a valid symbol. Please change it to AMLT and your request should work.

      Reply

      • Not totally sure what you're referring to, which endpoint are you using? I see info for, say, Enjin Coin with a URL like https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?symbol=ENJ

  21. Hey Amanda, I would just pull the data into a 'raw data' sheet, then use Google Sheets' VLOOKUP or MATCH functions for this, that way it doesn't matter if the source rows get rearranged (the source rows will always get rearranged since sort order changes between pulls). Or if you meant you want fewer columns returned, you can use the "aux" parameter described in their documentation to cut out response properties you don't need. For example, https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?id=1&aux=max_supply would send a response without data tags.
    Update: You can now use API Connector's visual field editor to do this more simply.

    Reply

  22. Hello,

    is it possible te replace the list of symbols

    https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?symbol=CHSB,XRP,BTC

    with a reference cell that would contain all the symbols ?
    What is the good syntax ?

    thank you !

    Reply

      • Hi, I tried you URL as above and the result is displayed horizontally. (only 1 row, all tickers & their values displayed horizontally in many many columns)
        How do I fix this? Thank you.

      • Hi I think this is the issue mentioned in the article under the header "latest quotes for BTC and ETH, converted to euros". Please check that section for how to resolve it with JMESPath, or use a different endpoint.

  23. How do I pull all the information for coins in DeFi because using

    https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest?tag=defi

    Only pulls the first hundred.
    I paid but I'm still a little confused.
    Thank you.

    Reply

    • Hi there, by default CoinMarketCap limits data to 100 rows. Just add &limit=1000 to the end of your URL to get more.
      If you still need more records after that, you can apply pagination handling. Check the section above called "Handle Pagination" for information on how to set that up, or feel free to respond if you'd like further clarification.

      Reply

    • Hey Guillermo, sorry, I'm not really sure what your question means, but it looks like people might be discussing it here? So maybe you can check or post in there for more info.

      Reply

  25. Hello! Is it possible to just pull a list of specific coins? I'm trying to track the prices against my purchases but the way the API organizes by CMC rank messes with how my table links to different cells... I.e, if a coin changes rank, my price tracker will link to the wrong coin. Does that make sense?

    Reply

    • Hey there, yeah, there's an example in the article of pulling in specific coins. But it sounds like your table is linking to specific cells, right? When you make a spreadsheet you usually donā€™t want to reference cells directly, since itā€™s pretty normal for source data to move around. You can use a Sheets function like VLOOKUP so the data stays in place regardless of its exact location in the list. With VLOOKUP you set the name of the coin as your lookup value, then set your sheets to read from that, rather than having them look for a fixed cell location. Hope that helps pointĀ you in the right direction!

      Reply

  26. Hello Ana,
    Congratulations on your work.
    I am working on excel, everything is working fine when I send the following requests
    https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest?limit=600&convert_id=2790
    and
    https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest?limit=600&convert_id=2781

    but I would like a return with 2 currencies usd "id 2781" and eur "id 2790"
    I have tried the following queries which do not work.
    https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest?limit=600&convert_id=2781,2790
    https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?id=1,2,3&convert_id=2781,2790

    Thank you
    Sergio from France

    Reply

    • Hey Sergio, what does the error message say? I tried it in my own account and it says "Your plan is limited to 1 convert options" so it looks like you need to be on a paid plan for this request to work.

      Reply

      • Thanks Ana for your quick response.
        I have an HTTP 400 Bad Request in my dashboard.
        and in excel i have the same.
        DataSource.Error: Web.Contents failed to get content from "https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?id=9180&convert_id=2781,2790" (400): Bad Request
        Details:
        DataSourceKind = Web
        DataSourcePath = https: //pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest
        Url = https: //pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest? Id = 9180 & convert_id = 2781,2790

      • Yep, the "Your plan is limited" error is an HTTP 400 error. I'm not familiar with using Excel to connect to APIs so I can't comment on that, but I suspect it's the same issue.

  27. Hello Ana,

    I don't know why, but for some reason in the Summary Tab the columns percent_change_7d (0 value), percent_change_24h (0 value), last_updated and market_cap remain empty while all the other columns get regular values. It should do the vlookup, but somehow it is not successful. I tried it with the template sheet out of the box.
    Anyone else got this problem?

    Thank you for your great work!
    Phil

    Reply

    • Hey Phil, thanks for the message! I see the problem, if coins have a lot of tags, it's possible they may produce too many columns and go outside the bounds of the VLOOKUP. To fix, go to the Summary tab cell B9. You'll see a formula like this:
      =arrayformula(if($B$9:$B<>"",iferror(vlookup($B9:$B,Data!$D:$AZ,match(E$7,Data!$1:$1,0)-3,false),""),""))

      Change the AZ to ZZ, like this:
      =arrayformula(if($B$9:$B<>"",iferror(vlookup($B9:$B,Data!$D:$ZZ,match(E$7,Data!$1:$1,0)-3,false),""),""))

      Then copy it all the way across the row and the data should populate correctly. I've updated the template as well.

      Reply

      • Hey Ana,
        yep, this was exactly what caused the problem!
        Thank you so much for your quick and helpful response!
        All the best for you and your assets! šŸ™‚
        Cheers!
        Phil

  28. I just want to show only the price of the currency in USDT without any other information in one sell lets choose ETH for example

    Reply

    • By default API Connector will return whatever the API sends back. You can check their documentation to confirm, but I don't think CoinMarketCap has any endpoint that returns only the price of the currency without any other information, but you can edit the fields displayed in your sheet.

      Reply

  30. Hello Ana,

    First of all, thank you very much for the tutorial as it has helped me to understand the CoinmarketCap API.

    I was writing to you in case you can guide me to capture the different markets for one or more currencies. For example, for BTC with USDT

    # 1 - BTC - Binance - BTCUSDT - 39,000
    # 2 - BTC - Huobi - BTCUSDT - 38950

    Is it possible with the API?

    Reply

    • Hey Van! I don't think anything has changed, I just tested that URL and it worked fine for me. Also, their API documentation says 400 (Bad Request) = "The server could not process the request, likely due to an invalid argument" which implies a misconfigured URL, but I don't see any problem here. Can you please confirm that this is the URL producing the error? Are you running any other requests in your sheet?

      Reply

      • I just tried it again in a new worksheet and I get the same errors.

        Details: "Web.Contents failed to get contents from 'https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?symbol=ADA,AGI,ALGO,ANKR,ATOM,BAND,BAT,BNB,' (400): Bad Request

        Seems odd to me it was working fine and now it doesn't.

      • That doesn't look like an API Connector error message... I guess you're running this in Power BI? Anyway I tried your URL in API Connector and got the following error message: "error_code":400,"error_message":"Invalid value for "symbol": "AGI""
        So it looks like AGI is no longer valid. Just remove that symbol from your list and it should work, please try and let me know how it goes.

  32. Thank you so much for checking and your reply. I tried it again this morning and still failing with that error. I don't have any other script running. It was working before but not sure why it does not work now. Here's a copy of the whole string.

    DataSource.Error: Web.Contents failed to get contents from 'https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?symbol=ADA,AGI,ALGO,ANKR,ATOM,BAND,BAT,BNB,BRD,BTC,CEL,CSC,DAI,DOT,DSLA,ENJ,ETH,FLR,Gzil,HBAR,HOT,ICX,LINK,MATIC,NPXS,OCE,ONE,PAC,POLX,QNT,RSR,SNX,STMX,TFUEL,THETA,UBX,VET,VTHO,WAN,XDC,XLM,XRP,XTZ,ZIL' (400): Bad Request
    Details:
    DataSourceKind=Web
    DataSourcePath=https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest
    Url=https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?symbol=ADA,AGI,ALGO,ANKR,ATOM,BAND,BAT,BNB,BRD,BTC,CEL,CSC,DAI,DOT,DSLA,ENJ,ETH,FLR,Gzil,HBAR,HOT,ICX,LINK,MATIC,NPXS,OCE,ONE,PAC,POLX,QNT,RSR,SNX,STMX,TFUEL,THETA,UBX,VET,VTHO,WAN,XDC,XLM,XRP,XTZ,ZIL

    Reply

  33. Thank you so much Ana, that worked. There was an update on AGI to AGIX. You ROCK!!! I appreciate your time and expertise in this matter. Have an awesome week.

    Reply

  34. It doesn't work. Sorry I wasted an hour on it. Starting with "step 3 - there IS NO API key value provided ANYWHERE.

    Reply

    • Sure, you could use an endpoint like this: https://pro-api.coinmarketcap.com/v1/tools/price-conversion?amount=1&symbol=BTC&convert=USD. That will give you the current price of BTC in USD (you can substitute those symbols for other currencies if you like).
      CoinMarketCap sends back some additional metadata fields in the response, so if you ONLY want to see the price, you could do an HLOOKUP on that field from another tab, like =hlookup("quote.USD.price",'Sheet1'!A1:K2,2,false). Or, if you wanted to do it all in one go, you could add a JMESPath filter to your request like data.{quote:quote}

      As for auto-updating the price, you can check out scheduling.

      Reply

  37. If i am using Symbol to pull lets say prices for 100 coins. All of a sudden 1 coin came up with a wrong / duplicate symbol, hence pulling in the wrong price. Can i update the URL to use symbols for 99 coins and ID for the 1 coin? OR would i have to change the entire URL and update all 100 coins to ID instead of symbol (which is what I am trying to avoid)? When i tried to put symbol and id in the same URL, it gave an error.

    Reply

    • Their documentation says "Alternatively pass one or more comma-separated cryptocurrency symbols" so unfortunately I think it's just one or the other, you can't mix and match.

      Reply

  38. Hi,

    I try to get data for symbol DXT (Dexit finance), but it always displays DXT (data wallet) - which is also the correct symbol. What can I do to get dexit finance values imported?

    Reply

    • Since the symbol isn't unique, you should use the ID instead. You can get the ID with a request URL like this:
      https://pro-api.coinmarketcap.com/v1/cryptocurrency/map?symbol=DXT
      Once you have it, you use it in requests like this:
      https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?id=11661&convert=USD

      Reply

  39. Hi! I'm wondering how to differentiate between coins with the same ticker symbol (e.g. there are 2+ different MIST coins).

    Right now I'm running this query: https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?symbol=MIST&convert=USD, but it's pulling the wrong MIST.

    TYSM for your help!

    Reply

  40. I did this and it is working! thank you!
    but the time stamp of the data is always old not the actual time I make the request. Is there a solution for this?

    Reply

    • Do you mean the timestamp sent back from CoinMarketCap, or the timestamp set by API Connector (info)? The timestamp from API Connector should match the time you made the request, if not please double-check your sheet locale.

      Reply

  41. Hey Ana,

    Have been using the paid version. I need get the market close prices on a particular date for multiple tokens from Coinmarketcap. Is there a specific query for that. Need this urgently.

    Reply

    • Hey Eugene, I checked CoinMarketCap's documentation and it looks like you can get this from a URL like https://pro-api.coinmarketcap.com/v1/cryptocurrency/ohlcv/historical?time_period=daily&symbol=BTC,ETH&time_start=2020-11-29&time_end=2020-11-30

      Please check the documentation link as it describes their requirements for access (you'll need a paid CMC plan).

      Reply

  43. How can we find out the ID for a particular coin? Is it listed on coinmarketcap.com somewhere? Like if I wanted to find the ID of a random coin like "SHIB" for instance.

    Reply

    • And some of the coins I'm trying to find the ID for aren't in the top 5000. So putting https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest?limit=5000 as the API url doesn't work.

      Like this coin for instance: "https://coinmarketcap.com/currencies/metawars/"

      Reply

    • You can do a request to https://pro-api.coinmarketcap.com/v1/cryptocurrency/map?symbol=SHIB,WARS,BTC. As long as you have the symbols, you can list them out to get the IDs.

      Reply

  45. Hi! Is there a way that I can use a symbol + id request? I'm currently working with lots of symbols, but there is only one that have multiple coins on the same symbol. The way I could correct that is by using the id search instead but that would make me change all symbols to ids. So every time I need to add a coin, I need to look for it's symbol?...

    Example:
    https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?symbol=BTC,ETH,SOL,FTM,RAY,SRM,SHIB,RUNE,ATOM,BNB,USDT,CRP

    In this case, I need CRP for CropperFinance, not Crypton.

    Reply

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

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

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

  50. Hi everyone,
    I am trying to get certain variables from coinmarketcap API, such as price and so on.. I am using the line:

    https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?symbol=

    It works well for almost all of the projects I am interested in, but I am having troubles with one symbol A.O.T, project Age of Tanks. For some reason I am not getting anything for this project. It looks like API has some problem with the symbol "A.O.T". Can you show me some tricks how to solve it? Thank you.

    Reply

    • Hmm I'm not sure either, their error message says "symbol should only include comma-separated alphanumeric cryptocurrency symbols" so it seems like a bug on their side related to the periods in the symbol name. I can only get it to work when I use the ID instead like this:
      https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?id=15439

      Reply

  51. Hey there, great article, very helpful thanks a lot !
    Quick feedback, for some reason I can't run the request since 2 days while it was working perfectly before. The error message received is the following :

    ERROR: The request could not be satisfied 403 ERROR The request could not be satisfied. Request blocked. We can't connect to the server for this app or website at this time. There might be too much traffic or a configuration error. Try again later, or contact the app or website owner. If you provide content to customers through CloudFront, you can find steps to troubleshoot and help prevent this error by reviewing the CloudFront documentation. Generated by cloudfront (CloudFront) Request ID: xj3pJgljwmuhrszJ0ZsVg2TKhAxpxxWta_QwcaQCAa06i_rfpGEF4A==

    Would you have any idea where it's coming from ?

    Reply

    • Hi guy's,

      I have the same problem, even if I modify the request it doesn't work.
      If you have found a solution could you explain it and how I can solve it .
      Many thanks

      Reply

      • Quite a lot of people are reporting this error right now, you can see some more reports in our user forum here.
        The error comes from CoinMarketCap, so unfortunately there's nothing we can do on this side besides report the error to them through their contact form. Actually, strangely enough CoinMarketCap requests are still working fine for me. Can you please share the exact request URL you're using so I can test it?

      • Hey Ana,

        Thanks for the reply ! I'm using the exact same url than the one shared by Anonymous, so that's really strange. I'll try submitting a contact form to CMC.

        Cheers,

      • After some back and forth, I received this response from CMC: Thanks for your patience. We can confirm that there was indeed a 403 error due to some backend configuration issues on our end. We've since remedied the issue, and we are seeing a dissipation of 403 errors.
        So hopefully the issue is now resolved.

  53. Hello!
    I have the same problem, but the weird thing is that on the example that you recently posted it works fine, but on my sheet doesn't, do you know what did you update recently ?

    Reply

    • Hey Marco, we haven't updated anything recently. I'm not sure what you mean by the example works, but not in your sheet. Where does it work?

      Reply

    • I donā€™t think thereā€™s an endpoint for that specifically but you can request a coin list from Binance and then compare it to a coin list from CoinMarketCap to see the difference.

      Reply

  56. Hi,

    Thanks for such a great post. It's been very helpful and one of the only APIs I could figure it out. I am having trouble with part 5 (to get more than just the top 100). When I put in the parameters it skipped from coin#100 to coin #1000 and then continued from there. Is there something I am doing wrong?

    Reply

    • Hey Daniel, I'm glad this works for you. I think the issue here is that pagination settings only apply to loops after the first loop, so you need to add your limit parameter to that first request URL too, like this https://pro-api.coinmarketcap.com/v1/global-metrics/quotes/latest?limit=1000.
      Can you please check if that resolves it?

      Reply

  57. Hi,
    Thanks a lot for the deatailed document
    One question i would like to ask is there away to get the price and volume of a coin from CMC to google sheet for smaller time frame like 5,10,15,20,30 min please?
    Thanks for you help

    Reply

  58. Hi Ana- Thanks very much for a great app here. I've sent your site to a few friends as well. Question for you... is there a way to make a single click button on my Google sheet that will trigger the "Refresh All Now" function in the the API connector instead of having to click on Extensions>API Connector>Refresh All Now

    Reply

  59. Hello... I have used the api to keep track of my crytos portfolio. I have a problem. If I include SHDW, it reports the Shadow Token, but I want to have the GenesysGo Shadow, which apparently has the same ticker in coinmarket. To do?

    Reply

  60. Hello - I have been using this for a couple of months but now it is no longer working. Every time I try to refresh the API pull in Google Sheets I get an error saying:

    "Request failed: "data" is not defined."

    When I look at CoinMarketCap it is showing the API pull happens (evidenced by the time stamp and CMC saying I've used credits). However, Google sheets is not working or updating because of this error.

    Reply

    • Hi Kyle, sorry! That was a bug related to (what should have been) a very minor update.
      Can you please refresh your sheet and try again? I noticed and fixed the issue about 10 minutes ago so it should work now, though it might take a few minutes for the update to propagate through to everyone.

      Reply

  61. Hi,

    Thank you Ana. Wonderful job!

    I know a lot of people have already asked about this particular issue of wrong symbol returning by api but I'm using your template and we can only enter symbols in "Inputs" tab.

    Not possible to use id instead? Any workaround?..

    Thank you for your help!

    Reply

    • Hi Alex! Thank you for the suggestion, I just updated the template to accept either IDs or symbols. Please copy it again and you should see the updates (you'll need to import the new JSON file too).

      Reply

  63. Hi! Ana,
    First of all, i would like to thank for your very informative articles.

    How can i get the historical data of the coins such as Bitcoin, Etherium and Eth withinin 90 days?

    Reply

    • Thanks, Khaled!
      CoinMarketCap only provides historical data on their paid plans, so if you're looking for free data, I suggest using a different crypto API like CryptoCompare. With CryptoCompare you can get 90 days of historical data for bitcoin with a URL like https://min-api.cryptocompare.com/data/v2/histoday?fsym=BTC&tsym=USD&limit=90 (please check the linked article for more info)

      Reply

  64. Hello,

    You need to find a solution because we can't use anymore this google sheet.

    Appsheet changed something, and they don't support anymore the free plan

    Reply

  66. Hi Ana,

    Thanks for this great article.
    I have couple of problems, first my Pagination doesnt change, It is on none and I have no option to change it.
    Second how can I get the entire historical data (starting from 2013) till today for all coins?
    P.S. I have educational subscription which allows me to get whole data

    Reply

    • For getting historical data, you should be able to manually change parameters or use pagination handling to automatically cycle through a complete data set, but the specifics depend on the endpoint. Which endpoint are you using?

      Reply

      • Thanks Ana.
        Yes, I just purchased the pagination feature, and now I have access to entire coins but just recent information (mostly last 90 days). what I want is to have the historical daily information for all coins (their daily prices and volume for example).
        I have used many thing but is just not working.
        the endpoint that I am using is cryptocurrency.

      • I don't think CoinMarketCap provides historical daily info for all coins at once. As far as I can see, these are your options:
        1) The Listings Historical endpoint returns all cryptocurrencies on a specific date, like this: https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/historical?Date=2021-01-01&limit=5000&aux=date_added

        2) The OHLCV Historical endpoint returns historical open, high, low, close, and volume data for a list of currencies over a date range, like this: https://pro-api.coinmarketcap.com/v2/cryptocurrency/ohlcv/historical?slug=bitcoin,ethereum&time_period=daily&time_start=2018-09-19&time_end=2022-05-30

        So whichever method you choose, looks like you'll need to run multiple requests to retrieve all the data. Our multi-query request function can help automate that by letting you cycle through a list of currencies or dates.

  67. Thanks Ana. unfortunately none of these URLs works. first goes with error :

    1) AT2: Completed with errors
    - We received an error from coinmarketcap.com (400) show response
    {"status":{"timestamp":"2022-05-30T12:16:01.801Z","error_code":400,"error_message":"\"date\" is required","elapsed":0,"credit_count":0}}

    and second one ends with no results or no error!

    Reply

    • Sorry, I didn't test these first as I don't have a paid CMC account. But the first error message looks like the "date" parameter is case-sensitive, so you just need to change "Date" to "date". I'm not sure why the second one didn't work. If you'd like, please feel free to message support and I can run some tests with you directly in your sheet.

      Reply

      • Thanks Ana, I just submit a form to support.
        I am in hurry as my subscription to coinmarket will end in 7 days. So I would appreciate if you could help me ASAP.

  68. Hi,

    First, I would like to thank you for you job, it's amazing ! I never heard about API...just discovered a wonderful tool!

    I tried some tests with the recommanded endpoint (specific crypto), but I can't find all the crypto I want on the list.

    When I try another recommanded endpoint with all crypto, I can find these cryptos but I don't know how to put them on the previous endpoint ?

    I would like to put KNC, utk and dozen of others. So, do I have to go to edit the and inser my crypto on the code himself or can I had these cryptos on the recommanded formular ?

    Reply

    • Thank you for your nice comment. You can simply type in the cryptocurrency slugs or symbols and hit enter, you aren't limited to those that you see on the dropdown list.

      Reply

  69. Hi Ana,

    Thank you for your brilliant work! I am currently trying to use your template and getting the following error, when trying to refresh the data:

    " 1) Data: Completed with errors
    - We received an error from coinmarketcap.com (401) show response
    { "status": { "timestamp": "2022-07-23T10:48:22.816Z", "error_code": 1002, "error_message": "API key missing.", "elapsed": 0, "credit_count": 0 } } "

    Could you please tell what can I do with it? Thx!

    Reply

  70. getting this error today, anyone else?

    1) active cryptocurrencies: Completed with errors
    - We received an error from coinmarketcap.com (504) show response
    504 Gateway Time-out 504 Gateway Time-out Sorry for the inconvenience. Please report this message and include the following information to us. Thank you very much! URL: http://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest?limit=1000&aux=num_market_pairs%2Cdate_added Server: nginx_dispatcher_rl_0ec5dc4fac2d464d6 Date: 2022/08/13 18:30:45 Powered by Tenginetengine

    Reply

  72. Hi Ana,

    I read almost your comments and feedbacks. I hope that you can help me about API.

    I want to sort API with filter: Marketcap $10M - $100M and 24h Volume ($10M - $100M)

    Thank you, Ana.

    Reply

    • You can add filters to the /listings/latest endpoint. I think this custom request URL should work, please check: https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest?market_cap_min=10000000&market_cap_max=100000000&volume_24h_min=10000000&volume_24h_max=100000000&aux=num_market_pairs,cmc_rank,date_added

      Reply

  73. hi ana,

    thanks for the great work; having a problem looking up certain coins like: RVN (2577), HNT (5665), etc.

    when i put these in the inputs sheet, it returns N/A in the summary?

    Reply

    • I just tested and those IDs seemed to work fine on this side. I wonder if it's gone over some limit, how many coins are you pulling data for? Feel free to contact support if you'd like me to take a look there.

      Reply

  74. Hi, glad I could come across this, I am using the free api in CoinMarketCap

    is there a way to get only one token at a time, with 1h, 1d and 30d and other data to show and does it refresh when i start google sheets

    Reply

    • You can use the /cryptocurrency/quotes/latest endpoint and enter a single value for the slug parameter to get data for one token at a time. However, to get 1h, 1d, and 30d data you'll need to use CMC's historical data endpoint. That one is only available on CMC's Hobbyist and up paid plans.

      Reply

    • I believe you'd use the (paid) endpoint shown here: https://coinmarketcap.com/api/documentation/v1/#operation/getV1ExchangeQuotesLatest. So an example request URL would be https://pro-api.coinmarketcap.com/v1/exchange/quotes/latest?slug=binance,coinbase.
      If you're looking for free data, you can try one of the data sources listed here, e.g. https://api.coinlore.net/api/exchanges/ will get you a list of exchanges with their volume (btw, this API returns data in a somewhat unusual format, you can enter a single * into the JMESPath field to make it print out neatly).

      Reply

  76. hi there, im having some issues with token symbols randomly changing what token they get data for. for instance symbol OCEAN which used to pull data from Ocean Protocol, now pulls data from something called Burst Ocean which is weird because its not even a token listen on coinmarketcap. I can see that potentially happening for tokens that share a ticker, but not sure whats happening here. Is there a way to solve it? thanks!

    Reply

    • You can use the ID instead of the symbol to avoid this issue. The /map endpoint will map symbols to IDs, e.g. https://pro-api.coinmarketcap.com/v1/cryptocurrency/map?symbol=OCEAN returns a data ID of 3911 for Ocean Protocol, and this ID will never change.

      Reply

      • thanks for the reply Ana. How would i do that? I'm currently using the template sheet you provided, where i input the ticker symbols on the one page and then I get a dashboard with the data on the other page

        I noticed that just in the last couple of days a whole bunch of tokens that used to work fine are now pulling the wrong data, i wonder if some IDs have changed on coinmarket cap

        thank you for your help!

      • If you check the Inputs tab, there's a small dropdown selector in cell B3 that lets you choose whether to identify coins by their ID or their symbol. If you toggle it to ID, you can then enter all the coin IDs. It does sound like CoinMarketCap must have pushed through some update that changed a lot of symbols at once, so using IDs will be more reliable going forward. Just let me know if you run into any issues.

  77. ok great thanks for letting me know. My sheet was not showing that option but I just realised its because it was an old version. I downloaded the new one now and I see it. Thanks again for taking the time to help!

    Reply

  78. Hi Ana,

    I am receiving a,

    REPORT STATUS
    1) Data: Completed with errors
    - We received an error from coinmarketcap.com (401) show response

    when trying to Run the request (Extensions >> API Connector >> Refresh All Now)

    to refresh to current prices

    thx

    Reply

    • What does the full error message say when you click "show response"? Generally 401 errors from CoinMarketCap are due to an invalid or missing API key, so please make sure you've entered your key correctly.

      Reply

  79. Heya Ana!

    Thanks for the template, first off! Quick coupla setup questions:

    Where do I set the destination to import the data from my API connector? (What tab/cell, precisely?).

    Also...if I set the coins I have on the Inputs tab, will only those get populated? (I don't really need to watch the top 100).

    Reply

    • You don't need to change the data destination if you're using the template, since it's all already set up in the API request file linked in the sheet (please check the "Information" tab if you missed it). The request file sets the data destination to cell A1 of sheet Data. And that's correct, if you set your coins in the Inputs tab, only those coins will be populated.

      Reply

  80. Hi Ana,
    I have tried over ten times since yesterday to use my Coinmarketcap API - I am on the free plan - to download latest quotes without succcess. Each time I get an error as follows:-
    "Completed with errors
    - We received an error from coinmarketcap.com (400) show response
    {"status":{"timestamp":"2023-12-01T13:33:51.192Z","error_code":400,"error_message":"\"value\" must contain at least one of [id, symbol, slug]","elapsed":0,"credit_count":0}} "

    Please help me to sort out where I am going wrong. Thanks,

    Reply

    • Hey Efam, is this through our preset integration or your own custom reqeust URL? The error message says you need to include the id, symbol, or slug parameter, so I'd make sure one of those is included, e.g.
      https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?id=1,2. If that doesn't resolve the issue, please share how you've configured your request and I can give you more specific feedback.

      Reply

  81. hey Ana, I get the following error across 6 reports. As far as i can tell. nothing has changed on the sheet! Thank you

    4) Data_2_2: Completed with errors
    - We received an error from coinmarketcap.com (400) show response
    {"status":{"timestamp":"2023-12-07T10:30:12.444Z","error_code":400,"error_message":"\"id\" should only include comma-separated numeric CoinMarketCap cryptocurrency ids","elapsed":0,"credit_count":0}}

    Reply

    • Hey Richard, are these reports being run through our preset integration, your own custom request, or the template sheet on this page? If it's the template, can you please check the value in Inputs!D8? It should be a comma separated list of IDs, let me know if that's not what you're seeing.

      Reply

  82. Hi Ana - the list of coins seems to be missing some on my sheet - is there a reason for that?
    For example, Beam (current CMC rank 63), Optimism (39), Arbitrum,

    Is there a limit im not aware of? thanks

    Reply

  85. Hi, Ana
    Please tell me how to tie up the update of the coin exchange rate by now, so that you don't have to click "refresh all now" all the time

    Reply

    • Hi Adrian,

      Mixed Analytics has built-in support for CoinMarketCap (select Application > CoinMarketCap when creating a new request). Go ahead and select the `/v2/cryptocurrency/quotes/latest` endpoint, and add the selected coins under request parameters in the `symbol` field.

      Reply

      • HI, thank you. I think for LABS Group and for zkSwap Finance (ZF) doesn't exist now for API, for the rest works. Wenn i enter one of this for example zkSwap Finance i habe this error.) API-Data: Completed with errors
        - We received an error from coinmarketcap.com (400) show response

Leave a Comment

Jump To