Google Sheets多表数据归集及表头排序问题求助（含超时优化）

Key Fixes & Improvements #

• Header Alignment: Map each sheet's columns to your target headers regardless of their original order
• Batch Processing: Process 30 sheets at a time to avoid script timeouts, with progress tracking between runs
• Efficient Data Handling: Skip empty rows and unwanted sheets automatically
• Error Prevention: Handle missing headers and incorrect sheet references gracefully

Complete Script #

// Configuration - Update these values to match your spreadsheet
const SKIP_SHEETS = ["URL_LIST", "Filter", "Master", "Template"];
const TARGET_HEADERS = ['firstName', 'companyName', 'BadLinkURL', 'DiscreditProofURL', 'email', 'Niche'];
const MASTER_SHEET_NAME = "Master";
const BATCH_SIZE = 30; // Number of sheets to process per run

function consolidateSheets() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const masterSheet = ss.getSheetByName(MASTER_SHEET_NAME) || ss.insertSheet(MASTER_SHEET_NAME);
  const properties = PropertiesService.getScriptProperties();
  
  // Get progress tracker (next sheet index to process)
  let nextSheetIndex = parseInt(properties.getProperty('nextSheetIndex')) || 0;
  const allSheets = ss.getSheets();
  
  // Reset and reprocess all sheets if we've finished the last batch
  if (nextSheetIndex >= allSheets.length) {
    masterSheet.clearContents();
    masterSheet.getRange(1, 1, 1, TARGET_HEADERS.length).setValues([TARGET_HEADERS]);
    nextSheetIndex = 0;
    properties.setProperty('nextSheetIndex', 0);
    SpreadsheetApp.getUi().alert("All sheets processed! Master sheet reset for fresh consolidation.");
    return;
  }
  
  // Process current batch of sheets
  const endIndex = Math.min(nextSheetIndex + BATCH_SIZE, allSheets.length);
  const consolidatedData = [];
  
  for (let i = nextSheetIndex; i < endIndex; i++) {
    const sheet = allSheets[i];
    const sheetName = sheet.getName();
    
    // Skip excluded sheets
    if (SKIP_SHEETS.includes(sheetName)) continue;
    
    // Get sheet data and headers
    const range = sheet.getDataRange();
    const values = range.getValues();
    if (values.length <= 1) continue; // Skip sheets with no data rows
    
    const sheetHeaders = values[0].map(header => header.trim());
    
    // Map each data row to target header order
    for (let rowIndex = 1; rowIndex < values.length; rowIndex++) {
      const row = values[rowIndex];
      const mappedRow = [];
      
      TARGET_HEADERS.forEach(targetHeader => {
        const columnIndex = sheetHeaders.indexOf(targetHeader);
        mappedRow.push(columnIndex !== -1 ? row[columnIndex] : "");
      });
      
      // Only add rows with at least one non-empty value
      if (mappedRow.some(cell => cell !== "")) {
        consolidatedData.push(mappedRow);
      }
    }
  }
  
  // Append processed data to Master sheet
  if (consolidatedData.length > 0) {
    const startRow = masterSheet.getLastRow() + 1;
    masterSheet.getRange(startRow, 1, consolidatedData.length, TARGET_HEADERS.length).setValues(consolidatedData);
  }
  
  // Update progress for next run
  nextSheetIndex = endIndex;
  properties.setProperty('nextSheetIndex', nextSheetIndex);
  
  // Show progress update
  SpreadsheetApp.getUi().alert(`Processed sheets ${nextSheetIndex - BATCH_SIZE + 1} to ${endIndex}. ${allSheets.length - endIndex} sheets remaining. Run the script again to continue.`);
}

// Reset progress tracker (use if you need to start over)
function resetProgress() {
  PropertiesService.getScriptProperties().deleteProperty('nextSheetIndex');
  SpreadsheetApp.getUi().alert("Progress reset. Next script run will start from the first sheet.");
}

How to Use the Script #

1. Open your Google Spreadsheet
2. Go to Extensions > Apps Script to open the script editor
3. Replace any existing code with the script above
4. Save the script (click the floppy disk icon) and name it something like "ConsolidateSheets"
5. Run the consolidateSheets function – you'll need to authorize the script first (follow prompts, click "Advanced" to allow access)
6. Keep running the function until you get the alert saying all sheets are processed

Setting Up the Filter Sheet #

Create a dynamic filter sheet with these steps:

1. Make a new sheet named "Filter"
2. In cell A1, use this QUERY formula (adjust range/column name as needed):

=QUERY(Master!A:F, "SELECT * WHERE Niche = '"&B1&"'", 1)

3. Type your filter value in cell B1 (e.g., "Marketing") to see filtered results automatically

Key Issue Explanations #

• Header Mismatch: The script maps each row to your target headers by checking the index of each header in the source sheet, ensuring consistent column order in the Master sheet
• Timeout Errors: Batch processing limits each run to 30 sheets, keeping execution time under Google's 6-minute limit
• Original Script Errors: Fixed the getDataRange issue (you were calling it on an array of sheets instead of individual sheets) and removed redundant nested loops

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