填入以下程式碼
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 noDataMatch = html.match(/查無資料/i);
if (noDataMatch) {
return ["查無資料", "查無資料", "查無資料", "查無資料", "查無資料", "查無資料"];
}
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) throw new Error(html);
//if (rightTds.length < 7) return ["-", "-", "-", "-", "-", "-"];
return [rightTds[0] || "--", rightTds[1] || "--", rightTds[2] || "--",
rightTds[4] || "--", rightTds[5] || "--", rightTds[6] || "--"];
}
// ── M14：<pre> 全形冒號 ───────────────────────
function extractM14(html) {
const preMatch = html.match(/<pre[^>]*>([\s\S]*?)<\/pre>/i);
if (!preMatch) throw new Error(html);
//if (!preMatch) return ["-", "-", "-", "-", "-", "-"];
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 "--";
const parts = line.split('：');
return parts.length > 1 ? parts[parts.length - 1].trim() : "---";
}
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 batch = [], batchLinks = [], totalWritten = 0;
let skipCount = 0, zeroCount = 0, f36Count = 0, m14Count = 0;
function flushBatch() {
if (!batch.length) return;
const startRow = sheet.getLastRow() + 1;
const numRows = batch.length;
// 先寫所有純文字值
sheet.getRange(startRow, 1, numRows, batch[0].length).setValues(batch);
// 再對 F 欄（主旨，第6欄）補 HYPERLINK 公式
const formulas = batchLinks.map(([url, text]) =>
[`=HYPERLINK("${url}","${text.replace(/"/g, '""')}")`]
);
sheet.getRange(startRow, 6, numRows, 1).setFormulas(formulas);
totalWritten += numRows;
Logger.log(`📥 已寫入 ${totalWritten} 筆`);
batch = [];
batchLinks = []; // ← 同步清空
}
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]);
batchLinks.push([hyperlink, subject]); // ← 新增
zeroCount++;
} else if (anCode === "F36") {
try {
Utilities.sleep(600);
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]);
batchLinks.push([hyperlink, subject]); // ← 新增
Logger.log(`[F36] ${coId} ${coName} | ${vals[0]}`);
f36Count++;
} catch (e) {
Logger.log(`[F36 錯誤] ${coId}：${e}`);
throw e;
//batch.push([cdateAd, ctime, coId, coName, anCode, subject, ...ZERO]);
}
} else if (anCode === "M14") {
try {
Utilities.sleep(600);
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]);
batchLinks.push([hyperlink, subject]); // ← 新增
Logger.log(`[M14] ${coId} ${coName} | ${vals[0]}`);
m14Count++;
} catch (e) {
Logger.log(`[M14 錯誤] ${coId}：${e}`);
throw 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);
}
function onOpen() {
SpreadsheetApp.getUi()
.createMenu("📊 股利爬蟲")
.addItem("執行爬蟲", "fetchDividendToSheet")
.addToUi();
}
