How to sync google sheets database with google contacts

Question

I have a google sheets database with contacts

here is an example of my database

I need to sync this contacts to my googlee contacts with a google apps script

If there is a new contact to upload it

If there is an existing contact that changed any of his data information to update it in google contacts

Any help on this pelease ?

-- Update:

so far I got this script working

 function myFunction() {
  // Sheet Variables Below
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var sheet = ss.getSheetByName('xx')
  var headerRows = 1;
  var MaxRow = sheet.getLastRow();
  var dataRange = sheet.getDataRange();
  var data = dataRange.getValues();     // Read all data
  data.splice(0, headerRows);            // Remove header rows

  function addContact() {
    for (var i = 0; i < data.length; i++) {
      var row = data[i];
      var firstName = row[1];
      var lastName = row[2];
      var email = row[3];
      var phone = row[4];
      var group = row[5];
      var atiende = row[6];
      var address1 = row[7];
      var address2 = row[8];
      var address3 = row[9]
      var notes = row[10];
      var customfields = row[11];
      var status = row[12];
      var contactid = row[13];

      var contact = ContactsApp.createContact(firstName, lastName, email);
      contact.addPhone(ContactsApp.Field.MOBILE_PHONE, phone);
      contact.addAddress(ContactsApp.Field.HOME_ADDRESS, address1);
      contact.setNotes(address2 + ' ' + address3)
      contact.addCompany(atiende)

      var group = ContactsApp.getContactGroup("System Group: My Contacts");
      group.addContact(contact);
   
    }
  }
  addContact();    
}

I have changed the data structure to this

this works in order to upload all the contacts to google contacts

But I need now to check if the contact exist by phone number, and if exists update it, if there is no changes to skip it, other way im having duplicates in google contacts

I believe its important to mention than this is a database generated automatically, is not something that I change the data manually

Tom Tom · Accepted Answer · 2021-02-07T14:22:23

You could update contacts when you add a new row to yr ss using the event object

warning: I'm an amateur tho enthusiastic gscripter so be careful using any advice I give

function newContact(e) {
  
 var sht = e.source.getActiveSheet();
 var row = e.range.getRow();

 var drng = sht.getRange(row, 2, 1, 6).getValues();
 // drng is a single row 2D array
 // adjust indexes to suit 
  
    var first = drng[0][0];
    var surname = drng[0][1];
    var phone = drng[0][2];
    var email = drng[0][3];
    var consentDate = drng[0][5];
    var grp = 'group-name'; 
  
    //create contact
    var contact = ContactsApp.createContact(first, surname, email);
    var contactID = contact.getId();
          
    //add info via bug workaround ie getting the new contact via contactID 
    contact = ContactsApp.getContactById(contactID); 
    contact.addPhone('mobile', phone);    
    
    //update contact
    var group = ContactsApp.getContactGroup(grp);
    contact = contact.addToGroup(group);
}

How to sync google sheets database with google contacts

1 Answers