XYZ的筆記本: Google Sheets 抓取公開資訊觀測站股利公告

在 Google Sheets 使用 Apps Script 抓取公開資訊觀測站(公告快易查)股票股利公告。

步驟：

新增一個空白工作表
到「擴充功能」 -> 「Apps Script」

填入以下程式碼

function fetchDividendToSheet() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const lastRowS = sheet.getLastRow();
  Logger.log(`lastRowS：${lastRowS} `);
  // ══════════════════════════════════════════════
  // 設定區
  // ══════════════════════════════════════════════
  let SDATE = "115/02/10";//開始日期
  let EDATE = "115/02/10";//結束日期
  const BATCH_SIZE = 100;

  // ── 日期工具函式 ─────────────────────────────
  function rocToDate(rocStr) {             // "115/02/26" → Date
    const [y, m, d] = rocStr.split("/");
    return new Date(+y + 1911, +m - 1, +d);
  }
  function toRocDate(dt) {                 // Date → "115/02/26"
    return `${dt.getFullYear() - 1911}/${String(dt.getMonth() + 1).padStart(2, '0')}/${String(dt.getDate()).padStart(2, '0')}`;
  }
  function rocToAd(rocStr) {              // "115/02/26" → "2026/02/26"
    if (!rocStr) return "";
    const [y, m, d] = rocStr.split("/");
    return `${+y + 1911}/${m}/${d}`;
  }

  Logger.log(`本次查詢：${SDATE} ～ ${EDATE}`);

  // ── 呼叫 ezsearch_query ───────────────────────
  const searchResp = UrlFetchApp.fetch(
    "https://mopsov.twse.com.tw/mops/web/ezsearch_query",
    {
      method: "post",
      payload: `step=00&RADIO_CM=1&TYPEK=sii&CO_MARKET=&CO_ID=&PRO_ITEM=D02&SUBJECT=&SDATE=${SDATE}&EDATE=${EDATE}&lang=TW&AN=`,
      headers: {
        "Content-Type": "application/x-www-form-urlencoded;charset=UTF-8",
        "Referer": "https://mopsov.twse.com.tw/mops/web/ezsearch",
        "X-Requested-With": "XMLHttpRequest",
        "User-Agent": "Mozilla/5.0"
      },
      muteHttpExceptions: true
    }
  );
  const records = JSON.parse(searchResp.getContentText("UTF-8")).data || [];
  Logger.log(`API 回傳共 ${records.length} 筆`);

  // ── F36：align='right' TD ────────────────────
  function extractF36(html) {
    const strip = s => s.replace(/<[^>]+>/g, '').replace(/&nbsp;/g, '').trim();
    const rightTds = [...html.matchAll(/<TD[^>]*align=['"]right['"][^>]*>([\s\S]*?)<\/TD>/gi)]
      .map(m => strip(m[1]));
    Logger.log("F36 right TDs: " + rightTds.join(" | "));
    if (rightTds.length < 7) return ["0", "0", "0", "0", "0", "0"];
    return [rightTds[0] || "0", rightTds[1] || "0", rightTds[2] || "0",
    rightTds[4] || "0", rightTds[5] || "0", rightTds[6] || "0"];
  }

  // ── M14：<pre> 全形冒號 ───────────────────────
  function extractM14(html) {
    const preMatch = html.match(/<pre[^>]*>([\s\S]*?)<\/pre>/i);
    if (!preMatch) return ["0", "0", "0", "0", "0", "0"];
    const lines = preMatch[1].split('\n').map(l => l.trim()).filter(l => l);
    function findVal(kw) {
      const line = lines.find(l => l.includes(kw));
      if (!line) return "0";
      const parts = line.split('：');
      return parts.length > 1 ? parts[parts.length - 1].trim() : "0";
    }
    return [
      findVal("盈餘分配之現金股利"), findVal("法定盈餘公積發放之現金"),
      findVal("資本公積發放之現金"), findVal("盈餘轉增資配股"),
      findVal("法定盈餘公積轉增資配股"), findVal("資本公積轉增資配股"),
    ];
  }

  // ── 初次執行時寫入表頭（工作表為空才加）────────
  if (sheet.getLastRow() === 0) {
    sheet.appendRow([
      "發言日期", "發言時間", "公司代號", "公司簡稱", "AN_CODE", "主旨",  // A~F
      "盈餘分配之現金股利(元/股)",      // G
      "法定盈餘公積發放之現金(元/股)",  // H
      "資本公積發放之現金(元/股)",      // I
      "盈餘轉增資配股(元/股)",          // J
      "法定盈餘公積轉增資配股(元/股)",  // K
      "資本公積轉增資配股(元/股)"       // L
    ]);
  }

  // ── 逐筆處理 ─────────────────────────────────
  const ZERO = ["0", "0", "0", "0", "0", "0"];
  let batch = [], totalWritten = 0;
  let skipCount = 0, zeroCount = 0, f36Count = 0, m14Count = 0;

  function flushBatch() {
    if (!batch.length) return;
    const startRow = sheet.getLastRow() + 1;
    sheet.getRange(startRow, 1, batch.length, batch[0].length).setValues(batch);
    totalWritten += batch.length;
    Logger.log(`📥 已寫入 ${totalWritten} 筆`);
    batch = [];
  }

  records.forEach(rec => {
    const subject = (rec.SUBJECT || "").replace(/[\r\n]/g, " ");
    const coId = rec.COMPANY_ID || "";
    const coName = rec.COMPANY_NAME || "";
    const anCode = rec.AN_CODE || "";
    const hyperlink = rec.HYPERLINK || "";
    const cdateAd = rocToAd(rec.CDATE || "");
    const ctime = rec.CTIME || "";

    // 篩選
    if (subject.includes("子公司")) { skipCount++; return; }
    if (subject.includes("基準日")) { skipCount++; return; }
    if (subject.includes("交易日")) { skipCount++; return; }
    if (subject.includes("發放日")) { skipCount++; return; }
    if (!subject.includes("股利")) { skipCount++; return; }

    if (subject.includes("不分派股利")) {
      batch.push([cdateAd, ctime, coId, coName, anCode, subject, ...ZERO]);
      zeroCount++;

    } else if (anCode === "F36") {
      try {
        Utilities.sleep(400);
        const html = UrlFetchApp.fetch(hyperlink, {
          headers: { "Referer": "https://mopsov.twse.com.tw", "User-Agent": "Mozilla/5.0" },
          muteHttpExceptions: true
        }).getContentText("UTF-8");
        const vals = extractF36(html);
        batch.push([cdateAd, ctime, coId, coName, anCode, subject, ...vals]);
        Logger.log(`[F36] ${coId} ${coName} | ${vals[0]}`);
        f36Count++;
      } catch (e) {
        Logger.log(`[F36 錯誤] ${coId}：${e}`);
        batch.push([cdateAd, ctime, coId, coName, anCode, subject, ...ZERO]);
      }

    } else if (anCode === "M14") {
      try {
        Utilities.sleep(400);
        const html = UrlFetchApp.fetch(hyperlink, {
          headers: { "Referer": "https://mopsov.twse.com.tw", "User-Agent": "Mozilla/5.0" },
          muteHttpExceptions: true
        }).getContentText("UTF-8");
        const vals = extractM14(html);
        batch.push([cdateAd, ctime, coId, coName, anCode, subject, ...vals]);
        Logger.log(`[M14] ${coId} ${coName} | ${vals[0]}`);
        m14Count++;
      } catch (e) {
        Logger.log(`[M14 錯誤] ${coId}：${e}`);
        batch.push([cdateAd, ctime, coId, coName, anCode, subject, ...ZERO]);
      }

    } else {
      skipCount++;
    }

    if (batch.length >= BATCH_SIZE) flushBatch();
  });
  flushBatch();

  // ── 內建 removeDuplicates（A,C,D,G~L 欄）────────
  // 保留舊資料一起判斷，不清空，只刪重複列
  // 欄位 1-based：A=1, C=3, D=4, G=7, H=8, I=9, J=10, K=11, L=12
  sheet.getDataRange().removeDuplicates([1, 3, 4, 7, 8, 9, 10, 11, 12]);
  Logger.log("去重完成");

  let totalChange = sheet.getLastRow() - lastRowS;
  if (lastRowS == 0) {
    totalChange--;
  }
  const msg = `✅ 完成！抓取 ${totalWritten} 筆\n去除重複資料後異動 ${totalChange} 筆\n` +
    `F36：${f36Count}　M14：${m14Count}　不分派：${zeroCount}　略過：${skipCount}\n` +
    `查詢區間：${SDATE} ～ ${EDATE}`;
  Logger.log(msg);
  SpreadsheetApp.getUi().alert(msg);
}