在 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(/ /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); } - 按「儲存」
- 選要執行的函示「fetchDividendToSheet」，按「執行」
- 初次執行，需要授權，按「審核權限」
- 按「進階」
- 繼續前往專案
- 抓取資料須「連線至外部服務」權限，
寫入試算表須「查看、編輯、建立及刪除您的所有 Google 試算表檔案」權限。
兩者勾選後，按「繼續」便會開始執行。
- 執行結果
