zondag 24 november 2019

Adding data from spreadsheet to custom fields in Google Contacts with GAS

At my work data of clients, opposing parties, courts, third parties and cases (among which case names) are stored in a spreadsheet. The spreadsheet is the source for contacts in Google Contacts.

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