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.
Import JSON from any HTTPS API into Sheets—then chart, alert, and share.
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.
Under the hood: UrlFetchApp → JSON.parse → 2D array → Sheet range.
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.
Open your Google Sheet → Extensions → Apps Script.
Delete any placeholder code and paste the function below.
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.
Point at the endpoint, pick a path, and your table updates live.
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
Tab Data: call 2–3 endpoints (crypto top coins, 3–5 tickers).
Tab KPIs: summarize price, 24h change, pre‑market flags with QUERY.
Charts: sparkline trends, bar of daily % changes, and traffic‑light conditional formatting.
2) Portfolio tracker with alerts
Positions table: Ticker, Qty, Cost Basis.
Live quotes via ImportJSON or GOOGLEFINANCE fallback.
Alert rules: email when price crosses target (Apps Script MailApp).
3) FX exposure monitor
Fetch USD→EUR/GBP/JPY hourly.
Compute blended rate for invoices and set pricing guard rails.
Design for clarity: KPIs, trend lines, and color‑coded moves.
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
Install function: add IMPORTJSON script above.
Create Data tab: list endpoints in column B; use =IMPORTJSON(B2) outputs below.
Clean & label: use INDEX, FILTER, and LET to shape tidy tables.
KPIs tab: pull last price, 24h change; add =SPARKLINE() for quick trends.
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.
