How to get live gold prices in Google Sheets

// google-sheets: Apps Script Code.gs
const SPOT_CACHE_TTL = 55; // Cache successful spot responses for 55 seconds.
const BARS_CACHE_TTL = 300; // Cache successful daily bars for 5 minutes.
const REFRESH_RANGE_NAME = 'GOLDPRICE_REFRESH_TOKEN';
const SPREADSHEET_ID_KEY = 'GOLDPRICE_SPREADSHEET_ID';
const INSTALLER_EMAIL_KEY = 'GOLDPRICE_INSTALLER_EMAIL';
const LAST_REFRESH_AT_KEY = 'GOLDPRICE_LAST_REFRESH_AT';
const MIN_REFRESH_INTERVAL_MS = 4 * 60 * 60 * 1000;

/**
 * Returns the current spot price for a metal/currency pair.
 * @customfunction
 */
function GOLDPRICE(metal, currency, refreshToken) {
  const symbol = metal + '-' + currency + '-SPOT';
  const cache = CacheService.getScriptCache();
  const cacheKey = 'spot_' + symbol;
  const cached = cache.get(cacheKey);
  if (cached) return cached;

  const url = `https://api.goldprice.dev/v1/spot/${symbol}`;
  const resp = UrlFetchApp.fetch(url, {
    muteHttpExceptions: true,
  });

  const code = resp.getResponseCode();
  if (code === 401) return '#ACCESS_ERROR: use a server-side proxy';
  if (code === 403) return '#PLAN_GATED: symbol not on your tier';
  if (code === 429) return '#RATE_LIMIT: wait a minute and retry';
  if (code !== 200) return '#ERROR_' + code;

  const data = JSON.parse(resp.getContentText());
  if (!data || !data.price) return '#NO_DATA';

  // Price stays a string, same as the API wire format — Sheets displays and
  // sorts it fine. Wrap in VALUE() if you need it in arithmetic.
  cache.put(cacheKey, data.price, SPOT_CACHE_TTL);
  return data.price;
}

/**
 * Compares live spot with a reference cell in the same currency and unit.
 * Spills [signed price delta, signed percentage delta] into two cells.
 * @customfunction
 */
function GOLDPRICEDELTA(referencePrice, metal, currency, refreshToken) {
  const reference = Number(referencePrice);
  if (!Number.isFinite(reference) || reference <= 0) {
    return [['#BAD_REFERENCE: enter a positive price', '']];
  }

  const liveResult = GOLDPRICE(metal, currency, refreshToken);
  const live = Number(liveResult);
  if (!Number.isFinite(live)) return [[liveResult, '']];

  const signedDelta = live - reference;
  return [[Number(signedDelta.toFixed(2)), signedDelta / reference]];
}

/**
 * Returns chart-ready daily closes for a date range.
 * @customfunction
 */
function GOLDHISTORY(fromDate, toDate, refreshToken) {
  const now = new Date();
  const rollingFrom = new Date(now.getTime() - 29 * 24 * 60 * 60 * 1000);
  const from = toIsoDate_(
    fromDate === '' || fromDate == null ? rollingFrom : fromDate
  );
  const to = toIsoDate_(toDate === '' || toDate == null ? now : toDate);
  if (!from || !to) return [['#BAD_DATE: put real dates in B2/B3']];

  const cache = CacheService.getScriptCache();
  const cacheKey = 'bars_' + from + '_' + to;
  const cached = cache.get(cacheKey);
  if (cached) return JSON.parse(cached);

  const url = 'https://api.goldprice.dev/v1/bars?symbol=XAU-USD-SPOT&interval=1d'
    + '&from=' + from + '&to=' + to;
  const resp = UrlFetchApp.fetch(url, {
    muteHttpExceptions: true,
  });

  const code = resp.getResponseCode();
  if (code === 401) return [['#ACCESS_ERROR: use a server-side proxy']];
  if (code === 403) return [['#PLAN_GATED: range not on your tier']];
  if (code === 429) return [['#RATE_LIMIT: wait a minute and retry']];
  if (code !== 200) return [['#ERROR_' + code]];

  const data = JSON.parse(resp.getContentText());
  const bars = Array.isArray(data.bars) ? data.bars : [];
  if (bars.length === 0) return [['#NO_DATA: empty range']];

  // /v1/bars returns bars newest-first; reverse to chronological order so
  // a line chart built from this range reads left-to-right in time.
  const rows = bars.slice().reverse().map(function (bar) {
    return [bar.bar_start.slice(0, 10), Number(bar.close)];
  });

  cache.put(cacheKey, JSON.stringify(rows), BARS_CACHE_TTL);
  return rows;
}

