Google Sheetsでリアルタイムの金価格を取得する方法

// google-sheets: Apps Script Code.gsconst 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: 更新用トークンです。refreshGoldData()が書き込みます。// B2/B3: 空欄にすると直近30暦日が対象になります。期間を固定する場合は日付を入力します。// C1: トロイオンス当たりのUSD建てに換算した基準価格です。入力は任意です。// 現在のUSD建てスポット価格です。B1が変わるたびに再計算します。=GOLDPRICE("XAU", "USD", $B$1)// 現在のスポット価格からC1を引き、[符号付きのUSD差額, 符号付きの変化率]を複数セルに展開します。// 結果の2番目のセルの表示形式をパーセントに設定します。=GOLDPRICEDELTA($C$1, "XAU", "USD", $B$1)// 現在のEUR建てスポット価格です。同じトークンセルで両方の価格を更新します。=GOLDPRICE("XAU", "EUR", $B$1)// グラフに使える30日分の日次終値です（日付, 終値）。=GOLDHISTORY($B$2, $B$3, $B$1)

{  "symbol": "XAU",  "quote_currency": "USD",  "unit": "troy_ounce",  "contract_type": "spot",  "price": "4012.75",  "is_stale": false,  "computed_at": "2026-10-23T07:00:18Z"}