如何将Google表格数据库与Google通讯录同步 [英] How to sync google sheets database with google contacts

问题描述

我有一个包含通讯录的Google表格数据库

I have a google sheets database with contacts

这是我的数据库示例

我需要使用Google Apps脚本将此联系人同步到我的googlee联系人

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

如果有新联系人要上传

如果现有联系人已更改其任何数据信息以在Google联系人中进行更新

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

有关此问题的任何帮助吗?

Any help on this pelease ?

-更新:

到目前为止，我已使该脚本正常工作

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

此方法可以将所有联系人上传到Google联系人

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

但是我现在需要通过电话号码检查联系人是否存在，如果存在，请更新该联系人，如果没有更改以跳过该联系人，否则我会在Google联系人中重复

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

推荐答案

使用事件对象向yrss添加新行时，您可以更新联系人

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);
}

这篇关于如何将Google表格数据库与Google通讯录同步的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！