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