My first addon: draft mails in Gmail & events in Calendar

At my workplace 'project' names (names of dossiers) are anchors for automating tasks. They are used for merging, time tracking, (gmail)labels, folder names, file names, calendar events, emails etcetera.

My first addon is another way to use 'project' names in Google calendar to add an event and in Gmail to make a draft email. The 'project' names are stored in a Google spreadsheet and used in a dropdown menu in the addon. The same goes for specified event locations and event descriptions.

CardService Class SelectionInput want the input as an object.

function getObject(column,ssid,sheetname){
  
  var ss = SpreadsheetApp.openById(ssid);
  var sheet = ss.getSheetByName(sheetname);
  var array = sheet.getRange(column + "1:" + column).getValues().map(function(row){return row[0]}).filter(function(row){return row!== ""}).sort();
  
  var object = {"key":""};
  
  for(var a=0;a<array.length;a++){
    object["key"+ a]=array[a];
  }
  return object;
}


function getWidgetDropDown(title,fieldname,object){

  var widget = CardService.newSelectionInput()
  .setType(CardService.SelectionInputType.DROPDOWN)
  .setTitle(title)
  .setFieldName(fieldname);
  
  for(var o in object){
    widget.addItem(object[o],object[o],false); 
  }
    
  return widget;
  
}

Other widgets I am using are the DateTimePicker and the TextButton. 

function getWidgetDateTimePicker(title,fieldname){
  
  var widget = CardService.newDateTimePicker()
  .setTitle(title)
  .setFieldName(fieldname);
  
}


function getWidgetTextButton(bgcolorrgb,text,functionname){
  
  var widget = CardService.newTextButton()
  .setTextButtonStyle(CardService.TextButtonStyle.FILLED)
  .setBackgroundColor(bgcolorrgb)
  .setText(text)
  .setOnClickAction(CardService.newAction().setFunctionName(functionname));
  
  return widget;
  
}

At last but not least the functions with which the addon card, the draft email and the event are created.

 

function buildCard(){
  
 var card=CardService.newCardBuilder();
  
 var widgetProjectName = getWidgetDropDown("Kies een zaaknaam","project_name",getObject("A",IDGMAKWRK(),"HLP_ZAAKNAMEN"));
 card.addSection(CardService.newCardSection().addWidget(widgetProjectName));

 var widgetDraftMail = getWidgetTextButton("#ff0000","Concept email","createDraftMail");
 card.addSection(CardService.newCardSection().addWidget(widgetDraftMail));
 
 
 var widgetEventStartDateTime = CardService.newDateTimePicker().setTitle("Kies startdatum").setFieldName("event_start");
 var widgetEventDuration = CardService.newTextInput().setTitle("Kies duur in minuten").setFieldName("event_duration"); 
 var widgetEventLocation = getWidgetDropDown("Kies een locatie","event_location",getObject("A",IDGMAKWRK(),"HLP_LOCATIE"));
 var widgetEventDescription = getWidgetDropDown("Kies een beschrijving","event_description",getObject("A",IDGMAKWRK(),"HLP_EVENT"));
 var widgetEvent = getWidgetTextButton("#ff0000","Afspraak","createEvent");
 card.addSection(CardService.newCardSection().addWidget(widgetEventStartDateTime).addWidget(widgetEventDuration).addWidget(widgetEventLocation).addWidget(widgetEventDescription).addWidget(widgetEvent)); 
 
 return card.build();
  
}


function createDraftMail(e){
  
  var projectName = e.formInput.project_name;
  
  if(projectName == undefined){
    
    return CardService.newActionResponseBuilder().setNotification(CardService.newNotification().setText("Zaaknaam niet ingevuld")).build();
    
  }
  else{
  
  CardService.newComposeActionResponseBuilder().setGmailDraft(GmailApp.createDraft("",projectName,"")).build();
    
  }
  
}


