ImportJSON for Google Sheets (2025): Live Crypto & Stocks

If you’ve ever hit the limits of GOOGLEFINANCE or needed real‑time data from modern APIs, the ImportJSON approach in Google Sheets is your secret weapon. With one lightweight Apps Script, you can pull live crypto prices, stock quotes, FX rates, and any JSON payload directly into your sheet—then build dashboards that auto‑refresh. This 2025 guide gives you a production‑ready ImportJSON function, copy‑paste recipes for popular APIs, refresh strategies, and reliability tips so your models don’t break at 9:30 a.m.

Why ImportJSON for Google Sheets (primary keyword)

GOOGLEFINANCE is great, but it doesn’t cover everything and can be delayed. ImportJSON lets you call any HTTPS JSON API and convert that response into rows/columns on the fly. You can pull:

• Crypto tickers (BTC, ETH) from public APIs.
• US/EU equities via quote endpoints.
• FX, commodities, indexes, and even custom business APIs.
• Operational metrics (app stats, server health) for live boards.

Set up: a safe, readable ImportJSON function

We’ll create a custom function named IMPORTJSON that accepts a URL and an optional JSONPath‑style selector. It returns a clean 2D array you can chart or reference.

1. Open your Google Sheet → Extensions → Apps Script.
2. Delete any placeholder code and paste the function below.
3. File → Save. Name the project ImportJSON.

/**
 * IMPORTJSON(url, [path])
 * Fetches JSON from a URL and returns a 2D array. Optional 'path' filters keys.
 * Examples:
 *  =IMPORTJSON("https://api.coindesk.com/v1/bpi/currentprice.json")
 *  =IMPORTJSON(A2, "bpi.USD")
 */
function IMPORTJSON(url, path) {
  if (!url) return [["Error", "Missing URL"]];
  var resp = UrlFetchApp.fetch(url, { muteHttpExceptions: true, followRedirects: true });
  var code = resp.getResponseCode();
  if (code < 200 || code >= 300) return [["HTTP", code]];
  var obj;
  try {
    obj = JSON.parse(resp.getContentText());
  } catch (e) {
    return [["Error", "Invalid JSON"]];
  }
  // Traverse by simple dot path e.g. bpi.USD
  if (path) {
    var parts = String(path).split(".");
    for (var i = 0; i < parts.length; i++) {
      if (obj == null) break;
      obj = obj[parts[i]];
    }
  }
  return toTable_(obj);
}

// Convert JSON value to a 2D table
function toTable_(value) {
  if (value == null) return [[""]];
  if (Array.isArray(value)) {
    if (value.length === 0) return [[""]];
    // If array of primitives
    if (typeof value[0] !== 'object' || value[0] === null) {
      var header = ["value"];
      var rows = value.map(function(v){ return [v]; });
      return [header].concat(rows);
    }
    // Array of objects: build union header
    var keys = {};
    value.forEach(function(row){ Object.keys(row || {}).forEach(function(k){ keys[k] = true; }); });
    var headerArr = Object.keys(keys);
    var out = [headerArr];
    value.forEach(function(row){
      var line = headerArr.map(function(k){ return getPrimitive_(row ? row[k] : null); });
      out.push(line);
    });
    return out;
  }
  // Single object → two-column table key/value
  if (typeof value === 'object') {
    var kv = Object.keys(value).map(function(k){ return [k, getPrimitive_(value[k])]; });
    return [["key","value"]].concat(kv);
  }
  // Primitive → single cell
  return [[value]];
}

function getPrimitive_(v){
  if (v == null) return "";
  if (typeof v === 'object') return JSON.stringify(v);
  return v;
}

Notes:

• This function is intentionally minimal and readable. It returns headers and handles arrays/objects.
• For authenticated APIs, use pre‑signed URLs or write a wrapper function with headers and caching (see below).

Core recipes: crypto, stocks, FX (secondary keyword)

Always read the official API docs for parameter names, frequency limits, and terms of use. Many providers require API keys; store keys in Script Properties or a protected cell.

1) Crypto (public example)

Endpoint: Coindesk BTC price (no key required)

=IMPORTJSON("https://api.coindesk.com/v1/bpi/currentprice.json", "bpi.USD")

For CoinGecko market data (verify docs and parameters):

=IMPORTJSON("https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&ids=bitcoin,ethereum")

2) Stocks (keyed examples — confirm on official pages)

Alpha Vantage (time series):

=IMPORTJSON("https://www.alphavantage.co/query?function=TIME_SERIES_INTRADAY&symbol=AAPL&interval=5min&apikey=" & $B$1)

Finnhub (quote):

=IMPORTJSON("https://finnhub.io/api/v1/quote?symbol=MSFT&token=" & $B$1)

3) FX rates

ExchangeRate.host (public; verify current docs):

=IMPORTJSON("https://api.exchangerate.host/latest?base=USD&symbols=EUR,GBP,JPY", "rates")

Refresh strategies and rate limits (LSI keyword variation)

• Manual refresh: Edit a parameter (e.g., add “&ts=”&NOW()) to bust cache sparingly.
• Time‑driven triggers: Build a refresh sheet script that reads URLs and writes outputs to ranges, scheduled every N minutes. Respect quotas.
• Caching: Store last response in CacheService for X seconds to avoid hammering APIs.
• Backoff: If your provider returns HTTP 429, slow down. Exponential backoff protects your keys.

