松禧老師教學日誌: 「Google Apps Script」校慶運動會獎狀列印 + 公告標題貼紙

🎯特別感謝：頂番國小李清展老師讓線上列印夢想成真

💡 簡單的比喻

想像你在玩樂高積木：

1. 匯入與合併：就像是你叫大家把各自的樂高桶子（資料）倒在你桌上，然後機器人幫你把所有相同顏色的積木挑出來，組合成一個超大的城堡（總表）。

2. 生成獎狀：就像是有一個印章（範本），機器人拿著名單，自動幫你在每一張紙上蓋章，並在旁邊寫上不同人的名字。

3. 標籤列印：就像是你要把不同形狀的積木塞進一個有很多小格子的收納盒（標籤紙）。機器人會先拿尺把每個格子量得精準無比，然後把積木一個一個塞進去，完全不會凸出來。

這段程式碼就是把這些繁瑣的「手工藝」變成了「自動化工廠」！

這段程式碼就像是一位**「全能型的學校行政總管」**。

試想一下，如果你是學校的行政工讀生，老師丟給你三個超煩人的任務：

1. 把十個班級繳交的 Excel 檔全部複製貼上變成一張大總表。

2. 依照名單，幫 500 個學生做獎狀。

3. 要寄信給家長，需要印 500 張地址標籤貼紙，而且格式要對得很齊。

這段程式碼就是幫你把這三件「苦力活」全部自動化。只要點幾個按鈕，它就會幫你在幾秒鐘內做完。以下我們把它拆解成四個主要部門來介紹：

1. 指揮中心（自訂選單與設定）

這位總管很貼心，它不會讓你去寫程式碼。

• 專屬按鈕：當你打開這個 Google 試算表時，它會自動在上面的選單列增加一個新選項叫「🎯資料匯整」。

• 功能列表：按下去後，你會看到「匯入資料」、「合併資料」、「生成獎狀」、「產生標籤」等功能，就像是用手機 App 一樣簡單。

• 規則書 (Setting)：它運作時會去查閱一個叫做 "Setting" 的分頁。這裡面寫著重要的資訊（例如：其他檔案的 ID 編號、獎狀範本長怎樣、標籤貼紙的長寬尺寸）。

2. 資料收集部（匯入與合併）

這是幫你處理「複製貼上」地獄的部門。

• 匯入資料：它會根據你在 Setting 頁面填寫的檔案 ID，自動跑到別的 Google 試算表檔案裡，把指定的工作表（分頁）全部複製過來這裡。

• 合併總表：

◦ 它會把所有分頁的資料「疊」在一起，變成一張新的總表（檔名會加上今天的日期）。

◦ 超級聰明：它知道第一列通常是標題（如：姓名、學號），所以在合併第二張表以後的資料時，它會自動把標題切掉，只保留內容，這樣你的總表就不會中間一直出現重複的標題。

◦ 自動清理：它還會順便幫你檢查，如果是完全空白的列，它就直接丟掉不合併，保持資料乾淨。

3. 獎狀印刷部（生成 Google 簡報）

這個部門負責把名單變成漂亮的獎狀。

• 找模具：它會先去讀取你指定的 Google 簡報範本（模具）。

• 填空題：它會看著名單，一列一列讀取資料。如果名單上有 100 個人，它就在簡報裡複製 100 張投影片。

• 自動填寫：接著，它會把表格裡的資料（第一格、第二格...），依序填進獎狀上的文字框裡。就像是它幫你拿著筆，一張一張把名字寫上去。

4. 標籤排版部（精準列印）

這是最需要數學頭腦的部門，專門用來印貼紙（例如地址標籤）。

• 精密計算：它會讀取 Setting 頁面裡你設定的毫米 (mm) 數值（邊界、貼紙寬度、間距）。

• 自動排版：

◦ 它會把試算表的格子寬度和高度，調整到跟你設定的一模一樣，確保印出來不會跑版。

