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()

}

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;
 
 }

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

Importing a (part of a) PDF schedule into Google Calendar with #GoogleAppsScript

Twice a year I get a schedule like the schedule above. It is part of a PDF document. Every row is a 'shift' in which asylum applications are processed at Airport Schiphol in the so called border procedure. In every 'shift' there are 5 days for a lawyer.

How to get all these days into my calendar.

I copy the schedule and paste it into a mail to myself with $$$$$ in the subject.

An then:

function shiftToCalendar(){
  
  var kindOfShiftDays = [[0,"dagmineen"],[1,"a&c eerste gehoor"],[2,"a&c nader gehoor"],[3,"zienswijze"],[4,"beschikking"]]
  var threads = GmailApp.search("newer_than:1d from:me subject:$$$$$");
  var message = threads[0].getMessages()[0];
  
  if(message){
    
  var shifts = message.getBody().split(/A11608/g); 
    
    for(var d=1;d<shifts.length;d++){
      
      var shift = shifts[d];
      
      if(/AC /.test(dienst) && shift.match(/\d{1,2}\-\d{1,2}\-\d{4}/gm) !== null){
            
        var shiftDays = shift.match(/\d{1,2}\-\d{1,2}\-\d{4}/gm);
        
            for(var e=0;e<shiftDays.length;e++){
             
              var hlpShiftDay = shiftDays[e].split(/\-/g);
              var shiftDay = new Date(hlpShiftDay[2],hlpShiftDay[1]-1,hlpShiftDay[0]); 
              
              for(var f=0;f<kindOfShiftDays.length;f++){
                  
                if(kindOfShiftDays[f][0] == e){var what = kindOfShiftDays[f][1]}
                 
                  }
            
              CalendarApp.getCalendarById(MIJNKALENDER()).createAllDayEvent(what,shiftDay).setColor(d);
            
            }
        }
    }  
  
    message.moveToTrash();
  }  
}

Get the number of the week with #GoogleAppsScript

Javascript does not have a method to get the number of the week.

It seems that the fourth of January always is in the first week of a year. If that is correct GAS can help to get the number of the week we are living in.

Very interesting to know.

function getWeekNumber(){
  
  var thisYear = (new Date()).getFullYear();
  var fourthDayOfTheYear = new Date(thisYear,0,4);
  
  var dayFirstMonday = fourthDayOfTheYear.getDate() + 8 - fourthDayOfTheYear.getDay();
  var dateFirstMonday = new Date(thisYear,0,dayFirstMonday);
  var msFirstMonday = dateFirstMonday.getTime(); 
  
  var msDateToday = (new Date()).getTime(); 
  var msOneWeek = 7*24*60*60*1000; 

  for(var i=0;i<52;i++){
    
    var msNextMonday = msFirstMonday + (i * msOneWeek);
    var msComingNextMonday = msNextMonday + msOneWeek;
    
    if(msNextMonday <= msDateToday && msDateToday < msComingNextMonday){
      
      var weekNumber = i+2;
      
    }
  }
  return weekNumber;
}  

Combining Google Meet and Google Voice outside the US, Canada

In times of social distancing communicating with a detained person for the communication with whom an interpreter is needed is difficult. Because of rules a detained person can not be called but has to call me and an interpreter can not call me but has to be called.

With Google Meet someone can call in into a (non) video meeting but with Google Meet it is not possible to call a number that is not a number from the US or Canada.

Luckily there is Voicemeeter Banana an Advanced Audio Mixer Application endowed with Virtual Audio Device used as Virtual I/O to mix and manage any audio sources from or to any audio devices or applications (in their words).

With the audio mixer you can mix-minus (yourself and) Google Meet and (a softphone like) Google Voice. And it works!

Google Meet audio settings:

Google Voice audio settings:

A Walk A Day Keeps The Doctor Away #GoogleAppsScript

