How to Import Crypto Prices in Google Sheets (Easy Guide with Examples)

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:

1. Connect Google Sheets to CoinGecko API with an API Connector
2. Import a list of coin ids and symbols
3. Get live crypto price data for specific cryptocurrencies
4. Retrieve historical crypto prices for specific coins
5. Fetch total crypto market cap data
6. 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:

1. Install an API Connector add-on from Google's Marketplace.
2. Run the extension from your Google Sheet.
3. Enter your API key in the connector.
4. 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!

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.

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://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 to 10 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 of the 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 the Request URL into its respective field within the add-on.

Before executing the query, we'll expand ‘Output options’ and select a ‘grid’ reporting style, 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 up to 10+ years of historical crypto price data since 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 days param 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

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 API endpoint, 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 options and 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 /global/market_cap_chart is an 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 the error 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 therefore overcome 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 also opt to hard-cap API calls to avoid incurring 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 data for the top 500 coins using an ImportJSON AppsScript.

CoinGecko's Content Editorial Guidelines

CoinGecko’s content aims to demystify the crypto industry. While certain posts you see may be sponsored, we strive to uphold the highest standards of editorial quality and integrity, and do not publish any content that has not been vetted by our editors.

Learn more

Julia Ng

Julia leads Growth at CoinGecko and is passionate about onboarding more women onto Web3. That said, she is generally poor at timing the market, so she DCAs for safety. Follow the author on Twitter @ngxinyajulia