How to get live gold prices in Google Sheets

Wire the goldprice.dev API into a custom =GOLDPRICE() formula via Apps Script. Free tier covers most workbooks.

Updated

To get live gold prices in Google Sheets, paste a small Apps Script custom function that calls api.goldprice.dev/v1/spot/XAU-USD-SPOT with your API key in the X-API-Key header. Then write =GOLDPRICE("XAU","USD") in any cell. The free tier covers 1,000 calls/month, no credit card. Tested April 2026 against Apps Script V8 runtime. Below: copy-pasteable function, multi-currency variants, and a 60-second cache that fits the quota.

  1. 1.Get your goldprice.dev API key

    Sign up for a free account at goldprice.dev/onboarding. After email confirmation, your dashboard shows a key starting with goldprice_live_. Copy it — you will paste it into Apps Script in the next step.

    The free tier includes 1,000 calls/month and supports gold spot in 13 native currencies. No credit card required.

    Get your free API key1,000 calls/mo, no credit card
    Sign up free →

  2. 2.Open Apps Script from your Sheet

    Open any Google Sheet. From the menu bar, click Extensions → Apps Script. A new tab opens with the script editor showing a default function myFunction() {}. Delete its contents — you will replace them in the next step.

  3. 3.Paste the GOLDPRICE custom function

    Replace the editor contents with this function. It calls the goldprice.dev API, caches the response for 60 seconds via CacheService, and handles the two most common errors (401 + 429).

    JAVASCRIPT · Code.gs
    // google-sheets: Apps Script Code.gs
// Replace API_KEY with your goldprice.dev key from /onboarding

const API_KEY = 'goldprice_live_replace_with_your_key_here';
const CACHE_TTL = 60; // seconds

function GOLDPRICE(metal, currency) {
  const symbol = `${metal}-${currency}-SPOT`;
  const cacheKey = `goldprice_${symbol}`;
  const cache = CacheService.getScriptCache();
  const cached = cache.get(cacheKey);
  if (cached) return Number(cached);

  const url = `https://api.goldprice.dev/v1/spot/${symbol}`;
  const resp = UrlFetchApp.fetch(url, {
    headers: { 'X-API-Key': API_KEY },
    muteHttpExceptions: true,
  });

  if (resp.getResponseCode() === 401) return '#AUTH_ERROR';
  if (resp.getResponseCode() === 429) return '#RATE_LIMIT';

  const data = JSON.parse(resp.getContentText());
  cache.put(cacheKey, String(data.price), CACHE_TTL);
  return Number(data.price); // API returns string; coerce so cells sort/sum
}

  4. 4.Use =GOLDPRICE() in any cell

    Back in your sheet, type =GOLDPRICE("XAU","USD") in any empty cell. After about one second the cell shows the current spot price. The same function works for silver (XAG), copper (HG), and the other supported metals. Press Cmd+R (Mac) or Ctrl+R (Windows) to force a recalculation.

    JAVASCRIPT · Sheets formula · multi-currency
    // USD spot
=GOLDPRICE("XAU", "USD")        // 4705.72

// INR — Indian gold market (largest retail search globally)
=GOLDPRICE("XAU", "INR")        // 393847.21

// EUR — European pricing surfaces
=GOLDPRICE("XAU", "EUR")        // 4321.10

// Silver in USD
=GOLDPRICE("XAG", "USD")        // 58.42

  5. 5.Add a time-driven trigger (optional)

    Apps Script does not auto-refresh custom functions on its own. To get live updates, add a time-based trigger that touches the cell every minute. From the Apps Script left rail, click Triggers → + Add Trigger, choose GOLDPRICE, event source Time-driven, type Minutes timer, interval Every minute. Save.

Expected output

The API returns this shape:

