Checking duplicates

Identifies repeating values in a selected column and visually highlights rows with duplicates. Used for data cleaning and analysis - for example, in customer lists, orders, or employee records:

• select column for checking by determining the column that should not have repeating values (e.g., Email or ID);
• analyze values by scanning from top to bottom and identifying duplicates (ApiRange/GetValue);
• highlight duplicates by marking rows with repeating values using background color (ApiRange/SetFillColor).

This approach is commonly used for data validation, customer database cleanup, preventing duplicate entries, and maintaining data integrity.

• Code
• Result

let worksheet = Api.GetActiveSheet();

// Sample data with headers and some duplicate emails
let headers = ["ID", "Name", "Email", "Department", "Phone"];
let employeeData = [
  [1, "Alice Johnson", "alice.johnson@company.com", "Marketing", "+1-555-0101"],
  [2, "Bob Smith", "bob.smith@company.com", "Sales", "+1-555-0102"],
  [3, "Carol Davis", "carol.davis@company.com", "IT", "+1-555-0103"],
  [4, "David Wilson", "bob.smith@company.com", "HR", "+1-555-0104"], // Duplicate email
  [5, "Emma Brown", "emma.brown@company.com", "Finance", "+1-555-0105"],
  [6, "Frank Miller", "frank.miller@company.com", "Marketing", "+1-555-0106"],
  [7, "Grace Lee", "alice.johnson@company.com", "Sales", "+1-555-0107"], // Duplicate email
  [8, "Henry Taylor", "henry.taylor@company.com", "IT", "+1-555-0108"],
  [9, "Ivy Chen", "ivy.chen@company.com", "HR", "+1-555-0109"],
  [10, "Jack Anderson", "frank.miller@company.com", "Finance", "+1-555-0110"] // Duplicate email
];

// Fill headers
for (let i = 0; i < headers.length; ++i) {
  worksheet.GetRangeByNumber(0, i).SetValue(headers[i]);
}

// Fill employee data
for (let i = 0; i < employeeData.length; ++i) {
  for (let j = 0; j < employeeData[i].length; ++j) {
    worksheet.GetRangeByNumber(i + 1, j).SetValue(employeeData[i][j]);
  }
}

// Format the data as a table
let dataRange = "A1:E" + (employeeData.length + 1);
worksheet.FormatAsTable(dataRange);

// Auto-fit column widths for better presentation
worksheet.GetRange("A1").SetColumnWidth(5);  // ID
worksheet.GetRange("B1").SetColumnWidth(15); // Name
worksheet.GetRange("C1").SetColumnWidth(25); // Email
worksheet.GetRange("D1").SetColumnWidth(12); // Department
worksheet.GetRange("E1").SetColumnWidth(15); // Phone

// Check for duplicates in Email column (column C)
let emailColumnIndex = 2; // Column C (0-based index)
let emailValues = [];
let duplicateRows = [];

// Scan through all data rows (skip header)
for (let row = 1; row <= employeeData.length; ++row) {
  let emailValue = worksheet.GetRangeByNumber(row, emailColumnIndex).GetValue();
  
  // Check if this email already exists in our tracking array
  let isDuplicate = false;
  for (let i = 0; i < emailValues.length; ++i) {
    if (emailValues[i].email === emailValue) {
      isDuplicate = true;
      // Mark both the original and current row as duplicates
      if (duplicateRows.indexOf(emailValues[i].row) === -1) {
        duplicateRows.push(emailValues[i].row);
      }
      duplicateRows.push(row);
      break;
    }
  }
  
  // Add current email to tracking array
  emailValues.push({
    email: emailValue,
    row: row
  });
}

// Highlight duplicate rows with light red background
let lightRedColor = Api.CreateColorFromRGB(255, 200, 200);
for (let i = 0; i < duplicateRows.length; ++i) {
  let rowNumber = duplicateRows[i] + 1; // Convert to 1-based for range notation
  let rowRange = worksheet.GetRange("A" + rowNumber + ":E" + rowNumber);
  rowRange.SetFillColor(lightRedColor); // Light red background
}

// Add a summary of found duplicates
let summaryRow = employeeData.length + 3;
worksheet.GetRangeByNumber(summaryRow, 0).SetValue("Duplicate Check Summary:");
worksheet.GetRangeByNumber(summaryRow + 1, 0).SetValue("Total rows checked: " + employeeData.length);
worksheet.GetRangeByNumber(summaryRow + 2, 0).SetValue("Duplicate rows found: " + duplicateRows.length);
worksheet.GetRangeByNumber(summaryRow + 3, 0).SetValue("Duplicate emails highlighted in red");

// Format summary section
let grayColor = Api.CreateColorFromRGB(100, 100, 100);
let summaryRange = worksheet.GetRange("A" + (summaryRow + 1) + ":A" + (summaryRow + 4));
summaryRange.SetFontColor(grayColor); // Gray text
worksheet.GetRangeByNumber(summaryRow, 0).SetBold(true);