使用方式
研習現場建議：請優先使用本 HTML 頁的一鍵複製，不要直接在瀏覽器開啟 .gs 檔案複製，以避免瀏覽器或主機未正確宣告編碼時造成中文亂碼。
- 建立 Google Sheet。
- 開啟「擴充功能 → Apps Script」。
- 將下方 GAS 程式碼完整複製貼上。
- 儲存 Apps Script 專案。
- 回到試算表重新整理，依講義操作初始化流程。
- 若要測試 Email 功能，請先用自己的測試信箱完成學生首次登錄，不要在研習現場大量寄送正式學生名單。
取得 GAS 程式碼
本頁程式碼由專案內對應 .gs 原始檔產生，已透過 HTML 頁面宣告 UTF-8，適合研習現場直接複製。
展開完整 GAS 程式碼
/**
* 一週三報｜大學課程內容重點工作流平台
*
* 版本：University MVP 0.2
* 使用方式：
* 1. 建立新的 Google Sheet
* 2. 擴充功能 → Apps Script
* 3. 貼上本檔為 Code.gs
* 4. 儲存並重新整理試算表
* 5. 使用「一週三報｜大學課程內容重點」選單操作
*
* 本檔是獨立版本，不會修改既有「一週三報.gs」。
* 自訂函式使用 UCR_ 前綴，降低與其他 GAS 檔案命名衝突的機率。
*/
const UCR_CONFIG = {
TIMEZONE: "Asia/Taipei",
PLATFORM_NAME: "一週三報｜大學課程內容重點工作流平台",
SHEETS: {
DASHBOARD: "00_課程中控台",
WEEKLY_DATA: "01_課程週次資料",
COPY_OUTPUT: "02_三端內容輸出",
PUBLISH_LOG: "03_發布紀錄",
SETTINGS: "04_設定",
INDEX: "05_歷週索引",
PLATFORM_MATERIALS: "06_課程平台發布素材",
STUDENTS: "07_學生名單",
},
DEFAULT_SETTINGS: [
["設定項目", "設定值", "說明"],
["課程名稱", "大學課程名稱", "顯示於標題、Docs 與發布素材"],
["授課教師", "授課教師姓名", "顯示於署名與通知"],
["開課系所", "開課系所", "顯示於課程資訊"],
["學期", "114-1", "例如 114-1、2026 Spring"],
["課程平台網址", "", "LMS、Moodle、Google Classroom 或課程網站網址"],
["課程協作空間網址", "", "Notion、Google Chat 空間說明頁、共編文件等，可空白"],
["Drive根資料夾ID", "", "可空白；空白時會自動建立根資料夾"],
["Google Chat Webhook URL", "", "可空白；用於發布課程內容重點摘要到協作空間"],
["是否啟用協作平台發布", "否", "填「是」才允許一鍵發布到協作平台"],
["協作平台名稱", "Google Chat", "顯示於中控台與發布紀錄"],
],
PLACEHOLDERS: {
topic: "本週課程單元或主題：",
concepts: "核心概念／理論重點：\n- ",
activities: "課堂活動、案例、討論或實作：\n- ",
assignments: "指定閱讀、作業、測驗或繳交事項：\n- ",
nextPreview: "下週預告與預習方向：\n- ",
resources: "補充資源、影片、資料集或延伸閱讀連結：",
},
STATUS: {
NOT_CREATED: "⚪ 尚未建立",
DRAFT: "🟡 草稿中",
COPY_READY: "🟢 已產生三端內容",
DOC_READY: "🔵 已產生 Docs",
COLLAB_READY: "🟣 已發布協作平台",
COMPLETED: "✅ 已完成發布",
},
};
function onOpen() {
UCR_onOpen();
}
function UCR_onOpen() {
SpreadsheetApp.getUi()
.createMenu("一週三報｜大學課程內容重點")
.addItem("1. 初始化平台", "UCR_initPlatform")
.addItem("重新整理中控台", "UCR_refreshDashboardView")
.addSeparator()
.addItem("2. 建立本週課程重點範本", "UCR_createCurrentWeekReport")
.addItem("建立下一週課程重點範本", "UCR_createNextWeekReport")
.addItem("開啟課程週次填寫表單", "UCR_showWeeklyInputForm")
.addItem("3. 建立本週課程素材資料夾", "UCR_createMaterialsFolderForActiveWeek")
.addSeparator()
.addItem("開啟學生首次登錄表單", "UCR_showStudentRegisterForm")
.addItem("開啟學生名單", "UCR_openStudentsSheet")
.addSeparator()
.addItem("開啟本週發布面板", "UCR_showWeeklyPublishPanel")
.addSeparator()
.addItem("4. 產生三端內容", "UCR_generateThreeChannelCopies")
.addItem("5. 產生 Google Docs 課程重點", "UCR_generateGoogleDocForActiveWeek")
.addItem("產生課程平台發布素材", "UCR_generatePlatformPublishMaterials")
.addItem("一鍵發布到協作平台", "UCR_publishToCollaborationPlatform")
.addSeparator()
.addItem("標記課程平台已發布", "UCR_markCoursePlatformPublished")
.addItem("寄送本週週三報給學生", "UCR_sendWeeklyEmailToStudents")
.addItem("標記 Email 已發布", "UCR_markEmailPublished")
.addItem("標記 LMS/社群已發布", "UCR_markLmsPublished")
.addSeparator()
.addItem("更新歷週索引", "UCR_updateWeeklyIndex")
.addToUi();
}
function UCR_initPlatform() {
UCR_runAction_("初始化平台", function () {
const ss = SpreadsheetApp.getActiveSpreadsheet();
UCR_setupDashboardSheet_(ss);
UCR_setupWeeklyDataSheet_(ss);
UCR_setupCopyOutputSheet_(ss);
UCR_setupPublishLogSheet_(ss);
UCR_setupSettingsSheet_(ss);
UCR_setupIndexSheet_(ss);
UCR_setupPlatformMaterialsSheet_(ss);
UCR_ensureStudentsSheet_(ss);
UCR_alert_("初始化完成。請先到「04_設定」確認課程資料，再建立本週課程重點範本。");
});
}
function UCR_refreshDashboardView() {
UCR_runAction_("重新整理中控台", function () {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const activeWeekId = UCR_getActiveWeekId_() || UCR_getLatestWeekId_();
UCR_setupDashboardSheet_(ss);
UCR_setupIndexSheet_(ss);
UCR_ensurePlatformMaterialsSheet_(ss);
UCR_refreshStudentsSummaryOnDashboard_();
if (activeWeekId) {
UCR_refreshDashboardForWeek_(activeWeekId);
UCR_updateWeeklyIndex_();
}
UCR_alert_("中控台已重新整理，不會清空課程週次資料與三端內容。");
});
}
function UCR_setupDashboardSheet_(ss) {
const sheet = UCR_getOrCreateSheet_(ss, UCR_CONFIG.SHEETS.DASHBOARD);
sheet.clear();
const values = [
[UCR_CONFIG.PLATFORM_NAME, "", "", "", "", ""],
["操作提示", "建立課程週次 → 填寫課程重點 → 產生三端內容 → 產生 Docs / 平台素材 → 發布與標記", "", "", "", ""],
["", "", "", "", "", ""],
["目前週次ID", "", "", "總狀態", UCR_CONFIG.STATUS.NOT_CREATED, ""],
["日期區間", "", "", "Google Docs", "尚未建立", ""],
["課程平台", "尚未設定", "", "課程素材資料夾", "尚未建立", ""],
["", "", "", "", "", ""],
["流程狀態", "", "", "", "", ""],
["課程草稿", "⚪ 尚未建立", "三端內容", "⚪ 尚未產生", "Google Docs", "⚪ 尚未產生"],
["外部發布", "⚪ 尚未完成", "協作平台", "⚪ 尚未發布", "", ""],
["", "", "", "", "", ""],
["發布狀態", "", "", "", "", ""],
["課程平台", "尚未發布", "", "", "", ""],
["Email提醒", "尚未發布", "", "學生登錄人數", "0", ""],
["LMS/社群", "尚未發布", "", "訂閱週三報人數", "0", ""],
["協作平台", "尚未發布", "", "有效 Email 人數", "0", ""],
["", "", "", "", "", ""],
["本週流程步驟", "", "", "", "", ""],
["1", "建立週次資料", "⚪ 待處理", "下一步", "建立或確認本週課程重點資料列", ""],
["2", "填寫課程重點", "⚪ 待處理", "", "補齊核心概念、活動、作業與下週預告", ""],
["3", "產生三端內容", "⚪ 待處理", "", "產生課程平台公告、Email、LMS/社群文案", ""],
["4", "建立 Docs / 素材資料夾", "⚪ 待處理", "", "建立正式 Docs 與課程素材資料夾", ""],
["5", "發布三端", "⚪ 待處理", "", "複製文案到課程平台、Email、LMS/社群後標記", ""],
["6", "發布協作平台", "⚪ 待處理", "", "送出 Google Chat 協作通知", ""],
];
sheet.getRange(1, 1, values.length, 6).setValues(values);
sheet.getRange("A1:F1").merge();
sheet.getRange("B2:F2").merge();
sheet.getRange("A8:F8").merge();
sheet.getRange("A12:F12").merge();
sheet.getRange("A18:F18").merge();
sheet.getRange("A1")
.setFontSize(18)
.setFontWeight("bold")
.setFontColor("#ffffff")
.setHorizontalAlignment("center")
.setBackground("#243b53");
sheet.getRange("A2:F2").setBackground("#edf4fb");
sheet.getRange("A4:A6").setFontWeight("bold");
sheet.getRange("D4:D6").setFontWeight("bold");
sheet.getRange("A8:F8").setFontWeight("bold").setBackground("#d9eaf7");
sheet.getRange("A12:F12").setFontWeight("bold").setBackground("#d9eaf7");
sheet.getRange("A18:F18").setFontWeight("bold").setBackground("#d9eaf7");
sheet.getRange("A9:F10").setBackground("#f8fafc");
sheet.getRange("A13:B16").setBackground("#f8fafc");
sheet.getRange("D14:E16").setBackground("#eef7ff");
sheet.getRange("D14:D16").setFontWeight("bold");
sheet.getRange("A19:F24").setBackground("#f8fafc");
sheet.getRange("E4").setFontWeight("bold");
sheet.setColumnWidth(1, 150);
sheet.setColumnWidth(2, 250);
sheet.setColumnWidth(3, 150);
sheet.setColumnWidth(4, 150);
sheet.setColumnWidth(5, 300);
sheet.setColumnWidth(6, 40);
sheet.setFrozenRows(1);
}
function UCR_setupWeeklyDataSheet_(ss) {
const sheet = UCR_getOrCreateSheet_(ss, UCR_CONFIG.SHEETS.WEEKLY_DATA);
sheet.clear();
const headers = [
"週次ID",
"日期區間",
"課程單元主題",
"本週核心概念",
"課堂活動與討論",
"指定閱讀與作業",
"下週預告",
"補充資源",
"課程素材資料夾URL",
"建立時間",
"最後更新時間",
];
sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
UCR_styleHeader_(sheet, headers.length);
sheet.setFrozenRows(1);
sheet.setColumnWidths(1, headers.length, 180);
sheet.setColumnWidth(3, 260);
sheet.setColumnWidth(4, 320);
sheet.setColumnWidth(5, 320);
sheet.setColumnWidth(6, 320);
sheet.setColumnWidth(7, 300);
sheet.setColumnWidth(8, 300);
}
function UCR_setupCopyOutputSheet_(ss) {
const sheet = UCR_getOrCreateSheet_(ss, UCR_CONFIG.SHEETS.COPY_OUTPUT);
sheet.clear();
const headers = ["週次ID", "課程平台公告版", "Email短提醒版", "LMS社群貼文版", "產生時間", "是否確認"];
sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
UCR_styleHeader_(sheet, headers.length);
sheet.setFrozenRows(1);
sheet.setColumnWidth(1, 150);
sheet.setColumnWidth(2, 440);
sheet.setColumnWidth(3, 380);
sheet.setColumnWidth(4, 420);
}
function UCR_setupPublishLogSheet_(ss) {
const sheet = UCR_getOrCreateSheet_(ss, UCR_CONFIG.SHEETS.PUBLISH_LOG);
sheet.clear();
const headers = ["週次ID", "課程平台", "Email", "LMS/社群", "協作平台", "Google Docs URL", "發布備註", "最後更新時間"];
sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
UCR_styleHeader_(sheet, headers.length);
sheet.setFrozenRows(1);
sheet.setColumnWidths(1, headers.length, 180);
sheet.setColumnWidth(6, 360);
}
function UCR_setupSettingsSheet_(ss) {
const sheet = UCR_getOrCreateSheet_(ss, UCR_CONFIG.SHEETS.SETTINGS);
sheet.clear();
sheet.getRange(1, 1, UCR_CONFIG.DEFAULT_SETTINGS.length, 3).setValues(UCR_CONFIG.DEFAULT_SETTINGS);
UCR_styleHeader_(sheet, 3);
sheet.setFrozenRows(1);
sheet.setColumnWidth(1, 220);
sheet.setColumnWidth(2, 360);
sheet.setColumnWidth(3, 460);
}
function UCR_setupIndexSheet_(ss) {
const sheet = UCR_getOrCreateSheet_(ss, UCR_CONFIG.SHEETS.INDEX);
sheet.clear();
const headers = ["週次ID", "日期區間", "摘要", "Google Docs", "素材資料夾", "課程平台", "發布狀態", "最後更新時間"];
sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
UCR_styleHeader_(sheet, headers.length);
sheet.setFrozenRows(1);
sheet.setColumnWidths(1, headers.length, 180);
sheet.setColumnWidth(3, 460);
sheet.setColumnWidth(7, 260);
}
function UCR_setupPlatformMaterialsSheet_(ss) {
const sheet = UCR_getOrCreateSheet_(ss, UCR_CONFIG.SHEETS.PLATFORM_MATERIALS);
sheet.clear();
const headers = ["週次ID", "平台標題", "摘要", "純文字版", "HTML貼上版", "Google Docs連結", "素材資料夾連結", "產生時間"];
sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
UCR_styleHeader_(sheet, headers.length);
sheet.setFrozenRows(1);
sheet.setColumnWidth(1, 150);
sheet.setColumnWidth(2, 300);
sheet.setColumnWidth(3, 380);
sheet.setColumnWidth(4, 520);
sheet.setColumnWidth(5, 520);
}
function UCR_setupStudentsSheet_(ss) {
const sheet = UCR_getOrCreateSheet_(ss, UCR_CONFIG.SHEETS.STUDENTS);
sheet.clear();
const headers = UCR_getStudentSheetHeaders_();
sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
UCR_styleHeader_(sheet, headers.length);
sheet.setFrozenRows(1);
sheet.setColumnWidth(1, 180);
sheet.setColumnWidth(2, 140);
sheet.setColumnWidth(3, 140);
sheet.setColumnWidth(4, 260);
sheet.setColumnWidth(5, 180);
sheet.setColumnWidth(6, 260);
sheet.setColumnWidth(7, 150);
sheet.setColumnWidth(8, 180);
sheet.getRange("D:D").setNumberFormat("@");
sheet.getRange("G:G").setNumberFormat("@");
const rule = SpreadsheetApp.newDataValidation().requireValueInList(["是", "否"], true).setAllowInvalid(false).build();
sheet.getRange(2, 7, Math.max(sheet.getMaxRows() - 1, 1), 1).setDataValidation(rule);
UCR_fillDefaultStudentSubscription_(sheet);
}
function UCR_ensureStructure_(ss) {
UCR_ensureSettingsRows_(ss);
UCR_ensurePublishLogColumns_(ss);
UCR_ensurePlatformMaterialsSheet_(ss);
UCR_ensureStudentsSheet_(ss);
}
function UCR_ensureSettingsRows_(ss) {
const sheet = ss.getSheetByName(UCR_CONFIG.SHEETS.SETTINGS);
if (!sheet) return;
const lastRow = sheet.getLastRow();
const existingKeys =
lastRow >= 2
? sheet.getRange(2, 1, lastRow - 1, 1).getValues().map(function (row) {
return row[0];
})
: [];
const missingRows = UCR_CONFIG.DEFAULT_SETTINGS.slice(1).filter(function (row) {
return existingKeys.indexOf(row[0]) === -1;
});
if (missingRows.length > 0) {
sheet.getRange(sheet.getLastRow() + 1, 1, missingRows.length, 3).setValues(missingRows);
}
}
function UCR_ensurePublishLogColumns_(ss) {
const sheet = ss.getSheetByName(UCR_CONFIG.SHEETS.PUBLISH_LOG);
if (!sheet) return;
const headers = ["週次ID", "課程平台", "Email", "LMS/社群", "協作平台", "Google Docs URL", "發布備註", "最後更新時間"];
sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
UCR_styleHeader_(sheet, headers.length);
}
function UCR_ensurePlatformMaterialsSheet_(ss) {
const sheet = ss.getSheetByName(UCR_CONFIG.SHEETS.PLATFORM_MATERIALS);
if (!sheet) {
UCR_setupPlatformMaterialsSheet_(ss);
}
}
function UCR_ensureStudentsSheet_(ss) {
let sheet = ss.getSheetByName(UCR_CONFIG.SHEETS.STUDENTS);
if (!sheet) {
UCR_setupStudentsSheet_(ss);
return;
}
const headers = UCR_getStudentSheetHeaders_();
const lastColumn = Math.max(sheet.getLastColumn(), headers.length);
const existingHeaders = sheet.getRange(1, 1, 1, lastColumn).getValues()[0];
const missingHeaders = headers.filter(function (header) {
return existingHeaders.indexOf(header) === -1;
});
if (missingHeaders.length > 0 || lastColumn < headers.length) {
sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
}
UCR_styleHeader_(sheet, headers.length);
sheet.setFrozenRows(1);
sheet.setColumnWidth(1, 180);
sheet.setColumnWidth(2, 140);
sheet.setColumnWidth(3, 140);
sheet.setColumnWidth(4, 260);
sheet.setColumnWidth(5, 180);
sheet.setColumnWidth(6, 260);
sheet.setColumnWidth(7, 150);
sheet.setColumnWidth(8, 180);
sheet.getRange("D:D").setNumberFormat("@");
sheet.getRange("G:G").setNumberFormat("@");
const rule = SpreadsheetApp.newDataValidation().requireValueInList(["是", "否"], true).setAllowInvalid(false).build();
sheet.getRange(2, 7, Math.max(sheet.getMaxRows() - 1, 1), 1).setDataValidation(rule);
}
function UCR_getStudentSheetHeaders_() {
return ["登錄時間", "學號", "姓名", "Email", "系級／班級", "備註", "是否訂閱週三報", "最後更新時間"];
}
function UCR_fillDefaultStudentSubscription_(sheet) {
if (!sheet || sheet.getLastRow() < 2) return;
const range = sheet.getRange(2, 1, sheet.getLastRow() - 1, 8);
const values = range.getValues();
let changed = false;
values.forEach(function (row) {
const hasStudentData = Boolean(row[1] || row[2] || row[3]);
if (hasStudentData && !row[6]) {
row[6] = "是";
changed = true;
}
});
if (changed) range.setValues(values);
}
function UCR_createCurrentWeekReport() {
UCR_runAction_("建立本週課程重點範本", function () {
const result = UCR_createWeekReportByInfo_(UCR_getCurrentWeekInfo_());
if (!result) return;
UCR_alert_(result.existed ? "本週課程重點已存在：" + result.weekId : "已建立本週課程重點範本：" + result.weekId);
});
}
function UCR_createNextWeekReport() {
UCR_runAction_("建立下一週課程重點範本", function () {
const result = UCR_createWeekReportByInfo_(UCR_getNextWeekInfo_());
if (!result) return;
UCR_alert_(result.existed ? "下一週課程重點已存在：" + result.weekId : "已建立下一週課程重點範本：" + result.weekId);
});
}
function UCR_createWeekReportByInfo_(weekInfo) {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName(UCR_CONFIG.SHEETS.WEEKLY_DATA);
if (!sheet) {
UCR_alert_("請先執行「初始化平台」。");
return null;
}
const weekId = weekInfo.weekId;
const dateRange = weekInfo.dateRange;
if (UCR_findRowByWeekId_(sheet, weekId) > 0) {
UCR_refreshDashboardForWeek_(weekId);
return { weekId: weekId, dateRange: dateRange, existed: true };
}
const now = new Date();
const p = UCR_CONFIG.PLACEHOLDERS;
sheet.appendRow([
weekId,
dateRange,
p.topic,
p.concepts,
p.activities,
p.assignments,
p.nextPreview,
p.resources,
"",
now,
now,
]);
UCR_ensurePublishLogRow_(weekId);
UCR_refreshDashboardForWeek_(weekId);
UCR_updateWeeklyIndex_();
return { weekId: weekId, dateRange: dateRange, existed: false };
}
function UCR_getCurrentWeekInfo_() {
return UCR_getWeekInfoForDate_(new Date());
}
function UCR_getNextWeekInfo_() {
const nextWeek = new Date();
nextWeek.setDate(nextWeek.getDate() + 7);
return UCR_getWeekInfoForDate_(nextWeek);
}
function UCR_getWeekInfoForDate_(today) {
const timezone = UCR_CONFIG.TIMEZONE;
const day = today.getDay();
const diffToMonday = day === 0 ? -6 : 1 - day;
const monday = new Date(today);
monday.setDate(today.getDate() + diffToMonday);
const friday = new Date(monday);
friday.setDate(monday.getDate() + 4);
const year = Utilities.formatDate(today, timezone, "yyyy");
const weekNumber = UCR_getWeekNumber_(today);
return {
weekId: year + "-W" + String(weekNumber).padStart(2, "0"),
dateRange:
Utilities.formatDate(monday, timezone, "yyyy/MM/dd") +
"–" +
Utilities.formatDate(friday, timezone, "yyyy/MM/dd"),
};
}
function UCR_getWeekNumber_(date) {
const target = new Date(date.valueOf());
const dayNumber = (date.getDay() + 6) % 7;
target.setDate(target.getDate() - dayNumber + 3);
const firstThursday = new Date(target.getFullYear(), 0, 4);
const firstDayNumber = (firstThursday.getDay() + 6) % 7;
firstThursday.setDate(firstThursday.getDate() - firstDayNumber + 3);
return 1 + Math.round((target - firstThursday) / (7 * 24 * 60 * 60 * 1000));
}
function UCR_createMaterialsFolderForActiveWeek() {
UCR_runAction_("建立本週課程素材資料夾", function () {
const weekId = UCR_getActiveWeekId_();
if (!weekId) {
UCR_alert_("目前沒有週次ID，請先建立本週課程重點範本。");
return;
}
const url = UCR_createMaterialsFolderForWeek_(weekId);
if (!url) return;
UCR_refreshDashboardForWeek_(weekId);
UCR_updateWeeklyIndex_();
UCR_alert_("已取得本週課程素材資料夾：\n" + url);
});
}
function UCR_createMaterialsFolderForWeek_(weekId) {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName(UCR_CONFIG.SHEETS.WEEKLY_DATA);
if (!sheet) {
UCR_alert_("請先執行「初始化平台」。");
return "";
}
const row = UCR_findRowByWeekId_(sheet, weekId);
if (row <= 0) {
UCR_alert_("找不到課程週次資料：" + weekId);
return "";
}
const existingUrl = sheet.getRange(row, 9).getValue();
if (existingUrl) return existingUrl;
const settings = UCR_getSettings_();
const courseName = settings["課程名稱"] || "課程";
const root = UCR_getOrCreateRootFolder_(settings);
const folder = root.createFolder(weekId + "_" + courseName + "_課程素材");
const url = folder.getUrl();
sheet.getRange(row, 9).setValue(url);
sheet.getRange(row, 11).setValue(new Date());
return url;
}
function UCR_getOrCreateRootFolder_(settings) {
const rootFolderId = settings["Drive根資料夾ID"];
if (rootFolderId) {
try {
return DriveApp.getFolderById(rootFolderId);
} catch (e) {
UCR_alert_("「Drive根資料夾ID」無法讀取，系統將自動建立新的根資料夾。\n\n錯誤：" + e.message);
}
}
const ss = SpreadsheetApp.getActiveSpreadsheet();
const folder = DriveApp.createFolder(ss.getName() + "_大學課程內容重點資料夾");
const settingsSheet = ss.getSheetByName(UCR_CONFIG.SHEETS.SETTINGS);
const row = UCR_findSettingsRow_("Drive根資料夾ID");
if (settingsSheet && row > 0) settingsSheet.getRange(row, 2).setValue(folder.getId());
return folder;
}
function UCR_generateThreeChannelCopies() {
UCR_runAction_("產生三端內容", function () {
const weekId = UCR_getActiveWeekId_();
if (!weekId) {
UCR_alert_("目前沒有週次ID，請先建立本週課程重點範本。");
return;
}
const result = UCR_generateThreeChannelCopiesForWeek_(weekId);
if (!result) return;
let message = "已產生三端內容。請到「02_三端內容輸出」或發布面板查看。";
if (result.warnings.length > 0) {
message += "\n\n提醒：下列欄位仍是預設提示文字或尚未填寫：\n- " + result.warnings.join("\n- ");
}
UCR_alert_(message);
});
}
function UCR_generateThreeChannelCopiesForWeek_(weekId) {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const outputSheet = ss.getSheetByName(UCR_CONFIG.SHEETS.COPY_OUTPUT);
if (!outputSheet) {
UCR_alert_("請先執行「初始化平台」。");
return null;
}
const data = UCR_getWeeklyDataByWeekId_(weekId);
if (!data) {
UCR_alert_("找不到課程週次資料：" + weekId);
return null;
}
const settings = UCR_getSettings_();
const platformCopy = UCR_buildCoursePlatformCopy_(data, settings);
const emailCopy = UCR_buildEmailCopy_(data, settings);
const lmsCopy = UCR_buildLmsCopy_(data, settings);
const now = new Date();
const existingRow = UCR_findRowByWeekId_(outputSheet, weekId);
if (existingRow > 0) {
outputSheet.getRange(existingRow, 2, 1, 5).setValues([[platformCopy, emailCopy, lmsCopy, now, "否"]]);
} else {
outputSheet.appendRow([weekId, platformCopy, emailCopy, lmsCopy, now, "否"]);
}
UCR_refreshDashboardForWeek_(weekId);
UCR_updateWeeklyIndex_();
return {
warnings: UCR_getIncompleteFieldWarnings_(data),
};
}
function UCR_buildCoursePlatformCopy_(data, settings) {
const courseName = settings["課程名稱"] || "課程";
const instructor = settings["授課教師"] || "授課教師";
return [
"【" + courseName + "｜" + data.weekId + " 課程內容重點】",
"",
"日期區間：" + data.dateRange,
"",
"一、課程單元主題",
UCR_normalizeText_(data.topic),
"",
"二、本週核心概念",
UCR_normalizeText_(data.concepts),
"",
"三、課堂活動與討論",
UCR_normalizeText_(data.activities),
"",
"四、指定閱讀與作業",
UCR_normalizeText_(data.assignments),
"",
"五、下週預告",
UCR_normalizeText_(data.nextPreview),
"",
"六、補充資源",
UCR_normalizeText_(data.resources),
data.materialsFolderUrl ? "\n課程素材資料夾：" + data.materialsFolderUrl : "",
"",
instructor,
].join("\n").trim();
}
function UCR_buildEmailCopy_(data, settings) {
const courseName = settings["課程名稱"] || "課程";
const platformUrl = settings["課程平台網址"] || "";
return [
"主旨：【" + courseName + "】" + data.weekId + " 課程重點與下週提醒",
"",
"同學好，以下是本週課程重點與需留意事項：",
"",
"課程主題：",
UCR_compactText_(data.topic),
"",
"核心概念：",
UCR_compactText_(data.concepts),
"",
"指定閱讀與作業：",
UCR_compactText_(data.assignments),
"",
"下週預告：",
UCR_compactText_(data.nextPreview),
"",
platformUrl ? "課程平台：" + platformUrl : "請留意課程平台後續公告。",
].join("\n");
}
function UCR_buildLmsCopy_(data, settings) {
const courseName = settings["課程名稱"] || "課程";
return [
"【" + courseName + "｜" + data.weekId + " 學習重點】",
"",
"本週主題：" + UCR_compactText_(data.topic),
"",
"你需要掌握的重點：",
UCR_compactText_(data.concepts),
"",
"課堂討論／活動：",
UCR_compactText_(data.activities),
"",
"請完成：",
UCR_compactText_(data.assignments),
"",
data.materialsFolderUrl ? "課程素材：" + data.materialsFolderUrl : "",
].join("\n").trim();
}
function UCR_generateGoogleDocForActiveWeek() {
UCR_runAction_("產生 Google Docs 課程重點", function () {
const weekId = UCR_getActiveWeekId_();
if (!weekId) {
UCR_alert_("目前沒有週次ID，請先建立本週課程重點範本。");
return;
}
const docUrl = UCR_createGoogleDocForWeek_(weekId);
if (!docUrl) return;
UCR_refreshDashboardForWeek_(weekId);
UCR_updateWeeklyIndex_();
UCR_alert_("已產生 Google Docs 課程重點：\n" + docUrl);
});
}
function UCR_createGoogleDocForWeek_(weekId) {
const existingUrl = UCR_getPublishInfo_(weekId).docUrl;
if (existingUrl) return existingUrl;
const data = UCR_getWeeklyDataByWeekId_(weekId);
if (!data) {
UCR_alert_("找不到課程週次資料：" + weekId);
return "";
}
const settings = UCR_getSettings_();
const courseName = settings["課程名稱"] || "課程";
const doc = DocumentApp.create(courseName + "_" + weekId + "_課程內容重點");
const body = doc.getBody();
body.clear();
body.setMarginTop(54).setMarginBottom(54).setMarginLeft(54).setMarginRight(54);
UCR_buildFormalDocBody_(body, data, settings);
UCR_buildFormalDocFooter_(doc, settings);
doc.saveAndClose();
const url = doc.getUrl();
UCR_updatePublishLogDocUrl_(weekId, url);
return url;
}
function UCR_buildFormalDocBody_(body, data, settings) {
const courseName = settings["課程名稱"] || "課程";
const instructor = settings["授課教師"] || "授課教師";
const department = settings["開課系所"] || "";
body.appendParagraph(courseName + "｜課程內容重點").setHeading(DocumentApp.ParagraphHeading.TITLE).setAlignment(DocumentApp.HorizontalAlignment.CENTER);
const meta = body.appendParagraph(data.weekId + "｜" + data.dateRange).setAlignment(DocumentApp.HorizontalAlignment.CENTER);
UCR_setParagraphTextColor_(meta, "#666666");
if (department) {
const dep = body.appendParagraph(department).setAlignment(DocumentApp.HorizontalAlignment.CENTER);
UCR_setParagraphTextColor_(dep, "#666666");
}
body.appendHorizontalRule();
UCR_appendDocSection_(body, "一、課程單元主題", data.topic);
UCR_appendDocSection_(body, "二、本週核心概念", data.concepts);
UCR_appendDocSection_(body, "三、課堂活動與討論", data.activities);
UCR_appendDocSection_(body, "四、指定閱讀與作業", data.assignments);
UCR_appendDocSection_(body, "五、下週預告", data.nextPreview);
UCR_appendDocSection_(body, "六、補充資源", data.resources);
if (data.materialsFolderUrl) {
body.appendParagraph("課程素材資料夾").setHeading(DocumentApp.ParagraphHeading.HEADING2);
const p = body.appendParagraph("");
p.appendText(data.materialsFolderUrl).setLinkUrl(data.materialsFolderUrl);
}
body.appendHorizontalRule();
body.appendParagraph(instructor).setAlignment(DocumentApp.HorizontalAlignment.RIGHT);
}
function UCR_appendDocSection_(body, heading, text) {
body.appendParagraph(heading).setHeading(DocumentApp.ParagraphHeading.HEADING2);
UCR_normalizeText_(text).split("\n").forEach(function (line) {
if (line.trim()) body.appendParagraph(line.trim()).setLineSpacing(1.15);
});
}
function UCR_buildFormalDocFooter_(doc, settings) {
const footer = doc.addFooter();
const p = footer.appendParagraph((settings["課程名稱"] || "課程") + "｜" + (settings["授課教師"] || "授課教師"));
p.setAlignment(DocumentApp.HorizontalAlignment.CENTER);
UCR_setParagraphTextColor_(p, "#888888");
}
function UCR_generatePlatformPublishMaterials() {
UCR_runAction_("產生課程平台發布素材", function () {
const weekId = UCR_getActiveWeekId_();
if (!weekId) {
UCR_alert_("目前沒有週次ID，請先建立本週課程重點範本。");
return;
}
const result = UCR_generatePlatformPublishMaterialsForWeek_(weekId);
if (!result) return;
UCR_alert_("已產生課程平台發布素材，請到「06_課程平台發布素材」查看。");
});
}
function UCR_generatePlatformPublishMaterialsForWeek_(weekId) {
const ss = SpreadsheetApp.getActiveSpreadsheet();
UCR_ensurePlatformMaterialsSheet_(ss);
const data = UCR_getWeeklyDataByWeekId_(weekId);
if (!data) {
UCR_alert_("找不到課程週次資料：" + weekId);
return null;
}
const settings = UCR_getSettings_();
const publishInfo = UCR_getPublishInfo_(weekId);
const copies = UCR_getThreeChannelCopiesForWeek_(weekId);
const plain = copies.platform || UCR_buildCoursePlatformCopy_(data, settings);
const title = UCR_buildPlatformMaterialTitle_(data, settings);
const summary = UCR_makeWeeklySummary_(data);
const html = UCR_buildPlatformHtml_(title, summary, plain, publishInfo.docUrl, data.materialsFolderUrl);
const sheet = ss.getSheetByName(UCR_CONFIG.SHEETS.PLATFORM_MATERIALS);
const rowValues = [weekId, title, summary, plain, html, publishInfo.docUrl || "", data.materialsFolderUrl || "", new Date()];
const existingRow = UCR_findRowByWeekId_(sheet, weekId);
let row = existingRow;
if (existingRow > 0) {
sheet.getRange(existingRow, 1, 1, rowValues.length).setValues([rowValues]);
} else {
sheet.appendRow(rowValues);
row = sheet.getLastRow();
}
UCR_setLinkCell_(sheet, row, 6, "開啟 Docs", publishInfo.docUrl, "尚未建立");
UCR_setLinkCell_(sheet, row, 7, "開啟素材資料夾", data.materialsFolderUrl, "尚未建立");
return { title: title, summary: summary, plainText: plain, html: html };
}
function UCR_buildPlatformMaterialTitle_(data, settings) {
return (settings["課程名稱"] || "課程") + "｜" + data.weekId + " 課程內容重點";
}
function UCR_buildPlatformHtml_(title, summary, plainText, docUrl, folderUrl) {
const paragraphs = UCR_escapeHtml_(plainText)
.split(/\n{2,}/)
.map(function (block) {
return "<p>" + block.replace(/\n/g, "<br>") + "</p>";
})
.join("\n");
const links = [];
if (docUrl) links.push('<li><a href="' + UCR_escapeHtml_(docUrl) + '">Google Docs 課程重點</a></li>');
if (folderUrl) links.push('<li><a href="' + UCR_escapeHtml_(folderUrl) + '">課程素材資料夾</a></li>');
return [
'<article class="course-weekly-summary">',
" <h1>" + UCR_escapeHtml_(title) + "</h1>",
' <p class="summary">' + UCR_escapeHtml_(summary) + "</p>",
' <div class="body">',
paragraphs,
" </div>",
links.length ? ' <ul class="links">' + links.join("") + "</ul>" : "",
"</article>",
].filter(function (line) {
return line !== "";
}).join("\n");
}
function UCR_publishToCollaborationPlatform() {
UCR_runAction_("一鍵發布到協作平台", function () {
const weekId = UCR_getActiveWeekId_();
if (!weekId) {
UCR_alert_("目前沒有週次ID，請先建立本週課程重點範本。");
return;
}
const settings = UCR_getSettings_();
if (String(settings["是否啟用協作平台發布"] || "").trim() !== "是") {
UCR_alert_("尚未啟用協作平台發布。請到「04_設定」將「是否啟用協作平台發布」填為「是」。");
return;
}
const webhookUrl = String(settings["Google Chat Webhook URL"] || "").trim();
if (!webhookUrl) {
UCR_alert_("尚未設定 Google Chat Webhook URL。");
return;
}
if (!UCR_hasThreeChannelCopies_(weekId)) {
UCR_alert_("三端內容尚未產生。請先執行「產生三端內容」。");
return;
}
let data = UCR_getWeeklyDataByWeekId_(weekId);
let publishInfo = UCR_getPublishInfo_(weekId);
let docUrl = publishInfo.docUrl;
if (!docUrl) {
if (!UCR_confirm_("Google Docs 尚未建立", "是否立即建立 Google Docs 課程重點後再發布？")) return;
docUrl = UCR_createGoogleDocForWeek_(weekId);
}
let folderUrl = data.materialsFolderUrl;
if (!folderUrl) {
if (!UCR_confirm_("素材資料夾尚未建立", "是否立即建立本週課程素材資料夾後再發布？")) return;
folderUrl = UCR_createMaterialsFolderForWeek_(weekId);
data = UCR_getWeeklyDataByWeekId_(weekId);
}
UCR_generatePlatformPublishMaterialsForWeek_(weekId);
publishInfo = UCR_getPublishInfo_(weekId);
const message = UCR_buildGoogleChatMessage_(weekId, data, settings, publishInfo, docUrl, folderUrl);
UCR_sendGoogleChatWebhook_(webhookUrl, message);
UCR_updatePublishLogCollaborationStatus_(weekId, settings["協作平台名稱"] || "Google Chat");
UCR_refreshDashboardForWeek_(weekId);
UCR_updateWeeklyIndex_();
UCR_alert_("已發布課程內容重點到協作平台。");
});
}
function UCR_buildGoogleChatMessage_(weekId, data, settings, publishInfo, docUrl, folderUrl) {
return [
"【" + (settings["課程名稱"] || "課程") + "｜" + weekId + " 課程內容重點】",
"日期區間：" + data.dateRange,
"",
"摘要：",
UCR_makeWeeklySummary_(data),
"",
"Google Docs：" + (docUrl || "尚未建立"),
"課程素材：" + (folderUrl || "尚未建立"),
"",
"發布狀態：",
"課程平台：" + (publishInfo.platformStatus || "尚未發布"),
"Email：" + (publishInfo.emailStatus || "尚未發布"),
"LMS/社群：" + (publishInfo.lmsStatus || "尚未發布"),
].join("\n");
}
function UCR_sendGoogleChatWebhook_(webhookUrl, message) {
const response = UrlFetchApp.fetch(webhookUrl, {
method: "post",
contentType: "application/json; charset=UTF-8",
payload: JSON.stringify({ text: message }),
muteHttpExceptions: true,
});
const code = response.getResponseCode();
if (code < 200 || code >= 300) {
throw new Error("Google Chat Webhook 發送失敗，HTTP " + code + "：" + response.getContentText());
}
}
function UCR_showWeeklyInputForm() {
UCR_runAction_("開啟課程週次填寫表單", function () {
const html = HtmlService.createHtmlOutput(UCR_buildWeeklyInputFormHtml_()).setTitle("課程週次填寫表單");
SpreadsheetApp.getUi().showSidebar(html);
});
}
function UCR_getWeeklyInputFormData() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
UCR_ensureStructure_(ss);
const weekId = UCR_getActiveWeekId_() || UCR_getLatestWeekId_();
const settings = UCR_getSettings_();
if (!weekId) {
return {
hasWeek: false,
weekId: "",
dateRange: "",
courseName: settings["課程名稱"] || "課程",
fields: UCR_getEmptyFormFields_(),
warnings: [],
};
}
const data = UCR_getWeeklyDataByWeekId_(weekId);
if (!data) {
return {
hasWeek: false,
weekId: "",
dateRange: "",
courseName: settings["課程名稱"] || "課程",
fields: UCR_getEmptyFormFields_(),
warnings: [],
};
}
return {
hasWeek: true,
weekId: weekId,
dateRange: data.dateRange,
courseName: settings["課程名稱"] || "課程",
fields: {
topic: data.topic || "",
concepts: data.concepts || "",
activities: data.activities || "",
assignments: data.assignments || "",
nextPreview: data.nextPreview || "",
resources: data.resources || "",
},
warnings: UCR_getIncompleteFieldWarnings_(data),
};
}
function UCR_getEmptyFormFields_() {
const p = UCR_CONFIG.PLACEHOLDERS;
return {
topic: p.topic,
concepts: p.concepts,
activities: p.activities,
assignments: p.assignments,
nextPreview: p.nextPreview,
resources: p.resources,
};
}
function UCR_saveWeeklyInputForm(formData) {
return UCR_runFormAction_("儲存課程週次資料", function () {
return UCR_saveWeeklyInputForm_(formData, false);
});
}
function UCR_saveWeeklyInputFormAndGenerate(formData) {
return UCR_runFormAction_("儲存並產生三端內容", function () {
const result = UCR_saveWeeklyInputForm_(formData, true);
if (!result.ok) return result;
const generateResult = UCR_generateThreeChannelCopiesForWeek_(result.weekId);
return {
ok: true,
message:
"已儲存課程週次資料，並產生三端內容。" +
(generateResult && generateResult.warnings.length
? "\n仍需確認欄位：" + generateResult.warnings.join("、")
: ""),
};
});
}
function UCR_saveWeeklyInputForm_(formData, allowCreateIfMissing) {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName(UCR_CONFIG.SHEETS.WEEKLY_DATA);
if (!sheet) {
return { ok: false, message: "請先執行「初始化平台」。" };
}
const weekId = String(formData && formData.weekId ? formData.weekId : UCR_getActiveWeekId_()).trim();
if (!weekId) {
if (!allowCreateIfMissing) {
return { ok: false, message: "目前沒有週次ID，請先建立本週課程重點範本。" };
}
const created = UCR_createWeekReportByInfo_(UCR_getCurrentWeekInfo_());
if (!created) return { ok: false, message: "無法建立本週課程重點範本。" };
return UCR_saveWeeklyInputForm_(
Object.assign({}, formData || {}, { weekId: created.weekId }),
false,
);
}
const row = UCR_findRowByWeekId_(sheet, weekId);
if (row <= 0) {
return { ok: false, message: "找不到課程週次資料：" + weekId };
}
const values = [
String(formData.topic || "").trim(),
String(formData.concepts || "").trim(),
String(formData.activities || "").trim(),
String(formData.assignments || "").trim(),
String(formData.nextPreview || "").trim(),
String(formData.resources || "").trim(),
new Date(),
];
sheet.getRange(row, 3, 1, 6).setValues([values.slice(0, 6)]);
sheet.getRange(row, 11).setValue(values[6]);
UCR_refreshDashboardForWeek_(weekId);
UCR_updateWeeklyIndex_();
const updated = UCR_getWeeklyDataByWeekId_(weekId);
const warnings = UCR_getIncompleteFieldWarnings_(updated);
return {
ok: true,
weekId: weekId,
message:
"已儲存課程週次資料。" +
(warnings.length ? "\n仍需確認欄位：" + warnings.join("、") : ""),
};
}
function UCR_runFormAction_(actionName, callback) {
try {
UCR_ensureStructure_(SpreadsheetApp.getActiveSpreadsheet());
return callback();
} catch (e) {
Logger.log(e && e.stack ? e.stack : e);
return {
ok: false,
message: actionName + "失敗：" + (e && e.message ? e.message : e),
};
}
}
function UCR_buildWeeklyInputFormHtml_() {
return [
"<!doctype html>",
"<html><head><base target=\"_top\"><style>",
"body{margin:0;background:#f6f8fb;color:#182230;font-family:Arial,'Noto Sans TC',sans-serif;font-size:13px}.app{padding:14px}h1{font-size:18px;margin:0 0 4px;color:#102a43}.muted{color:#667085;font-size:12px;line-height:1.45}.notice{border:1px solid #f6d58f;background:#fff8e5;border-radius:8px;padding:10px;margin:10px 0;color:#7a4b00;line-height:1.5}.panel{background:#fff;border:1px solid #d9e2ec;border-radius:8px;padding:12px;margin:10px 0;box-shadow:0 1px 2px rgba(16,42,67,.04)}label{display:block;font-weight:700;color:#243b53;margin:12px 0 6px}input,textarea{box-sizing:border-box;width:100%;border:1px solid #cbd5e1;border-radius:8px;padding:10px;font-size:13px;line-height:1.5;background:#fff;color:#111827}textarea{min-height:104px;resize:vertical}.short{min-height:74px}.actions{display:grid;grid-template-columns:1fr;gap:8px;margin-top:12px}.two{grid-template-columns:1fr 1fr}button{border:1px solid #b8c7d9;border-radius:8px;background:#fff;color:#17324d;font-weight:700;font-size:12px;padding:10px;cursor:pointer}.primary{background:#243b53;border-color:#243b53;color:#fff}.success{background:#087443;border-color:#087443;color:#fff}button:disabled{opacity:.55;cursor:wait}#toast{position:sticky;bottom:0;margin-top:10px;background:#102a43;color:#fff;border-radius:8px;padding:9px 10px;display:none;white-space:pre-line}.warn{color:#854d0e;font-size:12px;line-height:1.5}",
"</style></head><body><div class=\"app\"><h1>課程週次填寫表單</h1><div class=\"muted\" id=\"meta\">讀取中...</div><div id=\"root\"></div><div id=\"toast\"></div></div>",
"<script>",
"var current=null;",
"function esc(v){return String(v||'').replace(/[&<>\"']/g,function(c){return {'&':'&','<':'<','>':'>','\"':'"',\"'\":'''}[c];});}",
"function toast(msg){var el=document.getElementById('toast');el.textContent=msg;el.style.display='block';setTimeout(function(){el.style.display='none';},3600);}",
"function busy(on){Array.prototype.forEach.call(document.querySelectorAll('button'),function(btn){btn.disabled=on;});}",
"function val(id){return document.getElementById(id).value;}",
"function payload(){return {weekId:current.weekId,topic:val('topic'),concepts:val('concepts'),activities:val('activities'),assignments:val('assignments'),nextPreview:val('nextPreview'),resources:val('resources')};}",
"function runCreate(fn){busy(true);var r=google.script.run.withSuccessHandler(function(){busy(false);load();}).withFailureHandler(function(e){busy(false);toast(e&&e.message?e.message:String(e));});if(fn==='current')r.UCR_createCurrentWeekReport();if(fn==='next')r.UCR_createNextWeekReport();}",
"function save(generate){busy(true);var r=google.script.run.withSuccessHandler(function(res){busy(false);toast(res.message||'已完成');load();}).withFailureHandler(function(e){busy(false);toast(e&&e.message?e.message:String(e));});if(generate){r.UCR_saveWeeklyInputFormAndGenerate(payload());}else{r.UCR_saveWeeklyInputForm(payload());}}",
"function load(){google.script.run.withSuccessHandler(render).withFailureHandler(function(e){toast(e&&e.message?e.message:String(e));}).UCR_getWeeklyInputFormData();}",
"function render(d){current=d;document.getElementById('meta').textContent=d.hasWeek?(d.courseName+'｜'+d.weekId+'｜'+d.dateRange):d.courseName+'｜尚未建立週次';var h='';if(!d.hasWeek){h+='<div class=\"notice\">尚未建立本週課程重點範本。請先建立週次，再填寫內容。</div><div class=\"actions\"><button class=\"primary\" onclick=\"runCreate(\\'current\\')\">建立本週課程重點範本</button><button onclick=\"runCreate(\\'next\\')\">建立下一週課程重點範本</button></div>';document.getElementById('root').innerHTML=h;return;}if(d.warnings&&d.warnings.length){h+='<div class=\"notice\">仍需確認：'+esc(d.warnings.join('、'))+'</div>';}h+='<div class=\"panel\"><label>課程單元主題</label><textarea class=\"short\" id=\"topic\">'+esc(d.fields.topic)+'</textarea><label>本週核心概念</label><textarea id=\"concepts\">'+esc(d.fields.concepts)+'</textarea><label>課堂活動、案例、討論或實作</label><textarea id=\"activities\">'+esc(d.fields.activities)+'</textarea><label>指定閱讀、作業、測驗或繳交事項</label><textarea id=\"assignments\">'+esc(d.fields.assignments)+'</textarea><label>下週預告與預習方向</label><textarea id=\"nextPreview\">'+esc(d.fields.nextPreview)+'</textarea><label>補充資源、影片、資料集或延伸閱讀連結</label><textarea class=\"short\" id=\"resources\">'+esc(d.fields.resources)+'</textarea><div class=\"actions two\"><button onclick=\"save(false)\">儲存草稿</button><button class=\"success\" onclick=\"save(true)\">儲存並產生三端內容</button></div></div>';document.getElementById('root').innerHTML=h;}",
"load();",
"</script></body></html>",
].join("");
}
function UCR_showStudentRegisterForm() {
UCR_runAction_("開啟學生首次登錄表單", function () {
const html = HtmlService.createHtmlOutput(UCR_buildStudentRegisterFormHtml_()).setTitle("學生首次登錄表單");
SpreadsheetApp.getUi().showSidebar(html);
});
}
function UCR_openStudentsSheet() {
UCR_runAction_("開啟學生名單", function () {
const ss = SpreadsheetApp.getActiveSpreadsheet();
UCR_ensureStudentsSheet_(ss);
ss.setActiveSheet(ss.getSheetByName(UCR_CONFIG.SHEETS.STUDENTS));
});
}
function UCR_buildStudentRegisterFormHtml_() {
return [
"<!doctype html>",
"<html><head><base target=\"_top\"><style>",
"body{margin:0;background:#f6f8fb;color:#182230;font-family:Arial,'Noto Sans TC',sans-serif;font-size:13px}.app{padding:14px}h1{font-size:18px;margin:0 0 4px;color:#102a43}.muted{color:#667085;font-size:12px;line-height:1.45}.notice{border:1px solid #bed9e6;background:#eef8fb;border-radius:8px;padding:10px;margin:10px 0;color:#174a5e;line-height:1.5}.panel{background:#fff;border:1px solid #d9e2ec;border-radius:8px;padding:12px;margin:10px 0;box-shadow:0 1px 2px rgba(16,42,67,.04)}label{display:block;font-weight:700;color:#243b53;margin:12px 0 6px}input,textarea{box-sizing:border-box;width:100%;border:1px solid #cbd5e1;border-radius:8px;padding:10px;font-size:13px;line-height:1.5;background:#fff;color:#111827}textarea{min-height:74px;resize:vertical}.check{display:flex;align-items:center;gap:8px;margin-top:12px;font-weight:700;color:#243b53}.check input{width:auto}.actions{display:grid;grid-template-columns:1fr;gap:8px;margin-top:12px}button{border:1px solid #b8c7d9;border-radius:8px;background:#243b53;color:#fff;font-weight:700;font-size:13px;padding:11px 12px;cursor:pointer}button:disabled{opacity:.55;cursor:wait}#toast{position:sticky;bottom:0;margin-top:10px;background:#102a43;color:#fff;border-radius:8px;padding:9px 10px;display:none;white-space:pre-line}.error{background:#7f1d1d}",
"</style></head><body><div class=\"app\"><h1>學生首次登錄表單</h1><div class=\"muted\">請填寫收件 Email。系統會依訂閱狀態寄送課程週三報。</div><div class=\"notice\">必填：學號、姓名、Email。若資料有異動，再送出一次即可更新。</div><div class=\"panel\"><label>學號 *</label><input id=\"studentId\" autocomplete=\"off\"><label>姓名 *</label><input id=\"name\" autocomplete=\"name\"><label>Email *</label><input id=\"email\" type=\"email\" autocomplete=\"email\"><label>系級／班級</label><input id=\"className\"><label>備註</label><textarea id=\"note\"></textarea><label class=\"check\"><input id=\"subscribed\" type=\"checkbox\" checked>訂閱週三報 Email</label><div class=\"actions\"><button id=\"submitBtn\" onclick=\"submitForm()\">送出登錄資料</button></div></div><div id=\"toast\"></div></div>",
"<script>",
"function v(id){return document.getElementById(id).value.trim();}",
"function toast(msg,isError){var el=document.getElementById('toast');el.textContent=msg;el.className=isError?'error':'';el.style.display='block';}",
"function busy(on){document.getElementById('submitBtn').disabled=on;}",
"function submitForm(){var payload={studentId:v('studentId'),name:v('name'),email:v('email'),className:v('className'),note:v('note'),subscribed:document.getElementById('subscribed').checked};if(!payload.studentId||!payload.name||!payload.email){toast('請填寫學號、姓名與 Email。',true);return;}if(!/^[^\\s@]+@[^\\s@]+\\.[^\\s@]+$/.test(payload.email)){toast('Email 格式看起來不正確，請再確認。',true);return;}busy(true);google.script.run.withSuccessHandler(function(res){busy(false);toast(res&&res.message?res.message:'已完成登錄。',!(res&&res.ok));}).withFailureHandler(function(e){busy(false);toast(e&&e.message?e.message:String(e),true);}).UCR_submitStudentRegistration(payload);}",
"</script></body></html>",
].join("");
}
function UCR_submitStudentRegistration(payload) {
return UCR_runFormAction_("學生首次登錄", function () {
const ss = SpreadsheetApp.getActiveSpreadsheet();
UCR_ensureStudentsSheet_(ss);
const sheet = ss.getSheetByName(UCR_CONFIG.SHEETS.STUDENTS);
const studentId = String(payload && payload.studentId ? payload.studentId : "").trim();
const name = String(payload && payload.name ? payload.name : "").trim();
const email = String(payload && payload.email ? payload.email : "").trim();
const className = String(payload && payload.className ? payload.className : "").trim();
const note = String(payload && payload.note ? payload.note : "").trim();
const subscribed = payload && payload.subscribed === false ? "否" : "是";
if (!studentId || !name || !email) {
return { ok: false, message: "請填寫學號、姓名與 Email。" };
}
if (!UCR_isValidEmail_(email)) {
return { ok: false, message: "Email 格式不正確，請重新確認。" };
}
const now = new Date();
const existingRow = UCR_findStudentRowById_(sheet, studentId);
const emailRows = UCR_findStudentRowsByEmail_(sheet, email).filter(function (row) {
if (existingRow > 0 && row === existingRow) return false;
const existingId = String(sheet.getRange(row, 2).getValue() || "").trim();
return existingId !== studentId;
});
const duplicateEmailWarning = emailRows.length > 0 ? "\n提醒：此 Email 已出現在其他學號資料中，請教師或助教人工確認是否為共用信箱或誤填。" : "";
if (existingRow > 0) {
const registeredAt = sheet.getRange(existingRow, 1).getValue() || now;
sheet.getRange(existingRow, 1, 1, 8).setValues([[registeredAt, studentId, name, email, className, note, subscribed, now]]);
UCR_refreshStudentsSummaryOnDashboard_();
return { ok: true, message: "已更新你的登錄資料。" + duplicateEmailWarning };
}
sheet.appendRow([now, studentId, name, email, className, note, subscribed, now]);
UCR_refreshStudentsSummaryOnDashboard_();
return { ok: true, message: "已完成首次登錄，之後課程週三報會寄送到你的 Email。" + duplicateEmailWarning };
});
}
function UCR_getSubscribedStudentRecipients_() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
UCR_ensureStudentsSheet_(ss);
const sheet = ss.getSheetByName(UCR_CONFIG.SHEETS.STUDENTS);
if (!sheet || sheet.getLastRow() < 2) return [];
return sheet.getRange(2, 1, sheet.getLastRow() - 1, 8).getValues().map(function (row) {
return {
studentId: String(row[1] || "").trim(),
name: String(row[2] || "").trim(),
email: String(row[3] || "").trim(),
subscribed: String(row[6] || "").trim(),
};
}).filter(function (student) {
return student.subscribed === "是" && student.email && UCR_isValidEmail_(student.email);
}).map(function (student) {
return { studentId: student.studentId, name: student.name, email: student.email };
});
}
function UCR_getStudentsSummary_() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
UCR_ensureStudentsSheet_(ss);
const sheet = ss.getSheetByName(UCR_CONFIG.SHEETS.STUDENTS);
const summary = { total: 0, subscribed: 0, validEmail: 0, invalidEmail: 0 };
if (!sheet || sheet.getLastRow() < 2) return summary;
sheet.getRange(2, 1, sheet.getLastRow() - 1, 8).getValues().forEach(function (row) {
const studentId = String(row[1] || "").trim();
const name = String(row[2] || "").trim();
const email = String(row[3] || "").trim();
const subscribed = String(row[6] || "").trim();
if (!studentId && !name && !email) return;
summary.total++;
if (subscribed === "是") summary.subscribed++;
if (email && UCR_isValidEmail_(email)) {
summary.validEmail++;
} else if (email) {
summary.invalidEmail++;
}
});
return summary;
}
function UCR_showWeeklyPublishPanel() {
UCR_runAction_("開啟本週發布面板", function () {
const html = HtmlService.createHtmlOutput(UCR_buildWeeklyPublishPanelHtml_()).setTitle("本週課程發布面板");
SpreadsheetApp.getUi().showSidebar(html);
});
}
function UCR_getWeeklyPublishPanelData() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
UCR_ensureStructure_(ss);
const weekId = UCR_getActiveWeekId_() || UCR_getLatestWeekId_();
const settings = UCR_getSettings_();
const empty = {
hasWeek: false,
weekId: "",
dateRange: "",
overallStatus: UCR_CONFIG.STATUS.NOT_CREATED,
nextAction: "請先建立本週課程重點範本。",
coursePlatformUrl: settings["課程平台網址"] || "",
platformName: settings["協作平台名稱"] || "Google Chat",
copiesReady: false,
docReady: false,
folderReady: false,
links: { docUrl: "", folderUrl: "", coursePlatformUrl: settings["課程平台網址"] || "" },
publishInfo: UCR_getPublishInfo_(""),
copies: { platformPlain: "", platformHtml: "", email: "", lms: "", collaborationPreview: "" },
};
if (!weekId) return empty;
const data = UCR_getWeeklyDataByWeekId_(weekId);
if (!data) return empty;
const publishInfo = UCR_getPublishInfo_(weekId);
const copies = UCR_getThreeChannelCopiesForWeek_(weekId);
const copiesReady = Boolean(copies.platform || copies.email || copies.lms);
const title = UCR_buildPlatformMaterialTitle_(data, settings);
const plain = copies.platform || UCR_buildCoursePlatformCopy_(data, settings);
const html = UCR_buildPlatformHtml_(title, UCR_makeWeeklySummary_(data), plain, publishInfo.docUrl, data.materialsFolderUrl);
return {
hasWeek: true,
weekId: weekId,
dateRange: data.dateRange,
overallStatus: UCR_getOverallStatus_(publishInfo, copiesReady),
nextAction: UCR_getNextActionText_(data, publishInfo, copiesReady),
coursePlatformUrl: settings["課程平台網址"] || "",
platformName: settings["協作平台名稱"] || "Google Chat",
copiesReady: copiesReady,
docReady: Boolean(publishInfo.docUrl),
folderReady: Boolean(data.materialsFolderUrl),
links: { docUrl: publishInfo.docUrl || "", folderUrl: data.materialsFolderUrl || "", coursePlatformUrl: settings["課程平台網址"] || "" },
publishInfo: publishInfo,
copies: {
platformPlain: plain,
platformHtml: html,
email: copies.email || "",
lms: copies.lms || "",
collaborationPreview: UCR_buildGoogleChatMessage_(weekId, data, settings, publishInfo, publishInfo.docUrl, data.materialsFolderUrl),
},
};
}
function UCR_buildWeeklyPublishPanelHtml_() {
return [
"<!doctype html>",
"<html><head><base target=\"_top\"><style>",
"body{margin:0;background:#f6f8fb;color:#182230;font-family:Arial,'Noto Sans TC',sans-serif;font-size:13px}.app{padding:14px}h1{font-size:18px;margin:0;color:#102a43}.top{display:flex;justify-content:space-between;gap:10px;margin-bottom:12px}.muted{color:#667085;font-size:12px;line-height:1.45}.status{border:1px solid #d6dee8;border-radius:8px;background:#fff;padding:6px 8px;font-weight:700;white-space:nowrap}.notice{border:1px solid #f6d58f;background:#fff8e5;border-radius:8px;padding:10px;margin:10px 0;color:#7a4b00;line-height:1.5}.panel{background:#fff;border:1px solid #d9e2ec;border-radius:8px;padding:12px;margin:10px 0;box-shadow:0 1px 2px rgba(16,42,67,.04)}.panel h2{font-size:15px;margin:0 0 8px;color:#102a43}.grid{display:grid;grid-template-columns:1fr 1fr;gap:8px}.field{border:1px solid #e1e8f0;background:#fbfcfe;border-radius:8px;padding:8px;min-width:0}.field label{display:block;color:#667085;font-size:11px;margin-bottom:4px}a{color:#1155cc;text-decoration:underline;word-break:break-word}textarea{box-sizing:border-box;width:100%;min-height:130px;border:1px solid #cbd5e1;border-radius:8px;padding:10px;font-size:12px;line-height:1.5;background:#fff;color:#111827;resize:vertical}.html{min-height:170px;font-family:Menlo,Consolas,monospace;font-size:11px}.actions{display:grid;grid-template-columns:1fr;gap:8px;margin-top:8px}.two{grid-template-columns:1fr 1fr}button{border:1px solid #b8c7d9;border-radius:8px;background:#fff;color:#17324d;font-weight:700;font-size:12px;padding:9px 10px;cursor:pointer}.primary{background:#243b53;border-color:#243b53;color:#fff}.success{background:#087443;border-color:#087443;color:#fff}.pill{font-size:11px;border-radius:999px;padding:4px 7px;background:#eef2f7;color:#344054}.ok{background:#dcfce7;color:#166534}.missing{background:#fff7d6;color:#854d0e}#toast{position:sticky;bottom:0;margin-top:10px;background:#102a43;color:#fff;border-radius:8px;padding:9px 10px;display:none}",
"</style></head><body><div class=\"app\"><div class=\"top\"><div><h1>本週課程發布面板</h1><div class=\"muted\" id=\"meta\">讀取中...</div></div><div class=\"status\" id=\"status\">...</div></div><div id=\"root\"></div><div id=\"toast\"></div></div>",
"<script>",
"function esc(v){return String(v||'').replace(/[&<>\"']/g,function(c){return {'&':'&','<':'<','>':'>','\"':'"',\"'\":'''}[c];});}",
"function link(label,url){return url?'<a href=\"'+esc(url)+'\" target=\"_blank\">'+esc(label)+'</a>':'<span class=\"muted\">尚未建立</span>';}",
"function pill(ok,text){return '<span class=\"pill '+(ok?'ok':'missing')+'\">'+esc(text)+'</span>';}",
"function toast(msg){var el=document.getElementById('toast');el.textContent=msg;el.style.display='block';setTimeout(function(){el.style.display='none';},2600);}",
"function copyFrom(id,label){var el=document.getElementById(id);el.focus();el.select();if(navigator.clipboard){navigator.clipboard.writeText(el.value).then(function(){toast(label+'已複製。');});}else{document.execCommand('copy');toast(label+'已選取。');}}",
"function run(fn){var r=google.script.run.withSuccessHandler(load).withFailureHandler(function(e){toast(e&&e.message?e.message:String(e));});if(fn==='genCopies')r.UCR_generateThreeChannelCopies();if(fn==='genDoc')r.UCR_generateGoogleDocForActiveWeek();if(fn==='folder')r.UCR_createMaterialsFolderForActiveWeek();if(fn==='platformDone')r.UCR_markCoursePlatformPublished();if(fn==='emailDone')r.UCR_markEmailPublished();if(fn==='lmsDone')r.UCR_markLmsPublished();if(fn==='collab')r.UCR_publishToCollaborationPlatform();if(fn==='current')r.UCR_createCurrentWeekReport();if(fn==='next')r.UCR_createNextWeekReport();}",
"function load(){google.script.run.withSuccessHandler(render).withFailureHandler(function(e){toast(e&&e.message?e.message:String(e));}).UCR_getWeeklyPublishPanelData();}",
"function render(d){document.getElementById('meta').textContent=d.hasWeek?(d.weekId+'｜'+d.dateRange):'尚未建立週次';document.getElementById('status').textContent=d.overallStatus;var h='';if(!d.hasWeek){h+='<div class=\"notice\">尚未建立本週課程重點範本。</div><div class=\"actions\"><button class=\"primary\" onclick=\"run(\\'current\\')\">建立本週課程重點範本</button><button onclick=\"run(\\'next\\')\">建立下一週課程重點範本</button></div>';document.getElementById('root').innerHTML=h;return;}h+='<div class=\"notice\"><strong>下一步：</strong>'+esc(d.nextAction)+'</div>';h+='<div class=\"grid\"><div class=\"field\"><label>Google Docs</label>'+link('開啟 Docs',d.links.docUrl)+'</div><div class=\"field\"><label>素材資料夾</label>'+link('開啟素材資料夾',d.links.folderUrl)+'</div><div class=\"field\"><label>課程平台</label>'+link('開啟課程平台',d.links.coursePlatformUrl)+'</div><div class=\"field\"><label>內容狀態</label>'+(d.copiesReady?pill(true,'已產生三端內容'):pill(false,'尚未產生三端內容'))+'</div></div>';if(!d.copiesReady){h+='<div class=\"notice\">尚未產生三端內容。</div><button class=\"primary\" onclick=\"run(\\'genCopies\\')\">產生三端內容</button>';}if(!d.docReady||!d.folderReady){h+='<div class=\"panel\"><h2>發布前準備</h2><div class=\"actions two\">';if(!d.docReady)h+='<button onclick=\"run(\\'genDoc\\')\">建立 Google Docs</button>';if(!d.folderReady)h+='<button onclick=\"run(\\'folder\\')\">建立素材資料夾</button>';h+='</div></div>';}h+=sectionPlatform(d)+sectionEmail(d)+sectionLms(d)+sectionCollab(d);document.getElementById('root').innerHTML=h;}",
"function sectionPlatform(d){return '<div class=\"panel\"><h2>課程平台公告 '+pill(d.copiesReady,'純文字 / HTML')+'</h2><textarea id=\"platformPlain\">'+esc(d.copies.platformPlain)+'</textarea><textarea class=\"html\" id=\"platformHtml\">'+esc(d.copies.platformHtml)+'</textarea><div class=\"actions two\"><button onclick=\"copyFrom(\\'platformPlain\\',\\'課程平台純文字\\')\">複製純文字</button><button onclick=\"copyFrom(\\'platformHtml\\',\\'課程平台HTML\\')\">複製HTML</button><button class=\"success\" onclick=\"run(\\'platformDone\\')\">標記課程平台已發布</button></div><div class=\"muted\">目前狀態：'+esc(d.publishInfo.platformStatus)+'</div></div>';}",
"function sectionEmail(d){return '<div class=\"panel\"><h2>Email提醒 '+pill(Boolean(d.copies.email),'可複製')+'</h2><textarea id=\"emailCopy\">'+esc(d.copies.email)+'</textarea><div class=\"actions two\"><button onclick=\"copyFrom(\\'emailCopy\\',\\'Email文案\\')\">複製Email文案</button><button class=\"success\" onclick=\"run(\\'emailDone\\')\">標記Email已發布</button></div><div class=\"muted\">目前狀態：'+esc(d.publishInfo.emailStatus)+'</div></div>';}",
"function sectionLms(d){return '<div class=\"panel\"><h2>LMS/社群 '+pill(Boolean(d.copies.lms),'可複製')+'</h2><textarea id=\"lmsCopy\">'+esc(d.copies.lms)+'</textarea><div class=\"actions two\"><button onclick=\"copyFrom(\\'lmsCopy\\',\\'LMS社群文案\\')\">複製LMS/社群文案</button><button class=\"success\" onclick=\"run(\\'lmsDone\\')\">標記LMS/社群已發布</button></div><div class=\"muted\">目前狀態：'+esc(d.publishInfo.lmsStatus)+'</div></div>';}",
"function sectionCollab(d){return '<div class=\"panel\"><h2>協作平台 '+pill(d.publishInfo.collaborationStatus&&d.publishInfo.collaborationStatus.indexOf('已發布')===0,d.platformName)+'</h2><textarea id=\"collabPreview\">'+esc(d.copies.collaborationPreview)+'</textarea><div class=\"actions\"><button class=\"primary\" onclick=\"run(\\'collab\\')\">發布到協作平台</button></div><div class=\"muted\">目前狀態：'+esc(d.publishInfo.collaborationStatus)+'</div></div>';}",
"load();",
"</script></body></html>",
].join("");
}
function UCR_markCoursePlatformPublished() {
UCR_runAction_("標記課程平台已發布", function () {
UCR_markPublished_("課程平台");
});
}
function UCR_sendWeeklyEmailToStudents() {
UCR_runAction_("寄送本週週三報給學生", function () {
const weekId = UCR_getActiveWeekId_() || UCR_getLatestWeekId_();
if (!weekId) {
UCR_alert_("目前沒有週次ID，請先建立本週課程重點範本。");
return;
}
const copies = UCR_getThreeChannelCopiesForWeek_(weekId);
if (!copies.email) {
UCR_alert_("尚未產生 Email短提醒版。請先執行「產生三端內容」。");
return;
}
const recipients = UCR_getSubscribedStudentRecipients_();
if (recipients.length === 0) {
UCR_alert_("沒有有效收件者。請先請學生完成首次登錄，並確認已訂閱週三報且 Email 格式有效。");
return;
}
// Google Apps Script / Gmail / MailApp 有每日寄送配額限制，實際上限依帳號類型不同。
// 研習現場避免無提示大量寄送；超過 80 人時先請教師分批或改由課程平台公告。
if (recipients.length > 80) {
UCR_alert_("目前有效收件者為 " + recipients.length + " 人，超過 80 人。為避免 Gmail / MailApp 配額與大量寄送風險，請先分批寄送或改由課程平台公告。");
return;
}
const remainingQuota = MailApp.getRemainingDailyQuota();
if (remainingQuota < recipients.length) {
UCR_alert_("今日 MailApp 剩餘寄送配額不足。\n有效收件者：" + recipients.length + " 人\n剩餘配額：" + remainingQuota + "\n請改日再寄送或改用課程平台公告。");
return;
}
if (!UCR_confirm_("確認寄送", "即將寄送本週週三報給 " + recipients.length + " 位已訂閱學生，是否繼續？")) {
UCR_alert_("已取消寄送。");
return;
}
const settings = UCR_getSettings_();
const courseName = settings["課程名稱"] || "課程";
const instructor = settings["授課教師"] || "授課教師";
const platformUrl = settings["課程平台網址"] || "";
const subject = "[" + courseName + "] 週三報｜" + weekId;
const body = UCR_buildStudentWeeklyEmailBody_(copies.email, courseName, instructor, platformUrl);
const to = UCR_getMailSenderAddress_();
if (!to) {
UCR_alert_("無法取得寄送者 Email。請確認目前帳號可使用 Gmail / MailApp，或改用手動複製 Email 文案。");
return;
}
MailApp.sendEmail({
to: to,
bcc: recipients.map(function (student) {
return student.email;
}).join(","),
subject: subject,
body: body,
name: courseName + " 週三報",
});
UCR_updatePublishLogEmailStatus_(weekId, "已寄送給學生", recipients.length);
UCR_refreshDashboardForWeek_(weekId);
UCR_updateWeeklyIndex_();
UCR_alert_("已寄送本週週三報給學生。\n有效收件者：" + recipients.length + " 人");
});
}
function UCR_markEmailPublished() {
UCR_runAction_("標記 Email 已發布", function () {
UCR_markPublished_("Email");
});
}
function UCR_markLmsPublished() {
UCR_runAction_("標記 LMS/社群 已發布", function () {
UCR_markPublished_("LMS/社群");
});
}
function UCR_markPublished_(channelName) {
const weekId = UCR_getActiveWeekId_();
if (!weekId) {
UCR_alert_("目前沒有週次ID，請先建立本週課程重點範本。");
return;
}
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName(UCR_CONFIG.SHEETS.PUBLISH_LOG);
UCR_ensurePublishLogRow_(weekId);
const row = UCR_findRowByWeekId_(sheet, weekId);
const nowText = Utilities.formatDate(new Date(), UCR_CONFIG.TIMEZONE, "yyyy/MM/dd HH:mm");
let col = 0;
if (channelName === "課程平台") col = 2;
if (channelName === "Email") col = 3;
if (channelName === "LMS/社群") col = 4;
if (!col) {
UCR_alert_("未知的發布管道：" + channelName);
return;
}
sheet.getRange(row, col).setValue("已發布 " + nowText);
sheet.getRange(row, 8).setValue(new Date());
UCR_refreshDashboardForWeek_(weekId);
UCR_updateWeeklyIndex_();
UCR_alert_(channelName + " 已標記發布。");
}
function UCR_ensurePublishLogRow_(weekId) {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName(UCR_CONFIG.SHEETS.PUBLISH_LOG);
if (!sheet) return;
if (UCR_findRowByWeekId_(sheet, weekId) > 0) return;
sheet.appendRow([weekId, "尚未發布", "尚未發布", "尚未發布", "尚未發布", "", "", new Date()]);
}
function UCR_updatePublishLogDocUrl_(weekId, docUrl) {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName(UCR_CONFIG.SHEETS.PUBLISH_LOG);
if (!sheet) {
UCR_alert_("找不到「03_發布紀錄」，請先執行「初始化平台」。");
return;
}
UCR_ensurePublishLogRow_(weekId);
const row = UCR_findRowByWeekId_(sheet, weekId);
sheet.getRange(row, 6).setValue(docUrl);
sheet.getRange(row, 8).setValue(new Date());
}
function UCR_updatePublishLogCollaborationStatus_(weekId, platformName) {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName(UCR_CONFIG.SHEETS.PUBLISH_LOG);
UCR_ensurePublishLogRow_(weekId);
const row = UCR_findRowByWeekId_(sheet, weekId);
const nowText = Utilities.formatDate(new Date(), UCR_CONFIG.TIMEZONE, "yyyy/MM/dd HH:mm");
sheet.getRange(row, 5).setValue("已發布到 " + platformName + " " + nowText);
sheet.getRange(row, 8).setValue(new Date());
}
function UCR_updatePublishLogEmailStatus_(weekId, statusPrefix, recipientCount) {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName(UCR_CONFIG.SHEETS.PUBLISH_LOG);
UCR_ensurePublishLogRow_(weekId);
const row = UCR_findRowByWeekId_(sheet, weekId);
const now = new Date();
const nowText = Utilities.formatDate(now, UCR_CONFIG.TIMEZONE, "yyyy/MM/dd HH:mm");
sheet.getRange(row, 3).setValue(statusPrefix + " " + nowText + "，有效收件者 " + recipientCount + " 人");
sheet.getRange(row, 8).setValue(now);
}
function UCR_updateWeeklyIndex() {
UCR_runAction_("更新歷週索引", function () {
UCR_updateWeeklyIndex_();
UCR_alert_("歷週索引已更新。");
});
}
function UCR_updateWeeklyIndex_() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const indexSheet = ss.getSheetByName(UCR_CONFIG.SHEETS.INDEX);
const weeklySheet = ss.getSheetByName(UCR_CONFIG.SHEETS.WEEKLY_DATA);
const logSheet = ss.getSheetByName(UCR_CONFIG.SHEETS.PUBLISH_LOG);
if (!indexSheet || !weeklySheet || !logSheet) return;
indexSheet.getRange(2, 1, Math.max(indexSheet.getMaxRows() - 1, 1), 8).clearContent().clearFormat();
UCR_styleHeader_(indexSheet, 8);
if (weeklySheet.getLastRow() < 2) return;
const settings = UCR_getSettings_();
const platformUrl = settings["課程平台網址"] || "";
const rows = [];
const links = [];
weeklySheet.getRange(2, 1, weeklySheet.getLastRow() - 1, 11).getValues().forEach(function (row) {
const weekId = row[0];
if (!weekId) return;
const data = UCR_rowToWeeklyData_(row);
const logRow = UCR_findRowByWeekId_(logSheet, weekId);
let docUrl = "";
let publishStatus = "尚未發布";
let lastUpdated = data.updatedAt || "";
if (logRow > 0) {
const log = logSheet.getRange(logRow, 1, 1, 8).getValues()[0];
docUrl = log[5];
publishStatus = UCR_buildPublishSummary_(log[1], log[2], log[3], log[4]);
lastUpdated = log[7] || lastUpdated;
}
rows.push([
weekId,
data.dateRange,
UCR_makeWeeklySummary_(data),
docUrl ? "開啟 Docs" : "尚未建立",
data.materialsFolderUrl ? "開啟素材資料夾" : "尚未建立",
platformUrl ? "開啟課程平台" : "尚未設定",
publishStatus,
lastUpdated,
]);
links.push({ docUrl: docUrl, folderUrl: data.materialsFolderUrl, platformUrl: platformUrl });
});
if (rows.length > 0) {
indexSheet.getRange(2, 1, rows.length, 8).setValues(rows);
links.forEach(function (item, i) {
const row = i + 2;
UCR_setLinkCell_(indexSheet, row, 4, "開啟 Docs", item.docUrl, "尚未建立");
UCR_setLinkCell_(indexSheet, row, 5, "開啟素材資料夾", item.folderUrl, "尚未建立");
UCR_setLinkCell_(indexSheet, row, 6, "開啟課程平台", item.platformUrl, "尚未設定");
});
}
}
function UCR_refreshDashboardForWeek_(weekId) {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const dashboard = ss.getSheetByName(UCR_CONFIG.SHEETS.DASHBOARD);
if (!dashboard) return;
UCR_updateDashboardStudentsSummary_(dashboard);
const data = weekId ? UCR_getWeeklyDataByWeekId_(weekId) : null;
const settings = UCR_getSettings_();
const platformUrl = settings["課程平台網址"] || "";
const publishInfo = UCR_getPublishInfo_(weekId);
const hasCopies = UCR_hasThreeChannelCopies_(weekId);
if (!data) {
dashboard.getRange("B4:B5").clearContent();
UCR_setStatusCell_(dashboard, "E4", UCR_CONFIG.STATUS.NOT_CREATED);
UCR_updateDashboardFlowSteps_(dashboard, null, publishInfo, hasCopies);
return;
}
dashboard.getRange("B4").setValue(data.weekId);
dashboard.getRange("B5").setValue(data.dateRange);
UCR_setStatusCell_(dashboard, "E4", UCR_getOverallStatus_(publishInfo, hasCopies));
UCR_setLinkCell_(dashboard, 5, 5, "開啟 Google Docs", publishInfo.docUrl, "尚未建立");
UCR_setLinkCell_(dashboard, 6, 2, "開啟課程平台", platformUrl, "尚未設定");
UCR_setLinkCell_(dashboard, 6, 5, "開啟素材資料夾", data.materialsFolderUrl, "尚未建立");
dashboard.getRange("B9").setValue(UCR_CONFIG.STATUS.DRAFT);
dashboard.getRange("D9").setValue(hasCopies ? UCR_CONFIG.STATUS.COPY_READY : "⚪ 尚未產生");
dashboard.getRange("F9").setValue(publishInfo.docUrl ? UCR_CONFIG.STATUS.DOC_READY : "⚪ 尚未產生");
dashboard.getRange("B10").setValue(UCR_isPublished_(publishInfo.platformStatus) && UCR_isPublished_(publishInfo.emailStatus) && UCR_isPublished_(publishInfo.lmsStatus) ? UCR_CONFIG.STATUS.COMPLETED : "⚪ 尚未完成");
dashboard.getRange("D10").setValue(UCR_isPublished_(publishInfo.collaborationStatus) ? UCR_CONFIG.STATUS.COLLAB_READY : "⚪ 尚未發布");
dashboard.getRange("B13").setValue(publishInfo.platformStatus || "尚未發布");
dashboard.getRange("B14").setValue(publishInfo.emailStatus || "尚未發布");
dashboard.getRange("B15").setValue(publishInfo.lmsStatus || "尚未發布");
dashboard.getRange("B16").setValue(publishInfo.collaborationStatus || "尚未發布");
UCR_updateDashboardFlowSteps_(dashboard, data, publishInfo, hasCopies);
}
function UCR_updateDashboardFlowSteps_(dashboard, data, publishInfo, hasCopies) {
const hasWeek = Boolean(data);
const fieldsDone = hasWeek && UCR_getIncompleteFieldWarnings_(data).length === 0;
const docAndFolderDone = hasWeek && Boolean(publishInfo.docUrl) && Boolean(data.materialsFolderUrl);
const threeDone = UCR_isPublished_(publishInfo.platformStatus) && UCR_isPublished_(publishInfo.emailStatus) && UCR_isPublished_(publishInfo.lmsStatus);
const collabDone = UCR_isPublished_(publishInfo.collaborationStatus);
dashboard.getRange(19, 3, 6, 1).setValues([hasWeek, fieldsDone, hasCopies, docAndFolderDone, threeDone, collabDone].map(function (done) {
return [done ? "✅ 已完成" : "⚪ 待處理"];
}));
dashboard.getRange("E19").setValue(hasWeek ? UCR_getNextActionText_(data, publishInfo, hasCopies) : "請先建立本週課程重點範本");
}
function UCR_getNextActionText_(data, publishInfo, hasCopies) {
if (!data) return "請先建立本週課程重點範本。";
if (UCR_getIncompleteFieldWarnings_(data).length > 0) return "請補齊 01_課程週次資料中仍是提示文字的欄位。";
if (!hasCopies) return "請產生三端內容。";
if (!publishInfo.docUrl) return "請建立 Google Docs 課程重點。";
if (!data.materialsFolderUrl) return "請建立本週課程素材資料夾。";
if (!UCR_isPublished_(publishInfo.platformStatus)) return "請複製課程平台素材並標記課程平台已發布。";
if (!UCR_isPublished_(publishInfo.emailStatus)) return "請複製 Email 文案並標記 Email 已發布。";
if (!UCR_isPublished_(publishInfo.lmsStatus)) return "請複製 LMS/社群文案並標記 LMS/社群已發布。";
if (!UCR_isPublished_(publishInfo.collaborationStatus)) return "可發布到協作平台，同步課程助教或共備團隊。";
return "本週課程內容重點流程已完成。";
}
function UCR_getOverallStatus_(publishInfo, hasCopies) {
if (UCR_isPublished_(publishInfo.platformStatus) && UCR_isPublished_(publishInfo.emailStatus) && UCR_isPublished_(publishInfo.lmsStatus)) return UCR_CONFIG.STATUS.COMPLETED;
if (UCR_isPublished_(publishInfo.collaborationStatus)) return UCR_CONFIG.STATUS.COLLAB_READY;
if (publishInfo.docUrl) return UCR_CONFIG.STATUS.DOC_READY;
if (hasCopies) return UCR_CONFIG.STATUS.COPY_READY;
return UCR_CONFIG.STATUS.DRAFT;
}
function UCR_getThreeChannelCopiesForWeek_(weekId) {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName(UCR_CONFIG.SHEETS.COPY_OUTPUT);
if (!sheet) return { platform: "", email: "", lms: "" };
const row = UCR_findRowByWeekId_(sheet, weekId);
if (row <= 0) return { platform: "", email: "", lms: "" };
const values = sheet.getRange(row, 1, 1, 6).getValues()[0];
return { platform: values[1] || "", email: values[2] || "", lms: values[3] || "" };
}
function UCR_getPublishInfo_(weekId) {
const empty = { platformStatus: "尚未發布", emailStatus: "尚未發布", lmsStatus: "尚未發布", collaborationStatus: "尚未發布", docUrl: "" };
if (!weekId) return empty;
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName(UCR_CONFIG.SHEETS.PUBLISH_LOG);
if (!sheet) return empty;
const row = UCR_findRowByWeekId_(sheet, weekId);
if (row <= 0) return empty;
const values = sheet.getRange(row, 1, 1, 8).getValues()[0];
return {
platformStatus: values[1] || "尚未發布",
emailStatus: values[2] || "尚未發布",
lmsStatus: values[3] || "尚未發布",
collaborationStatus: values[4] || "尚未發布",
docUrl: values[5] || "",
};
}
function UCR_hasThreeChannelCopies_(weekId) {
const copies = UCR_getThreeChannelCopiesForWeek_(weekId);
return Boolean(copies.platform || copies.email || copies.lms);
}
function UCR_refreshStudentsSummaryOnDashboard_() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const dashboard = ss.getSheetByName(UCR_CONFIG.SHEETS.DASHBOARD);
if (!dashboard) return;
UCR_updateDashboardStudentsSummary_(dashboard);
}
function UCR_updateDashboardStudentsSummary_(dashboard) {
const summary = UCR_getStudentsSummary_();
dashboard.getRange("E14").setValue(summary.total);
dashboard.getRange("E15").setValue(summary.subscribed);
dashboard.getRange("E16").setValue(summary.validEmail);
}
function UCR_getActiveWeekId_() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const dashboard = ss.getSheetByName(UCR_CONFIG.SHEETS.DASHBOARD);
if (!dashboard) return "";
return dashboard.getRange("B4").getValue();
}
function UCR_getLatestWeekId_() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName(UCR_CONFIG.SHEETS.WEEKLY_DATA);
if (!sheet || sheet.getLastRow() < 2) return "";
return sheet.getRange(sheet.getLastRow(), 1).getValue();
}
function UCR_getWeeklyDataByWeekId_(weekId) {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName(UCR_CONFIG.SHEETS.WEEKLY_DATA);
if (!sheet) return null;
const row = UCR_findRowByWeekId_(sheet, weekId);
if (row <= 0) return null;
return UCR_rowToWeeklyData_(sheet.getRange(row, 1, 1, 11).getValues()[0]);
}
function UCR_rowToWeeklyData_(values) {
return {
weekId: values[0],
dateRange: values[1],
topic: values[2],
concepts: values[3],
activities: values[4],
assignments: values[5],
nextPreview: values[6],
resources: values[7],
materialsFolderUrl: values[8],
createdAt: values[9],
updatedAt: values[10],
};
}
function UCR_getSettings_() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName(UCR_CONFIG.SHEETS.SETTINGS);
const settings = {};
if (!sheet || sheet.getLastRow() < 2) return settings;
sheet.getRange(2, 1, sheet.getLastRow() - 1, 2).getValues().forEach(function (row) {
if (row[0]) settings[row[0]] = row[1];
});
return settings;
}
function UCR_findSettingsRow_(settingName) {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName(UCR_CONFIG.SHEETS.SETTINGS);
if (!sheet) return -1;
const values = sheet.getRange(1, 1, sheet.getLastRow(), 1).getValues();
for (let i = 0; i < values.length; i++) {
if (values[i][0] === settingName) return i + 1;
}
return -1;
}
function UCR_findRowByWeekId_(sheet, weekId) {
if (!sheet || !weekId || sheet.getLastRow() < 2) return -1;
const values = sheet.getRange(2, 1, sheet.getLastRow() - 1, 1).getValues();
for (let i = 0; i < values.length; i++) {
if (values[i][0] === weekId) return i + 2;
}
return -1;
}
function UCR_findStudentRowById_(sheet, studentId) {
if (!sheet || !studentId || sheet.getLastRow() < 2) return -1;
const values = sheet.getRange(2, 2, sheet.getLastRow() - 1, 1).getValues();
for (let i = 0; i < values.length; i++) {
if (String(values[i][0] || "").trim() === studentId) return i + 2;
}
return -1;
}
function UCR_findStudentRowsByEmail_(sheet, email) {
if (!sheet || !email || sheet.getLastRow() < 2) return [];
const normalized = String(email || "").trim().toLowerCase();
const values = sheet.getRange(2, 4, sheet.getLastRow() - 1, 1).getValues();
const rows = [];
for (let i = 0; i < values.length; i++) {
if (String(values[i][0] || "").trim().toLowerCase() === normalized) rows.push(i + 2);
}
return rows;
}
function UCR_getIncompleteFieldWarnings_(data) {
const fields = [
["課程單元主題", "topic", data.topic],
["本週核心概念", "concepts", data.concepts],
["課堂活動與討論", "activities", data.activities],
["指定閱讀與作業", "assignments", data.assignments],
["下週預告", "nextPreview", data.nextPreview],
["補充資源", "resources", data.resources],
];
return fields.filter(function (field) {
return UCR_isPlaceholderOrBlank_(field[1], field[2]);
}).map(function (field) {
return field[0];
});
}
function UCR_isPlaceholderOrBlank_(key, value) {
const text = String(value || "").trim();
const placeholder = String(UCR_CONFIG.PLACEHOLDERS[key] || "").trim();
return text === "" || text === placeholder;
}
function UCR_makeWeeklySummary_(data) {
const source = [
UCR_stripPlaceholder_(data.topic, "topic"),
UCR_stripPlaceholder_(data.concepts, "concepts"),
UCR_stripPlaceholder_(data.assignments, "assignments"),
].filter(function (text) {
return text !== "";
}).join(" / ");
return UCR_truncateText_(source || "尚未填寫摘要", 180);
}
function UCR_buildPublishSummary_(platformStatus, emailStatus, lmsStatus, collaborationStatus) {
return [
"課程平台：" + (platformStatus || "尚未發布"),
"Email：" + (emailStatus || "尚未發布"),
"LMS/社群：" + (lmsStatus || "尚未發布"),
"協作：" + (collaborationStatus || "尚未發布"),
].join("\n");
}
function UCR_buildStudentWeeklyEmailBody_(emailCopy, courseName, instructor, platformUrl) {
const body = UCR_stripEmailSubjectLine_(emailCopy);
return [
body,
"",
"——",
"課程名稱：" + (courseName || "課程"),
"授課教師：" + (instructor || "授課教師"),
platformUrl ? "課程平台：" + platformUrl : "",
].filter(function (line) {
return line !== "";
}).join("\n");
}
function UCR_stripEmailSubjectLine_(emailCopy) {
const lines = String(emailCopy || "").split("\n");
if (lines.length > 0 && /^主旨[:：]/.test(String(lines[0] || "").trim())) {
return lines.slice(1).join("\n").replace(/^\s+/, "");
}
return String(emailCopy || "").trim();
}
function UCR_getMailSenderAddress_() {
const activeEmail = Session.getActiveUser().getEmail();
if (activeEmail) return activeEmail;
const effectiveEmail = Session.getEffectiveUser().getEmail();
return effectiveEmail || "";
}
function UCR_getOrCreateSheet_(ss, sheetName) {
let sheet = ss.getSheetByName(sheetName);
if (!sheet) sheet = ss.insertSheet(sheetName);
return sheet;
}
function UCR_styleHeader_(sheet, colCount) {
sheet.getRange(1, 1, 1, colCount).setFontWeight("bold").setBackground("#d9eaf7").setHorizontalAlignment("center");
}
function UCR_setStatusCell_(sheet, a1Notation, status) {
const range = sheet.getRange(a1Notation);
range.setValue(status).setFontWeight("bold").setHorizontalAlignment("center");
const colorMap = {};
colorMap[UCR_CONFIG.STATUS.NOT_CREATED] = "#f3f4f6";
colorMap[UCR_CONFIG.STATUS.DRAFT] = "#fff7d6";
colorMap[UCR_CONFIG.STATUS.COPY_READY] = "#dcfce7";
colorMap[UCR_CONFIG.STATUS.DOC_READY] = "#dbeafe";
colorMap[UCR_CONFIG.STATUS.COLLAB_READY] = "#ede9fe";
colorMap[UCR_CONFIG.STATUS.COMPLETED] = "#d1fae5";
range.setBackground(colorMap[status] || "#f3f4f6");
}
function UCR_setLinkCell_(sheet, row, col, label, url, emptyText) {
const range = sheet.getRange(row, col);
range.clearContent();
if (!url) {
range.setValue(emptyText || "尚未設定").setFontColor("#6b7280").setFontLine("none");
return;
}
const richText = SpreadsheetApp.newRichTextValue().setText(label).setLinkUrl(url).build();
range.setRichTextValue(richText).setFontColor("#1155cc").setFontLine("underline");
}
function UCR_setParagraphTextColor_(paragraph, color) {
paragraph.editAsText().setForegroundColor(color);
return paragraph;
}
function UCR_normalizeText_(text) {
if (!text) return "尚未填寫。";
return String(text).trim();
}
function UCR_compactText_(text) {
if (!text) return "尚未填寫。";
return String(text).split("\n").map(function (line) {
return line.trim();
}).filter(function (line) {
return line !== "";
}).join("\n");
}
function UCR_stripPlaceholder_(text, key) {
if (UCR_isPlaceholderOrBlank_(key, text)) return "";
return UCR_compactText_(text);
}
function UCR_truncateText_(text, maxLength) {
const normalized = String(text || "").replace(/\s+/g, " ").trim();
if (normalized.length <= maxLength) return normalized;
return normalized.slice(0, maxLength - 1) + "…";
}
function UCR_escapeHtml_(value) {
return String(value || "")
.replace(/&/g, "&")
.replace(/</g, "<")
.replace(/>/g, ">")
.replace(/"/g, """)
.replace(/'/g, "'");
}
function UCR_isPublished_(status) {
const text = String(status || "");
return text.indexOf("已發布") === 0 || text.indexOf("已寄送") === 0;
}
function UCR_isValidEmail_(email) {
return /^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(String(email || "").trim());
}
function UCR_alert_(message) {
SpreadsheetApp.getUi().alert(message);
}
function UCR_confirm_(title, message) {
const ui = SpreadsheetApp.getUi();
return ui.alert(title, message, ui.ButtonSet.YES_NO) === ui.Button.YES;
}
function UCR_runAction_(actionName, callback) {
try {
UCR_ensureStructure_(SpreadsheetApp.getActiveSpreadsheet());
callback();
} catch (e) {
Logger.log(e && e.stack ? e.stack : e);
UCR_alert_(actionName + "失敗。\n\n請確認已完成初始化，且已授權 Drive / Docs / Spreadsheet 權限。\n\n錯誤訊息：" + (e && e.message ? e.message : e));
}
}
提醒：請完整複製，不要只複製部分函式。若 Apps Script 要求授權，請依畫面完成授權流程。發布前仍建議由老師檢查內容與學生個資。
版本一致性提醒
本頁內嵌的 GAS 程式碼應與專案中的 .gs 原始檔保持一致。若日後修改 .gs，請同步更新此 HTML 程式碼頁，避免研習現場使用到舊版本。