◦ 它會幫你把資料組合好（例如：姓名 + 成績 + 名次），然後整齊地排列在格子裡。

◦ 如果一張紙印滿了（例如一頁只能印 10 張），它還會自動幫你加上分隔線或空白行，準備印下一頁。

🎯範本下載：

/**
 * ============================================================
 * Google Sheets 學校行政自動化整合系統 (最終完整版)
 * 功能：匯入資料、合併工作表、產生日期、製作獎狀、標籤排版
 * ============================================================
 */

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('🎯資料匯整')
    .addItem('🚀匯入資料 (讀取 Setting)', 'importSheetsFromMultipleSources')
    .addItem('🚀合併所有工作表資料', 'MergeSheets')
    .addSeparator()
    .addItem('📆產生日期 (於 F2)', 'createDate')
    .addItem('🥇生成獎狀 (當前工作表)', 'createSlidesFromSheet')
    .addItem('📂開啟獎狀連結', 'openSlides')
    .addSeparator()
    .addItem('🖨️ 產生標籤 (排除空白列)', 'tag_AddressPrint') 
    .addToUi();
}

// ==========================================
// 1. 日期與基礎工具
// ==========================================
function createDate() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getActiveSheet();
  
  // 防呆：禁止在 Setting 或 標籤列印頁面執行
  var sheetName = sheet.getName();
  if (sheetName === "Setting" || sheetName === "標籤列印") {
    SpreadsheetApp.getUi().alert('警告', '請勿在「' + sheetName + '」頁面執行此功能，請切換到資料工作表。', SpreadsheetApp.getUi().ButtonSet.OK);
    return;
  }
  sheet.getRange("F2").setValue(getTimemark());
}

function getTimemark() {
  var dateString = Utilities.formatDate(new Date(), "GMT+8", "yyyy-MM-dd");
  var parts = dateString.split('-');
  var year = parseInt(parts[0]);
  var month = parseInt(parts[1]); 
  var day = parseInt(parts[2]);
  return "中華民國 " + (year - 1911) + " 年 " + month + " 月 " + day + " 日";
}

// ==========================================
// 2. 匯入資料功能
// ==========================================
function importSheetsFromMultipleSources() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var settingSheet = ss.getSheetByName("Setting");

  if (!settingSheet) {
    SpreadsheetApp.getUi().alert('錯誤', '找不到名為 "Setting" 的工作表。', SpreadsheetApp.getUi().ButtonSet.OK);
    return;
  }

  // 1. 讀取目標試算表 ID
  var destinationSpreadsheetId = settingSheet.getRange("B2").getValue().toString().trim();
  // 如果 B2 空白，預設為當前檔案
  var destinationSpreadsheet = (destinationSpreadsheetId === "") ? ss : SpreadsheetApp.openById(destinationSpreadsheetId);

  // 2. 讀取來源試算表 IDs (從 B3 開始)
  var lastRow = settingSheet.getLastRow();
  var sourceSpreadsheetIds = [];
  
  if (lastRow >= 3) {
    var rangeData = settingSheet.getRange(3, 2, lastRow - 2, 1).getValues();
    for (var i = 0; i < rangeData.length; i++) {
      var id = rangeData[i][0].toString().trim();
      if (id !== "") sourceSpreadsheetIds.push(id);
    }
  }

  if (sourceSpreadsheetIds.length === 0) {
    SpreadsheetApp.getUi().alert('提示', 'Setting B3 之後沒有來源 ID。', SpreadsheetApp.getUi().ButtonSet.OK);
    return;
  }

  // 3. 執行匯入
  var successCount = 0;
  for (var s = 0; s < sourceSpreadsheetIds.length; s++) {
    var sourceId = sourceSpreadsheetIds[s];
    try {
      var sourceSpreadsheet = SpreadsheetApp.openById(sourceId);
      var sheets = sourceSpreadsheet.getSheets();
      
      for (var i = 0; i < sheets.length; i++) {
        var sheet = sheets[i];
        var sheetName = sheet.getName();

        // 如果目標不存在該工作表才匯入
        if (!destinationSpreadsheet.getSheetByName(sheetName)) {
          sheet.copyTo(destinationSpreadsheet);
          Logger.log("從來源 " + sourceId + " 匯入: " + sheetName);
          successCount++;
        }
      }
    } catch (e) {
      Logger.log("無法開啟來源 ID: " + sourceId + " - " + e.toString());
    }
  }
  SpreadsheetApp.getUi().alert('完成', '匯入作業結束。\n共匯入 ' + successCount + ' 個新工作表。', SpreadsheetApp.getUi().ButtonSet.OK);
}

