How to Import Crypto Prices in Google Sheets (With Examples)

Share This Post

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 totalidade 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!

google sheets crypto prices

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.

API Connector by Mixed Analytics | Fetch Crypto Prices with CoinGecko Crypto API

Once the add-on has been successfully installed, create a new Google Sheet. In the top navigation bar, select Extensions and click ‘Open’.

Import crypto prices google sheets Extensions navigation bar

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‘).

Fetch crypto prices into gsheets with an API Connector Mixed Analytics | CoinGecko API

Under ‘Authorization‘, enter your API key as 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 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

API Connector - configure API 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.

💡 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

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.

Google sheet crypto price gsheets import crypto prices into gsheets

💡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 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.

Configure API request for crypto API

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.

vs_currency param USD

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.

API connector on google sheets to import live crypto prices and market data

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!

Nested data parsed in rows

💡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.

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:

google sheets crypto prices - live example of crypto prices in google sheets

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.

view crypto prices in google sheets

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.

Top cryptocurrency categories by market cap on CoinGecko - TRY Stablecoin, Kommunitas Launchpad tokens, Discord Bots, Telegram Bots

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.

Discord bots cryptocurrency category - Trending Coins | CoinGecko

Navigate to the API documentation and input ‘discord-bots’ in the category parameter.

get live cryptocurrency price market data with crypto api | CoinGecko API

Upon running the query, you’ll get the following Request URL:

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:

If you’re a Paid API user, your Request URL will call from the pro-api root URL and appear as:

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.

Discord Bots cryptocurrency category token price data | CoinGecko API

Let’s move on now to importing historical crypto prices and the totalidade 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.

Pull historical Bitcoin price data - historical BTC prices with CoinGecko API

Fill in the parameters based on your desired data output and append your Demo API key at the end of the Request URL:

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:

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.

API Connector MixedAnalytics Crypto Price, Market Cap and Total Volume | CoinGecko API

Run the request and the data should populate accordingly in your destination cell and sheet.

Import cryptocurrency price data into Google Sheets spreadsheet - Live and auto-refresh | CoinGecko

In each cell, two values are returned:

Cryptocurrency API price and market data

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.

Using the split and substitute Google Sheets function

Price values are now split into its separate columns (F and G).

Get crypto price data using the best cryptocurrency API | CoinGecko API

To convert UNIX timestamps to a human readable date and time, apply this formula: =EPOCHTODATE(F3,2)

How to use EPOCHTODATE function google sheets example

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.


How to Import Cryptocurrency Prices in Google Sheets

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.

Import historical cryptocurrency prices for backtesting trading strategy | CoinGecko API

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

Historical crypto price data days parameter | CoinGecko API

💡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 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’.{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.

Output settings for API Connector

After running the query, two columns of data now populated: Market Cap and Volume.

Total crypto market cap and volume data on Google Sheets | CoinGecko API | API Connector

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.

historical crypto prices in gsheets google sheets (screenshot example)

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.

API Connector Refresh All Now

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).

API Connector Google Sheets Add-on Trigger Refresh Settings | CoinGecko API

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.

Getting rate limited on Google Sheets | Crypto API rate limits

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.

Crypto API for developers set up call consumption alerts | CoinGecko API

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. 

Enable or disable API overage option to hard cap api calls

how to increase api rate limit - CoinGecko API

Looking for similar guides? Check out this tutorial that covers importing real-time crypto data for the top 500 coins using an ImportJSON AppsScript.

Tell us how much you like this article!

Julia Ng

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

Related Posts

Is Bitcoin Slipping Back Toward a Bear Market?

Bitcoin is being weird again. Last month, it...

Analyst Lark Davis predicts Ethereum hitting $15K this bull run

crypto analyst Lark Davis forecasted Ethereum price soaring...

Ripple (XRP) Price Prediction: Recovering $0.50 Is Next

Ripple’s (XRP) price is likely exhausting the pessimistic...

Bitcoin drops below $60,000 before the big ‘halving’ event

Bitcoin is getting jittery before the big “halving”...

“Fantasy” Crypto Trading Card Game Debuts on Blast Mainnet

The innovative crypto trading card game Fantasy has...