dinsdag 9 november 2021

Sharing Chrome bookmarks but not all of them with #GoogleAppsScript

It is possible to export Chrome bookmarks  to a html file with the name bookmarks_dd-mm-yyyy.html:

  • select the three vertical points at the top right 
  • select bookmarks
  • select bookmark manager
  • select the three vertical points at the top right
  • select export bookmarks

The Chrome bookmarks can be imported somewhere else via the html file.

I do want to share bookmarks but not all of them. Only bookmarks stored in some specific  bookmark folders. That is possible [what not ;-)] with Google Apps Script.

Bookmark folder names need to be unique. 

It took some time before I understood that 'new lines' [\n] had to be added.

function bookmarx2Share(){

var bookmarkFolders = [["parentFolder_1","childFolder_11","childFolder_12","childFolder_13"],["parentFolder_2","childFolder_21","childFolder_22","childFolder_23"]];

var html = "";
html += '<!DOCTYPE NETSCAPE-Bookmark-file-1>\n';
html += '<!-- This is an automatically generated file.\n';
html += 'It will be read and overwritten.\n';
html += 'DO NOT EDIT! -->\n';
html += '<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=UTF-8">\n';
html += '<TITLE>Bookmarks</TITLE>\n';
html += '<H1>Bookmarks</H1>\n';

  html += '<DL><p>\n';
  

  for (var b=0;b< bookmarkFolders.length;b++){

    var bookmarkFolder = bookmarkFolders[b]; 

    html += '<DT><H3 ADD_DATE="" LAST_MODIFIED=">' + bookmarkFolder[0] + '</H3>\n';
    html += '<DL><p>\n'

    for (var c=1;c< bookmarkFolder.length;c++){

      var bookmarkSubFolder = bookmarkFolder[c]; 

      if(bookmarkSubFolder !== ""){

        var bookmarxPerSubFolder = bookmarxPerFolder_(bookmarkSubFolder); 

        html += '<DT><H3 ADD_DATE="" LAST_MODIFIED=">' + bookmarkSubFolder + '</H3>\n';
        html += '<DL><p>\n';

          for(var d=0;d<bookmarxPerSubFolder.length;d++){

            var bookmark = bookmarxPerSubFolder[d];

            html += '<DT><A HREF="' + bookmark[1] +  '" ADD_DATE="" ICON="">' +  bookmark[0] +  '</A>\n';

          }
        html += '</DL><p>\n';
        
      }
    }
  html += '</DL><p>\n';
  }
  
  html += '</DL><p>\n';
  
var bookmarxFile = DriveApp.createFile('Bookmarx',html,"text/html");

var emails = ContactsApp.getContactGroup("BOOKMARK_CONTACTS").getContacts().map(function(contact){return contact.getEmails()[0].getAddress()});

GmailApp.sendEmail(emails,"Bookmarx shared with you","",{attachments: [bookmarxFile.getAs(MimeType.HTML)]})

}



