大数据集去重优化需求：整行重复与指定列重复保留最早日期

Hey there, let's fix both the performance bottleneck and the broken "specified column + earliest date" deduplication logic in your script. The core issue with your original code is the nested O(n²) loop—comparing every row against every already-processed row is brutal for 8K+ rows, which explains the 3-minute runtime. Let's switch to an O(n) approach using hash maps (ES6 Map for V8 runtime) to drastically speed things up, while correctly handling both deduplication rules and counting deletions.

Key Requirements Recap #

• Remove true duplicates (entire rows identical)
• After true duplicates are removed, deduplicate by a specified column, keeping only the row with the earliest date in another column
• Return counts for both types of deleted duplicates
• Prioritize speed for future scaling to larger datasets

Optimized Script (V8 Runtime Enabled) #

This implementation uses Map for O(1) lookups, cutting runtime from minutes to seconds even for large datasets:

function removeDuplicates(sheet) {
  // Get all data and separate headers from rows (adjust if your sheet has no headers)
  const [header, ...data] = sheet.getDataRange().getValues();
  let trueDuplicateCount = 0;
  let diffDateDuplicateCount = 0;

  // Step 1: Remove true duplicates first
  const trueUniqueMap = new Map();
  data.forEach(row => {
    const rowKey = row.join('|'); // Use a unique separator to avoid false matches
    if (trueUniqueMap.has(rowKey)) {
      trueDuplicateCount++;
    } else {
      trueUniqueMap.set(rowKey, row);
    }
  });
  const trueUniqueRows = Array.from(trueUniqueMap.values());

  // Step 2: Deduplicate by specified column, keep earliest date
  // Adjust column indices here: specifyColumn = 1 (second column), dateColumn = 0 (first column)
  const specifyColumnIndex = 1;
  const dateColumnIndex = 0;
  const dateUniqueMap = new Map();

  trueUniqueRows.forEach(row => {
    const specifyKey = row[specifyColumnIndex];
    const currentDate = new Date(row[dateColumnIndex]);
    
    if (!dateUniqueMap.has(specifyKey)) {
      // First occurrence of this specified column value: add to map
      dateUniqueMap.set(specifyKey, { row, date: currentDate });
    } else {
      // Existing entry: compare dates, keep the earliest
      const existingEntry = dateUniqueMap.get(specifyKey);
      if (currentDate > existingEntry.date) {
        // Current row has later date: mark as duplicate to delete
        diffDateDuplicateCount++;
      } else {
        // Current row has earlier date: replace existing entry and count the old one as deleted
        diffDateDuplicateCount++;
        dateUniqueMap.set(specifyKey, { row, date: currentDate });
      }
    }
  });

  // Prepare final data (headers + unique rows)
  const finalData = [header, ...Array.from(dateUniqueMap.values()).map(entry => entry.row)];

  // Write back to sheet efficiently
  sheet.clearContents();
  sheet.getRange(1, 1, finalData.length, finalData[0].length).setValues(finalData);

  // Return deletion counts
  return [trueDuplicateCount, diffDateDuplicateCount];
}

Performance & Logic Improvements #

• O(n) Time Complexity: Using Map eliminates nested loops—each row is processed exactly twice (once for true duplicates, once for date-based deduplication)
• Accurate Date Comparison: Converts date values to Date objects for reliable comparison (avoids string comparison issues)
• Efficient Data Writing: Clears the sheet once and writes all data in a single batch, reducing Google Apps Script API calls (another major performance win)
• Configurable Columns: Easily adjust specifyColumnIndex and dateColumnIndex to match your sheet's structure

Compatibility & Final Notes #

• If you can't enable the V8 runtime, Tanaike's non-V8 compatible solution is a great fallback for legacy environments
• For future scaling (100K+ rows), this approach will hold up far better than nested loops, as it scales linearly with dataset size

As you mentioned, choosing the V8-based (Master's) solution is the right call for long-term scalability, while Tanaike's solution remains valuable for users on older runtime versions.

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