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
- Fetchtotal crypto market cap data
- Pulllive coin price data fortrending 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 keyas generated from your Developer's Dashboard.
💡Pro-Tip: This step-by-step guide walks through how you can go about creating a Demo API account and generating your free API key.
There may be some instances where a custom integration is unavailable on the API Connector. For these cases, select 'Custom' under Application andyou’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 ourcrypto 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/listendpoint.
To include the platforms' contract addresses, we'll toggle the 'include_platform'request parameter to 'true'.
Finally, specifywhere in the sheet you'd like the data to be generated and hit the'Run'button at the bottom right to execute the query.
💡Pro-Tip: If you're a Paid API subscriber, select 'CoinGecko Pro' under Application and follow the same steps above. The API Connector will automatically call the pro API root URL https://pro-api.coingecko.com/api/v3/.
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.
💡Pro-Tip: The CoinGecko team provides a publicly accessible list of coin ids and symbols,so you don’t necessarily have to generate your own.
Now that we have a comprehensive list of coin IDs and symbols, we can reference coinids 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 thepopular CoinGecko API endpoint'/simple/price'. This endpointfetchesreal-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 fromGoogle 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.
FetchingLive 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 onAPI 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 thefree API key on the Demo plan, be sure to append ?x_cg_demo_api_key=YOUR_API_KEYand replaceYOUR_API_KEYwith your own API key, at the end of the query. This means your finalRequest 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 andappear as:
https://pro-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_pro_api_key=YOUR-API-KEY
Copy this and head over to API Connector. Create a new request and paste this into the Request URL field to call the API. All cryptocurrencies listed in this category now show up in this sheet, and you can schedule a time to automatically refresh the data in this sheet or manually refresh it through the add-on.
Let’s move on now to importing historical crypto prices and the total crypto market cap into Google Sheets.
How to Pull Historical Crypto Price Data into Google Sheets
To import historical cryptocurrency prices into Google Sheets, use CoinGecko API's/coins/{id}/market_chart endpoint and access up to10 years worth of historical crypto data, from April 2013 to date.
Traders often leverage spreadsheets to build up a database of historical prices and other information, which can help with analysis and backtesting crypto trading strategies. These endpoints make pulling out historical crypto prices for specific coins like Bitcoin, Ethereum and so on, an extremely straightforward process.
Once again, navigate to the API documentation and find the endpoint /coins/{id}/market_chart. In this example, we’ll query 14 days of historical Bitcoin (BTC)price data, with a daily granularity.
Fill in the parameters based on your desired data output and append your Demo API key at the end ofthe Request URL:
https://api.coingecko.com/api/v3/coins/bitcoin/market_chart?vs_currency=usd&days=14&interval=daily&precision=full?x_cg_demo_api_key=YOUR_API_KEY
If you’re a Paid API user, your Request URL will appear as such. Similarly, append your Pro API key at the end of the Request URL:
https://pro-api.coingecko.com/api/v3/coins/bitcoin/market_chart?vs_currency=usd&days=14&interval=daily&precision=full&x_cg_pro_api_key=YOUR-API-KEY
As this endpoint is not integrated into API Connector, we'll be using their 'Custom' application once again. We'll copy and paste theRequest URLinto its respectivefield within the add-on.
Before executing the query, we'll expand ‘Output options’ and select a ‘grid’ reportingstyle, which will help to arrange the output data in a grid-like table.
Run the request and the data should populate accordingly in your destination cell and sheet.
In each cell, two values are returned:
The first value is the returned timestamp data in the UNIX milliseconds format. You may use tools like UNIX Epoch Converters to convert the UNIX timestamp data into a human readable date. Alternatively, follow the subsequent steps to format your sheet.
Use the following formula to remove the ‘[] and ‘]’ brackets, and split the values by the comma separator.
=SPLIT(SUBSTITUTE(SUBSTITUTE($A3,"[",""),"]",""), ",")
Apply the formula to the rest of the rows accordingly.
Price values are now split into its separate columns (F and G).
To convert UNIX timestamps to a human readable date and time, apply this formula: =EPOCHTODATE(F3,2)
Now that we have a clean date and time column, let’s move on to extract the data for Market Cap and Volume. Since the UNIX timestamp is repeated, we’ll now use the LEFT function to find the position of the first comma and remove data up to that point, effectively removing the first value from the string.
=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($B3,"[",""),"]",""), LEFT(SUBSTITUTE($B3,"[",""), FIND(",", SUBSTITUTE($B3,"[",""))), ""))
Copy the formula across the rest of the rows in column H, and use the same function on cell I3, mapped to the data on cell C3, for Volume.
This can be quickly replicated across other cryptocurrencies – simply reference the Coin List you’ve generated earlier, find the coin id and replace it in the Request URL accordingly.
How far back does the historical crypto data go?
You can access upto 10+ years of historical crypto price datasince April 2013, with the /coins/{id}/market_chart endpoint. However this is subject to availability, as certain coins may not have the full range of data points if the exchange is not supported at the time of trading commencement.
To get the full 10+ years of historical data, set the daysparam as 'max'.It's worth noting that depending on your inputs for the 'days' parameter, data retrieved will be of various granularities:
- Within 1 day from now = data granularity is at 5 minute intervals
- Between 1-90 days from now = data granularity is at hourly intervals
- More than 90 days from now = data granularity is at daily intervals, at 00:00 UTC
💡Pro-Tips:Use the /coins/{id}/market_chart/range endpoint to get a list of historical price and market data for a specific coin, for a specified date range in UNIX Timestamp between ‘from’ and ‘to’. You may even choose to pull out historical price data by contract addresses with these endpoints:
- /coins/{id}/contract/{contract_address}/market_chart
- /coins/{id}/contract/{contract_address}/market_chart/range
Import Total Crypto Market Cap Data into Google Sheets
You can fetch the total crypto market capitalization data using the/global/market_cap_chart CoinGecko APIendpoint, which will return the historical global market cap and volume data by a specific number of days away from now.This is another popular endpoint and its equivalent page on CoinGecko is this total crypto market cap chart.
Similar to the previous /coins/{id}/market_chart endpoint, data granularity of this endpoint is automatically set based on the number of days indicated in the parameter.
- 1 day from now = data granularity is at hourly intervals
- 2 days and above = data granularity is at daily intervals, at 00:00 UTC
In this example, we’ll retrieve the request URL from the Paid API documentation and import 14 days of historical price data (from today). As such, the ‘days’ parameter is set to ‘14’.
https://pro-api.coingecko.com/api/v3/global/market_cap_chart?days=14&x_cg_pro_api_key={YOUR-API-KEY}
Create a new request in the API Connector and paste the Request URL into the field. Before saving and executing the request, expand Output optionsand select ‘grid’ under Report Style.
After running the query, two columns of data now populated: Market Cap and Volume.
Use the spreadsheet functions above to remove the brackets, split the data and convert the UNIX timestamp to a human readable date. We now have daily 00:00 UTC data of the total crypto market cap, for the last 14 days.
Note that Crypto Global Market Data isan exclusive endpoint for Paid API subscribers. Demo API users can leverage the /global endpoint, however this will only return the current total crypto market cap data and not historical data.
Refreshing the Data: Manually & Automatically
There are two ways to refresh the data pull with this API Connector – manually and automatically.
Manual Refresh
This simply means triggering the refresh manually, when you want to refresh the sheet. Click on Extensions > API Connector > Refresh All Now in the Google Sheets top navigation bar.
Automatic Refresh
The second way is to automatically refresh the data by creating a trigger schedule via the API Connector. Click on the Schedule tab in the add-on panel, and you’ll see various customizable trigger settings. This provides the flexibility in running data refreshes for specific API requests at specific intervals (every hour, 3 hours, 6 hours, 12 hours, daily, weekly, monthly).
You may also consider using a mix of both methods to reduce the number of API calls, like manually refreshing coin lists or historical crypto prices, where real-time data is not as crucial, and automatically refreshing coin price data for trending categories.
Troubleshooting Error Code 429: You've Exceeded the Rate Limit on Google Sheets
You may encounter theerror code 429, which states "You've exceeded the Rate Limit". This is due to rate limits on Google Sheets, which restricts how much data you’re able to import with each API call.
Google Sheets rely on shared hosting – this means that one Google server hosts multiple spreadsheets and caters to multiple users sharing the same limit of API calls per minute. As such, you may get rate limited even when using only a few API calls.
There are two ways to troubleshoot – you can either sign up up for a free Demo API plan with keyed authentication, or subscribe to a paid CoinGecko API plan to get a Pro API key. View all API plans here.
By providing a key authentication, CoinGecko’s server will host and supply all data, which thereforeovercome Google Sheets' rate limits.
Avoid Incurring Overages
If a consideration for subscribing is running into unexpected overages, setting up a call consumption alert via your developer dashboard can help prevent you from running into overages.
As of February 2024, paid subscribers can alsoopt to hard-cap overages. By enabling the overate option (currently in beta), API usage will not exceed the monthly credits threshold.
Looking for similar guides? Check out this tutorial that covers importing real-time crypto datafor the top 500 coinsusing an ImportJSON AppsScript.
