Live Crypto Prices in Google Sheets Without an API Key

This guide builds a Google Sheet that reads ticker symbols from a column and returns live prices beside them, with no CoinMarketCap account, no API key and no paid add-on.

The data comes from CoinMarketCap's Keyless Public API, specifically /v3/cryptocurrency/quotes/latest, called from a short Apps Script function. By the end you will have a working custom function, a second function that adds 24-hour change and market cap, a scheduled refresh that actually updates cells, and a clear picture of where keyless access stops and a free API key begins.

What You Are Building

Column A holds tickers. Column B holds live prices. A second formula adds change and market cap columns, and a scheduled job keeps a separate copy of the prices current on a timer.

The reason this takes a script rather than a formula is mechanical. Google Sheets has one built-in function for pulling data from a URL without any code, IMPORTDATA, and Google's reference describes it as importing data "in .csv (comma-separated value) or .tsv (tab-separated value) format." CoinMarketCap returns JSON, and Sheets has no built-in JSON parser, so neither IMPORTDATA nor IMPORTXML can read the response. Apps Script can, and Google's custom-function documentation lists URL Fetch among the services a custom function is allowed to use. That is the route this guide takes.

Step 1: Make a Keyless Call and Read the Response

The Keyless Public API is a fixed subset of CoinMarketCap endpoints served under a /public-api path prefix. Requests are GET, and they carry no authentication header. Everything else about the URL matches the authenticated Pro API, which is what makes the later migration a two-line change.

curl "https://pro-api.coinmarketcap.com/public-api/v3/cryptocurrency/quotes/latest?symbol=BTC,ETH&convert=USD&aux=cmc_rank"

Two keyless routes return prices, and they suit different jobs:

A spreadsheet is built around tickers rather than numeric IDs, so this guide uses /v3/cryptocurrency/quotes/latest and its symbol parameter. The aux parameter is worth adding from the start: without it the response carries a long tags array and a set of supply and platform fields the script never reads, and trimming that keeps each call small. Here is the response with aux=cmc_rank, reduced to the fields the script uses:

{
  "data": [
    {
      "id": 1,
      "name": "Bitcoin",
      "symbol": "BTC",
      "slug": "bitcoin",
      "cmc_rank": 1,
      "quote": [
        {
          "id": 2781,
          "symbol": "USD",
          "price": 83163.77643878388,
          "percent_change_24h": -0.37736785,
          "market_cap": 1670834783398.857,
          "last_updated": "2026-09-29T04:57:59.000Z"
        }
      ]
    }
  ],
  "status": {
    "error_code": "0",
    "error_message": "",
    "elapsed": 31,
    "credit_count": 1
  }
}

Three details in that payload decide how the script is written.

data is an array, and quote is also an array. Each asset's quote is a list of currency objects rather than an object keyed by currency code, so you find the currency you asked for by matching on quote[].symbol. Indexing quote.USD returns nothing.

status.error_code changes type between routes. It is the string "0" on the quotes route and the integer 0 on /v1/cryptocurrency/map. The keyless documentation flags this directly, and both forms are easy to reproduce: a route outside the keyless subset returns "error_code":1005 as an integer, while a bad parameter on /v1/simple/price returns "error_code":"400" as a string. Comparing it as a string covers every case.

A ticker is not unique. This one changes the shape of the code, and it gets its own step.

Step 2: One Ticker, Many Assets

Ticker symbols are not unique on CoinMarketCap, and the duplicates are not obscure. Asking the keyless ID map for SOL returns eight assets sharing that symbol: Solana itself at rank 7, Wrapped Solana at rank 8027, and six inactive tokens including Solcoin and Sola Token.

curl "https://pro-api.coinmarketcap.com/public-api/v1/cryptocurrency/map?symbol=SOL&listing_status=active"

The quotes route behaves the same way. symbol=SOL returns several assets, every one of them carrying "symbol": "SOL", and the order is not by rank: on a live call Solcoin and Sola Token come back ahead of Solana, both with "price": null. Building a lookup keyed on the symbol alone means the last asset in the array wins, which is a coin toss between the asset you meant and a token that stopped trading in 2015.

The fix is to rank the candidates and keep one. cmc_rank is the field to sort on: the asset you meant is almost always the highest-ranked holder of the symbol, and delisted tokens carry cmc_rank: null.

One related caveat, since the ID map is the natural place to look symbol collisions up: listing_status=active does not currently filter the response down to active assets. The call above returns rows with "is_active": 0 alongside the active ones, so check is_active yourself rather than relying on the parameter.

