如何合并Google Sheets多标签页数据？求适配多标签的脚本方案

Absolutely, formulas can get unwieldy (and even crash!) when dealing with 30+ tabs—Google Apps Script is the perfect solution here. Below is a tested, robust script that will merge data from all your tabs (excluding the Combined sheet itself) into the Combined tab, using your unified header (Title, Type, Genre).

Step 1: Set Up the Script #

1. Open your Google Sheet
2. Click Extensions > Apps Script to launch the script editor
3. Delete the default myFunction() code snippet
4. Paste the script below:

function mergeTabs() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  let combinedSheet = spreadsheet.getSheetByName("Combined");

  // Create Combined sheet if it doesn't exist, add the unified header
  if (!combinedSheet) {
    combinedSheet = spreadsheet.insertSheet("Combined");
    combinedSheet.getRange(1, 1, 1, 3).setValues([["Title", "Type", "Genre"]]);
  } else {
    // Clear existing data but keep the header
    combinedSheet.getRange(2, 1, combinedSheet.getLastRow() - 1, 3).clearContent();
  }

  // List of tabs to skip (add more if needed, e.g., "Instructions" or "Archive")
  const excludedTabs = ["Combined"];
  const allSheets = spreadsheet.getSheets();

  let nextEmptyRow = combinedSheet.getLastRow() + 1;

  allSheets.forEach(sheet => {
    const sheetName = sheet.getName();
    // Skip excluded tabs
    if (excludedTabs.includes(sheetName)) return;

    // Get data from the sheet (skip the first row since we use our own header)
    const dataRange = sheet.getRange(2, 1, sheet.getLastRow() - 1, 3);
    const data = dataRange.getValues();

    // Skip completely empty sheets
    if (data.length === 0) return;

    // Append filtered data to Combined sheet
    combinedSheet.getRange(nextEmptyRow, 1, data.length, 3).setValues(data);
    nextEmptyRow += data.length;
  });

  SpreadsheetApp.getUi().alert("Merge complete! Check the Combined tab for your consolidated data.");
}

Step 2: Run the Script #

1. Click the run button (▶️) in the script editor
2. The first time you run it, you’ll need to authorize the script (follow the prompts—you may need to click "Advanced" > "Go to [Your Sheet Name] (unsafe)" to proceed; this is normal for custom scripts)
3. Once authorized, the script will automatically merge all your tab data into the Combined sheet

Optional Optimizations #

• Auto-refresh: To update the Combined tab automatically when data changes, add an installable trigger (go to Edit > Current project's triggers > Add trigger, set mergeTabs to run on "On edit" or "On change")
• Remove empty rows: Add const filteredData = data.filter(row => row.some(cell => cell !== "")); right after getting data to skip blank rows in source tabs
• Handle extra columns: If some tabs have more than 3 columns, adjust the 3 values in the script to match your needs (just ensure the header stays aligned)

内容的提问来源于stack exchange，提问作者BabyYoda