// ==========================================
// 3. 合併所有工作表資料 (強力優化版：自動過濾空白)
// ==========================================
function MergeSheets() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var today = Utilities.formatDate(new Date(), "GMT+8", "yyyyMMdd");
  var newSheetName = today + "_合併資料";

  ss.toast("正在準備合併...", "系統提示");

  // 1. 刪除舊的合併表 (如果存在)
  var existingSheet = ss.getSheetByName(newSheetName);
  if (existingSheet) ss.deleteSheet(existingSheet);
  
  // 建立新表
  var newSheet = ss.insertSheet(newSheetName);
  var sheets = ss.getSheets();
  var allData = [];
  var hasHeader = false;
  var headerRow = [];

  // 2. 循環讀取所有工作表
  for (var i = 0; i < sheets.length; i++) {
    var sheet = sheets[i];
    var sheetName = sheet.getName();

    // 排除名單
    if (sheetName.includes("合併資料") || sheetName === "All" || sheetName === "Setting" || sheetName === "標籤列印") {
      continue;
    }

    // 取得資料範圍
    var lastRow = sheet.getLastRow();
    if (lastRow < 1) continue; // 全空工作表跳過
    
    // 讀取該表所有資料
    var values = sheet.getRange(1, 1, lastRow, sheet.getLastColumn()).getValues();

    // ★★★ 關鍵優化：在記憶體中就先過濾掉空白列 ★★★
    // 這裡我們把「完全空白」的列篩選掉
    var cleanValues = values.filter(function(row) {
      return row.join("").trim() !== "";
    });

    if (cleanValues.length === 0) continue;

    // 3. 處理標題與內容
    if (!hasHeader) {
      // 第一個被讀到的表，取第一列當標題
      headerRow = cleanValues[0];
      allData = allData.concat(cleanValues); // 包含標題的所有資料放入
      hasHeader = true;
    } else {
      // 後續的表
      if (cleanValues.length > 1) {
        // 檢查該表第一列是否為標題 (通常是)，如果是就去掉
        // 簡單判斷：如果第一列內容跟目前的 headerRow 很像，就當作標題去掉
        // 這裡直接假設第一列就是標題，予以移除 (slice(1))
        allData = allData.concat(cleanValues.slice(1));
      } else {
        // 如果該表只有一行且不是標題，則加入；如果是標題則忽略
        // 這裡保守處理：若長度為1且 header已存在，假設它是資料(除非完全等於標題)
        if (cleanValues[0].join("") !== headerRow.join("")) {
           allData = allData.concat(cleanValues);
        }
      }
    }
  }

  // 4. 一次性寫入資料
  if (allData.length > 0) {
    // 補齊欄位長度 (避免不同工作表欄數不一致導致報錯)
    var maxColumns = 0;
    for (var r = 0; r < allData.length; r++) {
      if (allData[r].length > maxColumns) maxColumns = allData[r].length;
    }
    // 補空字串
    for (var r = 0; r < allData.length; r++) {
      while (allData[r].length < maxColumns) allData[r].push(""); 
    }

    // 寫入
    newSheet.getRange(1, 1, allData.length, maxColumns).setValues(allData);
    newSheet.activate(); 
    
    // 顯示成功視窗 (這會強制結束「執行中」的轉圈圈)
    SpreadsheetApp.getUi().alert('成功', '合併完成！\n已自動過濾空白列，共匯整 ' + allData.length + ' 列資料。', SpreadsheetApp.getUi().ButtonSet.OK);
  } else {
    SpreadsheetApp.getUi().alert('提示', '沒有找到任何有效資料可以合併。', SpreadsheetApp.getUi().ButtonSet.OK);
  }
}

