zaterdag 28 december 2019

Webapp: data from client to server to client with GAS

Finally I understand how to send data from a client to a server and get  (other) data returned from the server at the same time.

At the server side:


function fromAndToClient(dataFromClient){

//do something with dataFromClient

return dataToClient
}

At the client side:


<script>

document.getElementById("button").addEventListener("click",doSomething);

function doSomething(){

var dataFromClient = document.getElementById("idElement").value;
  
google.script.run.withSuccessHandler(fromServer).fromAndToClient(dataFromClient);
    
}
  
  
function fromServer(dataToClient){

  // do something with dataToClient

}
  
</script>
Gepost door op Geen opmerkingen:

vrijdag 27 december 2019

Inserting an emoji into a Google Spreadsheets sheet with a GAS made custom function

I came across (?) a tweet of @TheSheetsGuy on how to insert an emoji into a Google Spreadsheets sheet using Windows 10.

I don't use Windows so how to insert an emoji into a Google Spreadsheets sheet using Chrome OS?

With a GAS made custom function.


function EMO(number){
var emojies = [[1,"9993"],[2,"9994"],[3,"9995"],[4,"9996"],[5,"9889"]];
  for(var e=0;e<emojies.length;e++){ 
    if(emojies[e][0] == number){
      return String.fromCharCode(emojies[e][1]);
    }
  }
}

Sources:

Gepost door op Geen opmerkingen:

dinsdag 24 december 2019

Adding new tasks to a Google Tasks list with Google Apps Script

At work all files are send to a central folder named Postbus. In the folder Postbus the files are renamed and send to a folder into which they belong. Almost all done by GAS-script(s).

The new name of the file of a file contains a task if necessary:
case name-yymmdd-letter in tttyymmdd do something
A GAS-script collect the tasks and (1) send  an email once a week with all the tasks and (2) send an email the day before a task has to be done. Since a week or so a GAS-script adds te tasks also to Google Tasks. I now can see the tasks in Google Calender which is very handy.

function tasksToTasksList(){

var bestanden = DriveApp.searchFiles('title contains "ttt"'); 
  while (files.hasNext()){
     var file = files.next();
     var fileName = file.getName();
     var fileDescription = file.getDescription();
      
     if(/ttt\d\d\d\d\d\d/.test(fileName) && /toTasksList/.test(fileDescription) ==false){
      
        var taskDate = YYMMDD_TO_DATE(/\d\d\d\d\d\d/.exec(fileName.split(/ttt/)[1]));
        var caseName = fileName.split(/-\d{6}-/)[0]; 
        var taskWhat =  fileName.split(/ttt\d{6}/)[1].split(".")[0]; 
        var timeZone = Session.getScriptTimeZone();
        
        if(taskDate.getTime() >= (new Date).getTime()){
        
          var task = {
          title: caseName + " " + taskWhat,
          due: Utilities.formatDate(taskDate,timeZone,"yyyy-MM-dd'T'HH:mm:ss'Z'")
          };
          
          Tasks.Tasks.insert(task,hlptasksToTasksList());
          
          file.setDescription("toTasksList_" + DATE_TO_YYMMDD(new Date()));       } 
     } 
  }
}

function hlptasksToTasksList(){

var lists = Tasks.Tasklists.list().items;

  for(var i=0;i<lists.length;i++){
  
    if(lists[i].title == NAME_TASKSLIST){
    
      var idTasksList = lists[i].id;
      
    }
  }
  return idTasksList;
}

function YYMMDD_TO_DATE(yymmdd){
var dateString = yymmdd.toString(); 
var date = new Date(2000 + Number(datumstring.slice(0,2)),Number(dateString.slice(2,4))-1,Number(datumstring.slice(4,6)));
return date;  
}


function DATE_TO_YYMMDD(date){
var timeZone = Session.getScriptTimeZone();
return Utilities.formatDate(new Date(datum),timeZone, "yyMMdd");
}








Sources:













Gepost door



op





Geen opmerkingen:
  









































Advancing insight (Adding data from spreadsheet to custom fields in Google Contacts with GAS)










