Live stock and crypto prices in Google Sheets, beyond GOOGLEFINANCE

The GOOGLEFINANCE function is the built-in way to get a stock price into Google Sheets: =GOOGLEFINANCE("KO") returns a last price with no setup. Google says its quotes "may be delayed up to 20 minutes", it offers no bid or ask, and scripts cannot read its history. For more, write an Apps Script custom function that calls a stock API with UrlFetchApp, so =TLPRICE("US:KO") returns the same number your code gets.

This tutorial builds that function step by step: a single-cell TLPRICE, a watchlist version that fills a whole range from one call, a one-minute cache, a refresh trigger that only fires while the US market is open, and the request arithmetic that keeps a shared sheet inside its quota. It works the same for stocks, crypto pairs, currency pairs, indices, ETFs and commodities.

Where the GOOGLEFINANCE function stops

GOOGLEFINANCE is good at what it was built for: a personal portfolio tab that checks a few US listings once in a while. The limits show up as soon as the sheet feeds a decision or a report. The attribute list covers price, open, high, low, volume and a handful of fundamentals, but no bid or ask. Google's help page says historical data "cannot be downloaded or accessed via the Sheets API or Apps Script", which rules out scripting on top of it.

The usual workaround is IMPORTDATA or IMPORTXML pointed at a public page or a JSON URL. That breaks the day the page layout changes, and neither function can send request headers, so it cannot authenticate against an API that expects a key in a header. Apps Script can.

How a custom function fetches a price

The function calls one endpoint, GET /{asset}/snapshot/{symbol}, because a snapshot answers most spreadsheet questions in a single request: last trade, bid, ask, previous close and the day's change. Different cells that ask for different fields of the same symbol share one cached response.

{
  "symbol": "US:KO",1
  "bid": 88.15,
  "ask": 88.2,
  "bid_size": 100,
  "ask_size": 1000,
  "last_price": 88.151,2
  "last_timestamp": 1790593009119,3
  "prev_close": 87.81,4
  "change": 0.341,
  "change_percent": 0.3883,5
  "last_size": 100
}

Step 1: keep the key in Script Properties

1. 1Get a keyCreate a free account and copy the key from the dashboard. The free tier includes 3,000 REST requests a month.
2. 2Open the script editorIn the spreadsheet choose Extensions > Apps Script. The project is bound to this one sheet.
3. 3Add a script propertyOpen Project Settings (the gear), scroll to Script Properties, add TICKERLAYER_API_KEY with your key as the value, and save.
4. 4Paste the codeReplace the contents of Code.gs with the listing below and save. Custom functions need no authorization; installing the trigger in Step 3 asks for it once.

Step 2: the TLPRICE and TLQUOTES custom functions

The listing has two public functions. TLPRICE returns one field for one symbol. TLQUOTES takes a column of symbols and returns a block of rows (last, change, bid, ask, time), which Google recommends over hundreds of single-cell calls because each custom function call is a separate trip to the server. Both go through tlSnapshots_, which checks the cache, fetches only the misses in small parallel batches with UrlFetchApp.fetchAll, and caches the results for 60 seconds.

const TL_BASE = 'https://api.tickerlayer.com';
const TL_CACHE_SECONDS = 60; // one request per symbol per minute, at most
const TL_BATCH = 5;          // requests sent together; stay under your per-second limit
const TL_ASSETS = ['stocks', 'crypto', 'forex', 'indices', 'etfs', 'commodities'];
const TL_ERRORS = {
  400: 'invalid symbol', 401: 'check TICKERLAYER_API_KEY', 403: 'not in your plan',
  404: 'symbol not available', 429: 'rate limit or monthly quota reached',
};

/**
 * One field for one symbol, e.g. =TLPRICE("US:KO") or =TLPRICE("BTCUSD", "crypto", "bid").
 * @param {string} symbol Stocks as MARKET:TICKER (US:KO); other assets as listed (EURUSD).
 * @param {string} asset stocks, crypto, forex, indices, etfs or commodities. Optional for stocks.
 * @param {string} field last_price (default), bid, ask, prev_close, change, change_percent, last_timestamp.
 * @param {any} refresh Optional cell a trigger updates, to force a recalculation.
 * @customfunction
 */
function TLPRICE(symbol, asset, field, refresh) {
  const s = String(symbol).trim().toUpperCase();
  const data = tlSnapshots_([s], asset)[s];
  if (data.error) throw new Error(data.error);
  const f = field || 'last_price';
  if (!(f in data)) throw new Error(s + ' has no field ' + f);
  return f === 'last_timestamp' ? new Date(data[f]) : data[f];
}

/**
 * A watchlist: last, change %, bid, ask and time for a column of symbols.
 * @param {A2:A11} symbols A single column of symbols of one asset class.
 * @param {string} asset stocks, crypto, forex, indices, etfs or commodities.
 * @param {any} refresh Optional cell a trigger updates, to force a recalculation.
 * @customfunction
 */
