又到期末 #學習扶助 開班調查的時候了，
上次是學期初，看到教學組長手動完成套印後，
感覺應該可以有更方便的方法才對。
這才開始著手 #Google試算表 結合 #GAS 將資料合併到 #Google文件。
由於 👉 上次 是事後才開始處理線上合併列印的問題，
少了學習扶助資料整理的部分，所以這次教學組用起來還是卡卡的。
原因是目前學習扶助有 國、英、數 三種課程，
有些學生可能只有其中一科、二科，甚至三科都符合。
雖然課程全部免費，可能家裡因素也不是每位學生每科都會留下來參加！
為了學生權益，教學組就必須將每科分開來個別調查。
以我們學校來說，這次就要發 150幾份的調查表，
加上還要校對哪個科目學生需要，哪個學生不用，
說實話，這工作量對教學組來說真的也是一大負擔。
這次加入資料整理再加上可以選擇要列印的年級，
感覺應該更完善.......嗯.....應該是.......吧！
我們學校只能等明年8月學習扶助開班列印調查表時，
才會知道修改後是否會更順暢！
如果有使用的夥伴可以底下留言比較一下使用前和使用後的感想！
如果有需要改進的地方，也別客氣歡迎底下留言！
記得修改成自己的文件 ID
1.點選「使用範本」
2.修改上課時間
3.貼上學習扶助網站匯出個案資料
4.點選「自訂選單」-「修改範本文件」
5.套印範本確定無誤，接下來只要依序執行即可
5-1.1️⃣整理學生資料 - 程式會自動產生整理後的工作表
點選 - 產生的開班調查工作表 - 點選更改科目名稱
5-3.如果一次列印因為資料太多，系統會出現錯誤訊息，所以 3️⃣ 改列印各別的年級
5-4.點選要列印年級的套印資料工作表 - 4️⃣合併列印資料(這邊要等一下，需要一點點時間)
5-5.🗑️刪除合併內容以利再次套印
點選確定後，程式會清套印文件，以利套印其他年級資料！
// --- 設定全域變數 ---
var spreadsheetId = '試算表ID'; //輸入試算表ID
var templateDocumentId = '套印範本文件ID'; //輸入套印範本文件ID
var targetDocumentId = '目標空白文件ID'; //輸入空白文件ID
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('🚀自訂選單')
.addItem('📄修改範本文件', 'openTemplateDocumentLink')
.addSeparator()
.addItem('1️⃣整理學生資料', 'DataOrganization')
.addItem('2️⃣更改科目名稱', 'ReplaceColumnE')
.addItem('3️⃣輸入要列印的年級', 'PrintGrade')
.addItem('4️⃣合併套印資料', 'mergeDataToPages')
.addItem('🗑️刪除合併內容以利再次套印', 'clearTargetDocument')
.addToUi();
}
function ReplaceColumnE() {
// 開啟試算表
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
// 取得 E 欄的資料
var sheet = spreadsheet.getActiveSheet();
var range = sheet.getRange("E:E");
var values = range.getValues();
// 迴圈處理 E 欄的每個儲存格
for (var i = 0; i < values.length; i++) {
var cellValue = values[i][0];
// 如果儲存格不是空白
if (cellValue) {
// 取代科目名稱
cellValue = cellValue.replace(/[23456]X/g, ""); //移除 2X, 3X 等
cellValue = cellValue.replace(/國/g, "國語");
cellValue = cellValue.replace(/數/g, "數學");
cellValue = cellValue.replace(/英/g, "英語");
// 將取代後的文字寫回儲存格
sheet.getRange(i + 1, 5).setValue(cellValue);
}
}
}
function DataOrganization() {
// 取得目前試算表
var ss = SpreadsheetApp.getActiveSpreadsheet();
// 取得目前活動的工作表作為來源工作表
var sourceSheet = ss.getActiveSheet();
// 取得今天的日期並格式化為 YYYYMMDD
var today = new Date();
var year = today.getFullYear();
var month = ('0' + (today.getMonth() + 1)).slice(-2);
var day = ('0' + today.getDate()).slice(-2);
var formattedDate = year + month + day;
// 取得目前試算表的工作表數量
var sheetCount = ss.getSheets().length;
// 設定目標工作表名稱，加上年份和序號
var targetSheetName = year + '開班調查' + sheetCount;
// // 取得現有工作表數量
// var sheetCount = ss.getNumSheets();
// // 設定目標工作表名稱，加上序號
// var targetSheetName = '資料整理_' + sheetCount;
// 取得目標工作表，如果不存在則建立新的工作表
var targetSheet = ss.getSheetByName(targetSheetName);
if (targetSheet == null) {
targetSheet = ss.insertSheet(targetSheetName);
// 在 A1:F1 設定標題
targetSheet.getRange('A1:F1').setValues([['姓名', '年', '班', '座號', '班別', '日期']]);
}
// 取得來源工作表的最後一列
var lastRow = sourceSheet.getLastRow();
// 設定起始寫入列號為 2
var startRow = 2;
// 迴圈處理每一列資料，從第二列開始 (排除標題列)
for (var i = 2; i <= lastRow; i++) {
// 取得 A2:D2 的資料
var rowData = sourceSheet.getRange(i, 1, 1, 4).getValues()[0];
// 檢查 H 欄位是否有資料
if (sourceSheet.getRange('H' + i).getValue()) {
// 合併 A2:D2 資料和 H2, K2 欄位資料
var combinedData = [sourceSheet.getRange('H' + i).getValue(), sourceSheet.getRange('K' + i).getValue()];
targetSheet.getRange(startRow, 1, 1, rowData.length + combinedData.length).setValues([rowData.concat(combinedData)]);
startRow++;
}
// 檢查 I 欄位是否有資料
if (sourceSheet.getRange('I' + i).getValue()) {
// 合併 A2:D2 資料和 I2, L2 欄位資料
var combinedData = [sourceSheet.getRange('I' + i).getValue(), sourceSheet.getRange('L' + i).getValue()];
targetSheet.getRange(startRow, 1, 1, rowData.length + combinedData.length).setValues([rowData.concat(combinedData)]);
startRow++;
}
// 檢查 J 欄位是否有資料
if (sourceSheet.getRange('J' + i).getValue()) {
// 合併 A2:D2 資料和 J2, M2 欄位資料
var combinedData = [sourceSheet.getRange('J' + i).getValue(), sourceSheet.getRange('M' + i).getValue()];
targetSheet.getRange(startRow, 1, 1, rowData.length + combinedData.length).setValues([rowData.concat(combinedData)]);
startRow++;
}
}
}
function PrintGrade() {
// 取得目前試算表
var ss = SpreadsheetApp.getActiveSpreadsheet();
// 取得來源工作表 (假設為目前活動的工作表)
var sourceSheet = ss.getActiveSheet();
// 讓使用者輸入 B 欄的數字
var ui = SpreadsheetApp.getUi();
var response = ui.prompt('請輸入要列印的年級數字：');
// 檢查使用者是否有輸入數字
if (response.getSelectedButton() == ui.Button.OK) {
var targetNumber = Number(response.getResponseText());
// 設定目標工作表名稱
var targetSheetName = targetNumber + " 年級套印資料";
// 取得目標工作表，如果不存在則建立新的工作表
var targetSheet = ss.getSheetByName(targetSheetName);
if (targetSheet == null) {
targetSheet = ss.insertSheet(targetSheetName);
}
// 複製標題列
var sourceHeaders = sourceSheet.getRange(1, 1, 1, sourceSheet.getLastColumn());
var targetHeaders = targetSheet.getRange(1, 1, 1, sourceSheet.getLastColumn());
sourceHeaders.copyTo(targetHeaders);
// 取得來源工作表的最後一列
var lastRow = sourceSheet.getLastRow();
// 設定起始寫入列號為 2 (從第二列開始寫入)
var targetRow = 2;
// 迴圈處理每一列資料
for (var i = 1; i <= lastRow; i++) {
// 取得 B 欄的資料
var bCellValue = sourceSheet.getRange(i, 2).getValue(); // 2 代表 B 欄
// 檢查 B 欄位是否為指定的數字
if (bCellValue === targetNumber) {
// 複製整列資料到目標工作表
var sourceRow = sourceSheet.getRange(i, 1, 1, sourceSheet.getLastColumn());
var targetRange = targetSheet.getRange(targetRow, 1, 1, sourceSheet.getLastColumn());
sourceRow.copyTo(targetRange);
targetRow++;
}
}
}
}
function mergeDataToPages() {
// --- 讀取當前活動試算表所有資料 ---
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet(); // 取得當前活動的工作表
var data = sheet.getRange('A2:Z' + sheet.getLastRow()).getValues(); // 讀取 A 到 Z 欄位
var placeholders = sheet.getRange('A1:Z1').getValues()[0]; // 讀取第一列作為 placeholders
// --- 錯誤處理：檢查檔案是否存在 ---
try {
SpreadsheetApp.openById(spreadsheetId);
DriveApp.getFileById(templateDocumentId);
DocumentApp.openById(targetDocumentId);
} catch (e) {
SpreadsheetApp.getActiveSpreadsheet().toast('檔案不存在，請確認 ID 是否正確', '錯誤');
return;
}
// --- 取得目標文件 ---
var targetDoc = DocumentApp.openById(targetDocumentId);
var targetBody = targetDoc.getBody();
// --- 取得 Google 文件範本檔案 ---
var templateFile = DriveApp.getFileById(templateDocumentId);
var templateDoc = DocumentApp.openById(templateFile.getId());
var templateBody = templateDoc.getBody();
// --- 移除範本文件最上方的空白段落 ---
var firstElement = templateBody.getChild(0);
if (firstElement.getType() === DocumentApp.ElementType.PARAGRAPH && firstElement.getText().trim() === "") {
templateBody.removeChild(firstElement);
}
// --- 顯示合併中視窗 ---
var htmlOutput = HtmlService.createHtmlOutput('<p>資料合併中，請稍候...</p>');
var ui = SpreadsheetApp.getUi();
var dialog = ui.showModelessDialog(htmlOutput, '合併中');
// --- 顯示合併進度 ---
SpreadsheetApp.getActiveSpreadsheet().toast('開始合併...', '合併進度');
// --- 定義 placeholder 陣列 ---
var placeholders = ['{{姓名}}','{{年}}', '{{班}}', '{{座號}}', '{{班別}}', '{{時間}}']; // 依照試算表欄位順序設定
// --- 批次處理資料 ---
var batchSize = 100; // 設定每個批次處理的資料筆數
for (var i = 0; i < data.length; i += batchSize) {
var dataBatch = data.slice(i, i + batchSize); // 取得目前的資料批次
// --- 處理目前的資料批次 ---
for (var j = 0; j < dataBatch.length; j++) {
// --- 複製範本內容到目標文件 ---
var templateElements = templateBody.getNumChildren();
for (var k = 0; k < templateElements; k++) {
var element = templateBody.getChild(k).copy();
// --- 立即將資料複製到目標文件 ---
if (element.getType() === DocumentApp.ElementType.TABLE) {
var table = element;
for (var r = 0; r < table.getNumRows(); r++) {
for (var c = 0; c < table.getRow(r).getNumCells(); c++) {
var cell = table.getRow(r).getCell(c);
for (var l = 0; l < placeholders.length; l++) {
cell.replaceText(placeholders[l], dataBatch[j][l]);
}
}
}
targetBody.appendTable(table);
} else if (element.getType() === DocumentApp.ElementType.PARAGRAPH) {
var paragraph = element;
for (var l = 0; l < placeholders.length; l++) {
paragraph.replaceText(placeholders[l], dataBatch[j][l]);
}
targetBody.appendParagraph(paragraph);
} else if (element.getType() === DocumentApp.ElementType.LIST_ITEM) {
var listItem = element;
for (var l = 0; l < placeholders.length; l++) {
listItem.replaceText(placeholders[l], dataBatch[j][l]);
}
targetBody.appendListItem(listItem);
} else {
Logger.log('未處理的元素類型：' + element.getType());
}
}
// --- 在每個資料列的最後一筆資料處理完畢後插入分頁符號 ---
if (j < dataBatch.length - 1) {
targetBody.appendPageBreak();
}
}
// --- 更新合併進度 ---
SpreadsheetApp.getActiveSpreadsheet().toast('已合併 ' + (i + dataBatch.length) + '/' + data.length, '合併進度');
}
// --- 移除所有空白頁 ---
var numChildren = targetBody.getNumChildren();
for (var i = numChildren - 1; i >= 0; i--) {
var child = targetBody.getChild(i);
if (child.getType() === DocumentApp.ElementType.PAGE_BREAK) {
var nextChild = targetBody.getChild(i - 1);
if (nextChild && nextChild.getType() === DocumentApp.ElementType.PAGE_BREAK) {
targetBody.removeChild(child);
}
}
}
// --- 合併完成訊息 ---
SpreadsheetApp.getActiveSpreadsheet().toast('合併完成！', '合併進度');
// --- 關閉「合併中」視窗 ---
ui.alert('合併完成');
// --- 開啟目標檔案的連結 ---
var targetFileLink = `https://docs.google.com/document/d/${targetDocumentId}/edit`;
// --- 建立 HTML 輸出並開啟目標檔案視窗 ---
var htmlOutput = HtmlService.createHtmlOutput(`
<script>
window.open('${targetFileLink}', '_blank');
google.script.host.close();
</script>
`);
// --- 顯示開啟目標檔案的對話方塊 ---
ui.showModalDialog(htmlOutput, '開啟目標檔案');
}
function openTemplateDocumentLink() {
var templateFileLink = `https://docs.google.com/document/d/${templateDocumentId}/edit`;
var htmlOutput = HtmlService.createHtmlOutput(`
<script>
window.open('${templateFileLink}', '_blank');
google.script.host.close();
</script>
`);
SpreadsheetApp.getUi().showModalDialog(htmlOutput, '開啟範本檔案');
}
function clearTargetDocument() {
// --- 錯誤處理：檢查檔案是否存在 ---
try {
DocumentApp.openById(targetDocumentId); // 修正此行
} catch (e) {
SpreadsheetApp.getActiveSpreadsheet().toast('檔案不存在，請確認 ID 是否正確', '錯誤');
return;
}
var targetDoc = DocumentApp.openById(targetDocumentId);
var targetBody = targetDoc.getBody();
targetBody.clear();
SpreadsheetApp.getActiveSpreadsheet().toast('目標文件已清空！', '完成');
Browser.msgBox('目標文件已清空！');
}
~~快來試試看吧~~
沒有留言:
張貼留言
歡迎大家一起留言討論！