Advancing insight [voortschrijdend inzicht] made me change the script described in Adding data from spreadsheet to custom fields in Google Contacts with GAS.



More efficient.








function zaaknamenNaarContacten(){

  var ss = SpreadsheetApp.openById(ID_SS);
  var sheet = ss.getSheetByName("XXXX");
  var range = sheet.getRange(1,1,sheet.getMaxRows(),sheet.getMaxColumns());
  var data = range.getValues();
  var veldnamen = data[0];
  
  var contacten = ContactsApp.getContactGroup("Clienten").getContacts();
  
  for(var c=0;c<contacten.length;c++){
  
    var contact = contacten[c];
    var alfa = contact.getNickname();
    var zaaknamen = contact.getCustomFields("Zaaknaam").map(function(zaaknaam){return zaaknaam.getValue()});
    
    if(alfa !== ""){
    
     hlpZaaknamenNaarContacten(data,veldnamen,contact,alfa,zaaknamen);
    
    }
  }
}


function hlpZaaknamenNaarContacten(data,veldnamen,contact,alfa,zaaknamen){
  
  for(var d=0;d<data.length;d++){
  
  var row = data[d];
  var zaaknaam = row[veldnamen.indexOf("zaaknaam")];
  var contactidcl = row[veldnamen.indexOf("contactidcl")];
  var alfacl = row[veldnamen.indexOf("zaakalfacl")];
          
  if(contactidcl !== "" && alfacl == alfa && zaaknamen !== undefined && zaaknamen.indexOf(zaaknaam) == -1){  
    contact.addCustomField("Zaaknaam",zaaknaam);  
  }
  else if(contactidcl !== "" && alfacl == alfa && zaaknamen == undefined){  
    contact.addCustomField("Zaaknaam",zaaknaam);  
  }   
 }
}

















Gepost door



op





Geen opmerkingen:
  

































          

        

          

        

vrijdag 6 december 2019


          

        









Get weekday name from a date in the language of your choice










Until I read this blog  about getting a weekday name from a date, I used a summer time/winter time script I wrote a few months ago.








 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


var GMT_TIMES = [[2019,[2019,02,31],[2019,9,27]],[2020,[2020,02,29],[2020,9,25]]];

function gmtSummerAndWinterTime(){
  
  var today = new Date(); 
  var thisYear = today.getFullYear(); 
  var todayNumber = today.getTime();
  
  for(var g=0;g<GMT_TIMES.length;g++){
   
    var gmtTijd = GMT_TIMES[g]; 
    var dateSummerTimeNumber = new Date(GMT_TIMES[1][0],GMT_TIMES[1][1],GMT_TIMES[1][2]).getTime(); 
    var dateWinterTimeNumber = new Date(GMT_TIMES[2][0],GMT_TIMES[2][1],GMT_TIMES[2][2]).getTime();
         
    if(GMT_TIMES[0] == thisYear && dateSummerTimeNumber < todayNumber && todayNumber < dateWinterTimeNumber){
      var gmt = "GMT+2"; 
      return gmt;
    }
    else{
    
      gmt = "GMT+1";
      return gmt
    }
  }
}










The Session thing is so much easier.



'Problem' with formatDate(date,timeZone,format)  in combination with  SimpleDateFormat class is that it is in English.  There are people using other languages than English.



The Google Language Service could be used to solve the 'problem'.



I prefer the following 'solution'.










1
2
3
4
5
6
7
8


function getWeekDayName(){
  var weekdayNames = ["zondag","maandag","dinsdag","woensdag","donderdag","vrijdag","zaterdag"];  
  var today = new Date();
  var timeZone = Session.getScriptTimeZone(); 
  var weekdayNumber = Utilities.formatDate(today,timeZone,"u");
  var weekdayName = weekdayNames[weekdayNumber];
  Logger.log('Weekday name is: ' + weekdayName);
}












Resources:

Get weekday name from a date,  Phil Bainbridge, 2 december 2019









Gepost door



op





Geen opmerkingen:
  










































Renaming files with Google Apps Script


















At my work  files have to be renamed often.



