Delete duplicated or multiplied contacts from Google Contacts

Question

My goal was to make a spreadsheet of customers with their contact information, addresses, and notes that creates new Google Contacts entries and pulls back contact id and "addedAlready" on to the sheet which marks contacts already entered to Google Contacts with "ADDED" on the sheet, which I successfully did. Everything works fine, problem is only with multiple wrong entries of same person in Google Contacts, because whenever the contact is upgraded, it makes new entry.

function createContact() {
  var alreadyAdded = "ADDED";
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");
  var startRow = 5;  // First row of data to process
  var numRows = sheet.getRange('J2').getValue(); // Last row of data to process

  // Fetch the range of cells A5:J
  var dataRange = sheet.getRange(startRow, 1, numRows, 10)

  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
    for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var addedAlready = row[0]
    var firstName = row[1]
    var lastName = row[2]
    var company = row[3]  
    var phone = row[4]
    var notes = row[5]
    var address = row[6]
    var email = row[7];

      if (addedAlready != alreadyAdded) {
      // Create contact in Google Contacts and retrieve ID
      var contact = ContactsApp.createContact(firstName, lastName, phone);
      var group = ContactsApp.getContactGroup("System Group: My Contacts");
      group.addContact(contact);
      var id = contact.getId();

      // Add values to new contact
      contact.addCompany(company, "");
      contact.addPhone(ContactsApp.Field.WORK_PHONE, phone);
      contact.setNotes(notes);
      contact.addAddress(ContactsApp.Field.WORK_ADDRESS, address);
      contact.addEmail(ContactsApp.Field.WORK_EMAIL, email);
      sheet.getRange(startRow + i, 1).setValue(alreadyAdded);
      sheet.getRange(startRow + i, 9).setValue(id);
      }; 
    };

};

Below also "onEdit" script which removes "ADDED" status and enables the contact to be updated or in another words, doubled in Google Contacts.

function onEdit(e){
  var sh=e.range.getSheet();
  if(sh.getName()=="Sheet1" && e.range.columnStart==2,4,5,6 && e.range.rowStart>4) {
    var sh2=e.source.getSheetByName("Sheet2");
    sh2.getRange(e.range.rowStart,1,e.range.rowEnd-e.range.rowStart+1,1).clearContent()  
  }
} 

To solve and eleminate this problem I will now kindly ask you guys, because I'm completely lost here, if anyone can help me create a script that would retrieve all existing contact IDs from Google contacts and compare them with conatct ID's in the "I" column and delete all entries in Google Contacts which ID doesnt match to the ID's in the "I" column.

Any help would be greatly appreciated! Many thanks!

Answer 1

If I understand you correctly:

• You have different contact ID's in column I.
• You want to delete any contact from Google Contacts whose id is not in column I.

If that's the case, then you can do this:

function deleteContacts() {
  var contactIds = ContactsApp.getContacts().map(function(contact) {
    return contact.getId(); // Get current contact ids
  });
  var sh = SpreadsheetApp.getActive().getSheetByName("Sheet1"); // Please change accordingly
  var currentIds = sh.getRange("I:I").getValues().map(function(value) {
    return value[0];
  }).filter(function(val) {
    return val != "";
  })
  for (var i = 0; i < contactIds.length; i++) {
    if (currentIds.indexOf(contactIds[i]) == -1) {
      var contact = ContactsApp.getContactById(contactIds[i]);
      ContactsApp.deleteContact(contact);
    }
  }
}

This script does the following:

• Get the id's of all the current Contacts (variable contactIds) with getContacts().
• Get all the values in column I of Sheet1 (variable currentIds).
• For each id in Contacts, check if it exists in column I via indexOf(). If that's not the case, remove the contact (first retrieve it via getContactById(id) and then delete it via deleteContact(contact)).

Reference:

• ContactsApp.getContacts()
• Array.prototype.indexOf()
• ContactsApp.getContactById(id)
• ContactsApp.deleteContact(contact)

I hope this is of any help.