Duplicates in Column A

function onOpen() {

  SpreadsheetApp.getUi()

    .createMenu('Duplicates')

    .addItem('Check Duplicates in Column A', 'checkDuplicatesInColumnA')

    .addToUi();

}

function checkDuplicatesInColumnA() {

  try {

    const sheet = SpreadsheetApp.getActiveSheet();

    const lastRow = sheet.getLastRow();

    const columnARange = sheet.getRange("A1:A" + lastRow);

    const values = columnARange.getValues();

   

    // Clear existing highlighting in column A

    columnARange.setBackground('white');

   

    // Track duplicates

    let valueCount = {};

    let duplicateFound = false;

   

    // First pass: count occurrences

    values.forEach((row, index) => {

      let value = row[0].toString().trim();

      if (value !== '') {

        if (!valueCount[value]) {

          valueCount[value] = {

            count: 1,

            positions: [index + 1]

          };

        } else {

          valueCount[value].count++;

          valueCount[value].positions.push(index + 1);

          duplicateFound = true;

        }

      }

    });

   

    // Second pass: highlight duplicates and prepare message

    let message = '';

    for (let value in valueCount) {

      if (valueCount[value].count > 1) {

        // Highlight all instances of this duplicate

        valueCount[value].positions.forEach(rowNum => {

          sheet.getRange(rowNum, 1).setBackground('#FFA500'); // Orange color

        });

       

        message += `\n"${value}" appears ${valueCount[value].count} times in rows: ${valueCount[value].positions.join(', ')}`;

      }

    }

   

    // Show results

    if (duplicateFound) {

      SpreadsheetApp.getUi().alert('Duplicates found in column A:' + message);

    } else {

      SpreadsheetApp.getUi().alert('No duplicates found in column A');

    }

   

  } catch (error) {

    SpreadsheetApp.getUi().alert('Error: ' + error.toString());

  }

}