Google Sheets跨工作表重复项检测及标记脚本问题求助

Hey there, let's sort out this messy duplicate marking issue you're facing. I get it—you want to flag duplicates across every sheet in your Google Sheet, but right now even the headers are getting marked red, and the script you're using is behaving all over the place. Let's break down what's going wrong and fix it.

What's Likely Wrong With Your Current Script #

Looking at the snippet you shared (Array.prototype.countItem = function (item) { var counts = {}; for (var i = 0; i < this.length; i++) { var num = this[i]; counts[num] = counts[num] ? counts[num] + 1...), here are the common pitfalls causing chaos:

• No header exclusion: The script isn't skipping the first row (your headers), so it's treating them like regular data and marking them as duplicates.
• No data normalization: Google Sheets stores values as different types (numbers, text, dates) — if you're comparing a number 123 to a string "123", they'll be treated as unique, or vice versa, leading to missed duplicates or false positives.
• Possible single-sheet logic: If the script only checks duplicates within each sheet instead of aggregating all data first, cross-sheet duplicates won't be detected correctly.
• Uncontrolled color marking: It might be overwriting existing cell colors or targeting the wrong cell ranges, causing the messy results you see.

A Fixed Script That Works #

Here's a revised script that addresses all these issues. It'll skip headers, detect duplicates across every sheet, and only mark actual data duplicates (without messing up your existing formatting):

function markDuplicatesAcrossSheets() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = ss.getSheets();
  const valueCounts = {};

  // First pass: Collect all data values (skip headers) and count their occurrences
  sheets.forEach(sheet => {
    const values = sheet.getDataRange().getValues();
    // Start from row 2 (index 1) to skip the header row
    for (let rowIndex = 1; rowIndex < values.length; rowIndex++) {
      values[rowIndex].forEach(cellValue => {
        // Skip empty cells to avoid marking blanks as duplicates
        if (cellValue === "") return;
        // Normalize all values to strings to fix type mismatch issues
        const normalizedValue = String(cellValue);
        // Track how many times each value appears across all sheets
        valueCounts[normalizedValue] = (valueCounts[normalizedValue] || 0) + 1;
      });
    }
  });

  // Second pass: Go back to each sheet and mark duplicates
  sheets.forEach(sheet => {
    const range = sheet.getDataRange();
    const values = range.getValues();
    // Preserve existing background colors so we don't overwrite your formatting
    const backgroundColors = range.getBackgrounds();

    for (let rowIndex = 1; rowIndex < values.length; rowIndex++) {
      for (let colIndex = 0; colIndex < values[rowIndex].length; colIndex++) {
        const cellValue = values[rowIndex][colIndex];
        if (cellValue === "") continue;
        const normalizedValue = String(cellValue);
        // Mark cells with values that appear more than once with a light red background
        if (valueCounts[normalizedValue] > 1) {
          backgroundColors[rowIndex][colIndex] = "#ffcccc";
        }
      }
    }

    // Apply the updated colors to the sheet
    range.setBackgrounds(backgroundColors);
  });

  // Let you know when it's done
  SpreadsheetApp.getUi().alert("Duplicate marking finished! Check your sheets for light red highlighted duplicates.");
}

Key Fixes Explained #

• Header exclusion: We start looping from rowIndex = 1 (the second row) so headers never get included in duplicate checks.
• Value normalization: Converting every cell value to a string ensures numbers, dates, and text versions of the same value are counted as duplicates (e.g., 123 and "123" won't be treated as unique).
• Cross-sheet aggregation: We first collect all data across every sheet to count occurrences, then go back to mark duplicates—this ensures duplicates between sheets are caught correctly.
• Preserve existing formatting: We grab the current background colors before making changes, so only duplicate cells get updated (your other formatting stays intact).
• Empty cell handling: We skip blank cells so you don't end up with a sheet full of red-marked empty cells.

How to Use This Script #

1. Open your Google Sheet.
2. Click Extensions > Apps Script to open the script editor.
3. Delete any existing code and paste the script above.
4. Click the save button (💾) and name the project something like "CrossSheetDuplicateMarker".
5. Click the run button (▶️) — you'll need to grant permissions the first time (this is safe, it only accesses your current sheet).

Once it runs, any duplicate values across all sheets (excluding headers) will be highlighted in light red.

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