Import CoinMarketCap Data to Google Sheets
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
- Part 1: Get your CoinMarketCap API Key
- Part 2: Create your API Request URL
- Part 3: Pull API data into Sheets
- Part 4: More Example API URLs
- Part 5: Handle Pagination
- Appendix [PRO]: CoinMarketCap Crypto Portfolio Template
BEFORE YOU BEGIN
Click here to install the API Connector add-on from the Google Marketplace.
PART 1: GET YOUR COINMARKETCAP API KEY
- If you haven’t already, navigate to https://coinmarketcap.com/api/ and click Get Your API Key Now
- Fill out your details and click Create My Account
- 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.
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.
- Open up Google Sheets and click Add-ons > API Connector > Open.
- In the Create tab, enter the API URL we just created.
- Under Headers enter two sets of key-value pairs like this:
Replace YOUR_API_KEY with the value provided above in step 3.X-CMC_PRO_API_KEY YOUR_API_KEY Accept application/json - Leave authentication set to None. Create a new tab and click ‘Set current’ to use that tab as your data destination.
- Name your request and click Run. A moment later you’ll see the latest market data for all active cryptocurrencies in your Google Sheet:
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
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:https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?symbol=BTC,ETH&convert=EUR
data.values(@)
- 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
- 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.
To 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
- 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
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:
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
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?
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 thestart
parameter.I’ll update the article to reflect this.
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?
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?Good Article,Thank you Ana.
great article, i just wanted to know can we create forecast report using live price which will automatically update ?
Hi there, if you want data to automatically update you can either set up up scheduling (paid feature) or use the IMPORTAPI() function.
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.
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?
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.
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.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
I don’t think CoinMarketCap provides an option to get all at once. You need to specify IDs in a comma separated list, like this:
https://pro-api.coinmarketcap.com/v1/cryptocurrency/info?id=300,1,2
Alternately, you can list the slugs or symbols as described here: https://coinmarketcap.com/api/documentation/v1/#operation/getV1CryptocurrencyInfo
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
anddata » 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.
Amazing! It worked pretty well
Thanks a lot Ana. Have a wonderful day 🙂
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)?
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 theid
parameter. Btw, I recommend using compact report style (under Output Options) to make the output easier to read.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!
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…
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.
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 🙂
Automatic updates are handled by scheduling. You can click on the Scheduling tab to set that up, and see this article for more info: https://mixedanalytics.com/knowledge-base/api-connector-scheduling/
As another option, you can look into API Connector’s feature IMPORTAPI. It’s a different feature from scheduling, but it has some data refresh capabilities. You can see this article for more info on that (check the section called Fast Cell-Based Refresh): https://mixedanalytics.com/knowledge-base/importapi-custom-sheets-function/
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!
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?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?
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.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
Hi Angel, no need to apologize, I’m happy to help : )
Instead of referencing cells directly, you can use VLOOKUP, that way it doesn’t matter if the position of the coins change. Here’s one article about how VLOOKUP works: https://exceljet.net/excel-functions/excel-vlookup-function
If that isn’t clear, feel free to share your sheet with edit access, I can then help you in your sheet directly.
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.
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 usehttps://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?slug=the-graph
.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!
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.
Hi
I have been using this call:
https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?symbol=BTC,ETH&convert=GBP
……succesfully for a few days.
As of yesterday two things have changed:
1) the data is being offset by 6 cols containing
“2021-02-02T10:39:57.450Z 0 15 1 ”
2) The data used to come on a row for each currency named e.g. BTC, ETH but now it comes in one long string
Has something changed ?
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.
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
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.
How often do the prices update?
You can check CoinMarketCap’s docs for specifics, but their faq says “Most endpoints update every 1 minute”.
Hi All, how do I get this to change prices in real time. Thank you. https://docs.google.com/spreadsheets/d/e/2PACX-1vSOvzpKY_Zbq5vpzFgAXZAnktchaBTJj6_1LWiLbos7jQnJuEmvWfDvYkvafiLTb4OXhjvpn5D_xYIL/pubhtml#
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).
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)
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.
MUCHAS GRACIAS
Hi There!
just wanted to ask if there’s a way to get all the coins listed which have a market cap of $5M – $10M
Sure, you can check their documentation about it here: https://coinmarketcap.com/api/documentation/v1/#operation/getV1CryptocurrencyListingsLatest
Basically you’ll need to enter a URL like this:
https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest?market_cap_min=5000000&market_cap_max=10000000
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
Hey Jason, the template on this page only works with pro API Connector accounts since it requires JMESPath to reformat the data. Without that JMESPath query, it will all line up on a single line like you found. 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/