// ==========================================
// 4. 生成獎狀 (Google Slides)
// ==========================================
function getPresentationId() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Setting");
  if (!sheet) throw new Error("找不到 'Setting' 工作表");
  var id = sheet.getRange("B1").getValue();
  if (!id) throw new Error("請在 Setting B1 填入簡報 ID");
  return id.toString().trim();
}

function createSlidesFromSheet() {
  var PRESENTATION_ID;
  try { PRESENTATION_ID = getPresentationId(); } catch (e) { SpreadsheetApp.getUi().alert(e.message); return; }
  
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  
  // 防呆檢查
  if (sheet.getName() === "Setting" || sheet.getName() === "標籤列印") {
    SpreadsheetApp.getUi().alert('操作錯誤', '請切換到含有名單的資料工作表，再執行獎狀生成。', SpreadsheetApp.getUi().ButtonSet.OK);
    return;
  }
  
  var lastRow = sheet.getLastRow();
  if (lastRow < 2) {
    SpreadsheetApp.getUi().alert('錯誤', '目前工作表沒有足夠的資料。', SpreadsheetApp.getUi().ButtonSet.OK);
    return;
  }

  var data = sheet.getRange(2, 1, lastRow - 1, sheet.getLastColumn()).getValues();
  var presentation = SlidesApp.openById(PRESENTATION_ID);
  var slides = presentation.getSlides();
  var templateSlide = slides[0];
  var templateTextBoxes = getTextBoxes(templateSlide);
  
  // 移除舊的投影片 (保留模板)
  for (var i = slides.length - 1; i > 0; i--) slides[i].remove();

  var generatedCount = 0;
  for (var i = 0; i < data.length; i++) {
    var rowData = data[i];
    // 檢查該列是否有內容
    var hasContent = rowData.some(function(cell) { return cell && cell.toString().trim() !== ''; });
    
    if (hasContent) {
      var slide = presentation.appendSlide(templateSlide.getLayout());
      var slideTextBoxes = getTextBoxes(slide);
      var fillCount = Math.min(rowData.length, slideTextBoxes.length);
      
      for (var j = 0; j < fillCount; j++) {
        var textContent = (rowData[j] !== undefined) ? rowData[j].toString() : '';
        if (slideTextBoxes[j]) slideTextBoxes[j].setText(textContent);
      }
      generatedCount++;
    }
  }
  templateSlide.remove(); // 移除模板
  SpreadsheetApp.getUi().alert('完成', '已生成 ' + generatedCount + ' 張獎狀。', SpreadsheetApp.getUi().ButtonSet.OK);
}

function getTextBoxes(slide) {
  var textBoxes = [];
  var elements = slide.getPageElements();
  for (var i = 0; i < elements.length; i++) {
    if (elements[i].getPageElementType() == SlidesApp.PageElementType.SHAPE) {
      if (elements[i].asShape().getText()) textBoxes.push(elements[i].asShape().getText());
    }
  }
  return textBoxes;
}

function openSlides() {
  try {
    var id = getPresentationId();
    var html = HtmlService.createHtmlOutput('<script>window.open("https://docs.google.com/presentation/d/' + id + '/edit", "_blank");google.script.host.close();</script>');
    SpreadsheetApp.getUi().showModalDialog(html, '開啟中...');
  } catch (e) { SpreadsheetApp.getUi().alert(e.message); }
}