function TLQUOTES(symbols, asset, refresh) {
  const rows = (Array.isArray(symbols) ? symbols : [[symbols]])
    .map(r => String(r[0]).trim().toUpperCase());
  const data = tlSnapshots_([...new Set(rows.filter(s => s))], asset);
  return rows.map(s => {
    if (!s) return ['', '', '', '', ''];
    const d = data[s];
    if (d.error) return [d.error, '', '', '', ''];
    return [d.last_price, d.change_percent / 100, d.bid, d.ask, new Date(d.last_timestamp)];
  });
}

function tlSnapshots_(symbols, asset) {
  const cache = CacheService.getScriptCache();
  const out = {};
  const misses = [];
  for (const s of symbols) {
    const a = asset ? String(asset).toLowerCase() : (s.indexOf(':') > 0 ? 'stocks' : '');
    if (TL_ASSETS.indexOf(a) < 0) {
      out[s] = { error: s + ': add the asset, e.g. "crypto" or "forex"' };
      continue;
    }
    const key = 'tl:' + a + ':' + s;
    const hit = cache.get(key);
    if (hit) out[s] = JSON.parse(hit);
    else misses.push({ s: s, key: key, url: TL_BASE + '/' + a + '/snapshot/' + encodeURIComponent(s) });
  }
  if (!misses.length) return out;

  const headers = { 'x-api-key': tlKey_() };
  for (let i = 0; i < misses.length; i += TL_BATCH) {
    if (i > 0) Utilities.sleep(1000);
    const batch = misses.slice(i, i + TL_BATCH);
    const responses = UrlFetchApp.fetchAll(
      batch.map(m => ({ url: m.url, headers: headers, muteHttpExceptions: true })));
    responses.forEach((resp, j) => {
      const m = batch[j];
      const code = resp.getResponseCode();
      if (code === 200) {
        out[m.s] = JSON.parse(resp.getContentText());
        cache.put(m.key, resp.getContentText(), TL_CACHE_SECONDS);
      } else {
        out[m.s] = { error: m.s + ': ' + (TL_ERRORS[code] || 'HTTP ' + code) };
      }
    });
  }
  return out;
}

function tlKey_() {
  const key = PropertiesService.getScriptProperties().getProperty('TICKERLAYER_API_KEY');
  if (!key) throw new Error('Add TICKERLAYER_API_KEY under Project Settings > Script Properties');
  return key;
}

Three details matter. muteHttpExceptions: true lets the script read a 404 or 429 and write a readable message instead of failing the whole range. Errors are never cached, so a typo fixed in the sheet is retried at once. And the symbol goes through encodeURIComponent, which turns US:KO into US%3AKO; the API accepts both forms.

Step 3: refresh on a timer, only while the market is open

A custom function recalculates only when one of its arguments changes. Google does not allow volatile functions such as NOW() or RAND() as arguments, and the spreadsheet's "recalculate every minute" setting does not apply to custom functions. The dependable pattern is a time-driven trigger that writes the current time into one cell, plus a formula argument that points at that cell.

/** Run once from the editor to install a 5-minute trigger. */
function installTrigger() {
  ScriptApp.newTrigger('tlTick').timeBased().everyMinutes(5).create();
}

/** Writes the time into the named range TL_TICK during US regular hours. */
function tlTick() {
  const now = new Date();
  const day = Utilities.formatDate(now, 'America/New_York', 'EEE');
  const hhmm = Number(Utilities.formatDate(now, 'America/New_York', 'HHmm'));
  if (day === 'Sat' || day === 'Sun' || hhmm < 930 || hhmm > 1600) return; // outside 09:30 to 16:00
  SpreadsheetApp.getActive().getRangeByName('TL_TICK').setValue(now);
}

Create the named range first: pick an out-of-the-way cell such as Settings!B1, then Data > Named ranges > TL_TICK. Run installTrigger once from the editor. The clock gate skips weekends and nights but not holidays; if that matters, look the day up once a morning with the market hours API or check US market hours. A crypto watchlist trades around the clock, so drop the gate for that tab.

Quota maths for a shared stock tracker

Two budgets apply at once. Google limits a consumer account to 20,000 URL Fetch calls a day (100,000 on Workspace), each custom function call to 30 seconds, and trigger runtime to 90 minutes a day. Your API plan counts requests per month. With a sheet like this one, the API plan is almost always the budget you hit first, so do the arithmetic before you share the link.

The per-second limit matters too. TL_BATCH fires five requests together and pauses a second between batches, so a 50-symbol watchlist needs ten batches, roughly 10 to 20 seconds, inside the 30-second ceiling; split anything larger across two ranges. If a cell shows "rate limit or monthly quota reached", read what HTTP 429 means and check the limits for your plan on the limits page.

Errors you will see, and the fix for each