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!

Connect Google Sheets to CoinGecko API using an API Connector

For this guide, we’ll be using the API Connector by Mixed Analytics (their free plan allows for 3 saved requests and 250 requests/month and provides a 30 day free-trial of its Pro plan). Let’s kick things off by installing 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 and you’ll find a few fields that will configure your API 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

First, head over to CoinGecko’s Crypto API documentation and navigate to the /coins/list endpoint. Select ‘Try It Out’.

In the include_platform parameter dropdown, you may select ‘true’ if you want to retrieve platform contract addresses, else set it to ‘false’. Hit the Execute button and copy the Request URL (https://api.coingecko.com/api/v3/coins/list?include_platform=true).

Go back to your spreadsheet with the active API Connector panel and paste the copied URL into the ‘Request URL’ field.

If you’re a Paid API subscriber, change the root URL to https://pro-api.coingecko.com/api/v3/ and append your API key at the end to avoid getting rate limited by Google Sheets (more on this below).

Your API Request on the connector add-on will look similar to what’s in the following screenshot. Name it accordingly so that you can revisit this easily in future, including scheduling automatic data refreshes. In this example, we’ve named this request Coin List since we’ll be pulling out a list of coins and respective ids. Save and click Run.

An extensive list of coins data will populate your destination sheet – with that, we now have a comprehensive coin list directory to reference ids and symbols, to fetch all sorts of crypto price data.

Tip: The CoinGecko team also ensures this list of coin ids and symbols is publicly accessible, 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 the coins’ 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 allows you to fetch real-time crypto prices for multiple coins with just one API call, and is a publicly accessible API endpoint.

However, due to limitations with the API Connector add-on, we’ll require more than one API call in this demo, in order to get bitcoin, ethereum, dogecoin, apecoin and matic-network price data.

Head over to the API documentation once again and fill in respective parameters.

Hit Execute and copy the Request URL:

Paste it into the API Connector add-on and select a new destination sheet for this data. 

Tip: Remember to create a new request or duplicate the previous one. Essentially, avoid overwriting your previous request or you will experience stale data when your sheet auto-refreshes saved queries.

You may want to structure your worksheet as such:

Check ‘remove header row’ and uncheck ‘clear sheet data’. Ensure your destination cell is set to B2. Run the query and you’ll find that bitcoin data will now populate row 2.

Do the same with the rest of the cryptocurrencies you’d like to track. A more efficient way of setting up these numerous requests is to duplicate existing requests and change the coin id in the Request URL directly.

And done! Now we have real-time cryptocurrency data imported straight into 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.

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, the Request URL will be:

If you’re a Paid API user, your Request URL will appear as:

Copy this and head over to API Connector. As above, do not overwrite previous requests and instead Create a New Request. Paste this into the Request URL field and 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 your Request URL should appear along the lines of:

If you’re a Paid API user, your Request URL will appear as:

Copy this and head over to the API Connector, pasting this into the Request URL field.

This time, expand ‘Output options’ by clicking on it, then select ‘grid’ under Report style. This will help to arrange your 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 fetch up to 10 years worth of historical cryptocurrency prices since April 2013 with the /coins/{id}/market_chart endpoint, by setting the days parameter as ‘max’. Also, 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

Additional 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

How to Import Total Crypto Market Cap Data

The /global/market_cap_chart endpoint returns the historical global market cap and volume data, and is an exclusive endpoint for Paid API subscribers. 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’.

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.

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: Google Sheets API Rate Limits

An error you may encounter is error code 429, where you have exceeded the API 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.

Subscribing to paid CoinGecko API plans can help you overcome rate limits, as CoinGecko’s server will host and supply all data. 

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.

Are you a project founder, developer or builder looking for a custom solution? Get in touch with our API sales team for a custom solution:

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