function aWalkADayKeepsTheDoctorAway(){

 var file = DriveApp.getFilesByName("yyyyy").next(); //csv file with latitude/longitude points
 var fileBlob = file.getAs("text/csv");

 var fileData = Utilities.parseCsv(fileBlob.getDataAsString(), ",");
 var points = fileData.filter(function(row,index){return index>2}).map(function(row){return row[4].split(/geo:/)[1].split(",")}); 
  
 var directions = Maps.newDirectionFinder().setMode(Maps.DirectionFinder.Mode.WALKING).setOrigin(points[0]).setDestination(points[points.length-1]);
  for(var p=1;p<points.length;p++){
    directions.addWaypoint(points[p]);
  }
  
  var route = directions.getDirections().routes[0];
  var map = Maps.newStaticMap().addPath(route.overview_polyline.points);
 
  var distance  = route.legs.map(function(leg){return leg.duration.value}).reduce(function(accumulator,currentValue){return accumulator + currentValue;})/1000;
  
  var email = Session.getActiveUser().getEmail();
  MailApp.sendEmail
  (email,
  'A walk a day keeps the doctor away: ' + distance + " km",'Please open: ' + map.getMapUrl() + '&key=YOUR_API_KEY', 
    {
      htmlBody: '<br/><img src="cid:mapImage">',inlineImages: {mapImage: Utilities.newBlob(map.getMapImage(), 'image/png')                                                                }
    }
  );
  
}

How to 'manage' templated spreadsheets from a standalone script with GAS

A few weeks ago I came across this tweet of @bennettscience in which Brian E Bennet asks if there is a way to re-create a templete so that templated spreadsheets all point to one master script that can be accessed by all.

I tried to let a spreadsheet 'communicate' with a standalone apps script (with help of a doPost() function) but without succes and a lot of frustrations (yes I DID  (re)deploy the script, at least a 100.000 times).

Then I realised that  if the mountain won't come to Muhammad, then Muhammad must go to the mountain. If a standalone script can not be accessed by templated spreadsheets (at least by me)  then why not let the standalone script access the templated spreadsheets instead?

If the id's of the templated spreadsheets are stored in an array (by script or by hand) they can be 'looped' in and by the standalone apps script (and 'triggered').

If the stand alone apps script is turned into an webapp, a templated spreadsheet can 'order' the standalone apps script to do this or to do that by chosing it's name and the name of the script that has to be executed in a dropdown menues.

Something like this:

function doGet(){
return HtmlService.createHtmlOutputFromFile('html').setTitle("Do something with the spreadsheet of your choice");
}



//fromClient

function fromClientObject(object){
   
  Logger.log(object);
  if(object.ssname !== "" && object.functionname !== ""){
  Logger.log(getSsId(object))
   var ssid = getSsId(object);
   execFunction(object,ssid); 
  
  }
} 




//toClient

function toClientSsNames(){

var ssFilesNamesArray = [];
var ssData = getSsData();
  
  for(var i=0;i<ssData.length;i++){
    var ssName = ssData[i][0];
    ssFilesNamesArray.push(ssName);        
  }  
Logger.log(ssFilesNamesArray)
return ssFilesNamesArray;
}



function toClientFunctionNames(){
  
var scripts = [];

  for(k in this){
    if(/ForOne/.test(k)){
    scripts.push(k);
    }
  }  
Logger.log(scripts)
return scripts
}



//functions for all

function doSomethingForAll(){
  
  var ssData = getSsData();
 
  for(var i=0;i<ssData.length;i++){
    var ssId = ssData[i][1];
    var ss = SpreadsheetApp.openById(ssId);
    var firstSheet = ss.getSheets()[0];
    firstSheet.getRange("A1").setValue("Hello World");     
  }
}


function appendRowForAll(){
  //time trigger
  var now = new Date();
  var day = now.getDate();
  
  var ssData = getSsData();
 
  for(var i=0;i<ssData.length;i++){
    var ssId = ssData[i][1];
    var ss = SpreadsheetApp.openById(ssId);
    var firstSheet = ss.getSheets()[0];
    var newRow = [now,day];
    firstSheet.appendRow(newRow);
  }
}



