Pull live prices and exchange rates into Google Sheets or Excel

The request behind the cell

A sheet formula or script that shows a live price sends a GET request with an Authorization header and reads one field of the JSON that comes back. The first request asks for the gold spot price in US dollars. The field a cell needs is price.

curl -s -H "Authorization: Bearer $NARWHAL_API_KEY" \
  "https://api.narwhalapi.com/v1/commodities/spot/XAU/USD"

{
  "symbol": "XAU-USD",
  "commodity_key": "gold",
  "price": "3946.7972",
  "currency": "USD",
  "unit": "troy_ounce",
  "unit_key": "troy_ounce",
  "timestamp": "2026-11-03T02:29:00Z",
  "stale": false
}

price is a string with four decimals, unit says it is per troy ounce, and timestamp is when the price belongs to. stale is false when the price is current. Put timestamp in the next column so every price in the sheet carries its time.

Convert an amount in the same way

GET /v1/fx/convert takes from, to and amount and returns the converted figure. This request converts 1,000 US dollars to Indonesian rupiah.

curl -s -H "Authorization: Bearer $NARWHAL_API_KEY" \
  "https://api.narwhalapi.com/v1/fx/convert?from=USD&to=IDR&amount=1000"

{
  "from": "USD",
  "to": "IDR",
  "rate": "18135.35",
  "converted": "18135350.00",
  "market_session": "open",
  "timestamp": "2026-11-03T02:29:46.406043Z",
  "stale": false
}

converted is the amount in the target currency, rate is the rate used, and timestamp and stale work as they do for gold. Both numbers arrive as text, so the sheet has to turn them into numbers. For a rate table of several currencies, GET /v1/fx/rates/USD?currencies=EUR,IDR returns a rates list, one entry per currency.

Google Sheets with Apps Script

A sheet cannot send the header from a plain formula, so use Apps Script and UrlFetchApp. The IMPORTDATA and IMPORTXML functions have no way to send an Authorization header. Store the key in Script properties under the name NARWHAL_API_KEY, never in a cell, and let the script read it. The function below appends one row each time it runs: the date, the gold price, its timestamp and stale flag, then the rupiah amount, its timestamp and stale flag.

// Google Apps Script. Not run here: the two request URLs are the ones called with curl above.
function refreshRates() {
  const key = PropertiesService.getScriptProperties().getProperty("NARWHAL_API_KEY");
  const options = { headers: { Authorization: "Bearer " + key } };

  const gold = JSON.parse(UrlFetchApp.fetch(
    "https://api.narwhalapi.com/v1/commodities/spot/XAU/USD", options).getContentText());
  const idr = JSON.parse(UrlFetchApp.fetch(
    "https://api.narwhalapi.com/v1/fx/convert?from=USD&to=IDR&amount=1000", options).getContentText());

  SpreadsheetApp.getActiveSheet().appendRow([
    new Date(), Number(gold.price), gold.timestamp, gold.stale,
    Number(idr.converted), idr.timestamp, idr.stale,
  ]);
}

This script runs inside Google Sheets and is not run here. Its two request URLs are the ones requested with curl above, and the fields it reads, price, timestamp, stale and converted, are the ones in those responses. Run refreshRates once from the editor to approve the access it asks for, then open Triggers in the Apps Script editor and add a time-driven trigger, for example hourly or daily, so the sheet fills without you.

Avoid wrapping the request in a custom function used as a cell formula. Sheets runs custom functions again whenever the sheet recalculates, and each run sends a request. A script run by a trigger or a menu item sends one request per run and writes the value once.

Excel with Power Query

Excel has no formula that sends a header either, so use Power Query. On the Data tab choose Get Data, then From Other Sources, then Blank Query, and open the Advanced Editor. In the query, call Web.Contents with https://api.narwhalapi.com as the fixed address, the path such as v1/commodities/spot/XAU/USD in RelativePath, any query parameters in Query, and the Authorization header with the value Bearer followed by your key. Read the JSON with Json.Document, take the price field and convert it with Number.FromText.

When Excel asks about credentials for the web source, choose Anonymous. Power Query allows the header only with that setting, and the request still carries your key from the query. The key is stored inside the workbook, so share the file only with people who may see it. In the query properties you can set the query to refresh when the file opens and every few minutes while it is open. Excel has no background scheduler, so for an unattended refresh use Power Automate or a script outside Excel. The Excel tutorial has the full query, and the Power BI tutorial shows the same query with a scheduled refresh in the Power BI service.

Which route fits

The Google Sheets tutorial has a shared helper that writes any endpoint into its own tab. For a daily snapshot outside a spreadsheet, see the daily data pull guide.

Why does the price arrive as text?

Narwhal sends decimal numbers as strings so no digits are lost. "3946.7972" is text until the script converts it. In Apps Script use Number(...), and in Power Query use Number.FromText. Convert the string only at the point where you write the cell, and keep the original string if you need to compare it exactly.

Can IMPORTDATA read the API?

No. IMPORTDATA cannot send an Authorization header, and every Narwhal request needs one. A script with UrlFetchApp sends the header, so use it for any live value in Google Sheets.

Should the sheet check the stale flag?

Yes, keep it next to each value. stale is true when a price is out of date while its market is open. The script above writes the flag into the row, so a filter or a conditional format can hide any row where it is TRUE. A closed market does not make a price stale.