Import CoinGecko Data to Google Sheets
In this guide, we’ll walk through how to pull cryptocurrency market data from the CoinGecko API directly into Google Sheets, using the API Connector add-on for Sheets. The cool thing about CoinGecko is that their API is free and open, so we won’t need any API key. They also provide some unique API endpoints like most-searched coins.
If you want to skip the details, you can jump right to the end to grab your own copy of the CoinGecko crypto tracking template. You can also check out our YouTube tutorial video here.
- Before You Begin
- Part 1: Create your API Request URL
- Part 2: Pull CoinGecko API Data into Sheets
- Part 3: More Example API URLs
- Part 4: Handle Pagination
- Appendix: CoinGecko Template
BEFORE YOU BEGIN
Click here to install the API Connector add-on from the Google Marketplace.
PART 1: CREATE YOUR COINGECKO API REQUEST URL
We’ll first follow the CoinGecko API documentation to access the current price of ETH (Ethereum).
- API root: https://api.coingecko.com
- Endpoint: /api/v3/coins/markets
- Query String: ?vs_currency={currency}&ids={coin ID}
Query String Example: ?vs_currency=usd&ids=ethereum
Putting it together, we get the full API Request URL:
https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&ids=ethereum
PART 2: PULL COINGECKO API DATA INTO SHEETS
Now let’s paste that URL into API Connector and import some crypto 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.
- Leave the Headers section empty. We can leave authentication set to None, too.
- 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 exchange rate data for Ethereum to USD appear in your Google Sheet:
PART 3: MORE EXAMPLE API URLS
Experiment with endpoints and query strings as described in the documentation to see other types of currency and crypto data from the API. For example, you can try the following URLs, one at a time (substitute in other coin IDs or currencies, if you like).
- current price of bitcoin in USD
https://api.coingecko.com/api/v3/simple/price?ids=bitcoin&vs_currencies=usd
- all supported cryptocurrencies with price, market cap, volume, and other stats
https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd
- specific cryptocurrencies with price, market cap, volume, and other stats
https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&ids=bitcoin,ethereum,ripple,eos
- historical price snapshot of Bitcoin at a specified date
https://api.coingecko.com/api/v3/coins/bitcoin/history?date=30-12-2018
- list of all supported coins with ids, names, and symbols
https://api.coingecko.com/api/v3/coins/list
- list all exchanges and related info (year established, country, trade volume, etc)
https://api.coingecko.com/api/v3/exchanges
- get exchange rates for BTC in every currency
https://api.coingecko.com/api/v3/exchange_rates
By default the /exchange-rates request URL will return all exchange rates in a single row. To split the data into a table, you can add in a JMESPath (paid feature) query ofrates.*
as shown in the screenshot above. - most-searched coins on CoinGecko in past 24 hours
https://api.coingecko.com/api/v3/search/trending
PART 4: HANDLE PAGINATION
- For several endpoints, CoinGecko limits the number of records returned in each response. By default, only 100 records will be returned unless you use the ‘page’ and ‘per_page’ parameters as shown in their documentation.
To get 250 records, you’d use the ‘per_page’ parameter and to return more than 250 you’d then make a second request using the ‘page’ parameter. For example:
page 1:https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&per_page=250&page=1
page 2:https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&per_page=250&page=2
- With API Connector you can either run these URLs manually or loop through them automatically with page parameter pagination handling (paid feature), like this:
- API URL: enter your request URL as usual, making sure to include per_page=250
- Pagination type:
page parameter
- Page parameter:
page
- Number of pages: enter the number of pages you’d like to fetch
APPENDIX: COINGECKO TEMPLATE
In this template, everything is configured for you to simply type in whatever coins you’re interested in and get a dashboard like below:
You can jump right to a copy of the template here (click File > Make a copy to make your own copy). Happy data grabbing!
P.S. For dozens of other step-by-step guides for connecting crypto (and other) APIs to Google Sheets, click here: Knowledge Base
Hey so i did this but it seems like it doesn’t keep up with the price fluctuations and it just stays at the original price when i set up the API, do you know the reason why this is happening?
Hi there, the sheet doesn’t automatically pull in new data from the API. To get fresh data, you need to click Run or set up scheduling. The IMPORTAPI custom function also has some auto-refresh functionality. Let me know if anything isn’t clear and I’ll be happy to explain further.
Once I refresh the data, the sequence in which the data is downloaded is not always the same. For example, if I use the code https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&ids=ethereum,bitcoin,simple-token,ripple,litecoin,stratis,waves,litecoin-cash,holotoken,celo-gold
If I add another 10-20 tokens to the end of that code, the list appears in a different sequence each time. This makes it difficult to use this data in other sheets (since the reference cell no longer points to the correct token)
Hey Nik, 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 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.
Thank you for the tip, that was brilliant and it fixed the issue! Did not know about VLOOKUP so found a simple youtube tutorial.
Awesome! Next you can learn about MATCH, that will really blow your mind 😀
Hi!
So I ran a market API and only got back 200 coins! can I at least get back the first 1000 or 2000?
Good question. For markets, by default CoinGecko limits the amount of records they return at once. To get more you need to add
&per_page=250&page=1
,&per_page=250&page=2
,&per_page=250&page=3
, and so on to the end of your URL. I’ve just updated the article to reflect this.thank you!
Hi
I used this API
https://api.coingecko.com/api/v3/exchange_rates
But all the rates return in one row – is there a way to make a list with each rate on its own row….
🙂
Hey there, this is due to the way they’ve structured their response data. If you check it in an API tool that returns the raw JSON (e.g. Swagger), you’ll notice that the response from CoinGecko uses named objects where normally you would see arrays. This prevents API Connector from recognizing where to split the data into columns vs. rows when converting the JSON response into Sheets format.
In any case, to answer your question, you can use JMESPath, which is a very useful query language that allows you to filter and restructure the response data. The query
rates.*
removes the object names so that the response can be parsed correctly. I added a screenshot of this to the article.Note that JMESPath filtering is a paid feature, if you’re trying to do this for free, one other option is to create a second sheet containing a Sheets function like
=transpose(Exchange_rates!1:2)
to convert the response from one long row to one long column. I don’t think it’s a great solution but it’s still a bit better than the default response.Hi Ana
I came up with 3 solutions for this problem.
Solution 1 & 2 require you to enter the formula in the first Row of 4 cells. Then drag the formula down in this case for 61 Rows since there are 61 rates returned by the API (a horizontal row of 4×61=244 cells)
Solution 3 returns the whole table,
after you enter the formula in the top left cell where you want the table
Solution 1:
=INDEX($7:$7,(ROW()-11)*4+COLUMN())
Solution 2:
=OFFSET(A$7,0,(ROW()-11)*4)
Solution 3:
Note this assumes the API data is returned on Row A7:7 – just change that to suit your Row.
=arrayformula( query( query( iferror( if( {1,1,0}, floor( mod(row(A:A)-{1,1},{9^9, 4}), {4,1} ), transpose( split( regexreplace( query( transpose( query( transpose(A7:7 & char(9)), “”, 9^9 ) ), “”, 9^9 ), “\s+$”, “” ), char(9) & ” “, ) ) ) ), “select max(Col3) where Col3 ” group by Col1 pivot Col2″, 0 ), “offset 1”, 0 ) )
Solution 3 is care of the experts at Docs Editors Help.
Nice, thank you for sharing!
In the message above showing Solution 3 I was using the following
URL Path = https://api.coingecko.com/api/v3/exchange_rates
To use the Solution 3 above you need two Google Sheets
1 – Results Sheet (This is where the table will display)
2 – Data Sheet (This is where the API writes its ouput)
Also where you see the function – transpose(A7:7 – this shows my data was being read from row7 – BUT –
You should point to the sheet holding your Data with – transpose(‘Data’!A2:2 & char(9)
Results Sheet
=============
Col A Col B Col C Col D
Row(1) rates » btc » name rates » btc » unit rates » btc » value rates » btc » type
Row(2)
Data Sheet
==========
This is where the API Connctor will put the data.
1 – URL Path = https://api.coingecko.com/api/v3/exchange_rates
2 – Output Option = Overwrite
This means the data will always be written on to Row(2) starting with Cell A2
Is it possible to use the template but expand it to go beyond just 30 inputs? (I have around 100 coins I want to track). Can anyone help with what changes I would need to make?
Thanks
Should be possible, just do the following:
1) in the Inputs tab, add in as many rows as you want
2) in the Summary tab, change the Query formula in cell B8 to read from your new input rows
Hi there!
I’m using this code:
https://api.coingecko.com/api/v3/simple/price?ids=bitcoin,ethereum,litecoin,aave,morpheus-network,sparkpoint,zcash,0x&vs_currencies=usd
and for some reason it doesn’t pull zcash or 0x – I found some other ids didn’t pull as well. Any tips? Thanks!
Oh weird, there it goes. Before it was saying error 429
429 errors mean you’re hitting their rate limits. The rate limit for the CoinGecko API is 10 calls each second per IP address. Unfortunately it’s easy to run into these limits, since all Sheets calls are routed through Google’s IP address pool, meaning other people are using the same resources. It should work if you wait a minute and try again.
Hi Ana,
I’m using the template you shared and it all works great except super randomly, ren is ignored and doesnt print into the sheet, i’ve tired ren, REN, REN (REN), no juice.
Any idea why? I tried a few other random coins on CG and they work fine. https://www.coingecko.com/en/coins/ren
Many thanks, Marc
Ok hmm worked it out, it’s so opaque though, I used https://api.coingecko.com/api/v3/coins/list
and then hunted down “ren” and finally figured maybe it was ‘republic’ and sure enough it’s “republic-protocol”, but idk why they dont share that on the page for their API users. Cheers.
Good question, I checked and I think it’s because this request sheet is set up to look for the ID rather than the symbol. In this case, the ID is “republic-protocol” while the symbol is “ren”. In many cases the ID and symbol are the same, so that’s why this problem isn’t always evident. I’ll try to make that more clear.