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 they provide a huge amount of well-organized crypto data, including some unique API endpoints like most-searched coins. This tutorial will show how to get an API key before fetching data.
Contents
- Before You Begin
- Part 1: Get Your CoinGecko API Key
- Part 2: Pull CoinGecko API Data into Sheets
- Part 3: Create a Custom Request
- 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: Get Your CoinGecko API Key
- To get started, you’ll need a CoinGecko account, so, if you haven’t already, create an account and log in to coingecko.com.
- Once you’re logged in, navigate to https://www.coingecko.com/en/api/pricing.
- To subscribe to a paid API plan, click one of the Upgrade buttons in the pricing table. Alternatively, get a free API key by clicking Create Demo Account underneath the pricing table.
- If you select a paid plan, you’ll see the Billing Info form on the left (use the MIXEDANALYTICS offer code for a free month of the Analyst plan). If you are creating a free demo account, you’ll see the form on the right instead.
- Either way, once your account is set up, navigate to the Developer dashboard and click +Add New Key
- You’ll be prompted to label your key and click Create
- Your API key will now be listed on the page. Copy this key and keep it safe as we’ll use it shortly!
Part 2: Pull CoinGecko API Data into Sheets
The easiest way to get started with the CoinGecko API is through API Connector’s built-in integration.
- In Sheets, open API Connector and create a new request (Extensions > API Connector > Open > Create request)
- If you’re using a free API key through a Demo Account, select CoinGecko from the drop-down list of applications. If you are using a paid CoinGecko API plan, select CoinGecko Pro. The CoinGecko Pro application contains some unique endpoints that aren’t accessible to free users.
- Under Authorization, enter your API key.
- Choose an endpoint. For example, select /coins/markets, which is the endpoint for fetching the latest market data.
- In the parameters section, select which “vs_currency” you’d like to use.
- Optionally select other parameters, e.g. select specific coin IDs or set per_page to 250 to get more than 100 records.
- Choose a destination sheet, name your request, and hit Run to see the response data in your sheet.
- Optionally open the field editor to filter out any unnecessary fields from your report.
Part 3: Create a Custom Request
Alternatively, you can run your own custom requests instead of using API Connector’s pre-built integration, using any of the API URLs shown in the API documentation. Here’s an example request setup:
- Open up Google Sheets and click Extensions > API Connector > Open > Create request.
- In the request form enter the following. If you’re using CoinGecko’s paid API, change the base URL to https://pro-api.coingecko.com/api/v3/, and the header key to x-cg-pro-api-key.
- Application: Custom
- Method: GET
- Request URL: https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd
- Headers:
- x-cg-demo-api-key: your_key
- 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 a list of coins populate your sheet.
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 set the per_page parameter to 250:
- If you need more than 250 records, you can loop through multiple pages automatically with page parameter pagination handling (paid feature), like this:
- Pagination type: page parameter
- Page parameter: page
- Run until: choose when to stop running the request
Part 5: CoinGecko Limits
Update: As of October 2023, CoinGecko now supports API keys. You can now get your own personal API key to avoid running into shared rate limits!
CoinGecko does allow access to their API without a key. However, if you run requests through Google Sheets without an API key, you’ll probably come across error messages like these:
- API server responded with an error (429), error code: 1015
- API server responded with an error (403): error code: 1020
This is because CoinGecko applies rate limits, as shown in their terms & conditions.
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. Therefore, get a key first to avoid running into issues.
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!