// ==========================================
// 5. 標籤排版列印 (優化版：自動過濾空白)
// ==========================================
function tag_AddressPrint() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var mainWs = ss.getActiveSheet(); 
  
  if (mainWs.getName() === "Setting" || mainWs.getName() === "標籤列印") {
    SpreadsheetApp.getUi().alert("⚠️ 請先切換到「資料來源」工作表（例如：合併資料），再點選執行。");
    return;
  }

  ss.toast("正在讀取並過濾資料...", "步驟 1/3");

  // 1. 取得並過濾資料
  var lastRow = mainWs.getLastRow();
  if (lastRow < 2) {
    SpreadsheetApp.getUi().alert("資料不足，無法列印。");
    return;
  }
  
  var rawData = mainWs.getRange(2, 1, lastRow - 1, mainWs.getLastColumn()).getValues();
  
  // 過濾掉全空的列
  var cleanData = rawData.filter(function(row) {
    return row.join("").trim() !== "";
  });

  if (cleanData.length === 0) {
    SpreadsheetApp.getUi().alert("沒有有效資料（全是空白列）。");
    return;
  }

  // 2. 重建「標籤列印」工作表
  var tagSheetName = "標籤列印";
  var oldSheet = ss.getSheetByName(tagSheetName);
  if (oldSheet) ss.deleteSheet(oldSheet);
  var tagSheet = ss.insertSheet(tagSheetName);

  // 3. 執行排版與填入
  tagTypesetting(tagSheet, cleanData.length);
  WRAPROWS_B(tagSheet, cleanData);
}

function tagTypesetting(tagPrint_ws, dataCount) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var tag_setting_ws = ss.getSheetByName("Setting");

  if (!tag_setting_ws) {
    SpreadsheetApp.getUi().alert("錯誤：找不到 'Setting' 工作表。");
    return;
  }

  ss.toast("正在設定標籤格式...", "步驟 2/3");

  var upper_boundary = tag_setting_ws.getRange("F3").getValue();
  var lower_boundary = tag_setting_ws.getRange("F4").getValue();
  var left_boundary = tag_setting_ws.getRange("F5").getValue();
  var right_boundary = tag_setting_ws.getRange("F6").getValue();
  var wrap_count = tag_setting_ws.getRange("E7").getValue();
  var page_rows = tag_setting_ws.getRange("F7").getValue();
  var tag_width = tag_setting_ws.getRange("F8").getValue();
  var tag_height = tag_setting_ws.getRange("F9").getValue();
  var col_spacing = tag_setting_ws.getRange("F10").getValue();
  var row_spacing = tag_setting_ws.getRange("F11").getValue();
  
  var allColNums = wrap_count + (wrap_count - 1) + 2; 
  var onepage_row_numbers = page_rows + (page_rows - 1) + 2; 
  var pages = Math.ceil(dataCount / (wrap_count * page_rows));
  var allRowNums = pages * onepage_row_numbers; 

  // 警告：如果頁數過多
  if (allRowNums > 4000) {
    SpreadsheetApp.getUi().alert("警告：資料量過大 (" + allRowNums + "列)，可能執行逾時。");
  }

  // 調整工作表大小
  var currentMaxRows = tagPrint_ws.getMaxRows();
  var currentMaxCols = tagPrint_ws.getMaxColumns();
  
  if (allRowNums > currentMaxRows) tagPrint_ws.insertRowsAfter(currentMaxRows, allRowNums - currentMaxRows);
  if (allColNums > currentMaxCols) tagPrint_ws.insertColumnsAfter(currentMaxCols, allColNums - currentMaxCols);
  if (currentMaxCols > allColNums) tagPrint_ws.deleteColumns(allColNums + 1, currentMaxCols - allColNums);

  // 設定欄寬
  for(var i = 1; i <= allColNums; i++) {
    if (i % 2 == 0) tagPrint_ws.setColumnWidth(i, tag_width);
    else tagPrint_ws.setColumnWidth(i, col_spacing);
  }
  tagPrint_ws.setColumnWidth(1, left_boundary);
  tagPrint_ws.setColumnWidth(allColNums, right_boundary);

  // 設定列高
  for (var i = 1; i <= allRowNums; i++){
    var mod = i % onepage_row_numbers;
    if (mod == 1) tagPrint_ws.setRowHeight(i, upper_boundary);
    else if (mod == 0) tagPrint_ws.setRowHeight(i, lower_boundary);
    else {
      if (mod % 2 == 0) tagPrint_ws.setRowHeight(i, tag_height);
      else tagPrint_ws.setRowHeight(i, row_spacing);
    }
  }
  
  // 設定樣式
  tagPrint_ws.getRange(1, 1, allRowNums, allColNums)
             .setFontSize(12)
             .setHorizontalAlignment('left')
             .setVerticalAlignment('middle')
             .setWrap(true);
}

