---
title: "How do I get fund or ETF data by ISIN in Google Sheets?"
description: "Google Sheets fund data by ISIN: Apps Script custom function =FUNDFACTS(isin, path), since IMPORTDATA cannot send the key; key kept in script properties."
canonical: https://fundfactsapi.com/answers/fund-data-by-isin-in-google-sheets
published: 2026-09-12
updated: 2026-10-13
publisher: FundFacts API
---

# How do I get fund or ETF data by ISIN in Google Sheets?

**Short answer:** Google Sheets has no built-in JSON import and IMPORTDATA cannot send an Authorization header, so the lookup is a short Apps Script custom function: paste it under Extensions → Apps Script, store the key in Script properties, and =FUNDFACTS(A2, "headlineMetrics.ter") fills a cell. The request is GET https://fundfactsapi.com/api/v1/funds/{isin} with the header Authorization: Bearer <your key>; read name, data.headlineMetrics.ter, data.riskRating, data.topHoldings and data.dataAsOf from the JSON. A free key (5 lookups a month, no card) is enough to try it.

Google Sheets has no built-in JSON import and IMPORTDATA cannot send an Authorization header, so the lookup is a short Apps Script custom function: paste it under Extensions → Apps Script, store the key in Script properties, and =FUNDFACTS(A2, "headlineMetrics.ter") fills a cell.

## What you need