function bookmarxPerFolder_(folder) {

  var html = "bookmarks_dd-mm-yyyy.html"; 
  
  var folderPlus = ">" + folder + "<"; 
   
  var bookmarxArray =[];
  var files = DriveApp.getFilesByName(html);

  while(files.hasNext()){

    var file = files.next();
  
    var bookmarx = file.getBlob().getDataAsString().split(folderPlus)[1].split("</DL><p>")[0].split(/<DT><A HREF="/);
        
    for(var b=1;b<bookmarx.length;b++){

     var bookmark = bookmarx[b];

     var textBookmark = bookmark.split(/<\/A>/)[0].split(/">/)[1].trim();
     var urlBookmark = bookmark.split(/"/)[0].trim();
                    
     bookmarxArray.push([textBookmark,urlBookmark])
    }
  } 
  
  return bookmarxArray.sort()
}

zaterdag 30 januari 2021

Travel advice #GoogleAppsScript

 


I would like to send clients a travel advice (url) but I don't know how to do that with the Maps Service.

Luckely the website of the 'Nederlandse Spoorwegen' offers a possibility to do what I want to do (with some Google Apps Script). 

function travelAdvice(arriveTime,labelOrigin,labelDestination){

var geoOrigin = geoCodeAddress(labelOrigin);
var geoDestination = geoCodeAddress(labelDestination);

var urlTravelAdvice = "https://www.ns.nl/reisplanner/#/?"
urlTravelAdvice += "vertrek=" + geoOrigin;
urlTravelAdvice += "&vertrektype=geo&vertreklabel=" + labelOrigin;
urlTravelAdvice += "&aankomst=" + geoDestination;
urlTravelAdvice += "&aankomsttype=geo&aankomstlabel="+ labelDestination;
urlTravelAdvice += "&type=aankomst&tijd=" + arriveTime; //yyyy-MM-ddThh:mm

return travelAdvice;
}

function geoCodeAddress(address){
/*
https://developers.google.com/apps-script/reference/maps/geocoder#geocode(String)
*/
  if(address !== ""){
  var response = Maps.newGeocoder().geocode(address);
    for (var i = 0; i < response.results.length; i++) {
      var result = response.results[i];
    }
  }
return result.geometry.location.lat + "," +result.geometry.location.lng;
}

Updated: 31-01-2021

Dynamic dependent dropdown data validation lists #GoogleAppsScript

Recently I revisited 'my' script for labeling court decisions with dynamic dependent dropdown data validation lists.

The new script is a little bit less slow than the old one.

function onEdit(){
 
var ss=SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Data");
var data = ss.getSheetByName("Skelet").getDataRange().getValues();

dataValidation_(sheet,data,1);
dataValidation_(sheet,data,2);
dataValidation_(sheet,data,3);
dataValidation_(sheet,data,4);
dataValidation_(sheet,data,5);
}


function dataValidation_(sheet,data,dropDownLevel){
 
var dropDowns = 6;
var columnsBeforeDropDowns = 6;
var columnNumber = dropDownLevel + columnsBeforeDropDowns;
 
/*
See documentation for the difference between  
*/
var currentCell = sheet.getCurrentCell();
 
  if(currentCell.getColumn() == columnNumber && currentCell.getRow()>1){
   
/*
The validation in the cell on the right side of the current cell is dependent of the content of the current cell.
If the content of the current cell changes the content of the cell*S* on the right side of the current cell and their validations have to be cleared before the first cell on the right side of the current cell gets its new content and validation.
*/
   sheet.getRange(currentCell.getRow(),columnNumber+1,1,dropDowns).clearContent().clearDataValidations();
   
/*
The content of the current cell en the content of the cell*S* on the left side of the currect cell (if any) are used to filter the 'data' in the sheet '****' in order to find the validation for the cell on the right side of the current cell.
First (do not ask me why) the content of the current cell is used to filter the data.
Next the content of the cell*S* on the left side of the current cell (if any) are used to filter the data.
Look out: mind the difference between the index of a row and the number of the dropDownLevel
Look out: the content of the last dropDownLevel is not needed to find a validation
*/
   var filteredData = data.filter(function(row){return row[dropDownLevel-1] == currentCell.offset(0, 0).getValue()})
   for(var d=0;d<dropDownLevel-1;d++){
     filteredData.filter(function(row){return row[d] == currentCell.offset(0,d-dropDownLevel+1).getValue()})
   }

/*
The result of filtering the data are rows with more than one element.
What is needed is the content of the elements on the right side of the element with the content of the current cell
*/

   var dropDownArray = filteredData.map(function(row){return row[dropDownLevel]});
   
     if(dropDownArray[0]){
     var validationRule = SpreadsheetApp.newDataValidation().requireValueInList(dropDownArray).build();
     currentCell.offset(0,1).setDataValidation(validationRule);
   }  
 }
}

dinsdag 10 november 2020

How to randomize an array of persons with #GoogleAppsScript #DIY

 For a 'breakout room' or so ....

function randomizeAnArray(){

    var participantsColumn = "a number";
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName("SHEETNAME");
    var participants = sheet.getRange(2,participantsColumn,sheet.getLastRow(),1)
    .getValues()
    .filter(function(participant){return participant !== "";})
    .map(function(participant){return [Math.random(),participant]});
    
    return participants.sort()

}

maandag 9 november 2020

Third thursdays a month a year with #GoogleAppsScript

I am organising - I would like to organise - something every third thursday a month a year. So how to collect the dates of all those third thursdays.

 
function thirdThursdaysArray(year){
 
 var thirdThursdaysAMonthAYear = [];
 
 var daysAMonth = [31,28,31,30,31,30,31,31,30,31,30,31];
 
   for(var i=0;i< daysAMonth.length;i++){
  
   var thirdThursdaysAMonth = [];
   
     for(var j=0;j<daysAMonth[i];j++){
     
       var month = i;
       var day = j+1;
              
         if(new Date(year,month,day).getDay() == 4){
         
           thirdThursdaysAMonth.push(new Date(year,month,day));
           
         }
     }
     
     thirdThursdaysAMonthAYear.push(thirdThursdaysAMonth[2]);
   }
 
 return thirdThursdaysAMonthAYear;
 
 }

zaterdag 23 mei 2020

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 ;-).

dinsdag 5 mei 2020

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?