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.
Mixed Analytics users can currently get 20% off any CoinGecko API Plan by subscribing with the offer code MIXEDANALYTICS20.
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 MIXEDANALYTICS20 offer code for 20% off any API 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, selectCoinGeckofrom 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.
- UnderAuthorization, 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 hitRunto 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 APIdocumentation. Here's an example request setup:
- Open up Google Sheets and clickExtensions > 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 tox-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
- Application:
- Create a new tab and clickSet currentto use that tab as your data destination.
- Name your request and clickRun. 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, like this:
- Pagination type:
page parameter
- Page parameter:
page
- Run until: choose when to stop running the request
- Pagination type:
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!