For some reason names of cases are used as subject in Gmail. I use(d) Google Apps Script to add case names to Google Contacts. It took a lot of trial and error.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 | function caseNamesToContacts(){
var sheetName = "XXXXX";
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName(sheetName);
var range = sheet.getRange(1,1,sheet.getMaxRows(),sheet.getMaxColumns());
var data = range.getValues();
var indexCaseName = data[0].indexOf("casename");
var caseNames = data.map(function(row){return row[indexCaseName]}).sort().filter(function(cel){return cel !== "";});
for(var z=1;z<caseNames.length;z++){
var caseName = caseNames[z];
//if client data are in Google Contacts the Id of the Google (client) Contact (field name = "contactidclient") is stored in the spreadsheet
var idContactClient = searchCellValue(data,"casename","contactidclient",caseName);
if(idContactClient){
var uniqueNickNameClient = searchCellValue(data,"casename","uniquenicknameclient",caseName);
if(contactCaseNames(uniqueNickNameClient) !== undefined && contactCaseNames(uniqueNickNameClient).indexOf(caseName) == -1){
var clientContacts = ContactsApp.getContactGroup("Clients").getContacts();
var clientContact = clContacten.filter(function(contact){return contact.getNickname() == uniqueNickNameClient})[0].addCustomField("Case name",caseName);
}
}
}
}
function contactCaseNames(uniqueNickNameClient){
var clientContacts = ContactsApp.getContactGroup("Clients").getContacts();
var clientContact = clientContacts.filter(function(clientContact){return clientContact.getNickname() == uniqueNickNameClient;})[0];
if(clientContact){
var caseNameValues = contact.getCustomFields("Case name").map(function(caseName){return caseName.getValue()});
return caseNameValues;
}
return undefined
}
function searchCellValue(data,anchorFieldName,searchFieldName,anchorValue){
var indexAnchorFieldName = data[0].indexOf(anchorFieldName);
var indexSearchFieldName = data[0].indexOf(searchFieldName);
for (var d=1; d < data.length;d++){
if(data[d].indexOf(anchorValue) !== -1){
var result = data[d][indexSearchFieldName];
}
}
return result;
}
|
Geen opmerkingen:
Een reactie posten