Gold Price in Google Sheets and Excel: Live Prices via API

Live gold prices in a spreadsheet

A lot of gold pricing still happens in spreadsheets: a jeweler's price list, a bullion inventory, a scrap-gold quote sheet or a portfolio tracker. With a live gold price in Google Sheets or Excel, those numbers update by themselves instead of being copied from a website. This guide builds both: a =GOLDPRICE("EUR", "gram") custom function in Google Sheets with Apps Script, and a Power Query table in Excel, each refreshed automatically and with the API key kept out of the cells.

Both need a little code. MetalAPI returns JSON, and neither spreadsheet has a worksheet formula that parses JSON from a URL and sends an API key in a header. Apps Script and Power Query do both.

You need a MetalAPI key: create a free account, with no credit card, 100 requests per month and daily price updates. For Excel you also need a version with Power Query (Get & Transform), such as Excel for Microsoft 365 or Excel 2016 and later on Windows. Prices are as fresh as your plan allows: daily on Free, every 30 minutes on Lite, every 10 minutes on Standard and down to every 5 seconds on Enterprise.

How to get live gold prices in Google Sheets

The setup is a small Apps Script project bound to your spreadsheet: it reads the key from a script property, fetches prices with UrlFetchApp, caches them with CacheService and exposes custom functions for your cells. A time-driven trigger keeps them fresh.

1. Open the spreadsheet and choose Extensions → Apps Script. This creates a script project bound to that file.
2. In the Apps Script editor, open Project Settings (the gear icon), scroll to Script Properties and click Add script property. Enter METALAPI_KEY as the property, paste your key as the value and click Save script properties.
3. Back in the Editor, replace the contents of Code.gs with the two code blocks below and save.
4. Choose refreshPrices in the function menu and click Run. Google asks you once to authorize the script to connect to an external service and edit the spreadsheet.

Keep your API key out of the cells

Don't paste the key into a cell and build the URL with a formula. A key in a cell is visible to everyone who can open the spreadsheet, travels with every copy and export, and stays in the version history after you delete it. Anyone who has it can spend your quota.

Script properties are the better place: they aren't part of the grid, so they don't appear in cells, exports or version history. They are not a vault, though. Anyone with edit access to the spreadsheet can open Extensions → Apps Script and read them. So:

• Use a separate key per spreadsheet, so you can revoke it without breaking other integrations. The authentication guide covers creating and rotating keys.
• Give edit access only to people you would give the key to. Viewers and commenters can't open the script editor, but a copy of the spreadsheet includes a copy of its bound script. For widely shared files, turn off copying for viewers and commenters in the share settings.
• Send the key in a header. The script uses X-API-Key rather than the api_key parameter, so the key doesn't end up in logged URLs.
• Rotate when in doubt: create a new key, update the script property, then revoke the old key in the dashboard.

A GOLDPRICE custom function for Google Sheets

Paste this into Code.gs. METALPRICE covers the four precious metals and GOLDPRICE is a shortcut for gold. One request returns all four metals, so gold and silver in the same currency and unit share one cached response.

// MetalAPI prices for Google Sheets. The API key is read from the
// script property METALAPI_KEY (Project Settings), never from a cell.
const API = "https://metalapi.com/api/v1";
const SYMBOLS = ["XAU", "XAG", "XPT", "XPD"];
const CACHE_SECONDS = 3600; // 1 hour; Apps Script allows up to 21600

function fetchLatest_(base, unit) {
  const key = PropertiesService.getScriptProperties().getProperty("METALAPI_KEY");
  if (!key) throw new Error("Add the script property METALAPI_KEY first.");
  const url = API + "/latest?base=" + encodeURIComponent(base) +
    "&currencies=" + SYMBOLS.join(",") + "&unit=" + encodeURIComponent(unit);
  const res = UrlFetchApp.fetch(url, {
    headers: { "X-API-Key": key },
    muteHttpExceptions: true, // return error responses instead of throwing
  });
  let data;
  try {
    data = JSON.parse(res.getContentText());
  } catch (e) {
    throw new Error("MetalAPI: unexpected HTTP " + res.getResponseCode() + " response");
  }
  if (!data.success) {
    throw new Error("MetalAPI " + data.error.code + " (" + data.error.type + "): " + data.error.info);
  }
  return data;
}

function cachedLatest_(base, unit, refresh) {
  const cache = CacheService.getScriptCache();
  const cacheKey = ["latest", base, unit, refresh].join("|");
  const hit = cache.get(cacheKey);
  if (hit) return JSON.parse(hit);
  const data = fetchLatest_(base, unit);
  cache.put(cacheKey, JSON.stringify(data), CACHE_SECONDS);
  return data;
}

/**
 * Latest price of a precious metal from MetalAPI.
 *
 * @param {string} symbol XAU (gold), XAG (silver), XPT (platinum) or XPD (palladium).
 * @param {string} currency Currency code such as USD, EUR or GBP. Default USD.
 * @param {string} unit troy_oz (default), gram or kilogram.
 * @param {any} refresh Optional cell such as Prices!B1. When it changes, prices are fetched again.
 * @return The price of one unit of the metal in the currency.
 * @customfunction
 */
