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.

Feel free to also check out our YouTube tutorial here.

CONTENTS

BEFORE YOU BEGIN

Click here to install the API Connector add-on from the Google Marketplace.

PART 1: GET YOUR COINMARKETCAP API KEY

  1. If you haven’t already, navigate to https://coinmarketcap.com/api/ and click Get Your API Key Now
    coinmarketcap-img1
  2. Fill out your details and click Create My Account
    coinmarketcap-img2
  3. A moment later you’ll receive an email. Click on the email to verify your account, and you’ll be redirected into your new CoinMarketCap dashboard. Congrats! You now have access to the CoinMarketCap API. Hover over the API Key box in the top left and copy the key to your clipboard.
    coinmarketcap-img3

PART 2: CREATE YOUR API REQUEST URL

We’ll follow the CoinMarketCap documentation to access the latest crypto quotes.

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

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

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

PART 3: PULL COINMARKETCAP API DATA INTO GOOGLE SHEETS

We can now enter all our values into API Connector and import CoinMarketCap API data into Google Sheets.

  1. Open up Google Sheets and click Add-ons > API Connector > Open.
  2. In the Create tab, enter the API URL we just created.
    coinmarketcap-img4  
  3. Under Headers enter two sets of key-value pairs like this:
    X-CMC_PRO_API_KEYYOUR_API_KEY
    Acceptapplication/json
    Replace YOUR_API_KEY with the value provided above in step 3.

    coinmarketcap-img5

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

PART 4: MORE EXAMPLE API URLS

You can experiment with endpoints and query strings as described in the documentation to see other types of cryptocurrency data, but if you just want to jump in and get a feel for it, play around with the URLs you enter in the API URL path field. Try the following (one at a time).

  • latest global cryptocurrency market metrics
    https://pro-api.coinmarketcap.com/v1/global-metrics/quotes/latest
  • all active cryptocurrencies with latest market data, sorted by 24 hr volume
    https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest?sort=volume_24h
  • metadata (logo, description, official website URL, social links, etc) for BTC and ETH
    https://pro-api.coinmarketcap.com/v1/cryptocurrency/info?symbol=BTC,ETH
  • latest quotes for BTC and ETH, converted to euros
    https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?symbol=BTC,ETH&convert=EUR
    Due to the structure of the underlying JSON, by default the above request will return all selected symbols in a single row. To get each coin in its own row instead, add the following JMESPath: data.values(@)
    coinmarketcap-img11
  • Fundamental Crypto Asset Scores (FCAS) for measuring cryptocurrency project health
    https://pro-api.coinmarketcap.com/v1/partners/flipside-crypto/fcas/listings/latest

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

PART 5: HANDLE PAGINATION

  1. CoinMarketCap limits the number of records returned in each request. By default, only 100 records will be returned unless you use the ‘start’ and ‘limit’ parameters as described in their documentation. The documentation says you can specify a limit of up to 5000, but in my own tests large limits seemed to produce an error, so I used 1000 in the examples below. YMMV.
    coinmarketcap-img7To retrieve paginated records, add these ‘start’ and ‘limit’ parameters like this:
    page 1: https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest?limit=1000
    page 2: https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest?start=1001&limit=1000
  2. With API Connector you can either run these request URLs manually or loop through them automatically with offset-limit pagination handling (paid feature), like this:
    • API URL: enter your request URL as usual, making sure to include limit=1000
    • Pagination type: offset-limit
    • Offset parameter: start
    • Limit parameter: limit
    • Limit value: 1000
    • Number of pages: enter the number of pages you’d like to fetch
      coinmarketcap-img8

APPENDIX [PRO]: COINMARKETCAP TEMPLATE

In this template, everything is configured for you to simply add your API key along with whatever currencies you’re interested in and get a dashboard like below:
coinmarketcap-img10

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

P.S. For dozens of other step-by-step guides for connecting crypto (and other) APIs to Google Sheets, click here: Knowledge Base

IMPORTANT SECURITY NOTE

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

77 thoughts on “Import CoinMarketCap Data to Google Sheets”

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

    Reply

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

      Reply

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

    Woat could be the issue?

    Reply

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

      Reply

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

    Reply

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

      Reply

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

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

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

      • Hello Ana

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

        I made this but I get errors:

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

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

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

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

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

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

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

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

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

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

  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”)

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

  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

  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. Hi – I notice that the API sheet no longer format’s the data sheet.. which in turn means there is no summary.. I followed the article above. But it does not work. Last week it worked. Something changed?
    Thanks for the great work!

    Reply

    • Hi Adrian, are you referring to the template in this article? That template uses a JMESPath query, so you need pro access to API Connector for it to work (you would have had pro access during the 1st week as a trial). Sorry for the inconvenience and let me know if I’ve misunderstood your issue.

      Reply

    • Hey there, if you check the list of example API URLs in the article, I mentioned that the /quotes/latest endpoint always returns data in a long row unless you use a JMESPath query. JMESPath is a pro feature, so if JMESPath was working before, and now it’s not, the most likely reason is that you installed the add-on within the past week. When you install the add-on, you receive a 7-day free trial that activates all features. Sorry for the inconvenience, but I hope that clarifies.

      Reply

  15. Hello,

    I have this working and whilst the first use is great, any subsequent uses the data changes row / column each time. Which makes it impossible to do anything further with this data and link to other sheets etc.

    Is there any way in which it can just ‘update’ the fields as per the original? Thanks

    Reply

    • 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

    • 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

  20. Looks like an API change that has pushed all crypto into a single line on the template sheet and broken the sheet.

    I have tried to work it out but no luck.

    Any thoughts, advise?

    Thanks

    Reply

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

  23. Not working as of March 8th 2021, the data populated on “Data” sheet somehow is being populated horizontally rather than vertically (it was working before on March 7th and before).

    Reply

    • Hi Dylan, sorry, the report template uses a JMESPath query to reformat the data, so you need pro access to API Connector for it to work. If JMESPath was working before, and now it’s not, the most likely reason is that you installed the add-on within the past week. When you install the add-on, you receive a 7-day free trial that activates all features.

      If you’re using the free version of API Connector, I suggest trying a different crypto API that sends back the data in a more friendly way, for example you can find a very similar template for CoinGecko here: https://mixedanalytics.com/knowledge-base/import-coingecko-data-to-google-sheets/

      Reply

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

  25. Hello,
    I built a spreadsheet to keep track of my investment vs return for crypto. The problem I’m having is that every time the data updates, the crypto is rearranged and pulling the wrong prices into the main sheet. Is there a way for me to link each “current price” where all that is pulled is the current price of that specific crypto without tons of extra columns of info? I’ve tried following various tutorials but they’re not working.

    Reply

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

      Reply

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

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

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

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

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

Leave a Comment