如何通过循环利用Google Apps Script批量清除文件夹及子文件夹表格重复行

Got it, let's break this down. You want to automate the duplicate row removal across every Google Sheet in a Drive folder (including all subfolders), right? Here's how to adapt the single-sheet logic into a bulk, recursive solution:

Step 1: Core Approach Overview #

We’ll build two key components:

• A recursive function to traverse every folder/subfolder and locate all Google Sheets.
• A reusable duplicate-removal function that works on individual sheets, aligned with the logic you referenced for single spreadsheets.

Step 2: Full Script Implementation #

Drop this code into Google Apps Script (we’ll walk through how to use it next):

function bulkRemoveDuplicatesFromDriveFolders() {
  // Replace this with your root folder's ID (grab it from the Drive folder URL)
  const rootFolderId = "YOUR_ROOT_FOLDER_ID";
  const rootFolder = DriveApp.getFolderById(rootFolderId);
  
  // Start recursive processing from the root folder
  processFolder(rootFolder);
}

// Recursively process all files in a folder and its subfolders
function processFolder(folder) {
  // Get all Google Sheets in the current folder
  const sheetFiles = folder.getFilesByType(MimeType.GOOGLE_SHEETS);
  
  // Loop through each sheet file
  while (sheetFiles.hasNext()) {
    const file = sheetFiles.next();
    const spreadsheet = SpreadsheetApp.open(file);
    
    console.log(`Processing: ${file.getName()}`);
    
    // --- Choose one option below ---
    // Option 1: Process ALL sheets in the spreadsheet
    const allSheets = spreadsheet.getSheets();
    allSheets.forEach(sheet => {
      removeDuplicates(sheet);
      console.log(`  Cleaned sheet: ${sheet.getName()}`);
    });
    
    // Option 2: Only process a specific sheet (uncomment and comment Option 1 if needed)
    // const targetSheet = spreadsheet.getSheetByName("Your Sheet Name");
    // if (targetSheet) {
    //   removeDuplicates(targetSheet);
    //   console.log(`  Cleaned sheet: ${targetSheet.getName()}`);
    // }
  }
  
  // Recurse into subfolders to keep processing
  const subfolders = folder.getFolders();
  while (subfolders.hasNext()) {
    const subfolder = subfolders.next();
    console.log(`Entering subfolder: ${subfolder.getName()}`);
    processFolder(subfolder);
  }
}

// Reusable function to remove duplicate rows from a single sheet
function removeDuplicates(sheet) {
  const data = sheet.getDataRange().getValues();
  
  // Exit early if there aren't enough rows to have duplicates
  if (data.length < 2) return;
  
  // Track unique rows by converting them to strings (handles all data types)
  const uniqueRowSet = new Set();
  const cleanedData = [];
  
  for (const row of data) {
    const rowKey = JSON.stringify(row);
    if (!uniqueRowSet.has(rowKey)) {
      uniqueRowSet.add(rowKey);
      cleanedData.push(row);
    }
  }
  
  // Clear the sheet and write back the unique rows
  sheet.clearContents();
  sheet.getRange(1, 1, cleanedData.length, cleanedData[0].length).setValues(cleanedData);
  
  // Log how many duplicates were removed for visibility
  const removedCount = data.length - cleanedData.length;
  console.log(`    Removed ${removedCount} duplicate rows`);
}

Step 3: Optimize with Built-in Method (Faster for Large Datasets) #

If you don’t need custom duplicate-checking rules, Google Sheets has a native removeDuplicates() method that’s far more efficient for big sheets. Replace the removeDuplicates function above with this:

function removeDuplicates(sheet) {
  const dataRange = sheet.getDataRange();
  // Remove duplicates based on ALL columns (pass an array like [1,3] to check only columns A and C)
  const removedCount = dataRange.removeDuplicates().length;
  
  console.log(`    Removed ${removedCount} duplicate rows`);
}

Step 4: How to Run the Script #

1. Go to script.google.com or open the Apps Script editor from any Google Sheet (Extensions > Apps Script).
2. Paste the code, then replace YOUR_ROOT_FOLDER_ID with your actual folder ID (you’ll find this in the Drive folder’s URL).
3. Save the project, then run the bulkRemoveDuplicatesFromDriveFolders function.
4. Grant the necessary permissions (Google will ask for access to your Drive and Sheets—this is safe, you’re running your own script).
5. Check execution logs (View > Logs) to see which files were processed and how many duplicates were removed.

Important Notes #

• Backup First: Always make a copy of your Drive folder before running bulk scripts—better safe than sorry if something unexpected happens.
• Runtime Limits: Google Apps Script has a 6-minute execution limit. If you have hundreds of large sheets, split the job into smaller folders and run the script multiple times.
• Customization: Adjust the duplicate-checking logic if you only want to consider specific columns (modify the row key in the custom function, or pass column indices to the built-in method).

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