全站 Google 試算表自動同步
將下方範本貼入 Google 試算表的「Apps Script」並部署為網頁應用程式,團隊只要維護試算表分頁,網站就會自動同步所有資料(公告、看板、人員、專線、韌體、專標案、教育訓練、工具與指令)。
全站 Apps Script 範本(含 9 個分頁)
function doGet() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const readSheet = (name, mapper) => {
const sh = ss.getSheetByName(name);
if (!sh) return null;
const rows = sh.getDataRange().getValues();
return rows.slice(1).filter(r => r[0] !== "").map(mapper);
};
const payload = {
tasks: readSheet("看板任務", (r, i) => ({ id: "gs_" + i, title: r[0], owner: r[1], date: r[2], status: r[3] || "todo" })),
team: readSheet("人員清冊", r => ({ empId: r[0], name: r[1], role: r[2], duty: r[3], phone: r[4], corpEmail: r[5], gmail: r[6] })),
hotlines: readSheet("客服專線", r => ({ name: r[0], time: r[1], phone: r[2] })),
alerts: readSheet("近期公告", r => ({ type: r[0] || "warn", text: r[1] })),
firmware: readSheet("韌體清單", r => [r[0], r[1], r[2]]),
training: readSheet("教育訓練", r => [r[0], r[1], r[2], r[3], String(r[4])]),
projects: readSheet("專標案", (r, i) => ({
id: "gsp_" + i, code: r[0], name: r[1], partner: r[2], devices: r[3], tools: r[4],
docUrl: r[5] || "https://drive.google.com/drive/folders/15Tl49bJtGSKxD80qZOwlFu4QgqchREvr",
cli: r[6] || "", details: String(r[7] || "").split("\n").filter(Boolean),
links: r[8] ? [{ label: "相關雲端/總表", url: r[8] }] : []
})),
tools: readSheet("工具儀表", r => ({ badge: r[0], title: r[1], desc: r[2], url: r[3], btnText: r[4] || "開啟連結" })),
cpCli: (() => { const s = ss.getSheetByName("CheckPoint指令"); return s ? s.getRange("A2").getValue() : null; })()
};
return ContentService.createTextOutput(JSON.stringify(payload)).setMimeType(ContentService.MimeType.JSON);
}