function METALPRICE(symbol, currency, unit, refresh) {
  const s = String(symbol || "XAU").trim().toUpperCase();
  const base = String(currency || "USD").trim().toUpperCase();
  const u = String(unit || "troy_oz").trim().toLowerCase();
  const data = cachedLatest_(base, u, String(refresh || ""));
  const price = data.rates[data.base + s];
  if (price === undefined) throw new Error("No " + s + " price in " + base + ".");
  return price;
}

/**
 * Latest gold price from MetalAPI, for example =GOLDPRICE("EUR", "gram").
 *
 * @param {string} currency Currency code such as USD, EUR or GBP. Default USD.
 * @param {string} unit troy_oz (default), gram or kilogram.
 * @param {any} refresh Optional cell such as Prices!B1. When it changes, prices are fetched again.
 * @return The price of gold in the currency per unit.
 * @customfunction
 */
function GOLDPRICE(currency, unit, refresh) {
  return METALPRICE("XAU", currency, unit, refresh);
}

Now type a formula into any cell:

Under the hood:

• @customfunction adds the functions to formula autocomplete, with the comment as help text.
• muteHttpExceptions: true returns error responses instead of throwing, so the cell shows #ERROR! with MetalAPI's code and message on hover, such as 201 (invalid_base) for an unknown currency.
• CacheService keeps each response for CACHE_SECONDS, at most six hours. Cells with the same currency, unit and refresh value share it.
• Each uncached currency and unit costs one request, and each formula cell is a separate call to Google's servers. For sheets with hundreds of price cells, reference the Prices sheet below instead.

Validation errors such as an unknown currency are billed, so fix broken formulas promptly. The rate limits guide lists what counts.

Refresh the gold price in Google Sheets with a time-driven trigger

Google Sheets re-runs a custom function when its arguments change, not on a clock; the recalculation setting under File → Settings → Calculation only affects functions such as NOW and RAND. For scheduled updates, let a time-driven trigger write prices into a sheet. Add this below part 1:

/** Writes the latest prices to a sheet named "Prices". Run it from a time-driven trigger. */
function refreshPrices() {
  const data = fetchLatest_("USD", "troy_oz");
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("Prices") || ss.insertSheet("Prices");
  const rows = [
    ["Updated", new Date(data.timestamp * 1000)],
    ["Symbol", data.base + " per troy oz"],
  ];
  SYMBOLS.forEach(function (s) {
    rows.push([s, data.rates[data.base + s]]);
  });
  sheet.getRange(1, 1, rows.length, 2).setValues(rows);
}

/** Run once from the editor. Replaces any existing schedule for refreshPrices. */
function installTrigger() {
  ScriptApp.getProjectTriggers().forEach(function (t) {
    if (t.getHandlerFunction() === "refreshPrices") ScriptApp.deleteTrigger(t);
  });
  ScriptApp.newTrigger("refreshPrices").timeBased().everyHours(1).create();
}

Run installTrigger once from the editor, or add the trigger by hand under Triggers (the clock icon) → Add Trigger with refreshPrices, Time-driven and an interval.

Each run costs one request and writes the data time to Prices!B1 and one row per metal below it; Prices!B3 is gold in US dollars per troy ounce. Reference those cells directly, or pass Prices!B1 as the refresh argument: =GOLDPRICE("EUR", "gram", Prices!B1) fetches again whenever the trigger writes a new data time, once per currency and unit, and not at all over a weekend, when the time doesn't change. For the Prices sheet in another currency or unit, change the arguments of fetchLatest_ in refreshPrices.

Match the interval to your plan; refreshing faster than prices update only repeats them:

Custom functions with the refresh argument add one request per refresh for each currency and unit. On Lite, an hourly trigger plus GOLDPRICE("EUR", "gram", Prices!B1) can reach about 1,500 requests a month, over the plan's 1,000; use .everyHours(2) or the Prices sheet alone. Triggers run close to, not exactly at, the scheduled time, and atHour uses the script's time zone.

Gold price in Excel with Power Query

In Excel, Power Query calls the API, parses the JSON and loads a table that refreshes on a schedule:

1. On the Data tab, choose Get Data → From Other Sources → Blank Query.
2. In the Power Query Editor, choose Home → Advanced Editor, paste the query below and click Done.
3. When asked how to connect to https://metalapi.com/, click Edit Credentials, choose Web API, paste your key into Key and click Connect.
4. Rename the query GoldPrices and choose Home → Close & Load. Excel loads it into a new sheet as a table named after the query.