Before I entered the world of GAS webb apps I used a Google Spreadsheet to rename files by combining values from in-cell dropdown listst, in-cell date values etcetera. The only problem was that I (1) had to copy the file name AND (2) paste it into the name of the file that had to be renamed.



This week I made a webb app for renaming files & moving the files to the folder into which they belong.



The format of the file names is:



zaaknaam-jjmmdd-soort document toelichting jjmmdd toelichting taak

case name-yymmdd-document type short description yymmdd short description task



Files are collected in the post box folder (POSTBUS). To rename the proper file it has to have a unique name. GAS takes care of that (11-20).



The following arrays are 'imported' into the html web page via GAS from somewhere else:


    

  • array with the names of the files in the post box
    • 

  • array with case names (names of case folders)
    • 

  • array with document 'types' (email to, email from, letter to, letter from, etcetera)
    • 









 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
56
57
58
59
60
61
62
63
64
65
66
67


function doGet(){
return HtmlService.createHtmlOutputFromFile('html');
}


function toClientPostbusbestanden(){
var postbusbestandenArray = []; 
  
var postbusFiles = DriveApp.getFolderById(ID_POSTBUS).getFiles();
  
while (postbusFiles.hasNext()) {
var postbusFile = postbusFiles.next();
var postbusFileName = postbusFile.getName();
  if(postbusbestandenArray.indexOf(postbusFileName) == -1){
    postbusbestandenArray.push(postbusFileName);
  }
  else{
    postbusFile.setName(postbusFileName + "_" + new Date().getTime())
    postbusbestandenArray.push(postbusFile.getName())
  }
}
if(postbusbestandenArray.length < 1){postbusFileNames.push("dit is geen bestand in de folder Postbus maar een scripthelper")}  
  
return postbusbestandenArray.sort();
}


function toClientZaaknamen(){
  
  var zaaknamenArray = [];
  
  var cache = CacheService.getScriptCache();
  var cached = cache.get("zaaknamenCache");
  if (cached != null) {
    return cached.split(",").sort();
  }
  
  var foldersinFolderZAKEN = DriveApp.getFolderById(ID_ZAKEN).getFolders();
  while (foldersinFolderZAKEN.hasNext()) {
    var folderinFolderZAKEN = foldersinFolderZAKEN.next();
    zaaknamenArray.push(folderinFolderZAKEN.getName())
  }
  
  cache.put("zaaknamen", zaaknamenArray, 1500);
  
  return zaaknamenArray.sort();
}


function toClientSoortDocumenten(){
  
  var soortDocumentenArray = [];
  
  var doc = DocumentApp.openById(ID_HLP_DOCUMENT);
  var tabellen =  doc.getBody().getTables();
  
  var tabel = tabellen[HLP_SOORT_DOCUMENT];
  
  var tabelLengte = tabel.getNumRows();
  
  for(var t=0;t<tabelLengte;t++){
    
    soortDocumentenArray.push(tabel.getCell(t,0).getText());
    
  }
  return soortDocumentenArray
}












I am using (dropdown)  datalists because the arrays are very long.








  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
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122


<!DOCTYPE html>
<html>
<head>
<base target="_top">

<link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css">

<script>
function dropDownPostbusbestandenDoc(postbusbestanden) {
for(var i=0;i<postbusbestanden.length;i++){
var option = document.createElement("option");
option.text = postbusbestanden[i];
option.setAttribute("value", option.text);
document.getElementById("datalistpostbusbestanddoc").appendChild(option);
}
}
google.script.run.withSuccessHandler(dropDownPostbusbestandenDoc).toClientPostbusbestanden();

function dropDownZaaknamen(zaaknamen) {
for(var i=0;i<zaaknamen.length;i++){
var option = document.createElement("option");
option.text = zaaknamen[i];
option.setAttribute("value", option.text);
document.getElementById("datalistzaaknaamdoc").appendChild(option);
}
}
google.script.run.withSuccessHandler(dropDownZaaknamen).toClientZaaknamen();