function installGoldRefresh() {
  const lock = LockService.getScriptLock();
  lock.waitLock(30000);

  try {
    const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    if (!spreadsheet) throw new Error('Open the bound Google Sheet and run again');

    const installerEmail = Session.getEffectiveUser().getEmail();
    if (!installerEmail) throw new Error('Run the installer from your Google account');

    const properties = PropertiesService.getScriptProperties();
    const existingInstaller = properties.getProperty(INSTALLER_EMAIL_KEY);
    if (existingInstaller && existingInstaller !== installerEmail) {
      throw new Error('Gold refresh is already owned by another editor');
    }

    const refreshRange = spreadsheet.getActiveSheet().getRange('B1');
    spreadsheet.setNamedRange(REFRESH_RANGE_NAME, refreshRange);

    // getProjectTriggers() only returns this user's triggers. The shared
    // installer-email claim above prevents a second editor from creating a
    // second schedule, while this replacement keeps owner reruns idempotent.
    ScriptApp.getProjectTriggers()
      .filter(function (trigger) {
        return trigger.getHandlerFunction() === 'refreshGoldData';
      })
      .forEach(function (trigger) {
        ScriptApp.deleteTrigger(trigger);
      });

    ScriptApp.newTrigger('refreshGoldData')
      .timeBased()
      .everyHours(4)
      .create();

    properties.setProperties({
      [SPREADSHEET_ID_KEY]: spreadsheet.getId(),
      [INSTALLER_EMAIL_KEY]: installerEmail,
    });
  } finally {
    lock.releaseLock();
  }

  refreshGoldData();
}

function uninstallGoldRefresh() {
  const lock = LockService.getScriptLock();
  lock.waitLock(30000);

  try {
    const properties = PropertiesService.getScriptProperties();
    const installerEmail = Session.getEffectiveUser().getEmail();
    const existingInstaller = properties.getProperty(INSTALLER_EMAIL_KEY);
    if (existingInstaller && existingInstaller !== installerEmail) {
      throw new Error('Only the trigger owner can uninstall gold refresh');
    }

    ScriptApp.getProjectTriggers()
      .filter(function (trigger) {
        return trigger.getHandlerFunction() === 'refreshGoldData';
      })
      .forEach(function (trigger) {
        ScriptApp.deleteTrigger(trigger);
      });

    properties.deleteProperty(SPREADSHEET_ID_KEY);
    properties.deleteProperty(INSTALLER_EMAIL_KEY);
    properties.deleteProperty(LAST_REFRESH_AT_KEY);
  } finally {
    lock.releaseLock();
  }
}

function refreshGoldData(event) {
  const lock = LockService.getScriptLock();
  lock.waitLock(30000);

  try {
    // Clock triggers pass an event object, but they cannot supply the metal,
    // currency, date-range, or reference arguments required by sheet formulas.
    // This wrapper only changes the named token cell those formulas reference.
    const properties = PropertiesService.getScriptProperties();
    const spreadsheetId = properties.getProperty(SPREADSHEET_ID_KEY);
    if (!spreadsheetId) throw new Error('Run installGoldRefresh first');

    // Script properties and the script lock are shared across editors. This
    // gate coalesces legacy triggers owned by different users into one refresh
    // per four hours even though Apps Script cannot enumerate their triggers.
    const now = Date.now();
    const lastRefreshAt = Number(properties.getProperty(LAST_REFRESH_AT_KEY));
    if (lastRefreshAt && now - lastRefreshAt < MIN_REFRESH_INTERVAL_MS) return;

    const spreadsheet = SpreadsheetApp.openById(spreadsheetId);
    const refreshRange = spreadsheet.getRangeByName(REFRESH_RANGE_NAME);
    if (!refreshRange) throw new Error('Run installGoldRefresh to restore B1');
    refreshRange.setValue(new Date(now).toISOString());
    properties.setProperty(LAST_REFRESH_AT_KEY, String(now));
  } finally {
    lock.releaseLock();
  }
}

function toIsoDate_(value) {
  const d = value instanceof Date ? value : new Date(value);
  if (isNaN(d.getTime())) return null;
  return d.toISOString().slice(0, 10);
}

// B1: refresh token — written by refreshGoldData(), see step 5
// B2/B3: leave blank for a rolling 30-calendar-day range, or type fixed dates
// C1: optional reference quote normalized to USD per troy ounce

// Current USD spot — recalculates whenever B1 changes
=GOLDPRICE("XAU", "USD", $B$1)

// Live spot minus C1: spills [signed USD delta, signed percentage delta]
// Format the second result cell as Percent.
=GOLDPRICEDELTA($C$1, "XAU", "USD", $B$1)

// Current EUR spot — same token cell drives both
=GOLDPRICE("XAU", "EUR", $B$1)

// 30 days of daily closes, chart-ready (date, close)
=GOLDHISTORY($B$2, $B$3, $B$1)