How to Import Crypto Prices in Google Sheets (With Examples) | CoinGecko API
Google Sheets doesn’t natively support JSON data, which means it may be slightly tricky to fetch real-time crypto price data into your spreadsheet. Thankfully, no-code API connectors by Mixed Analytics and Apipheny make it extremely easy to help you pull crypto data from our API, in just a few clicks!
In this guide, learn how to:
- Connect Google Sheets to CoinGecko API with an API Connector
- Import a list of coin ids and symbols
- Get live crypto price data for specific cryptocurrencies
- Retrieve historical crypto prices for specific coins
- Fetch total crypto market cap data
- Pull live coin price data for trending categories
We will also briefly cover how you can refresh the data in your spreadsheet, and address potential rate limitations and what you can do about it.
Let’s jump in!
How to Use an API Connector to Pull Data into Google Sheets
To use an API connector to pull data into Google Sheets, follow these 4 steps:
- Install an API Connector add-on from Google’s Marketplace.
- Run the extension from your Google Sheet.
- Enter your API key in the connector.
- Execute and run the query.
In this guide, we’ll walk through how to use the API Connector by Mixed Analytics (CoinGecko users get 30% off the lifetime subscription with the offer code “COINGECKO”).
Following the first step, let’s install the add-on from Google’s Marketplace.
Once the add-on has been successfully installed, create a new Google Sheet. In the top navigation bar, select Extensions and click ‘Open’.
A panel will slide out from the right. Click on the ‘Create’ tab. Under ‘Application‘, you’ll see two connections: CoinGecko and CoinGecko Pro. Select the first option (‘CoinGecko‘) if you’re using the free API key generated through your Demo account. If you’re on any of our paid API plans, select the second option (‘CoinGecko Pro‘).
Under ‘Authorization‘, enter your API key as generated from your Developer’s Dashboard.
There may be some instances where a custom integration is unavailable on the API Connector. For these cases, select ‘Custom‘ under Application and you’ll find the following fields to configure your API call request. The commonly used ones in this guide will be:
- Request URL
- Output settings Destination sheet and cell
- Output options
- Naming the request
With that, your spreadsheet is now fully set up and ready to import some crypto price data!
How to Import a List of Coin IDs & Symbols
Referencing our crypto API documentation, we’ll be using the /coins/list endpoint to import a list of coin IDs and symbols. This is a navigational endpoint that will return a list of coin ‘id’s, which are unique identifiers of crypto assets on CoinGecko. This is especially useful as a reference point when retrieving crypto price data later on.
On the API Connector, fill in your Demo API key and select the /coins/list endpoint.
To include the platforms’ contract addresses, we’ll toggle the ‘include_platform‘ request parameter to ‘true‘.
Finally, specify where in the sheet you’d like the data to be generated and hit the ‘Run‘ button at the bottom right to execute the query.
An extensive list of coins data will populate your Google sheet – with that, we now have a comprehensive coin list directory to reference ids and symbols, to fetch all sorts of crypto price data.
Now that we have a comprehensive list of coin IDs and symbols, we can reference coin ids and get prices for specific cryptocurrencies in the next section.
How to Import Live Crypto Price Data into Google Sheets
The easiest way to import live crypto price data into Google Sheets is using the popular CoinGecko API endpoint ‘/simple/price’. This endpoint fetches real-time crypto prices for multiple coins with just one API call, and is a publicly accessible API endpoint.
As this endpoint has been integrated on API Connector, select it and fill in the request parameters accordingly, based on the cryptocurrencies you’d like to retrieve data for.
You are also required to specify the output currency in the ‘vs_currencies‘ parameter.
For this tutorial, we’ll be retrieving coin data in USD for: Apecoin, Arbitrum, Bitcoin, Dogecoin, Ethereum, Matic Network (Polygon) and Solana.
Running the query with ‘default (single row)‘ checked as the report style will result in the data appearing as such, with a new column for each nested element.
As this is not ideal especially for multiple coin queries, let’s toggle the report style to ‘grid‘ instead, and tick the ‘force rows‘ checkbox.
Execute the query again and get neatly organized data in a table format – we now have real-time crypto price data easily accessible from Google Sheets!
💡Pro-Tip: While only a few data points are shown above, you are able to expand your query by specifiying inputs in the request params. Specifically for the /simple/price endpoint, you can select ‘true’ to include 24h price changes, 24h trading volume, last updated time, market cap and even API call metadata like the call timestamp, request URL and body.
Fetching Live Crypto Prices for Coins in Trending Categories
In order to identify all categories on CoinGecko, we’ll use the endpoint /coins/categories. Although this endpoint has been integrated on API Connector, we will demonstrate how to query the pro root URL using the ‘Custom‘ application for this section.
Input the following Request URL in the API Connector accordingly and execute the query.
https://api.coingecko.com/api/v3/coins/categories
The full list of categories on CoinGecko with market cap data will now populate in the spreadsheet:
You may format values in column F, G and H with the =IMAGE([CELL],1) function to visualize the token logos, since the original values are hosted logo image links of each token. The ‘1’ at the end of the function simply refers to resizing the image to fit inside the cell, maintaining aspect ratio.
Now that we have a list of all categories, we can either sort it by 24 hour market cap change on the spreadsheet, or reference what’s trending in the last 7 days on our Top Crypto Categories by Market Cap page. A quick sort shows us that TRY Stablecoin, Kommunitas Launchpad tokens and Discord Bots are trending in the last 7 days.
For this example, we’ll pull out price data for all 15 coins in the Discord Bots category. We will first identify the category id, based on the earlier called data.
Navigate to the API documentation and input ‘discord-bots’ in the category parameter.
Upon running the query, you’ll get the following Request URL:
https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&category=discord-bots&order=market_cap_desc&per_page=100&page=1&sparkline=true&price_change_percentage=1h%2C24h%2C7d&locale=en&precision=full
If you’re using the free API key on the Demo plan, be sure to append ?x_cg_demo_api_key=YOUR_API_KEY and replace YOUR_API_KEY with your own API key, at the end of the query. This means your final Request URL will appear as:
https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&category=discord-bots&order=market_cap_desc&per_page=100&page=1&sparkline=true&price_change_percentage=1h%2C24h%2C7d&locale=en&precision=full?x_cg_demo_api_key=YOUR_API_KEY
If you’re a Paid API user, your Request URL will call from the pro-api root URL and appear as:
https://
Read More:How to Import Crypto Prices in Google Sheets (With Examples) | CoinGecko API