function dropDownSoortDocumenten(soortdocumenten) {
for(var i=0;i<soortdocumenten.length;i++){
var option = document.createElement("option");
option.text = soortdocumenten[i];
option.setAttribute("value", option.text);
document.getElementById("datalistsoortdocument").appendChild(option);
}
}
google.script.run.withSuccessHandler(dropDownSoortDocumenten).toClientSoortDocumenten();

</script>

</head>

<body>

<div class="w3-container w3-margin">

<div class="w3-panel w3-lime">

<div class="w3-xxlarge">
Hernoem document
</div>

<p></p>
<input id="inputpostbusbestanddoc" list="datalistpostbusbestanddoc">
<datalist id="datalistpostbusbestanddoc">
</datalist>
<label for="datalistpostbusbestanddoc">Kies een te hernoemen document</label>

<p></p>
<input id="inputzaaknaamdoc" list="datalistzaaknaamdoc">
<datalist id="datalistzaaknaamdoc">
</datalist>
<label for="datalistzaaknaamdoc">Kies een zaaknaam</label>

<p></p>
<input id="dddocument" type="date">
<label for="dddocument">Dagtekening document</label>

<p></p>
<input id="inputsoortdocument" list="datalistsoortdocument">
<datalist id="datalistsoortdocument">
</datalist>
<label for="datalistsoortdocument">Soort document</label>

<p></p>
<input id= "toelichtingdocument" type="text">
<label for="toelichtingdocument">Toelichting op soort document</label>

<p></p>
<input id="ddtermijnzitting" type="date">
<label for="ddtermijnzitting">Datum termijn of zitting</label>

<p></p>
<input id= "toelichtingtermijnzitting" type="text">
<label for="toelichtingtermijnzitting">Toelichting op termijn of datum strafzaak</label>

<p></p>
<button id="buttondoc" class="w3-button w3-black">Hernoem document</button>

<script>

document.getElementById("buttondoc").addEventListener("click",doSomethingDoc);

function doSomethingDoc() {
  
  var object = {};
  object.postbusbestandsnaam = document.getElementById("inputpostbusbestanddoc").value;
  object.zaaknaam = document.getElementById("inputzaaknaamdoc").value;
  object.datumdocument = document.getElementById("dddocument").value;
  object.soortdocument = document.getElementById("inputsoortdocument").value;
  object.toelichtingdocument = document.getElementById("toelichtingdocument").value;
  object.datumtermijn = document.getElementById("ddtermijnzitting").value;
  object.toelichtingtermijn = document.getElementById("toelichtingtermijnzitting").value;
  
  google.script.run.fromClientObjectDocument(object);
  
  document.getElementById("inputpostbusbestanddoc").value = "";
  document.getElementById("inputzaaknaamdoc").value= "";
  document.getElementById("dddocument").value= "";
  document.getElementById("inputsoortdocument").value= "";
  document.getElementById("toelichtingdocument").value= "";
  document.getElementById("ddtermijnzitting").value= "";
  document.getElementById("toelichtingtermijnzitting").value= "";
}

</script>

</div>
</div>

</body>
</html>










An object with input data is 'exported' from the html page to the apps script.












 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


function fromClientObjectDocument(object){
   
    if(object && object.zaaknaam && object.datumdocument && object.soortdocument){
    
    var timeZone = Session.getScriptTimeZone();
    var jjmmddDagtekening = Utilities.formatDate(new Date(object.datumdocument), timeZone,"yyMMdd");
    
      if(new Date(object.datumtermijn)){
      var jjmmddTermijn = " ttt" + Utilities.formatDate(new Date(object.datumtermijn), timeZone,"yyMMdd");
      var toelichtingTermijn = object.toelichtingtermijn;  
      }
      else{
        var jjmmddTermijn = ""; 
        var toelichtingTermijn = ""; 
      }
      
      var nieuweBestandsnaam = object.zaaknaam + "-" + jjmmddDagtekening + "-"; 
      nieuweBestandsnaam += object.soortdocument + " " + object.toelichtingdocument;
      nieuweBestandsnaam += jjmmddTermijn + " " + toelichtingTermijn + ".pdf"; 
      
      var postbusFiles = DriveApp.getFolderById(ID_POSTBUS).getFiles();
      while (postbusFiles.hasNext()) {
        var postbusFile = postbusFiles.next();
        
        if(postbusFile.getName().indexOf(object.postbusbestandsnaam) !== -1){
        postbusFile.setName(nieuweBestandsnaam);
          
          var zaakFolders = DriveApp.getFoldersByName(object.zaaknaam);
          while (zaakFolders.hasNext()) {
            var zaakFolder = zaakFolders.next();
            var idZaakFolder = zaakFolder.getId();
            DriveApp.getFolderById(idZaakFolder).addFile(postbusFile);
            DriveApp.getFolderById(ID_POSTBUS).removeFile(postbusFile);
          }
        }
      }
    }
  }





