//functions for one spreadsheet

function doSomethingForOne(ssid){

  var ss = SpreadsheetApp.openById(ssid);
  var firstSheet = ss.getSheets()[0];
  firstSheet.getRange("A2").setValue("Hello New World");
}



function doSomethingElseForOne(ssid){

  var ss = SpreadsheetApp.openById(ssid);
  var firstSheet = ss.getSheets()[0];
  firstSheet.getRange("A3").setValue("Hello Newest World");
}




//helper functions

function getSsId(object){
  
  var ssData = getSsData();
  var ssName = object.ssname;
  
  return ssData.filter(function(element){return element[0] == ssName})[0][1];

}


function execFunction(object,ssid){
  
  var functionName = object.functionname;

  for(var k in this){
    if(k == functionName){
    this[k](ssid);
    }
  }  
}



function getSsData(){

var ssFilesDataArray = [];
  
var ssFolderId = "XXXX";
var ssFiles = DriveApp.getFolderById(ssFolderId).getFiles();

  while(ssFiles.hasNext()){
     var ssFile = ssFiles.next();
     var ssFileId = ssFile.getId();
     var ssFileName = ssFile.getName();
     var ssFileMimeType = ssFile.getMimeType();
     if(ssFileMimeType == "application/vnd.google-apps.spreadsheet"){
       ssFilesDataArray.push([ssFileName,ssFileId]);
     }
  }
return ssFilesDataArray; 
}

and this:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    
    <link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css">

    <script>
    
    function dropDownDatalist(array,datalistid){
      for(var i=0;i<array.length;i++){
      var option = document.createElement("option");
      option.text = array[i];
      option.setAttribute("value", option.text);
      document.getElementById(datalistid).appendChild(option);
      }
    }
    
    function dropDownArraySsNames(array){
      var datalistid = "datalistssnames"; 
      dropDownDatalist(array,datalistid);
    }
    google.script.run.withSuccessHandler(dropDownArraySsNames).toClientSsNames();
    
    function dropDownArrayFunctionNames(array){
      var datalistid = "datalistfunctionnames"; 
      dropDownDatalist(array,datalistid);
    }
    google.script.run.withSuccessHandler(dropDownArrayFunctionNames).toClientFunctionNames();
    
    </script>
    
    
  </head>
  <body>
  
  <div class="w3-container w3-margin">
  
  <div class="w3-xxlarge">
  Do something with a spreadsheet of your choice
  </div>
    
  <div class="w3-panel w3-leftbar w3-border-black">
  <div class="w3-xlarge">
  Spreadsheet of your choice
  </div>
    
  <p></p>
  <input id="ssnames" list="datalistssnames"  required />
  <datalist id="datalistssnames">
  </datalist>
  <label for="datalistssnames">Spreadsheet of your choice</label>
  </div>
  
  <div class="w3-panel w3-leftbar w3-border-black">
  <div class="w3-xlarge">
  Function of your choice
  </div>
    
  <p></p>
  <input id="functionnames" list="datalistfunctionnames"  required />
  <datalist id="datalistfunctionnames">
  </datalist>
  <label for="datalistfunctionnames">Function of your choice</label>
  </div>
    
  <p></p>
  <button id="button" class="w3-button w3-black">Click!</button>
  
  
  <script>

      document.getElementById("button").addEventListener("click",doSomething);
      
      function doSomething(){
      
        var object = {};
             
        object.ssname = document.getElementById("ssnames").value;
        object.functionname = document.getElementById("functionnames").value;
                        
        google.script.run.fromClientObject(object);
    
        var inputs = document.getElementsByTagName("INPUT");
        for(var i=0;i<inputs.length;i++){
        var inputid = inputs[i].id;
        document.getElementById(inputid).value = "";
        }
      }
    
  </script>
  
  </div>  
  </body>
