Kutools had made us a spreadsheet in Excel to avoid the double entry of an email address. But we have lost this spreadsheet. So my question is whether this same macro can be made to work on Google Sheets?
Please try this VBA in the Google Sheets.
function checkDuplicates() {
var sheet = SpreadsheetApp.getActiveSheet();
var data = sheet.getDataRange().getValues();
var emailCol = 2; // Replace 2 with the column number of the email column
var emails = {};
var duplicates = [];
// Loop through the data and check for duplicates
for (var i = 1; i < data.length; i++) {
var email = data[i][emailCol];
if (email && email !== "" && emails[email]) {
// Duplicate found
duplicates.push(i + 1); // Add row number to duplicates array
} else {
// Add email to hash table
emails[email] = true;
}
}
if (duplicates.length > 0) {
// Display error message
var message = "Duplicate email(s) found on row(s): " + duplicates.join(", ");
SpreadsheetApp.getUi().alert(message);
}
}