Search API Connector Documentation

Print

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

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

https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest?aux=cmc_rank

Part 3: Pull CoinMarketCap API Data into Google Sheets

Now let’s get that data into Sheets.

  1. Open up Google Sheets and click Extensions > API Connector > Open.
  2. In the Create tab, enter the API URL we just created.
    coinmarketcap-url  
  3. Leave OAuth authentication set to None. Under Headers enter two sets of key-value pairs like this:
    X-CMC_PRO_API_KEYYOUR_API_KEY
    Acceptapplication/json
    .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-results
  6. (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.
    coinmarketcap-editfields

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
    https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?symbol=btc,eth&aux=cmc_rank
    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: data.values(@)
    coinmarketcap-jmespath
  • latest overall global market metrics
    https://pro-api.coinmarketcap.com/v1/global-metrics/quotes/latest
  • metadata (logo, description, official website URL, social links, etc) for BTC and ETH
    https://pro-api.coinmarketcap.com/v1/cryptocurrency/info?symbol=BTC,ETH
  • Get a list of all symbols and IDs
    https://pro-api.coinmarketcap.com/v1/cryptocurrency/map
  • Fundamental Crypto Asset Scores (FCAS) for measuring cryptocurrency project health
    https://pro-api.coinmarketcap.com/v1/partners/flipside-crypto/fcas/listings/la

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

Here is the template link.

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

174 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

  13. Hey Stuart, API Connector returns the data in the order it comes back from the API. If the problem is with columns shifting, please check this article for some suggestions on how to handle these cases where the API doesn’t keep the data order consistent (check the section called “Columns Shifting”). If the problem is with rows shifting, then you can choose from many different Sheets functions to handle this. The most popular is VLOOKUP, which lets you look for a value in a sheet rather than relying on a fixed position. Basically it’s very common for source data to shift around (for any type of data set, not just this one), so you generally need to build a summary sheet that looks up the data it needs rather than relying on data always being in exactly the same cell.

    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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  61. 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, I’m so 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

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

      • Hi Ana,

        One more issue. How do you update the coins list? I want to add new token id’s that are not in your tab.

        Can you add a script to do that?

        Thank you for your help

      • Hey Alex, you can run https://pro-api.coinmarketcap.com/v1/cryptocurrency/map to see all coins and their IDs.

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

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

Leave a Comment

Table of Contents