</html>

Accessing Google Apps Scripts (as such) and (re)using them as scripts

For a script I needed a list of (some) other scripts of the same Google Apps Script project. I started to google and as almost always I found an answer at Stack Overflow (this is the answer).

The second question that needed an answer was how to (re)use the listed scripts as scripts. I continued to google and found the (beginning of) an answer here.

It works!

Updated 09-02-2020

function scriptsToArrayToScripts(){

var scripts = [];

  for(k in this){
  
    if(/something/.test(k)){
    
      scripts.push([k,this[k]]);
         
    }
  }
  
  for(var s=0;s<scripts.length;s++){

  var paramOne = "abc";

  var paramTwo = "xyz";
  
     if(scripts[s][0] == "somethingelse"){
  
        scripts[s][1](paramOne,paramTwo);
      
      }
   }   
}

Using GAS Logger for 'creating' variables

Some times information of a file or folder is needed in a script, for example an ID or whatever.  Using Google Apps Script Logger is an easy way to get that information.

(The existence of the folder or file and a unique name of the folder or file is required)

Example one:

function useLoggerToCreateVariablesOne(){

var array = [];

var files = DriveApp.searchFiles('title = "AAA"');
while (files.hasNext()) {
  var file = files.next();
  array.push('var ID_AAA = "' + file.getId() + '"; \n');
}

var folders = DriveApp.searchFolders('title = "BBB"');
while (folders.hasNext()) {
  var folder = folders.next();
  array.push('var ID_BBB = "' + folder.getId()  + '"; \n');
}

var folders = DriveApp.searchFolders('title = "CCC"');
while (folders.hasNext()) {
  var folder = folders.next();
  array.push('var ID_CCC = "' + folder.getId()  + '"; \n\n');
}
  
var myEmail = Session.getActiveUser();
  array.push('var MY_EMAIL = "' + myEmail  + '"; \n');


Logger.log(array.toString().replace(/,/g,""))

/*
[20-02-05 08:42:02:163 CET] var ID_AAA = "aaaaa"; 
var ID_BBB = "bbbbb"; 
var ID_CCC = "ccccc"; 

var MY_EMAIL = "xxx@xxxxxxxxxx.nl"; 
*/

//Copy and past (somewhere else):

var ID_AAA = "aaaaa"; 
var ID_BBB = "bbbbb"; 
var ID_CCC = "ccccc"; 

var MY_EMAIL = "xxx@xxxxxxxxxx.nl";

}

Example two:

function useLoggerToCreateVariablesTwo(){

var query = "mimeType contains 'application/vnd.adobe.xfdf' and title contains '_form'";

var file = DriveApp.searchFiles(query).next();
var fileId = file.getId();Logger.log(file.getName())
var fileContent = file.getBlob().getDataAsString();

var modified = fileContent.split(/modified="/)[1].split(/"/)[0].toString();
var original = fileContent.split(/original="/)[1].split(/"/)[0].toString();
var formName = fileContent.split(/f href="/)[1].split(/"/)[0].toString();

Logger.log('var MODIFIED = "' + modified + '";\n' + 'var ORIGINAL = "' + original + '";\n' + 'var FORMNAME = "' + formName + '";');

DriveApp.getFileById(fileId).setTrashed(true);

/*
[20-02-05 08:26:43:771 CET] var MODIFIED = "xxxxx";
var ORIGINAL = "yyyyy";
var FORMNAME = "zzzzz.pdf";
*/

//Copy and past (somewhere else):

var MODIFIED = "xxxxx";
var ORIGINAL = "yyyyy";
var FORMNAME = "zzzzz.pdf";
}

Merging PDF form fields with spreadsheet data using Google Apps Script

At work I sometimes have to fill in PDF forms which is boring and  time consuming. Today I found a way to fix that.

