Notionの保有銘柄DBの株価をGASで毎朝自動更新する方法【Yahoo Finance API】

const CONFIG = {
  NOTION_VERSION: '2022-06-28',
  NOTION_API_BASE: 'https://api.notion.com/v1',
  YAHOO_CHART_ENDPOINT: 'https://query1.finance.yahoo.com/v8/finance/chart/',
  YAHOO_RANGE: '5d',
  YAHOO_INTERVAL: '1d',
  PROPERTY_KEYS: {
    TICKER: 'ティッカーシンボル',
    PRICE: '価格',
    PRICE_DATE: '価格取得日'
  },
  RETRY_COUNT: 3,
  RETRY_WAIT_MS: 2000,
  NOTION_THROTTLE_MS: 400
};

function syncStockPrices() {
  var pages = queryAllPages(getNotionDatabaseId_());
  console.log('対象ページ数: ' + pages.length);

  var ok = 0;
  var ng = 0;

  pages.forEach(function(page) {
    var ticker = extractTicker(page);
    if (!ticker) {
      console.warn('ティッカー取得失敗: ' + page.id);
      ng++;
      return;
    }

    try {
      var result = fetchLatestClose(ticker);
      var props = buildPriceProperties(result.price, result.time);
      updatePageProperty(page.id, props);
      console.log(ticker + ': ' + result.price + ' を更新');
      ok++;
    } catch (e) {
      console.error(ticker + ' でエラー: ' + e.message);
      ng++;
    }

    Utilities.sleep(CONFIG.NOTION_THROTTLE_MS);
  });

  console.log('done: ok=' + ok + ', ng=' + ng);
}

function fetchLatestClose(ticker) {
  const url = CONFIG.YAHOO_CHART_ENDPOINT + encodeURIComponent(ticker)
    + '?interval=' + CONFIG.YAHOO_INTERVAL
    + '&range=' + CONFIG.YAHOO_RANGE;

  var lastErr = null;
  for (var attempt = 0; attempt < CONFIG.RETRY_COUNT; attempt++) {
    try {
      var res = UrlFetchApp.fetch(url, { muteHttpExceptions: true });
      var code = res.getResponseCode();
      if (code < 200 || code >= 300) {
        throw new Error('Yahoo HTTP ' + code + ': ' + res.getContentText().slice(0, 200));
      }
      var json = JSON.parse(res.getContentText());
      var result = json.chart && json.chart.result && json.chart.result[0];
      if (!result || !result.meta) {
        throw new Error('Yahoo レスポンス形式不正: ' + ticker);
      }
      var price = result.meta.regularMarketPrice;
      var time = result.meta.regularMarketTime;
      if (typeof price !== 'number' || typeof time !== 'number') {
        throw new Error('regularMarketPrice / regularMarketTime が取れない: ' + ticker);
      }
      return { price: price, time: time };
    } catch (e) {
      lastErr = e;
      if (attempt < CONFIG.RETRY_COUNT - 1) {
        Utilities.sleep(CONFIG.RETRY_WAIT_MS);
      }
    }
  }
  throw lastErr;
}

function getNotionDatabaseId_() {
  const databaseId = PropertiesService.getScriptProperties().getProperty('NOTION_DATABASE_ID');
  if (!databaseId) {
    throw new Error('NOTION_DATABASE_ID がスクリプトプロパティに未設定です。GAS UI → プロジェクト設定 → スクリプトプロパティで登録してください。');
  }
  return databaseId;
}

function getNotionToken_() {
  const token = PropertiesService.getScriptProperties().getProperty('NOTION_API_KEY');
  if (!token) {
    throw new Error('NOTION_API_KEY がスクリプトプロパティに未設定です。GAS UI → プロジェクト設定 → スクリプトプロパティで登録してください。');
  }
  return token;
}

function notionHeaders_() {
  return {
    'Authorization': 'Bearer ' + getNotionToken_(),
    'Notion-Version': CONFIG.NOTION_VERSION,
    'Content-Type': 'application/json'
  };
}

function queryAllPages(databaseId) {
  const url = CONFIG.NOTION_API_BASE + '/databases/' + databaseId + '/query';
  const results = [];
  let startCursor = null;

  do {
    const payload = { page_size: 100 };
    if (startCursor) payload.start_cursor = startCursor;

    const res = UrlFetchApp.fetch(url, {
      method: 'post',
      headers: notionHeaders_(),
      payload: JSON.stringify(payload),
      muteHttpExceptions: true
    });

    const code = res.getResponseCode();
    if (code < 200 || code >= 300) {
      throw new Error('Notion query 失敗 (' + code + '): ' + res.getContentText());
    }

    const json = JSON.parse(res.getContentText());
    (json.results || []).forEach(function(p) { results.push(p); });
    startCursor = json.has_more ? json.next_cursor : null;
  } while (startCursor);

  return results;
}

function updatePageProperty(pageId, properties) {
  const url = CONFIG.NOTION_API_BASE + '/pages/' + pageId;
  const res = UrlFetchApp.fetch(url, {
    method: 'patch',
    headers: notionHeaders_(),
    payload: JSON.stringify({ properties: properties }),
    muteHttpExceptions: true
  });
  const code = res.getResponseCode();
  if (code < 200 || code >= 300) {
    throw new Error('Notion update 失敗 (' + code + '): ' + res.getContentText());
  }
}

function extractTicker(page) {
  const richText = page &&
    page.properties &&
    page.properties[CONFIG.PROPERTY_KEYS.TICKER] &&
    page.properties[CONFIG.PROPERTY_KEYS.TICKER].rich_text;
  if (!richText || richText.length === 0) return null;
  const content = richText[0].text && richText[0].text.content;
  if (!content) return null;
  return content.trim();
}

function buildPriceProperties(price, unixTime) {
  const iso = new Date(unixTime * 1000).toISOString();
  const props = {};
  props[CONFIG.PROPERTY_KEYS.PRICE] = { number: price };
  props[CONFIG.PROPERTY_KEYS.PRICE_DATE] = { date: { start: iso } };
  return props;
}