JSON · GET /v1/spot/XAU-USD-SPOT
{
  "symbol": "XAU",
  "quote_currency": "USD",
  "unit": "troy_ounce",
  "contract_type": "spot",
  "price": "4726.01",
  "bid": "4726.68",
  "ask": "4725.33",
  "is_stale": false,
  "divergence_flag": false,
  "computed_at": "2026-04-27T04:49:01.706844+00:00",
  "sources": [
    {
      "source": "wgc.fsapi.usd",
      "display_name": "World Gold Council (live spot)",
      "price": "4726.01",
      "unit": "troy_ounce",
      "timestamp": "2026-04-27T04:47:24+00:00",
      "is_stale": false,
      "fetched_at": "2026-04-27T04:48:06.072185+00:00",
      "source_timestamp": "2026-04-27T04:47:24+00:00"
    }
    /* + cmc.paxg + cmc.xaut entries — full sources[] in live response */
  ],
  "value_stale": false,
  "price_gram_24k": "151.9447",
  "price_gram_22k": "139.2827",
  "price_gram_18k": "113.9586",
  "price_gram_14k": "88.6344",
  "open_price": "4681.305",
  "high_price": "4729.56",
  "low_price": "4672.927",
  "prev_close_price": "4681.302",
  "ch": "44.6260",
  "chp": "0.9533",
  "open_time": 1777248000
}

The cell will display the price as a plain number (e.g., 4705.72). Format it as currency via Format → Number → Currency if you want a $ prefix.

Common errors

CodeSymptomFix
401Cell shows #AUTH_ERRORAPI key missing or invalid. Re-copy from your dashboard and paste into API_KEY at the top of Code.gs.
429Cell shows #RATE_LIMITYou hit the free-tier per-minute cap. The 60s cache should normally prevent this; if many cells call the function at once, raise CACHE_TTL to 300 (5 minutes).
N/ACell shows Loading… foreverApps Script needs external-fetch authorization. Click the function name in the editor toolbar and run it once manually to trigger the auth prompt, then accept.

FAQ

How often does the price update?

The spot endpoint refreshes every 60 seconds — Pyth + WGC + CME aggregation. Gold traded around $4,705/oz on . The cached function above respects this cadence; repeated calls within 60s return the same value.

Will this fit the free tier with auto-refresh?

Yes for most workbooks. Sheets recalculates =GOLDPRICE() on cell change, so it may fire ~3–50 calls/day depending on edit volume. The 1,000/mo free tier covers ~33 calls/day. The function above caches results for 60 seconds via CacheService so frequent edits stay safe. For very high-volume sheets, raise CACHE_TTL to 300 or upgrade — see /pricing for Basic and Pro tiers.

Can I use this commercially?

The Free tier is for personal use. For commercial use (apps, dashboards, products you sell), upgrade to Basic or Pro — see /pricing.

What metals are supported?

Gold (XAU), silver (XAG), copper (HG), platinum (XPT), palladium (XPD). Substitute the metal symbol in GOLDPRICE("<metal>","USD").

Is the free tier really free?

Yes — 1,000 calls/mo, no credit card, no expiration. Get the key at /onboarding.

Can I have Claude write this for me?

Yes. Open Claude Desktop with the goldprice.dev MCP server installed and ask: Build a Google Sheets custom function called GOLDPRICE that takes a metal symbol and currency and returns the live price via the goldprice.dev API. Add a 60-second cache. My API key is in env. The MCP server gives Claude access to the schema, so the generated function uses the correct field names and error codes on first try.

Does Apps Script work with Excel?

No. For Excel see the Excel tutorial — Excel uses Power Query M instead of Apps Script.

Going further

  • Build a price-alert formula: =IF(GOLDPRICE("XAU","USD") > 4800, "ALERT", "OK")
  • Track silver and copper alongside gold for portfolio dashboards
  • Convert ounces → grams via =GOLDPRICE("XAU","USD") / 31.1034768 (or read the inline price_gram_24k field)
  • Pull historical prices via /v1/history/XAU-USD-SPOT?days=30 for backtesting

Next steps

Try the same setup in a different platform:

ExcelWire the goldprice.dev API into Excel via Power Query M. Refreshes on workboo...Read tutorial →Power BIPull live gold prices into Power BI via Power Query M, with scheduled refresh...Read tutorial →ZapierPull live gold prices into Zapier via Webhooks by Zapier — Custom Request GET...Read tutorial →

Browse all 6 tutorials →

Ready to ship?Free tier: 1,000 calls/mo, no credit card. Upgrade for commercial use.
Get your API key →