Gepost door



op





Geen opmerkingen:
  

































          

        

          

        

vrijdag 29 november 2019


          

        









My first WebApp: a html-dropdown-menu populated with values from a Google Spreadsheet














Finally I discovered how to get data from a Google Spreadsheet into a GAS WebApp.



A very very very basic dropdown menu populated with names of cases which names are used as anchor for almost everything at my work so they must be accessible 24/7.











The script and the html:








 1
 2
 3
 4
 5
 6
 7
 8
 9
10


function doGet(){
return HtmlService.createHtmlOutputFromFile('Html');
}

function ssCaseNames(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Zaken");
  var caseNames = sheet.getRange("C2:C").getValues().map(function(cel){return cel[0]});
  return caseNames;
}



















 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


<!DOCTYPE html>
<html>
<head>
<base target="_top">
<script>
function dropDown(caseNames) {
for(var c=0;c<caseNames.length;c++){
var option = document.createElement("option");
option.text = caseNames[c];
option.setAttribute("value", option.text);
document.getElementById("element").appendChild(option);
}
}
google.script.run.withSuccessHandler(dropDown).ssCaseNames();
</script>
</head>
<body>

<input list="element">
<datalist id="element">
</datalist>

</body>
</html>

















Gepost door



op





Geen opmerkingen:
  

































          

        

          

        

woensdag 27 november 2019


          

        










Multidimensional array in Javascript; Comparing running times (slow, slower, slowst)










Today GAS filled 100.000 cells in a fresh & fruity Google Spreadsheet with some random stuff. It took some time before I realised that the variable row must be in the first loop and not before the loops.










 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19


function fillSlowSlowerSlowst(){
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();  
  var sheet = ss.getSheetByName("SlowSlowerSlowst");
  
  var data = [];
  
  for(var h=1;h<1001;h++){
    var row = [];
    for (var i=1; i<101;i++){
      
      row.push(Math.random() * h * i);
      
    }
    
    data.push(row);
  }
  sheet.getRange(1,1,data.length,data[0].length).setValues(data);
}










Then GAS was running the next script, which was inspired by this blog of Dariusz Kuśnierek, every 5 minutes for a few hours.










 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


function countSlowSlowerSlowst(){
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();  
  var sheet = ss.getSheetByName("SlowSlowerSlowst");
  var sheetB = ss.getSheetByName("SlowSlowerSlowstVervolg");
  var seconds = []
  
  seconds.push((new Date()).getTime());
  
  var rangeGetLast = sheet.getRange(1,1,sheet.getLastRow(),sheet.getLastColumn());
  var dataGetLast = rangeGetLast.getValues();
  
  seconds.push((new Date()).getTime());
  
  var rangeGetDataRage = sheet.getDataRange();
  var dataGetDatarange = rangeGetDataRage.getValues();
  
  seconds.push((new Date()).getTime());
  
  var rangeGetMax = sheet.getRange(1,1,sheet.getMaxRows(),sheet.getMaxColumns());
  var dataGetMax = rangeGetMax.getValues();
  
  seconds.push((new Date()).getTime());
      
  sheetB.appendRow(seconds)
  
}










And the winners are the siblings getMaxRows() & getMaxColumns()











Sources:

How to keep track of Google Apps Script’s total execution time, Dariusz Kuśnierek

Google Spreadsheet Formulas (Minus, Average)









Gepost door



op





Geen opmerkingen:
  

































          

        

          

        

dinsdag 26 november 2019


          

        










