How to Track Crypto Prices in Google Sheets Using CoinMarketCap

·

Keeping track of cryptocurrency prices doesn't have to be complicated. By integrating CoinMarketCap's API with Google Sheets, you can automate the process of fetching real-time data for your portfolio or analysis. This guide provides a clear, step-by-step method to set up your own automated crypto price tracker.

Why Use CoinMarketCap?

When selecting a data provider for cryptocurrency prices, reliability is key. Many free APIs, including some popular alternatives, impose strict rate limits or IP-based restrictions that can disrupt automated data fetching. CoinMarketCap offers a robust and developer-friendly API that is well-suited for integration with tools like Google Sheets, minimizing the risk of blocked requests during critical updates.

Getting Started: What You’ll Need

To follow this tutorial, you’ll need:

Registering on CoinMarketCap and Obtaining an API Key

  1. Visit the CoinMarketCap website and sign up for a free account.
  2. Navigate to your account dashboard and locate the API section.
  3. Generate a new API key. This key will be used to authenticate your requests.

Remember to review the API usage limits to avoid exceeding your quota.

Setting Up Google Apps Script

Google Apps Script allows you to extend the functionality of Google Sheets with custom code. Here’s how to set it up:

  1. Open Google Sheets and create a new spreadsheet.
  2. Click on Extensions > Apps Script to open the script editor.
  3. Delete any sample code and paste the following script:
function GetCryptoPrice(coins, apiKey) {
  try {
    let url = "https://pro-api.coinmarketcap.com/v2/cryptocurrency/quotes/latest?slug=" + coins;
    const requestOptions = {
      method: 'GET',
      headers: {
        'X-CMC_PRO_API_KEY': apiKey
      },
      json: true,
    };
    let result = UrlFetchApp.fetch(url, requestOptions);
    let jsonResult = JSON.parse(result.getContentText());
    let priceList = coins.split(',');
    Object.keys(jsonResult.data).forEach((coin) => {
      let pair = [];
      pair.push(jsonResult.data[coin].symbol, jsonResult.data[coin].quote.USD.price.toFixed(4));
      const i = priceList.findIndex((ele) => ele == jsonResult.data[coin].slug);
      priceList.splice(i, 1, pair);
    });
    Logger.log(priceList);
    return priceList;
  } catch(e) {
    Logger.log(e.message);
  }
}

Testing the Script

Before integrating with Google Sheets, test the script within the Apps Script editor:

  1. Replace the example parameters with your own. Use the format:
    GetCryptoPrice("cryptocurrency1,cryptocurrency2", "your_api_key_here")
    For example:
    GetCryptoPrice("ethereum,bitcoin", "9e70...5841")
  2. Click the Run button. You’ll be prompted to authorize the script—grant the necessary permissions.
  3. Check the execution log to confirm that data is being fetched correctly.

Important: Use the exact cryptocurrency slugs as they appear in CoinMarketCap’s URL (e.g., bitcoin, ethereum).

After testing, comment out the test line by adding // at the beginning to avoid unnecessary API calls.

Integrating with Google Sheets

Once your script is working:

  1. Return to your Google Sheets document.
  2. In any cell, enter a formula referencing your custom function. For example:
    =GetCryptoPrice("bitcoin,ethereum,litecoin", "your_api_key")
  3. Press Enter. The sheet will now display the latest prices for the specified cryptocurrencies.

For best performance, request up to 12 currencies in a single call to maximize API efficiency.

👉 Explore more strategies for automating your data workflows

Frequently Asked Questions

Why did my API request fail?
This is often due to an invalid API key, incorrect cryptocurrency slug format, or exceeding the API rate limit. Double-check your parameters and ensure your key is active.

Can I track historical prices with this method?
No, this script only retrieves real-time prices. For historical data, consider using CoinMarketCap’s historical endpoints or exporting CSV reports.

Is there a cost associated with the CoinMarketCap API?
The basic plan is free but has usage limits. For high-frequency requests, review their pricing tiers for upgraded access.

How often does the data update in Google Sheets?
Google Sheets does not auto-refresh custom functions continuously. You can set triggers in Apps Script or manually recalculate to update values.

What should I do if I encounter a permissions error?
Ensure you’ve authorized the Apps Script project correctly. Re-run the script and grant all requested permissions when prompted.

Can I use this method for any cryptocurrency?
Yes, as long as the asset is listed on CoinMarketCap and you use the correct slug identifier.