问题描述
关于脚本编写，我不是菜鸟，所以请记住这一点.:-)我希望我的脚本从Google工作表中读取，并检查该联系人是否存在于Google联系人下，如果不创建该联系人.通过电子邮件检查联系人，并标记为客户".我无法获得if语句来确认联系人是否存在.如果我删除了If如果要检查联系人，它将为每个条目创建联系人，因此我认为该部分很好，但是我需要修复该部分如何检查联系人是否已存在，以便不会创建重复的条目.
I'm noob regarding scripting so keep that in mind. :-)I want my script to read from google sheet and and check if that contact exist under google contacts and if not to create one.Contacts are checked by email and have label "Client". I can't get if statement to confirm if contact exist or not. If i remove If for checking contacts it will create contact for every single entry so i think that that part is fine, but i need to fix part how to check if contact already exists so it wouldn't create duplicated entry.
function addClinet() {
var ss = SpreadsheetApp.openById('XXXX');
var sheetNew = ss.getSheetByName('NewClient');
var Avals = sheetNew.getRange('B1:B').getValues();
var lastRow = Avals.filter(String).length;
for (var i = 2 ; i <= lastRow; i++){
var nameID = sheetNew.getRange(i, 2).getValue();
var emailID = sheetNew.getRange(i, 8).getValue();
var mobID = sheetNew.getRange(i, 9).getValue();
var firstName = nameID.split(' ').slice(0, -1).join(' ');
var lastName = nameID.split(' ').slice(-1).join(' ');
var regex = new RegExp (/^\w/);
var firstChar = regex.exec(mobID);
var contacts = ContactsApp.getContact(emailID);
if (contacts == null){
if (firstChar == 8){
var mobID = 'xxx' + mobID;
}
var contact = ContactsApp.createContact(firstName,lastName, emailID);
var contacts = ContactsApp.getContact(emailID);
contact.addPhone(ContactsApp.Field.WORK_PHONE, mobID);
var group = ContactsApp.getContactGroup("Clients");
group.addContact(contact);
}
}
}
Thx
推荐答案
我不会使用 ContactsApp.getContact([email])函数-出于任何原因，Google Apps脚本的联系人都可以通过电子邮件太慢了.由于听起来好像您在任何给定时间都在对许多联系人进行排序，所以我建议您使用相同的联系人-而不是通过Google Apps脚本搜索电子邮件地址(此过程大约需要16到20秒的时间))
I wouldn't use the ContactsApp.getContact([email]) function -- For whatever reason Google Apps Script's contacts search by email is excruciatingly slow. Since it sounds like you have a number of contacts that you are sorting through at any given time, I would recommend you use the same -- instead of searching for the email address through Google Apps Script (this takes about 16-20 seconds PER CONTACT)
使用以下功能，您将能够为所有联系人创建一个大型JSON对象，并将其电子邮件地址作为键，以便您可以快速测试联系人中是否存在电子邮件地址(这大约需要11秒钟)大约5000个联系人:
Using the following function you will be able to create one large JSON object of all of your contacts, with their email addresses as the key so you can quickly test whether an email address is present in your contacts (this takes about 11 seconds for around 5000 contacts:
function emailsasJSON() {
var emailjson = {}
var myContacts = ContactsApp.getContactGroup('Clients').getContacts();
for (var i = 0; i < myContacts.length; i++) {
var emails = myContacts[i].getEmails();
var phonesobj = myContacts[i].getPhones();
var phones = {}
for (var j = 0; j < phonesobj.length; j++) {
phones[phonesobj[j].getPhoneNumber().replace(/[_)(\s.-]/g,'')] = 1;
}
for (var j = 0; j < emails.length; j++) {
emailjson[emails[j].getAddress().toLowerCase()] = {id: myContacts[i].getId(), phones: phones};
}
}
Logger.log(JSON.stringify(emailjson))
return emailjson;
}
使用emailjson对象，您可以更快地比较每个联系人-它将在大约10秒钟内创建它-我们将在以后使用.
Using the emailjson object you can compare each of your contacts MUCH faster -- It will create this in about 10 seconds -- we will use this later.
第二，您的代码中有一些内容需要清理-在我看来，您的工作表的名称在B列中，电子邮件在H列中，而移动电话在I列中.
Secondly, there are some things in your code that I would clean up -- it looks to me like you have a sheet with the name in column B, email in column H, and mobile number in column I.
您应该将整个数据集作为一个数组收集，然后以这种方式使用:
Instead of collecting all of those values individually per cell (takes a long time), you should collect the entire data set as an array and then work with it that way:
function addClinet() {
var ss = SpreadsheetApp.openById('XXXX');
var sheetNew = ss.getSheetByName('NewClient');
var clientsgroup = ContactsApp.getContactGroup('Clients')
//this is where we will insert the function from above to get the emailjson obj
var emailjson = emailsasJSON()
var contactarray = sheetNew.getDataRange().getValues();
for (var i = 1 ; i < contactarray.length; i++){
var name = contactarray[i][1]
var email = contactarray[i][7]
var phone = contactarray[i][8]
if(emailjson[email.toLowerCase()].id) { //check if email exists
if(!emailjson[email.toLowerCase()]['phones'][phone.replace(/[_)(\s.-]/g,'')]) { //if email exists but phone doesn't, add phone
ContactsApp.getContactById(emailjson[email.toLowerCase()].id).addPhone(ContactsApp.Field.MOBILE_PHONE, phone)
emailjson[email.toLowerCase()]['phones'][phone.replace(/[_)(\s.-]/g,'')] = 1; //add it to the emailjson object in case there are more iterations of this contact in the sheet
}
} else { //add new contact if it doesn't exist
var newcontact = ContactsApp.createContact(name.split(' ')[0],name.split(' ')[1], email)
newcontact.addPhone(ContactsApp.Field.MOBILE_PHONE, phone)
emailjson[email.toLowerCase()]['id'] = newcontact.getId();
emailjson[email.toLowerCase()]['phones'][phone.toString().replace(/[_)(\s.-]/g,'')] = 1;
clientsgroup.addContact(newcontact)
}
}
}
我没有您的数据表可对此进行错误检查，但这将大大提高您的功能.让我知道它是否抛出任何错误，或者，如果您可以给我一个示例表，我可以对其进行测试.
I don't have your datasheet to error check this but this should speed up your function by a considerable amount. Let me know if it throws any errors or, if you could give me an example sheet, I could test it.
最后，我想这不是您要不断更新的客户列表，因此我会将其从此工作表中移除，然后将其移至其他位置，尽管要花费大量的联系人来使用此功能下来.
Lastly, I imagine that this isn't a client list that you consistently update, so I would take them off of this sheet and move them elsewhere, although it would take a considerable number of contacts on the list to bog this function down.
这篇关于使用"ContactsApp.getContact"检查联系人是否存在于Google联系人下.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！