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 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 you’ll need a pro account with API Connector for it to work. 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.

52 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. That’s strange, could you please send a screenshot of what you’re seeing? Which browser are you using?

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

    Reply

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

    Reply

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

      Reply

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

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

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

      • Hello Ana

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

        I made this but I get errors:

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

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

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

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

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

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

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

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

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

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

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

    Reply

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

      Reply

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

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

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

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

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

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

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

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

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

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

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

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

Leave a Comment