- An API key from [fundfactsapi.com/signup](https://fundfactsapi.com/signup). The Free plan is 5 lookups a month and asks for no card. Keys start with `ffk_`.
- The ISIN of the share class you want. Every share class has its own ISIN; `GET /search?q=` turns a name into ISINs and is free.
- Google Sheets has no built-in JSON import, so the lookup is a small Apps Script custom function. Open Extensions → Apps Script, paste the code, store the key in Project Settings → Script properties as FUNDFACTS_API_KEY, then use `=FUNDFACTS(A2, "headlineMetrics.ter")` in a cell.

## The code

```javascript
/**
 * Read one field of a fund by ISIN. Path is dot-separated inside data, e.g.
 * "headlineMetrics.ter", "riskRating", "keyFacts.aum", "profile.category".
 * @customfunction
 */
function FUNDFACTS(isin, path) {
  var key = PropertiesService.getScriptProperties().getProperty("FUNDFACTS_API_KEY");
  var cache = CacheService.getScriptCache();
  var hit = cache.get("ff:" + isin);
  var body;
  if (hit) {
    body = JSON.parse(hit);
  } else {
    var res = UrlFetchApp.fetch("https://fundfactsapi.com/api/v1/funds/" + isin, {
      headers: { Authorization: "Bearer " + key },
      muteHttpExceptions: true,
    });
    if (res.getResponseCode() !== 200) return "ERR " + res.getResponseCode();
    body = JSON.parse(res.getContentText());
    cache.put("ff:" + isin, res.getContentText(), 21600); // 6 hours: do not spend a request per recalculation
  }
  if (!path) return body.name;
  return path.split(".").reduce(function (o, k) { return o == null ? null : o[k]; }, body.data);
}

/** Top holdings as a spill range: =FUNDFACTS_HOLDINGS(A2) */
function FUNDFACTS_HOLDINGS(isin) {
  var key = PropertiesService.getScriptProperties().getProperty("FUNDFACTS_API_KEY");
  var res = UrlFetchApp.fetch("https://fundfactsapi.com/api/v1/funds/" + isin, { headers: { Authorization: "Bearer " + key } });
  var d = JSON.parse(res.getContentText()).data;
  return [["Holding", "Weight %"]].concat(d.topHoldings.map(function (h) { return [h.name, h.weight]; }));
}
```

## What comes back

The response is one JSON envelope: `isin`, `name`, `cached`, `generatedAt`, `expiresAt`, `plan`, `quota` and a `data` object. Inside `data` you get `keyFacts` (asset class, currency, fund size, inception date, distribution policy, number of holdings), `headlineMetrics` (TER, AUM, 3-year volatility and Sharpe, yield to maturity and duration for bond funds), `riskRating` (the 1–7 KID indicator), `profile` (a rule-based classification: kind, category, risk band, region focus, sector tilt), `topHoldings`, `sector`, `geography`, `region`, `assetAllocation`, `creditQuality`, `maturity`, `calendarReturns`, `annualisedReturns`, `cumulativePerformance`, `indexedPerformance`, `metrics` (max drawdown, P/E, income yield…), `sfdrArticle`, `costs` (PRIIPs entry, exit, ongoing, transaction and performance fees, reduction in yield) and `dataAsOf`, the date of the underlying figures.

Money and percentages arrive as the fund house prints them, as strings such as `"0.20%"` or `"USD 151.7bn"`. Breakdown weights and return series are plain numbers in percent. Fields that do not apply to the fund's asset class are empty strings, `null` or empty arrays, never missing keys; treat empty as "not disclosed", not as zero.

A trimmed example for iShares Core MSCI World UCITS ETF ([IE00B4L5Y983](https://fundfactsapi.com/funds/IE00B4L5Y983)):

```json
{
  "isin": "IE00B4L5Y983",
  "name": "iShares Core MSCI World UCITS ETF",
  "cached": true,
  "expiresAt": "2026-09-13T06:00:00.000Z",
  "data": {
    "keyFacts": { "assetClass": "Equities", "currency": "USD", "aum": "USD 151.7bn", "inception": "2009-09-25", "distribution": "Accumulating", "holdings": 1253 },
    "headlineMetrics": { "ter": "0.20%", "volatility3y": "11.8%", "sharpe3y": "1.68" },
    "riskRating": 6,
    "profile": { "kind": "equity", "category": "Global Equity", "riskBand": "high", "regionFocus": "Global", "sectorTilt": "Broad" },
    "topHoldings": [ { "name": "NVIDIA", "weight": 5.48 }, { "name": "APPLE", "weight": 5.24 } ],
    "sector": [ { "label": "Information Technology", "weight": 29.8 }, { "label": "Financials", "weight": 16.46 } ],
    "geography": [ { "label": "United States", "weight": 72.07 }, { "label": "Japan", "weight": 5.85 } ],
    "calendarReturns": { "years": ["2023", "2024", "2025"], "fund": [23.9, 18.7, 21.2], "benchmark": [23.8, 18.7, 21.1] },
    "metrics": { "maxDrawdown": "-16.5%" },
    "dataAsOf": "2026-09-01"
  }
}
```

## Things to know before shipping

**First call is slow, later calls are fast.** A fund never loaded before is read on demand from the documents the fund house publishes. That first call takes 15 seconds to about 3 minutes; set your client timeout to 300 seconds and do not fire a second request for the same ISIN while the first is running. Every later call returns from the store in well under a second with `cached: true`; past 24 hours the payload comes back flagged `stale: true` while a refresh runs in the background, and nothing older than 7 days is served without a new read.

**Requests are counted per ISIN.** One request is counted per fund found. `X-RateLimit-Remaining` on every response tells you how many are left this month; a `429 rate_limited` response carries `Retry-After`. Search and `/me` are free. Cache payloads by ISIN until `expiresAt` and you will rarely spend more than one request per fund per day.

**Batch when you have a list.** `POST /funds` with `{ "isins": [...], "wait": true }` answers up to 10 ISINs per call on Starter, 50 on Pro and 200 on Scale. ISINs still loading come back with `status: "pending"`; send them again a minute later.

**Not every ISIN is a fund.** A stock, bond or index ISIN returns `404 fund_not_found`. Validate the format first (12 characters, Luhn check digit) so a typo does not spend a request; the [ISIN validator](https://fundfactsapi.com/tools/isin-validator) shows the rule.

**Show the as-of date.** `data.dataAsOf` is the date printed on the fund's documents. Display it next to any figure.

## Going further

- [Endpoints](https://fundfactsapi.com/docs/endpoints): batch, search, portfolio look-through, holdings overlap, factsheets, SCPI.
- [Field reference](https://fundfactsapi.com/docs/fields): every key of `data` with type and example.
- [OpenAPI 3.1 spec](https://fundfactsapi.com/openapi.json) to generate a client in any language.
- [AI agents & MCP](https://fundfactsapi.com/docs/ai-agents) if a coding agent is writing this code for you.

## FAQ

### Why cache the response in the script?

Sheets recalculates custom functions often. Without the CacheService line every recalculation would spend one request per ISIN; with it, one request per ISIN per six hours is enough, and the API's own 24-hour refresh means nothing is lost.

### Apps Script times out after 30 seconds. What about cold ISINs?

A custom function is limited to 30 seconds, so the very first load of a fund nobody has requested can fail once. Run the lookup again a minute later: the payload is then in the store and returns instantly. For a big list, warm the ISINs first with a small script or a batch call from the command line.

### Do I need a credit card to try it?

No. The Free plan is 5 lookups per month with no card; sign up with an e-mail address or Google and the key is shown in the dashboard.

### Which ISINs work?

Any share class of a UCITS fund, ETF, money-market fund or open-end fund whose management company publishes a product page, factsheet and KID. Each share class has its own ISIN and is looked up on its own. Stocks, bonds and indices are not funds and return 404 fund_not_found.

### How current are the figures?

Each ISIN is re-read from the fund house's documents at most every 24 hours; a request past that is answered at once from the store (stale: true) while the refresh runs in the background. data.dataAsOf is the as-of date printed on the documents, typically the last month end for a factsheet and the last trading day for holdings files.

## About FundFacts API

FundFacts turns any fund or ETF ISIN into one dated factsheet (fees and KID costs, the 1–7 risk indicator, holdings, sector and country exposure, returns) read from the fund house's own documents: as a client-ready PDF under your firm's name for advisers and wealth managers, as JSON through its API for developers, or inside ChatGPT and Claude through its MCP server.

FundFacts works inside ChatGPT and Claude as a connector (a remote MCP server): ask about any fund or ETF by ISIN, ticker or name and the assistant answers from FundFacts' dated figures, fees, risk, holdings, exposure and returns, with fund cards, side-by-side comparisons, holdings overlap and portfolio look-through.

- Free API key, no card: https://fundfactsapi.com/signup
- Documentation: https://fundfactsapi.com/docs (OpenAPI: https://fundfactsapi.com/openapi.json)
- MCP server: https://fundfactsapi.com/api/mcp (set-up for every client: https://fundfactsapi.com/mcp-server)
- Pricing: https://fundfactsapi.com/pricing
- Site index for AI assistants: https://fundfactsapi.com/llms.txt
- Informational data, not investment advice.