Slow, slower, slowest Google Apps Scripts










The spreadsheet that I am using at work is getting slower and slower.  The choice for a kind of  (GAS) scripting might be a reason for that.



Three options to get the values of a sheet in a Google Spreadsheet.










 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15


function slowSlowerSlowst(){
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();  
  var sheet = ss.getSheetByName("XXXXX");
  
  var rangeGetLast = sheet.getRange(1,1,sheet.getLastRow(),sheet.getLastColumn());
  var dataGetLast = rangeGetLast.getValues();
  
  var rangeGetDataRage = sheet.getDataRange();
  var dataGetDatarange = rangeGetDataRage.getValues();
  
  var rangeGetMax = sheet.getRange(1,1,sheet.getMaxRows(),sheet.getMaxColumns());
  var dataGetMax = rangeGetMax.getValues();
  
}










The results are a kind of shocking









The first option takes 47,984 seconds (4,637 + 21,05 + 22,297).

The second option takes 68,623 seconds (48,01 + 20,613).

The third option takes 22,597 seconds.



Another try.









 The first option takes 57,701 seconds (15 + 20,689 + 22,0).

 The second option takes 60,701 seconds (40,408 + 20,293).

 The third option takes 21,297 seconds.



Since I am using a 'anchor'  cell to get acces to a row in my Google Spreadsheet empty rows and columns are not a issue.



I do not understand why the moment of executing a script makes difference.









Gepost door



op





Geen opmerkingen:
  

































          

        

          

        

zondag 24 november 2019


          

        










Legal Aid administration and Google Apps Script


















Legal Aid administration used to be time consuming.



If a certificate for Legal Aid is applied for (?) something has to be done, if a certificate is issued something has te be done, if a certicate is invoiced (?) something has to be done, if a compensation is determined (?) something has to be done, if a compensation is paid something has to be done.



With Google Apps Script it is a piece of cake (blogs that will be published):




    

  • Part 1: Creating an XML document (with data from Google Spreadsheet & GAS);
    • 

  • Part 2: Renaming a file in Google Drive (with Google Spreadsheet & GAS);
    • 

  • Part 3: Creating a Google Document (with data from Google Spreadsheet & GAS);
    • 











Gepost door



op





Geen opmerkingen:
  









































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

















Gepost door



op





Geen opmerkingen:
  

































          

        

          

        

zondag 17 november 2019


          

        









RSS-feed on GAS with GAS










To get grip on Google Apps Script (GAS) I have started to collect information on GAS in a Google spreadsheet by scraping websites, blogs and emails.



The intention is to import the information, provided with relevant labels, into a Google document.