function createEvent(e){
  
  var projectName = e.formInput.project_name;
  var startDate =e.formInput.event_start.msSinceEpoch;Logger.log(startDate)
  var eventDuration = e.formInput.event_duration;
  var endDate = startDate + (eventDuration * 60 * 1000); Logger.log(endDate)
  var location = e.formInput.event_location;
  var description = e.formInput.event_description;
    
  if(projectName == undefined || startDate == undefined || eventDuration == undefined || startDate > endDate){
    
    return CardService.newActionResponseBuilder().setNotification(CardService.newNotification().setText("Zaaknaam en/of email en/of tijd en/of duur niet [goed] ingevuld")).build();
    
  }
  else{
    
   var calendar = CalendarApp.getCalendarById(MIJNEMAIL());
   calendar.createEvent(projectName, new Date(startDate), new Date(endDate),{description:description,location: location}); 
    
  }
    
}

Also this time I should have started with reading the documentation on the Card Service first ;-).

Contacts, enums for extended contact fields, enums for enums for extended contact fields & Google Apps Script. HELP

It took some time before I understood how I had to do it, adding extended fields to a contact in Google Contacts:

function pieterPukOne(){
 
  ContactsApp.getContact("puk@pieterpuk.xyz").addCustomField(ContactsApp.ExtendedField.SENSITIVITY,"CONFIDENTIAL");
  ContactsApp.getContact("puk@pieterpuk.xyz").addCustomField(ContactsApp.ExtendedField.GENDER,"MALE");
  
}

The first problem is dat I can not see the newly added custom fields in the contact information of the contact Pieter Puk.

The second problem is the result of 'searching for' contacts by custom field:

function pieterPukTwo(){
 
  Logger.log(ContactsApp.getContact("puk@pieterpuk.xyz").getCustomFields().map(function(customField){return customField.getValue()}));
  Logger.log(ContactsApp.getContact("puk@pieterpuk.xyz").getCustomFields(ContactsApp.ExtendedField.GENDER)[0].getValue());
  Logger.log(ContactsApp.getContactsByCustomField("puk pieter#no problem at all","Zaaknaam").map(function(iets){return iets.getFullName()}));
  Logger.log(ContactsApp.getContactsByCustomField("CONFIDENTIAL","SENSITIVITY").map(function(customField){return customField.getFullName()}));
  Logger.log(ContactsApp.getContactsByCustomField("MALE","GENDER").map(function(customField){return customField.getFullName()}));
 
}

Strange:

Contact Pieter Puk has three custom fields but the only 'custom field' returned with ContactsApp.getContactsByCustomField is 'Zaaknaam'.

I do not understand what I am doing wrong

The third problem is that I do not know what can be done with for example the extended field SENSITIVITY.CONFIDENTIAL.

Does it limit the way contact information can be used?

How to make a Projects & Tasks Manager with Google Apps Script

I borrowed 'my' Projects & Tasks Manager from Ilya Mikhelson because of his use of the Google Spreadsheet's formula Sparkline() for the timeline of the manager.  The formula is not the easiest one. The 'onion method' of Ben Collins helped me to understand it.

=SPARKLINE({INT(B4)-INT($B$2);INT(C4)-INT(B4)};
    {"charttype"\"bar";"color1"\"white";"color2"\IF(E4="Done";"LemonChiffon";
        IF(today()>C4;"OrangeRed";
            IF(AND(today()>B4;today()<C4;E4="To do");"Orange";
                IF(AND(today()>B4;today()<C4);"Yellow";"Chartreuse"))));
                    "max"\INT($C$2)-INT($B$2)})

'My' Projects & Tasks Manager makes a folder and a (gmail) label on spreadsheet and sheet (project) level. It also makes a contact group on sheet (project) level.

Every project (sheet tab, some sheet rows, folder) has its own color.

The Projects & Tasks Manager can be downloaded via this link. Make a copy and check the script before you use it (via the menu for which a trigger must be added)

My 'learning moments':

• the data part of the SPARKLINE formula {INT(B4)-INT($B$2);INT(C4)-INT(B4)}
• Session.getActiveUserLocale() and the LanguageApp to replace the name of the first sheet which name is in the language of the user
• Math.floor((Math.random() * n) + 1) to choose a color for the project.

Other sources of inspiration:

• Change Google Drive Folder color with Apps Script, Stéphane Giron, 3 may 2019
• G Suite Solutions — Apps Script Powered Workflow Automation, Laura Taylor, 12 March 2020

UPDATED 05-05-2020