一鍵合併所有 Google 試算表工作表資料

一鍵合併所有 Google 試算表工作表資料

function onOpen() {

  var ui = SpreadsheetApp.getUi();

  ui.createMenu('🎯資料匯整')

    .addItem('🚀合併所有工作表資料', 'MergeSheets')

    .addToUi();

}

function MergeSheets() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // 1. 設定新工作表名稱

  var today = Utilities.formatDate(new Date(), "GMT+8", "yyyyMMdd");

  var newSheetName = today + "_合併資料";

  // 2. 安全建立新工作表流程

  // 先插入一個未命名的工作表，確保檔案中至少有兩張表 (避免刪除舊表時若它是唯一一張表會報錯)

  var newSheet = ss.insertSheet();

  // 檢查是否已存在同名工作表，若有則刪除

  var existingSheet = ss.getSheetByName(newSheetName);

  if (existingSheet) {

    ss.deleteSheet(existingSheet);

  }

  // 將剛剛插入的新工作表重新命名

  newSheet.setName(newSheetName);

 

  // 3. *關鍵修正*：在刪除舊表、建立新表「之後」才取得工作表清單

  // 這樣 sheets 陣列裡就不會包含已經被刪除的無效工作表物件 (解決 "Sheet not found" 錯誤)

  var sheets = ss.getSheets();

 

  // 用來暫存所有資料的陣列

  var allData = [];

 

  // 用來記錄是否已經加入過標題列

  var hasHeader = false;

  // 4. 循環所有工作表

  for (var i = 0; i < sheets.length; i++) {

    var sheet = sheets[i];

    var sheetName = sheet.getName();

    // *重要*：跳過名稱包含 "合併資料" 的工作表，以及名為 "All" 的工作表

    if (sheetName.includes("合併資料") || sheetName === "All") {

      continue;

    }

    // 取得該工作表的資料

    var range = sheet.getDataRange();

   

    // 如果工作表是空的，則跳過

    if (range.isBlank()) {

      continue;

    }

   

    var values = range.getValues();

    // 5. 處理資料合併邏輯

    if (!hasHeader) {

      // 如果還沒有標題 (通常是第一個被讀取的工作表)，放入整份資料 (含標題)

      allData = allData.concat(values);

      hasHeader = true;

    } else {

      // 如果已經有標題了，之後的工作表都要去除第一列 (values.slice(1))

      // 確保該工作表不只一行 (避免只有標題列的情況報錯)

      if (values.length > 1) {

        allData = allData.concat(values.slice(1));

      }

    }

  }

  // 6. 一次性寫入資料 (效能較佳)

  if (allData.length > 0) {

    // === 解決欄位數不一致的問題 ===

   

    // 步驟 A: 找出所有資料中，最寬的那一列有多少欄

    var maxColumns = 0;

    for (var r = 0; r < allData.length; r++) {

      if (allData[r].length > maxColumns) {

        maxColumns = allData[r].length;

      }

    }

    // 步驟 B: 將所有列補齊到最寬的長度 (補空字串)

    for (var r = 0; r < allData.length; r++) {

      var currentRowLength = allData[r].length;

      if (currentRowLength < maxColumns) {

        // 算出差幾欄，就補幾個空字串

        for (var c = currentRowLength; c < maxColumns; c++) {

          allData[r].push("");

        }

      }

    }

    // === 修正結束 ===

    // 取得新工作表的寫入範圍：使用 maxColumns 作為寬度

    newSheet.getRange(1, 1, allData.length, maxColumns).setValues(allData);

    Logger.log("合併完成，共合併了 " + allData.length + " 列資料。");

  } else {

    Logger.log("沒有資料可以合併。");

  }

}

快來試試吧~