let
    BaseCurrency = "EUR",
    MassUnit = "gram",
    Symbols = {"XAU", "XAG", "XPT", "XPD"},
    Response = Json.Document(
        Web.Contents(
            "https://metalapi.com/api/v1/latest",
            [
                Query = [base = BaseCurrency, currencies = Text.Combine(Symbols, ","), unit = MassUnit],
                ApiKeyName = "api_key"
            ]
        )
    ),
    Updated = #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, Response[timestamp]),
    Prices = Table.FromRecords(
        List.Transform(
            Symbols,
            (symbol) => [
                Symbol = symbol,
                Price = Record.Field(Response[rates], Response[base] & symbol),
                Currency = Response[base],
                Unit = MassUnit,
                #"Updated (UTC)" = Updated
            ]
        )
    ),
    Typed = Table.TransformColumnTypes(
        Prices,
        {{"Symbol", type text}, {"Price", type number}, {"Currency", type text},
         {"Unit", type text}, {"Updated (UTC)", type datetime}}
    )
in
    Typed

The query reads the key prefixed with the response's base (EURXAU for EUR) and converts the Unix timestamp to UTC. Change BaseCurrency and MassUnit to suit you.

ApiKeyName = "api_key" keeps the key out of the file: Power Query takes it from the Web API credential and appends it as the api_key query parameter, one of the three ways MetalAPI accepts a key. The credential lives in Excel's data source settings on your computer, not in the workbook, so a colleague who opens the file is asked for their own key. Change or remove it under Data → Get Data → Data Source Settings.

Web.Contents also accepts Headers = [#"X-API-Key" = "..."], but that saves the key inside the workbook. Use it only for files that never leave your computer.

In formulas, look prices up in the table: =XLOOKUP("XAU", GoldPrices[Symbol], GoldPrices[Price]) returns gold in euros per gram, and INDEX with MATCH does the same in older versions.

Refresh gold prices in Excel automatically

A Power Query table doesn't refresh on its own. Open Data → Queries & Connections, right-click the GoldPrices query and choose Properties. Under Refresh control:

• Refresh every N minutes re-runs the query on a timer.
• Refresh data when opening the file fetches fresh prices each time the workbook is opened.
• Enable background refresh keeps Excel usable while the query runs.

Data → Refresh All (Ctrl+Alt+F5) refreshes on demand. The timer only runs while the workbook is open.

Each refresh is one request. Hourly during an eight-hour working day comes to around 180 a month, every 10 minutes to around 1,050. On Free, where prices update daily, refreshing when the file opens is enough.

Historical gold prices in Sheets and Excel

Past closing prices come from /timeframe: one entry per trading day between start_date and end_date, up to 30 days per request on Free and 365 on paid plans. In Excel, a second query gives a date column and one column per metal:

let
    Response = Json.Document(
        Web.Contents(
            "https://metalapi.com/api/v1/timeframe",
            [
                Query = [start_date = "2026-09-24", end_date = "2026-10-23", currencies = "XAU,XAG"],
                ApiKeyName = "api_key"
            ]
        )
    ),
    Days = Record.ToTable(Response[rates]),
    History = Table.FromRecords(
        List.Transform(
            Table.ToRecords(Days),
            (row) => [
                Date = Date.FromText(row[Name]),
                Gold = Record.FieldOrDefault(row[Value], Response[base] & "XAU", null),
                Silver = Record.FieldOrDefault(row[Value], Response[base] & "XAG", null)
            ]
        )
    ),
    Typed = Table.TransformColumnTypes(History, {{"Date", type date}, {"Gold", type number}, {"Silver", type number}})
in
    Typed

Weekends and market holidays are missing, so expect fewer rows than calendar days. In Apps Script, call /timeframe with UrlFetchApp the way fetchLatest_ calls /latest and write all rows with one setValues call.

Past closes never change, so load a range once and keep it. Historical gold and silver prices via API covers long backfills, storage and charts.

Next steps

Create a free account, add the key as a script property or a Web API credential, and you have a gold price in your spreadsheet in about ten minutes. The Free plan's daily prices suit a daily price list or portfolio sheet; for fresher prices or more refreshes, compare plans on the pricing page. The API docs list every endpoint you can call the same way, including /convert, and /carat for karat gold prices on paid plans.

FAQ

Can I import gold prices with IMPORTDATA or IMPORTXML instead?

Not from MetalAPI. Those functions read CSV, TSV, XML or HTML, while the API returns JSON, and they can't send a header, so the key would sit in the formula for every viewer to see.

How often does the gold price update in my spreadsheet?

As often as you refresh it and your plan allows: daily on Free, every 30 minutes on Lite, every 10 minutes on Standard and faster on higher plans. From Friday 17:00 to Sunday 17:00 New York time the spot market is closed and the price stays at Friday's close.

How many API requests will my spreadsheet use?

One per refresh of the Prices sheet or Power Query table, plus one per currency and unit when custom functions fetch fresh prices. An hourly trigger is about 744 requests a month, a daily one about 31. /usage reports your consumption without counting towards the quota.

Is it safe to share a spreadsheet that pulls gold prices?

Yes, if the key stays out of the cells: a script property in Google Sheets, with edit access only for people you trust with the key, and the Web API credential in Excel. Use a separate key per shared file so you can revoke it on its own.

Can I get the gold price per gram or in my local currency?

Yes. Pass a currency code and gram or kilogram, as in =GOLDPRICE("INR", "gram"), or change BaseCurrency and MassUnit in the Power Query. The API converts both from the same snapshot.