function WRAPROWS_B(tagPrint_ws, cleanData) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var tag_setting_ws = ss.getSheetByName("Setting");
  
  ss.toast("正在寫入資料...", "步驟 3/3");

  var wrap_count = tag_setting_ws.getRange("E7").getValue();
  var page_rows = tag_setting_ws.getRange("F7").getValue();
  var pad_width = "";
  var insert_col = 1; 

  var array_row = [];
  
  for (var i = 0; i < cleanData.length; i++) {
    var row = cleanData[i];
    
    // ============================================
    // ★ 在此定義標籤內容 (0=A欄, 1=B欄...)
    // ============================================
    var content = row[0] + " " + row[1] + "\n" +
                  "項目：" + row[2] + "\n" +
                  "成績：" + row[3] + "  名次：第" + row[4] + "名";

    array_row.push(content);
    
    for (var j = 0; j < insert_col && !(((i + 1) % wrap_count) == 0); j++) {
       array_row.push(pad_width);
    }
  }

  // 補齊最後的空位
  const insert_col_numbers = (wrap_count - 1) * insert_col + wrap_count;
  const put_more_pad = insert_col_numbers - (array_row.length % insert_col_numbers);
  if (array_row.length % insert_col_numbers > 0) {
    for (var i = 1; i <= put_more_pad; i++) array_row.push(pad_width);
  }

  var array_wrapped = [];
  const padding_row = Array(insert_col_numbers).fill(pad_width);
  const boundary_row = Array(insert_col_numbers).fill(""); 
  var totalDataRows = array_row.length / insert_col_numbers;

  for (var i = 0; i < totalDataRows; i++) {
    array_wrapped.push(array_row.slice(i * insert_col_numbers, (i + 1) * insert_col_numbers));
    
    var currentRowInPage = i % page_rows;
    if (currentRowInPage < page_rows - 1) {
      array_wrapped.push(padding_row);
    } else {
      if (i < totalDataRows - 1) {
         array_wrapped.push(boundary_row); 
         array_wrapped.push(boundary_row); 
      }
    }
  }

  if (array_wrapped.length > 0) {
    var writeRows = array_wrapped.length;
    var maxRows = tagPrint_ws.getMaxRows();
    if (writeRows > maxRows) tagPrint_ws.insertRowsAfter(maxRows, writeRows - maxRows);
    tagPrint_ws.getRange(2, 2, writeRows, array_wrapped[0].length).setValues(array_wrapped);
  }

  ss.toast("標籤產生完成！", "完成");
  ss.setActiveSheet(tagPrint_ws);
}

松禧老師教學日誌

橫幅按鈕

2025年12月6日星期六

「Google Apps Script」校慶運動會獎狀列印 + 公告標題貼紙

沒有留言:

張貼留言

橫幅按鈕

2025年12月6日 星期六

「Google Apps Script」校慶運動會獎狀列印 + 公告標題貼紙

沒有留言:

張貼留言

2025年12月6日星期六