For now I have decided to make a RSS-feed of the information with help of this tutorial of Amit Agarwal (I don't know if the sort function works properly)










 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


DATA_HEADER = ["today","date","author","title","twitter-hashtag","link"];
RSS_TITEL ="RSS GAS en Javascript";
RSS_DESCRIPTION = "Collectie van blogs met informatie over Google Apps Script en Javascript";
RSS_LINK = "https://scriptjes.blogspot.com/";

function doGet(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Data");  
  var eersterij = sheet.getLastRow() - 14;
  var bereik = sheet.getRange(eersterij,1,15,6).getValues();
  
  bereik.sort(function (a, b){return a[0] - b[0];}).reverse();
  
  var xml = '<rss version="2.0">';
  xml += '<channel>';
  xml += '<title>' + RSS_TITEL + '</title>';
  xml += '<description>' + RSS_DESCRIPTION + '</description>';
  xml += '<link>' + RSS_LINK + '</link>';
  
  for (var i=0;i<bereik.length;i++) {
    xml += '<item>';
    xml += '<title><![CDATA[' + bereik[i][3] + ']]></title>';
    xml += '<link>' + bereik[i][5] + '</link>';
    xml += '<pubDate>' + bereik[i][1].toUTCString() + '</pubDate>';
    xml += '</item>';
  }
  
  xml += '</channel>';
  xml += '</rss>';
  
  return ContentService.createTextOutput(xml).setMimeType(ContentService.MimeType.RSS);
}










Source: Digital Inspiration, Parse RSS Feeds with Google Apps Script














Gepost door



op





Geen opmerkingen:
  

































          

        

          

        

vrijdag 15 november 2019


          

        









split() here, split() there, split() everywhere










This morning I woke up and thougth that there must a better way (then this one) to scrape large strings. While walking on my way to my work (more than an hour) I had my eureka moment.



It works, at least in case of unique splitter-strings.






function testStringSplitter(){
  
  var url = "https://www.raadvanstate.nl/uitspraken/@117938/201809034-1-v3/";
  var myNotSoFavoriteString = UrlFetchApp.fetch(url).getContentText(); 
  var splitters = [["advocaat te Den Haag",1],["eisen van artikel 2:16, eerste lid, van de Awb",1],["verklaart het hoger beroep ",1],[";",0]]
  
  var decision = stringSplitter(myNotSoFavoriteString,splitters);
  
  //decision = gegrond (founded) 
  
}


function stringSplitter(string,splitters) {
  var s=0;
  while(s<splitters.length){
    var splitter = splitters[s];
    string = string.split(splitter[0])[splitter[1]];
    s++
  }
  return string;
}






My client was already released.









Gepost door



op





Geen opmerkingen:
  

































          

        

          

        

donderdag 14 november 2019


          

        










'Knowledge' Management Part 5 (connecting label-list-items and court decisions)




















This is the result.



Court decisions are connected to labels-list-items or vice versa. I don't know anymore what I have been doing here. It works however.









 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
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71


var naamSkelet = "Skelet";
var aantalKolommen = 5;
var dropDownEersteKolom = 7;
var indexDropDownEersteKolom = dropDownEersteKolom - 1;
var indexDropDownLaatsteKolom = dropDownEersteKolom + aantalKolommen;

function ImportsCaseLawInDocument(){
  var ss = SpreadsheetApp.openById(idSS); 
  var sheet = ss.getSheetByName("Data");
  var range = sheet.getRange(1,1,sheet.getMaxRows(),sheet.getMaxColumns());
  var data = range.getValues(); 
  
  for(var d=1;d<data.length;d++){
    var row = data[d];
        
    if(row[1]){
      var datum = ddKort(row[1]);
      var kenmerk = row[2];
      if(row[3]){var ecli = row[3];}else{var ecli = kenmerk;}
      var link = row[4];
      var instantie = row[5]; 
      var inhoudsindicatie = row[6];
      var tekst = inhoudsindicatie + " [" + instantie +  " " + datum +  " " + ecli +  " " + kenmerk +  "]";  
      var searchArray = [];
      
      var s= indexDropDownEersteKolom; 
      while(s<indexDropDownLaatsteKolom && row[s] !== ""){
        searchArray.push(row[s]);
        s++
      }
      
      if(searchArray){
        hlpImportsCaseLawInDocument(searchArray,tekst,ecli,link);
      }
    }
  }  
}

function hlpImportsCaseLawInDocument(searchArray,tekst,ecli,link){
  var doc = DocumentApp.getActiveDocument();
  var body = doc.getBody();
  var children = body.getListItems().filter(function(child){
    return child.getText() == searchArray[searchArray.length-1];
  });    
  
  for(var i=0;i<children.length;i++){
    var child = children[i]; 
    var resultArray = zoektVoorouders(child);
    
    if(comparesArrays(searchArray,resultArray) == true){
      var childIndex = body.getChildIndex(child);
      var childNestingLevel = body.getChild(childIndex).asListItem().getNestingLevel(); 
      var usp = body.insertListItem(childIndex+1,tekst).setNestingLevel(childNestingLevel + 1);
      addHyperlink(usp,ecli,link);
    }
  }
}

function comparesArrays(arrayeen,arraytwee){
  if(arrayeen.length !== arraytwee.length){
   return false; 
  }
  var i=0;
  while(i<arrayeen.length){
   if (arrayeen[i] !== arraytwee[i]){
   return false;  
   }
  i++  
  }
  return true;
}

















Gepost door



op





Geen opmerkingen:
  

































        

      









Abonneren op:
Posts (Atom)