It needs PDF software that makes it possible to import (data)fields into a PDF form and to import and export fields data.  I used the pro version of PDF Studio.

It also needs a Google Spreadsheet-sheet with a first row with 'fieldnames'.

I imported (data)fields (the Google Spreadsheet-sheet fieldnames) into a PDF form.

To learn how to import data into the (data)fields of the PDF-form I first had to export data into a xfdf-file (the export option I choose for).

The (xml-like) xfdf-file has a head, a trunk and a tail,

The head contains (among other things) the name of the PDF file and a modified-something and a original something.

The trunk contains information about the fieldnames and the values.

The tail contains ... the tail.

function pdfFormMerging(){

var ss = SpreadsheetApp.openById("XXX");
var sheet = ss.getSheetByName("YYY");
var range = sheet.getRange(1,1,sheet.getMaxRows(),sheet.getMaxColumns());
var data = range.getValues();
var uniqueAnchor = "ZZZ";
var object = {};

object = makesObjectOfRowInSpreadsheetWithUniqueAnchor(object,uniqueAnchor,data);

var modified = "aaa";
var original = "bbb";
var formName = "ccc.pdf";
var fileName = "ddd.xfdf"

//the head
var text = '<?xml version="1.0" encoding="UTF-8"?>';
text += '<xfdf xml:space="preserve" xmlns="http://ns.adobe.com/xfdf/">';
text += '<f href="' + formName + '"/>';
text += '<ids modified="' + modified + '" original="' + original + '"/>';
text += '<fields>';

//the trunk
  for(var k in object){
    text += '<field name="' + k + '"><value>' + object[k] + '</value></field>';
  }

//the tail
text += '</fields>';
text += '</xfdf>';

DriveApp.createFile(fileName,text,"text/xml"); 
}


function makesObjectOfRowInSpreadsheetWithUniqueAnchor(rowObject,uniqueAnchor,data){
  
var fieldNames = data[0];
 
  for(var i=0;i<data.length;i++){
    
    if(data[i].indexOf(uniqueAnchor) !== -1){ 
     
      for(var j=0;j<fieldNames.length;j++){
      
        if(fieldNames[j] !== ""){
          rowObject[fieldNames[j]] = data[i][j];   
        }
      }
    }
  }
  return rowObject;
} 

Javascript method reduce()

• JavaScript Array reduce() Method, w3schools
• Simplify your JavaScript – Use .map(), .reduce(), and .filter(), Etienne Talbot
• Write JavaScript loops using map, filter, reduce and find, Flavop Copes
• JavaScript’s Reduce Method Explained By Going On a Diet
• One reduce() to rule them all — How to use reduce in JavaScript, Kristian Poslek
• Javascript `reduce` 101, Igor Irianto

It took some reading before I started understanding the Javascript method reduce ().

According to w3schools:

• The reduce() method reduces the array to a single value.
• The reduce() method executes a provided function for each value of the array (from left-to-right).
• The return value of the function is stored in an accumulator (result/total).

A simple example of the method reduce():

function testOne(){

var serviceFee = 1000;
var invoice = [serviceFee,21*serviceFee/100];

  var result = invoice.reduce(function(accumulator,currentValue){
    return accumulator + currentValue;
  },0)
//1210.0
}

The single value to which an array is reduced can also be a text.

function testTwo(){

var nameArray = ["Mariette","Timmer"];

  var result =  nameArray.reduce(function(accumulator,currentValue){
    return accumulator + currentValue;
  })
//MarietteTimmer
}

The single value to which an array is reduced can even be an array (updated 12-01-2020 13:17).

function testThree(){

var arrayOne = ["Aap","Noot","Mies","Wim","Zus","Jet"];

  var result =  arrayOne.reduce(function(accumulator,currentValue){
    if(currentValue.length > 3){
      accumulator.push(currentValue);
    }
    return accumulator;
  },[])
//[Noot, Mies]
}

My own imagination seems to be the limit.