Step 3: Open the Script Editor

In your sheet, open Extensions, then Apps Script. A new tab opens on a file called Code.gs containing an empty myFunction. Replace the whole contents with the blocks in the next two steps, then save.

Name the sheet tab that holds your tickers Prices. Step 6 refers to it by name.

Step 4: Fetch, Disambiguate, Parse

The first function wraps the HTTP call and retries on HTTP 429, for reasons covered in step 7. It deliberately does not sleep after the final attempt, so the whole retry budget stays well inside the 30 seconds a custom function is allowed.

const CMC_KEYLESS = "https://pro-api.coinmarketcap.com/public-api";

/**
 * GET a keyless CoinMarketCap URL, retrying with exponential backoff on 429.
 * Sleeps 1s then 2s then 4s, so four attempts cost at most 7 seconds of the
 * 30-second custom-function budget.
 */
function cmcFetch_(url, tries) {
  tries = tries || 4;
  for (var attempt = 0; attempt < tries; attempt++) {
    var response = UrlFetchApp.fetch(url, {
      method: "get",
      muteHttpExceptions: true,
      headers: { Accept: "application/json" }
    });
    if (response.getResponseCode() !== 429) {
      return response;
    }
    if (attempt < tries - 1) {
      Utilities.sleep(Math.pow(2, attempt) * 1000);
    }
  }
  throw new Error("CoinMarketCap returned 429 after " + tries + " attempts");
}

The second function turns a list of tickers into a lookup of ticker to quote. One HTTP request covers every ticker in the list, and the rank comparison resolves the symbol collisions from step 2.

/**
 * Map of ticker symbol to its best-ranked quote object, for one batch.
 * Best-ranked means lowest non-null cmc_rank, which is how Solana wins the
 * SOL symbol over Solcoin and Sola Token.
 */
function cmcQuoteMap_(symbols, convert) {
  var url = CMC_KEYLESS + "/v3/cryptocurrency/quotes/latest"
    + "?symbol=" + encodeURIComponent(symbols.join(","))
    + "&convert=" + encodeURIComponent(convert)
    + "&aux=cmc_rank";

  var response = cmcFetch_(url);
  if (response.getResponseCode() !== 200) {
    throw new Error("HTTP " + response.getResponseCode() + ": "
      + response.getContentText().slice(0, 200));
  }

  var payload = JSON.parse(response.getContentText());

  // error_code is "0" as a string on this route and 0 as a number on others.
  // String() makes one comparison cover both.
  if (String(payload.status.error_code) !== "0") {
    throw new Error(payload.status.error_message
      || ("CoinMarketCap error " + payload.status.error_code));
  }

  var best = {};
  payload.data.forEach(function (asset) {
    // quote is an ARRAY of currency objects in v3, not an object keyed by
    // currency code. Match on symbol rather than indexing quote.USD.
    var quote = asset.quote.filter(function (q) {
      return q.symbol === convert;
    })[0];
    if (!quote || quote.price === null || quote.price === undefined) {
      return;
    }

    // Several assets can share one ticker. Keep the highest-ranked.
    // cmc_rank is null on delisted assets, so treat null as worst.
    var rank = (asset.cmc_rank === null || asset.cmc_rank === undefined)
      ? Infinity : asset.cmc_rank;
    var key = asset.symbol;
    if (!best[key] || rank < best[key].rank) {
      best[key] = { rank: rank, quote: quote, name: asset.name, id: asset.id };
    }
  });
  return best;
}

Step 5: The Custom Functions

This is what you type into a cell. It takes a range rather than a single cell, which matters for two reasons covered below.

/**
 * Live prices for a column of ticker symbols, from the CoinMarketCap
 * Keyless Public API. The whole range is served by one HTTP request.
 *
 * @param {A2:A20} range Column of ticker symbols, for example BTC.
 * @param {string} convert Currency code to price in. Defaults to USD.
 * @return {number[][]} A column of prices, aligned to the input range.
 * @customfunction
 */
function CMC_PRICES(range, convert) {
  convert = (convert || "USD").toUpperCase();

  var rows = Array.isArray(range) ? range : [[range]];
  var symbols = [];
  rows.forEach(function (row) {
    var cell = cmcCell_(row[0]);
    if (cell && symbols.indexOf(cell) === -1) {
      symbols.push(cell);
    }
  });
  if (symbols.length === 0) {
    return [[""]];
  }

  var best = cmcQuoteMap_(symbols, convert);

  return rows.map(function (row) {
    var cell = cmcCell_(row[0]);
    return [best[cell] ? best[cell].quote.price : ""];
  });
}

