Stock prices in Google Sheets,
one formula away.

Daily prices with IMPORTDATA

1. Create an API key, ideally one used only for this spreadsheet.
2. In an empty cell, enter an IMPORTDATA formula with format=csv and api_key in the URL.
3. The header row and data fill the cells to the right and below. Leave that range empty.

=IMPORTDATA("https://api.ohlc.io/v1/eod?symbols=XNAS:AAPL&from=2026-01-01&limit=500&format=csv&api_key=YOUR_API_KEY")

To show just the latest close, look the column up by name so the formula keeps working if columns are added:

=LET(t, IMPORTDATA("https://api.ohlc.io/v1/eod?symbols=XNAS:AAPL&order=desc&limit=1&format=csv&api_key=YOUR_API_KEY"), INDEX(t, 2, MATCH("close", INDEX(t, 1, 0), 0)))

Keep the key private in a shared sheet

Anyone who can view a sheet can read its formulas, including a key inside an IMPORTDATA URL. For a shared sheet, keep the key in Apps Script instead: open Extensions → Apps Script, save the key under Project Settings → Script properties as OHLC_API_KEY, and add this function. Viewers cannot see script properties; editors can.

function OHLC(path) {
  const key = PropertiesService.getScriptProperties().getProperty("OHLC_API_KEY");
  const url = "https://api.ohlc.io" + path + (path.includes("?") ? "&" : "?") + "format=csv";
  const response = UrlFetchApp.fetch(url, { headers: { Authorization: "Bearer " + key } });
  return Utilities.parseCsv(response.getContentText())
    .map((row) => row.map((v) => (v !== "" && !isNaN(v) ? Number(v) : v)));
}

=OHLC("/v1/eod?symbols=XNAS:AAPL&from=2026-01-01&limit=500")

Useful URLs for a stock spreadsheet

Any GET data endpoint works the same way. These cover most portfolio and research sheets; append &format=csv&api_key=YOUR_API_KEY to each, or pass the path to the OHLC function above.

The Free plan covers AAPL, NVDA, JNJ, PEP, MSFT, SPY and QQQ, which is enough to build and test a sheet. Developer and above cover all U.S.-listed stocks and ETFs, and Pro and above add Canada, the UK, France, the Netherlands and Australia. List-valued fields, such as the legs of a dividend, arrive as JSON text in a single cell.

What to know before you build a dashboard

• Google fetches the URL from its own servers, not your network, so a key restricted by an IP allowlist is refused. Use a key without one, scoped to the datasets the sheet needs.
• Sheets re-fetches imports on its own schedule. Every fetch is a request that counts toward your plan's credits and rate limit: daily bars cost 5 credits per symbol.
• A CSV response holds one page of up to 500 rows. Split long histories into several formulas by from and to; the Apps Script route can also read the X-Next-Cursor header to page.
• Daily bars are end-of-day values. For a delayed intraday price use /v1/quotes and its last_price column, at least 15 minutes old.
• Keep imports on one data tab and point charts, pivot tables and calculations at that range. One import per dataset is easier to audit and spends far fewer credits than an import in every cell.
• Timestamps such as last_at are UTC. Daily bars carry the exchange-local trading date in date, so they line up with the dates on a stock chart.

If a sheet or URL containing a key is shared by mistake, revoke the key in the portal and create a new one. A separate key per spreadsheet keeps the damage to one sheet.