function FETCH_TO_RANGE(){
  var sheet = SpreadsheetApp.getActive().getSheetByName('Data');
  var url = sheet.getRange('B2').getValue();
  var out = IMPORTJSON(url);
  sheet.getRange(5,1,out.length,out[0].length).setValues(out);
}

Practical dashboards you can ship today

1) Crypto + stocks blend board

1. Tab Data: call 2–3 endpoints (crypto top coins, 3–5 tickers).
2. Tab KPIs: summarize price, 24h change, pre‑market flags with QUERY.
3. Charts: sparkline trends, bar of daily % changes, and traffic‑light conditional formatting.

2) Portfolio tracker with alerts

1. Positions table: Ticker, Qty, Cost Basis.
2. Live quotes via ImportJSON or GOOGLEFINANCE fallback.
3. Alert rules: email when price crosses target (Apps Script MailApp).

3) FX exposure monitor

1. Fetch USD→EUR/GBP/JPY hourly.
2. Compute blended rate for invoices and set pricing guard rails.

Expert insights and reliability tips

• Prefer stable endpoints designed for polling; web page scrapes can break.
• Normalize column headers; your charts and formulas won’t break when schemas expand.
• Separate fetch from presentation. Keep a raw data tab and reference it from views.
• Document keys and paths in a small data dictionary note next to your formulas.
• Mix ImportJSON with Slicers for interactive filtering without editing formulas.

ImportJSON vs GOOGLEFINANCE vs add‑ons (comparison)

• GOOGLEFINANCE: Easy and free, but limited coverage and sometimes delayed.
• ImportJSON + APIs: Maximum flexibility and coverage; you manage keys/limits.
• Marketplace add‑ons: Polished UX and scheduling; verify vendor reliability and pricing on official pages.

Implementation guide: ship a live crypto + stocks sheet in 30 minutes

1. Install function: add IMPORTJSON script above.
2. Create Data tab: list endpoints in column B; use =IMPORTJSON(B2) outputs below.
3. Clean & label: use INDEX, FILTER, and LET to shape tidy tables.
4. KPIs tab: pull last price, 24h change; add =SPARKLINE() for quick trends.
5. Charts + Slicers: build bar/line charts and slicers for ticker/sector.

Security, terms, and compliance

• Keys: store in Script Properties or protected ranges; never publish keys in shared sheets.
• Terms: ensure your use complies with each provider’s ToS; many restrict redistribution.
• Quotas: respect rate limits; add caching and scheduled fetches.

Final recommendations and takeaways

• Use ImportJSON for coverage and control; fall back to GOOGLEFINANCE for basics.
• Separate raw fetch from dashboards for stability and speed.
• Cache and throttle to avoid 429s and keep your keys healthy.
• Document endpoints and paths so teammates can maintain the model.

Frequently asked questions

Is ImportJSON an official Google function?

No. It’s a custom Apps Script function you add to your Sheet. It uses UrlFetchApp to call APIs and JSON.parse to shape results.

Will this work on shared or view‑only sheets?

Viewers can see results after the owner’s functions run. For scheduled updates, use time‑driven triggers in Apps Script.

How do I add headers to the output?

The function above emits headers for arrays of objects and key/value for objects. You can also wrap output with your own header row.

How can I avoid hitting rate limits?

Use caching, increase refresh intervals, and consolidate calls. For many tickers, prefer batch endpoints if available.

Can I pass HTTP headers (for auth)?

Create a wrapper function using UrlFetchApp with a headers object, then feed its parsed result into toTable_.

Why does my table shift when the API adds fields?

Schema drift happens. Normalize columns with SELECT/INDEX in a staging range and reference that in charts/dashboards.

Is GOOGLEFINANCE good enough for stocks?

It’s fine for many use cases, but can lag or be incomplete. ImportJSON + a reliable API provides better control and coverage.

Can I trigger emails or alerts?

Yes—compare thresholds in a script and use MailApp.sendEmail or write to a log sheet that triggers a notification flow.

Recommended tools & deals

• Find handy data tools & connectors: AppSumo — discover CSV cleaners, dashboard kits, and API utilities.
• Design polished dashboards & icons: Envato — charts, UI kits, and slide templates for reporting.
• Host lightweight APIs your Sheet can call: Railway — deploy tiny endpoints and proxies for stable fetches.

Disclosure: Some links are affiliate links. If you click and purchase, we may earn a commission at no extra cost to you. We only recommend tools we’d use ourselves.

Go deeper: related internal guides

• Google Sheets QUERY Function (2025) — shape API data into clean tables.
• VLOOKUP vs XLOOKUP (2025) — fetch single values from your API tables safely.

Official docs and trusted sources

• Apps Script UrlFetchApp: developers.google.com
• Apps Script for Sheets: developers.google.com
• Google Sheets function reference: support.google.com
• Alpha Vantage API: alphavantage.co
• Finnhub API: finnhub.io
• CoinGecko API: coingecko.com
• ExchangeRate.host: exchangerate.host