求开发Google Apps Script脚本：遍历文件夹表格合并getdata至Advisory Master

Got it, here's a solid Google Apps Script solution to batch pull data from all Google Sheets in a specified folder and append it to your "Advisory Master" sheet. I’ll walk you through the code and key details so you can get it running quickly:

Complete Script Code #

function consolidateSheetsData() {
  // Replace with your target folder ID (grab from the folder's URL)
  const targetFolderId = "YOUR_FOLDER_ID_HERE";
  // Replace with your master spreadsheet ID, or use getActiveSpreadsheet() if script is bound to the master sheet
  const masterSpreadsheet = SpreadsheetApp.openById("YOUR_MASTER_SPREADSHEET_ID_HERE");
  const masterSheet = masterSpreadsheet.getSheetByName("Advisory Master");
  
  // Check if master sheet exists first
  if (!masterSheet) {
    throw new Error("Master sheet 'Advisory Master' not found! Double-check the sheet name.");
  }
  
  const targetFolder = DriveApp.getFolderById(targetFolderId);
  // Fetch only Google Sheets files in the folder
  const sheetFiles = targetFolder.getFilesByType(MimeType.GOOGLE_SHEETS);
  
  while (sheetFiles.hasNext()) {
    const currentFile = sheetFiles.next();
    const currentSpreadsheet = SpreadsheetApp.openById(currentFile.getId());
    const dataSheet = currentSpreadsheet.getSheetByName("getdata");
    
    // Skip files that don't have a "getdata" sheet
    if (!dataSheet) {
      console.log(`Skipping file: ${currentFile.getName()} (no 'getdata' sheet found)`);
      continue;
    }
    
    // Get all non-empty data from the sheet
    const dataRange = dataSheet.getDataRange();
    const dataValues = dataRange.getValues();
    
    // Skip empty sheets
    if (dataValues.length === 0) {
      console.log(`Skipping file: ${currentFile.getName()} (no data in 'getdata' sheet)`);
      continue;
    }
    
    // Skip header row (remove slice(1) if your sub-sheets don't have headers)
    const rowsToAppend = dataValues.slice(1);
    
    if (rowsToAppend.length > 0) {
      // Append data to the next empty row in the master sheet
      masterSheet.getRange(
        masterSheet.getLastRow() + 1,
        1,
        rowsToAppend.length,
        rowsToAppend[0].length
      ).setValues(rowsToAppend);
      
      console.log(`Successfully added ${rowsToAppend.length} rows from: ${currentFile.getName()}`);
    } else {
      console.log(`Skipping file: ${currentFile.getName()} (only headers, no data rows)`);
    }
  }
  
  // Alert when process is done
  SpreadsheetApp.getUi().alert("Batch consolidation complete! Check your Advisory Master sheet.");
}

Key Setup & Explanations #

• Replace Placeholder IDs:
  • Grab your target folder ID from its URL (it’s the string after drive/folders/ in the browser address bar) and replace YOUR_FOLDER_ID_HERE.
  • Replace YOUR_MASTER_SPREADSHEET_ID_HERE with the ID of the sheet containing your "Advisory Master" tab. If the script is bound directly to the master sheet, swap this line with const masterSpreadsheet = SpreadsheetApp.getActiveSpreadsheet().
• Header Handling: The script skips the first row of each "getdata" sheet (assuming it’s a header). If your sub-sheets don’t have headers, remove .slice(1) from the rowsToAppend line.
• Error Logging: Any issues (missing sheets, empty data) will show up in the script’s execution log, so you can debug easily.

Running the Script #

1. Open Google Sheets, go to Extensions > Apps Script to open the script editor.
2. Paste the code above, replace the placeholder IDs.
3. Click the run button (▶️). The first time you run it, you’ll need to grant permissions (this is safe—your data stays in your Google account).
4. Wait for the alert to pop up, then check your master sheet for the appended data.

Optional Optimizations #

• Deduplication: If you want to avoid adding duplicate rows, add a check against a unique identifier column in your master sheet before appending.
• Scheduled Runs: Use Google Apps Script’s Triggers menu to set up automatic weekly/monthly runs for recurring consolidation.
• Format Preservation: If you need to keep cell formatting, replace getValues() with getDisplayValues() or use the copyTo() method (note: this will slow down the script for large datasets).

内容的提问来源于stack exchange，提问作者R. Wurth