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 they severely rate limit requests through Google’s servers… you can see more on that in the “CoinGecko Limits” section.
- 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
- 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 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 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 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 (paid feature) 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.
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:
- 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:
- Number of pages: enter the number of pages you’d like to fetch
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
The 403 error just started appearing late July 2021 and we are still learning about it. A 429 error is a rate limit error. 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.
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!
P.S. For dozens of other step-by-step guides for connecting crypto (and other) APIs to Google Sheets, click here: Knowledge Base