Search API Connector Documentation
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.
The bad thing is that CoinGecko severely rate limits requests… you can see more on that in the “CoinGecko Limits” section. For that reason, I suggest checking out this list of alternative crypto APIs: https://mixedanalytics.com/knowledge-base/top-free-crypto-apis/
- 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
- Part 5: CoinGecko Limits
- Part 6: API Documentation
- 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 get a big list of crypto coins and their current prices.
- API root: https://api.coingecko.com
- Endpoint: /api/v3/coins/markets
- Query String: ?vs_currency=usd
Putting it together, we get the full API Request URL:
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 Extensions > 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 crypto data 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
- specific cryptocurrencies with price, market cap, volume, and other stats
- historical price snapshot of Bitcoin at a specified date
- list of all supported coins with ids, names, and symbols
- list all exchanges and related info (year established, country, trade volume, etc)
- get exchange rates for BTC in every currency
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 query of
rates.*as shown in the screenshot above.
- most-searched coins on CoinGecko in past 24 hours
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.
- With API Connector you can either paginate through these URLs manually as shown above, or loop through them automatically with page parameter pagination handling (paid feature), like this:
- API URL: enter your request URL, including per_page=250
- Pagination type:
- Page parameter:
- Run until: choose when to stop running the request
Part 5: CoinGecko Limits
If you use CoinGecko’s API for a while, you’ll probably come across an annoying problem, error messages like these:
- API server responded with an error (429), error code: 1015
- API server responded with an error (403): error code: 1020
CoinGecko limits requests to 10 calls per second per IP address, as shown in their terms & conditions:
Unfortunately, when you run your requests through API Connector / Google Sheets, you’re more likely to hit these rate limits because all requests running through Google Sheets share the same pool of IP addresses from Google’s servers.
There’s not much we can do about this except wait a minute and try again (and hope that CoinGecko whitelists Google in the future). In addition, make sure you’re combining requests into a single call wherever possible. DON’T smash their servers with thousands of requests as it will make the problem worse for everyone.
If you continue to have issues, I recommend switching to a crypto API that limits by your own API key rather than the shared pool of IP addresses. You can see a list of free crypto APIs here.
Part 6: API Documentation
Official API documentation: https://www.coingecko.com/en/api/documentation
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. Happy data grabbing!