/** Normalise one cell value to a clean uppercase ticker. */
function cmcCell_(value) {
  if (value === null || value === undefined) {
    return "";
  }
  return String(value).trim().toUpperCase();
}

Save the script, go back to the sheet, put tickers in A2:A20 and enter this in B2:

=CMC_PRICES(A2:A20)

The result fills column B, one price per ticker, blank where a row is empty or a ticker has no priced match. To price in another currency, pass a second argument:

=CMC_PRICES(A2:A20, "EUR")

The two reasons the function takes a range rather than a cell are worth stating plainly.

The first is request volume. A per-cell function makes one HTTP request per cell, so twenty tickers means twenty requests. Batching them into a single symbol=BTC,ETH,SOL call makes it one.

The second is recalculation. Google's documentation states that "to trigger recalculation, you must pass a referenced cell or cell range directly as an argument to the custom function." A function with no cell reference has nothing to react to. Passing the range gives Sheets a dependency: edit a ticker, and the prices recalculate.

Because cmcQuoteMap_ already returns the whole quote object, a second function that adds more columns costs no extra request:

/**
 * Price, 24-hour change and market cap for a column of tickers.
 *
 * @param {A2:A20} range Column of ticker symbols.
 * @param {string} convert Currency code. Defaults to USD.
 * @return {Array[]} Three columns, aligned to the input range.
 * @customfunction
 */
function CMC_QUOTES(range, convert) {
  convert = (convert || "USD").toUpperCase();

  var rows = Array.isArray(range) ? range : [[range]];
  var symbols = [];
  rows.forEach(function (row) {
    var cell = cmcCell_(row[0]);
    if (cell && symbols.indexOf(cell) === -1) {
      symbols.push(cell);
    }
  });
  if (symbols.length === 0) {
    return [["", "", ""]];
  }

  var best = cmcQuoteMap_(symbols, convert);

  return rows.map(function (row) {
    var hit = best[cmcCell_(row[0])];
    if (!hit) {
      return ["", "", ""];
    }
    // percent_change_24h arrives as a percentage figure, so divide by 100
    // for a cell you want to format with Sheets' own percent format.
    return [hit.quote.price, hit.quote.percent_change_24h / 100,
            hit.quote.market_cap];
  });
}

Enter =CMC_QUOTES(A2:A20) in C2 and it fills three columns. Format the middle one as a percentage.

Step 6: Refresh on a Schedule

Editing a cell recalculates the formula, but prices move when nobody is editing anything. Scheduled refresh works differently from the formula, and the difference is where most Sheets setups come unstuck.

A custom function recalculates only when its arguments change. The Spreadsheet service is available to custom functions in read-only form, so a custom function cannot write its own result into the grid, and a time-driven trigger has no way to force a cell formula to re-evaluate. Pointing a timer at the custom function runs the code on Google's servers and leaves the cell exactly as it was.

The pattern that does update cells on a timer is an ordinary function, with full authorization, that fetches prices and writes them with SpreadsheetApp:

/**
 * Writes current prices into column F and stamps the refresh time in H1.
 * Attach a time-driven trigger to THIS function, not to CMC_PRICES.
 */
function refreshPrices() {
  var sheet = SpreadsheetApp.getActive().getSheetByName("Prices");
  var tickers = sheet.getRange("A2:A20").getValues();

  var symbols = [];
  tickers.forEach(function (row) {
    var cell = cmcCell_(row[0]);
    if (cell && symbols.indexOf(cell) === -1) {
      symbols.push(cell);
    }
  });
  if (symbols.length === 0) {
    return;
  }

  var best = cmcQuoteMap_(symbols, "USD");

  var output = tickers.map(function (row) {
    var hit = best[cmcCell_(row[0])];
    return [hit ? hit.quote.price : ""];
  });

  sheet.getRange("F2:F20").setValues(output);
  sheet.getRange("H1").setValue(new Date());
}

To attach the timer, open the clock icon in the Apps Script sidebar, add a trigger, choose refreshPrices as the function, choose a time-driven source and pick an interval. The first run asks for authorization, because writing to a spreadsheet needs permissions a custom function never requests.

Column F now updates on the timer whether or not anyone has the sheet open. Column B stays a live formula, recalculating when you edit a ticker. Running both side by side makes the distinction visible, and once you have decided which behaviour you want, you can drop the other.

Step 7: Rate Limits, and Why They Land Differently Here

The Keyless Public API is rate-limited against a shared, IP-based pool, and CoinMarketCap publishes no per-caller number for it. The keyless documentation is direct about the consequence: expect an occasional HTTP 429 under load, and back off and retry rather than repeating the request immediately. That is what cmcFetch_ does.

Apps Script changes where that lands. UrlFetchApp requests originate from Google's servers rather than your home connection, so a keyless Sheets setup shares its rate-limit position with other traffic leaving the same infrastructure. The practical effect is that 429s are a normal response to design for here rather than an edge case.

Batching is the habit that keeps you comfortably inside the limit, and CMC_PRICES already does it: one request covers the whole range. Sending aux=cmc_rank keeps each response down to the fields you use. Then set the trigger interval to match how fast the number needs to move, since CoinMarketCap's free Basic tier updates quotes on a 60-second cycle and refreshing faster than once a minute spends requests on data that has not changed.

Step 8: Moving to a Free API Key

Keyless access is built for evaluation and its endpoint subset is fixed. Adding a key is a two-line change: drop the /public-api prefix and send the key as a header. The response envelope is identical, so nothing in cmcQuoteMap_ needs rewriting.

// Store the key once: Project Settings, Script Properties, key CMC_API_KEY.
// Never paste a key into a shared sheet or into the script body.
var apiKey = PropertiesService.getScriptProperties().getProperty("CMC_API_KEY");

var response = UrlFetchApp.fetch(
  "https://pro-api.coinmarketcap.com/v3/cryptocurrency/quotes/latest"
    + "?symbol=BTC,ETH&convert=USD&aux=cmc_rank",
  {
    method: "get",
    muteHttpExceptions: true,
    headers: {
      "Accept": "application/json",
      "X-CMC_PRO_API_KEY": apiKey
    }
  }
);

What the free Basic key adds, per the CoinMarketCap API pricing page as of 29 September 2026: 60+ endpoints, 15,000 monthly call credits, a 50 request per minute limit of your own rather than a shared pool, one API key, and commercial use rights. It converts to one currency per call, so a sheet showing prices in two currencies makes two calls. Paid tiers start at Builder, $29 per month for 150,000 credits and 300 requests per minute.

A key also opens endpoints the keyless subset does not carry. Historical daily quotes for a given asset are the ones a spreadsheet tends to want next, and they are a keyed route rather than a keyless one.

Common Mistakes

• Reaching for IMPORTDATA. It reads CSV and TSV. The API returns JSON, so the formula returns an error rather than a price.
• Keying results on the ticker alone. Several assets share a symbol, and the array is not ordered by rank. Compare cmc_rank and keep the highest-ranked.
• Sending an API key header on a keyless call. Keyless routes take GET requests with no X-CMC_PRO_API_KEY header. That header belongs to the authenticated base path.
• Assuming every endpoint is keyless. The subset is fixed. /public-api/v1/cryptocurrency/quotes/latest is not in it, and returns "error_code":1005 with the message "An API Key is required for this call." The v3 path this guide uses is in the subset.
• Using the wrong parameter for the version. /v1/simple/price takes ids and rejects id with "'ids' is a required parameter". The v3 quotes route takes id, slug or symbol.
• Indexing quote.USD. In v3, quote is an array of currency objects. Filter on quote[].symbol.
• Testing status.error_code === 0. It is the string "0" on the quotes route. String(...) handles both types.
• Trusting listing_status=active to exclude delisted assets. It currently returns is_active: 0 rows too. Check the field.
• One formula per cell. Twenty cells means twenty requests against a shared pool. One range argument means one request.
• Attaching a timer to the custom function. Time-driven triggers cannot refresh a cell formula. Attach them to refreshPrices, which writes values.
• Exceeding 30 seconds. Google caps a custom function call at 30 seconds, after which the cell shows #ERROR!. Long retry chains and very large ticker lists are what push a call past it.

Where to Go Next

The same cmcFetch_ helper reads every other keyless route without modification. /v1/global-metrics/quotes/latest adds total market cap and Bitcoin dominance to a dashboard tab, /v3/fear-and-greed/latest adds a sentiment reading, and the CMC 100 and CMC 20 index routes add broad-market benchmarks. Each is the same GET, the same envelope and the same String(status.error_code) check, so each is a few lines on top of what you already have.

If you would rather start from a keyed setup, the keyed Google Sheets walkthrough covers the same integration with an API key in the request header.

Frequently Asked Questions

Developer reference · Confirm endpoint access, keyless paths and plan availability in current CoinMarketCap API documentation.