/**There must be a better way to do it but I do not yet know how*/: november 2019

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:

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

<!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>

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.

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.

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)

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.

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.

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

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.

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

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)

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

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.

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.

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

'Knowledge' Management Part 4 (finding ancestors of a list item in a Google document)

This is the result.

Here I learned how to find ancestors of a list item in a Google document

voorouders = ancestors, kind = child, ouder = parent, grootouder = grandparent, overgrootouder = great grandparent, overovergrootouder = ?.

function zoektVoorouders(kind){
  var kindText = kind.getText();
  var array = [kindText];
  
  var ouder = getParent(kind); 
  if(ouder !== null){
    var ouderText = ouder.getText();
    array.push(ouderText);  
    
    var grootouder = getParent(ouder); 
    if(grootouder !== null){
      var grootouderText = grootouder.getText();
      array.push(grootouderText);
      
      var overgrootouder = getParent(grootouder);
      if(overgrootouder !== null){
        var overgrootouderText = overgrootouder.getText();
        array.push(overgrootouderText);
        
        var overovergrootouder = getParent(overgrootouder);
        if(overovergrootouder !== null){
          var overovergrootouderText = overovergrootouder.getText();
          array.push(overovergrootouderText);  
          
        }
      }
    }
  }
  return array.reverse();
}

function getParent(item) {
  if(item.getType() == DocumentApp.ElementType.LIST_ITEM && item.getNestingLevel() == 0){
    var sibling = item.getPreviousSibling(); 
    while (sibling) {
      if (sibling.getType() == DocumentApp.ElementType.PARAGRAPH) {
        return sibling;
      }
      sibling = sibling.getPreviousSibling(); 
    } 
  }
  if(item.getType() == DocumentApp.ElementType.LIST_ITEM && item.getNestingLevel() !== 0){
    var sibling = item.getPreviousSibling();
    while (sibling) {
      if (sibling.getNestingLevel() < item.getNestingLevel()) {
        return sibling;
      }
      sibling = sibling.getPreviousSibling(); 
    }
  } 
  return null;
}

'Knowledge' Management Part 3 (importing labels als list items in a Google document )

This is the result.

Labels of court decisions are imported as list item in a Google document. I don't know anymore what I have been doing here. It works however.

var idSS = "XXXXX";
var docnaam = "XXXXX ";
var doctitel = "XXXXX ";

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

var caseLawStyle = {};
caseLawStyle[DocumentApp.Attribute.FONT_SIZE] = 8;
caseLawStyle[DocumentApp.Attribute.FONT_FAMILY] = "Verdana"; 
caseLawStyle[DocumentApp.Attribute.FOREGROUND_COLOR] = "#0033cc";

var newCaseLawStyle = {};
newCaseLawStyle[DocumentApp.Attribute.FONT_SIZE] = 8;
newCaseLawStyle[DocumentApp.Attribute.FONT_FAMILY] = "Verdana";  
newCaseLawStyle[DocumentApp.Attribute.FOREGROUND_COLOR] = "#0033cc"; 
newCaseLawStyle[DocumentApp.Attribute.BACKGROUND_COLOR] = "#ccff33"; 

var skeletStyle = {};
skeletStyle[DocumentApp.Attribute.FONT_SIZE] = 9;
skeletStyle[DocumentApp.Attribute.FONT_FAMILY] = "Verdana";  
skeletStyle[DocumentApp.Attribute.FOREGROUND_COLOR] = "#330000";

function makesSkelet(){
  var datumvandaag = ddLang(new Date());
  var datumvandaagkort = ddKortGMAK(new Date());
  
  var document = DocumentApp.getActiveDocument();
  document.setName(docnaam + datumvandaagkort);
  var body = document.getBody();
  
  var ss = SpreadsheetApp.openById(idSS);
  var dataSheet = ss.getSheetByName("Data");
  var skeletSheet = ss.getSheetByName(naamSkelet);
  var labels = skeletSheet.getRange(1,1,skeletSheet.getLastRow(),aantalKolommen).getValues();
  
  body.appendParagraph(doctitel + datumvandaag).setAlignment(DocumentApp.HorizontalAlignment.CENTER).setAttributes(skeletStyle);
  body.appendParagraph("");
  
  var uniekeOuders = uniekeElementen(labels,0); 
  for(var i=0;i<uniekeOuders.length;i++){
    var ouder = uniekeOuders[i];
    body.appendParagraph("");
    var paragraafnaam = body.appendParagraph(ouder);
        
    var opOuderGefilterd = labels.filter(function(label){
      return label[0] === uniekeOuders[i];
    });
    var uniekeKinderen = uniekeElementen(opOuderGefilterd,1); 
    for(var j=0;j<uniekeKinderen.length;j++){
      var kind = uniekeKinderen[j];
      if(kind){
        var listKind = body.appendListItem(kind).setNestingLevel(0);
        var listKindId = listKind.getListId();
      }
      
      var opKindGefilterd = opOuderGefilterd.filter(function(fEen){
        return fEen[1] === uniekeKinderen[j];
      }); 
      var uniekeKleinkinderen = uniekeElementen(opKindGefilterd,2); 
      for(var k=0;k<uniekeKleinkinderen.length;k++){
        var kleinkind = uniekeKleinkinderen[k];
        if(kleinkind){
          var listKleinkind = body.appendListItem(kleinkind).setNestingLevel(1).setListId(listKind);
        }
        
        var opKleinkindGefilterd = opKindGefilterd.filter(function(fTwee){
          return fTwee[2] === uniekeKleinkinderen[k];
        }); 
        var uniekeAchterkleinkinderen = uniekeElementen(opKleinkindGefilterd,3); 
        for(var l=0;l<uniekeAchterkleinkinderen.length;l++){
          var achterkleinkind = uniekeAchterkleinkinderen[l]; 
          if(achterkleinkind){
            var listAchterkleinkind = body.appendListItem(achterkleinkind).setNestingLevel(2).setListId(listKind);
          }
          
          var opAchterkleinkindGefilterd = opKleinkindGefilterd.filter(function(fDrie){
            return fDrie[3] === uniekeAchterkleinkinderen[l];
          }); 
          var uniekeAchterachterkleinkinderen = uniekeElementen(opAchterkleinkindGefilterd,4); 
          for(var m=0;m<uniekeAchterkleinkinderen.length;m++){
            var achterachterkleinkind = uniekeAchterachterkleinkinderen[m];
            if(achterachterkleinkind){
              var listAchterachterkleinkind = body.appendListItem(achterachterkleinkind).setNestingLevel(3).setListId(listKind);
            }
            
            var opAchterkleinindGefilterd = opAchterkleinkindGefilterd.filter(function(fVier){
              return fVier[4] === uniekeAchterachterkleinkinderen[m];
            }); 
          }
        }
      }
    }
  }
}

function uniekeElementen(waarden,index){
  var hlpArray =[]; 
  for(var i=0;i<waarden.length;i++) {
    if(hlpArray.indexOf(waarden[i][index]) == -1){
      hlpArray.push(waarden[i][index]);
    }
  }
  return hlpArray;    
}

function ddKortGMAK(datum){
 var datumKort = Utilities.formatDate(new Date(datum), "GMT+0002", "yyMMdd");
return datumKort;
}

function ddLang(datum){
 var datumLang = LanguageApp.translate(Utilities.formatDate(new Date(datum), "GMT+0002", "dd MMMM yyyy"), 'en', 'nl');
return datumLang;  
}

'Knowledge' Management Part 2 (labeling court decisions with dynamic dependent dropdown data validation lists)

This is the result.

The labels, something like the following:

C211

In a Google spreadsheet it looks like this:

Here I learned how to do it Google Sheets Apps Script – Dynamic Dependent Dropdown Data Validation Lists.

The Google Apps Script (column 6 is for a brief description of a court decision):

var naamData = "Data";
var naamSkelet = "Skelet";

var eersteNiveauKolom = 7;
var tweedeNiveauKolom = 8;
var derdeNiveauKolom = 9;
var vierdeNiveauKolom = 10;
var vijfdeNiveauKolom = 11;
var laatsteKolom = 11;

function onEdit(){ 
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var activeCell = ss.getActiveCell(); 
  var val = activeCell.getValue(); 
  var r = activeCell.getRow();
  var c = activeCell.getColumn(); 
  var dataSheetNaam = activeCell.getSheet().getName(); 
  if(dataSheetNaam == naamData && c == eersteNiveauKolom && r>1){    
    applyFirstLevelValidation(val,r);
  }
  else if(dataSheetNaam === naamData && c === tweedeNiveauKolom && r>1){
    applySecondLevelValidation(val,r);
  }
  else if (dataSheetNaam === naamData && c === derdeNiveauKolom && r>1){
    applyThirdLevelValidation(val,r)
  }
  else if(dataSheetNaam === naamData && c === vierdeNiveauKolom && r>1){
    applyFourthLevelValidation(val,r) 
  }
}

function applyFirstLevelValidation(val,r){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var dataSheet = ss.getSheetByName(naamData);
  var skeletSheet = ss.getSheetByName(naamSkelet);
  var labels = skeletSheet.getRange(1,1,skeletSheet.getLastRow(),skeletSheet.getLastColumn()).getValues();
  
  clearCells(dataSheet,r,tweedeNiveauKolom);
  
  if(val !== ""){    
    var filteredLabels = labels.filter(function(label){return label[0] === val});
    
    var listToApply = filteredLabels.map(function(label){return label[1]});
    var cell = dataSheet.getRange(r,tweedeNiveauKolom);
    
    if(listToApply[0]){
      applyValidationToCell(listToApply,cell);
    }
  }
}

function applySecondLevelValidation(val,r){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var dataSheet = ss.getSheetByName(naamData);
  var skeletSheet = ss.getSheetByName(naamSkelet);
  var labels = skeletSheet.getRange(1,1,skeletSheet.getLastRow(),skeletSheet.getLastColumn()).getValues();
  
  clearCells(dataSheet,r,derdeNiveauKolom);
  
  if(val !== ""){   
    var eersteNiveauKolomWaarde = dataSheet.getRange(r,eersteNiveauKolom).getValue(); 
    
    var filteredLabels = labels.filter(function(label){return label[0] === eersteNiveauKolomWaarde && label[1] === val});
    
    var listToApply = filteredLabels.map(function(label){return label[2]}); Logger.log("second level "  + listToApply)
    var cell = dataSheet.getRange(r,derdeNiveauKolom);
    
    if(listToApply[0]){
      applyValidationToCell(listToApply,cell);
    }
  }
}

function applyThirdLevelValidation(val,r){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var dataSheet = ss.getSheetByName(naamData);
  var skeletSheet = ss.getSheetByName(naamSkelet);
  var labels = skeletSheet.getRange(1,1,skeletSheet.getLastRow(),skeletSheet.getLastColumn()).getValues();
  
  clearCells(dataSheet,r,vierdeNiveauKolom);
  
  if(val !== ""){   
    var eersteNiveauKolomWaarde = dataSheet.getRange(r,eersteNiveauKolom).getValue();
    var tweedeNiveauKolomWaarde = dataSheet.getRange(r,tweedeNiveauKolom).getValue();
    
    var filteredLabels = labels.filter(function(label){return label[0] === eersteNiveauKolomWaarde &&  label[1] === tweedeNiveauKolomWaarde  && label[2] === val});
    var listToApply = filteredLabels.map(function(label){return label[3]}); Logger.log("third "  + listToApply)
    var cell = dataSheet.getRange(r,vierdeNiveauKolom);
    
    if(listToApply[0]){
      applyValidationToCell(listToApply,cell);
    }
  }
}

function applyFourthLevelValidation(val,r){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var dataSheet = ss.getSheetByName(naamData);
  var skeletSheet = ss.getSheetByName(naamSkelet);
  var labels = skeletSheet.getRange(1,1,skeletSheet.getLastRow(),skeletSheet.getLastColumn()).getValues();
  
  clearCells(dataSheet,r,vijfdeNiveauKolom);
  
  if(val !== ""){   
    var eersteNiveauKolomWaarde = dataSheet.getRange(r,eersteNiveauKolom).getValue();
    var tweedeNiveauKolomWaarde = dataSheet.getRange(r,tweedeNiveauKolom).getValue();
    var derdeNiveauKolomWaarde = dataSheet.getRange(r,derdeNiveauKolom).getValue();
    
    var filteredLabels = labels.filter(function(label){return label[0] === eersteNiveauKolomWaarde &&  label[1] === tweedeNiveauKolomWaarde && label[2] === derdeNiveauKolomWaarde && label[3] === val});
    var listToApply = filteredLabels.map(function(label){return label[4]});
    var cell = dataSheet.getRange(r,vijfdeNiveauKolom);
    
    if(listToApply[0]){
      applyValidationToCell(listToApply,cell);
    }
  }
}

function clearCells(sheet,r,niveauKolom){
  var aantalKolommen = laatsteKolom - niveauKolom + 1; 
  sheet.getRange(r,niveauKolom,1,aantalKolommen).clearContent();
  sheet.getRange(r,niveauKolom,1,aantalKolommen).clearDataValidations();
}

function applyValidationToCell(list,cell){
  var rule = SpreadsheetApp.newDataValidation()
  .requireValueInList(list)
  .setAllowInvalid(false)
  .build();
  cell.setDataValidation(rule);
}

'Knowledge' Management Part 1 (importing court decisions into a Google spreadsheet)

This is the result.

Google Apps Script (GAS)

imports court decisions (on migration detention) into a Google spreadsheet
adds dependeing dropdown into the Google spreadsheet
imports court decisions into a tree structure in a Google Document.

Somewhere in between I am adding a short description to the court decisions and labeling them.

In this blog I will show the script with which the court decisions are imported into a Google spreadsheet.

I will use the court decisions of the Administrative Jurisdiction Division of the Council of State (Afdeling Bestuursrechtspraak Raad van State) as an example.

function scrapeCourtDecisionsRVS(){
  
  var months = ["januari","februari","maart","april","mei","juni","juli","augustus","september","oktober","november","december"];
  var courtDecisions = [];
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Data");
  var testColumn = sheet.getRange("C2:C").getValues().map(function(cell){return cell[0]});
  
  var url = "https://www.raadvanstate.nl/uitspraken/?zoeken=true&zoeken_term=&pager_rows=100";
  var sourceText = UrlFetchApp.fetch(url).getContentText(); 
  var courtDecisionsData = sourceText.split(/class=siteLink href="https:\/\/www.raadvanstate.nl\/uitspraken\/@/g);
  
  for(var i=1;i<courtDecisionsData.length;i++){
    
    var courtDecisionData = courtDecisionsData[i];
    
    if(/Datum uitspraak/.test(courtDecisionData)){
      var hlpCourtDecisionDate = courtDecisionData.split(/Datum uitspraak/)[1].split(/<dd>/)[1].split(/</)[0].trim().split(/\s/g); 
      var day = Number(hlpCourtDecisionDate[0]);
      var month = Number(months.indexOf(hlpCourtDecisionDate[1]));
      var year = Number(hlpCourtDecisionDate[2]); 
      var courtDecisionDate = Utilities.formatDate(new Date(year,month,day),"GMT+1","d-M-yyyy"); 
    }
    else{
      var courtDecisonDate = "no date";
    }
    
    if(/\d{9}\/\d\/[a-zA-Z]\d/.test(courtDecisionData)){
      var courtDecisionReference = /\d{9}\/\d\/[a-zA-Z]\d/.exec(courtDecisionData)[0]; 
    }
    else{
      var courtDecisionReference = "no reference";
    }
    
    if(/ECLI:NL:RVS:\d{4}:[A-Z0-9]+/.test(courtDecisionData)){
      var courtDecisionEcli = /ECLI:NL:RVS:\d{4}:[A-Z0-9]+/.exec(courtDecisionData)[0];
    }
    else{
      var courtDecisionEcli = "no ecli";
    }
    
    var courtDecisionUrl = courtDecisionData.split(/<a href="/)[1].split(/">/)[0];
    
    if(/<div class=summary>/.test(courtDecisionData) && /<div class=summary><\/div>/.test(courtDecisionData) == false){
      var courtDecisionSummary = courtDecisionData.split(/<div class=summary>/)[1].split(/<p>/)[1].split(/</)[0].trim(); 
    }
    else{
      var courtDecisionSummary = "";
    }
    
    if(testColumn.indexOf(courtDecisionEcli) ==-1 && courtDecisonDate !== "no date" && courtDecisionReference !== "no reference" && courtDecisionEcli !== "no ecli"  &&  /-v/.test(courtDecisionUrl) && /(vreemdelingenbewaring|bewaring|vrijheidsontnemende)/.test(courtDecisionSummary)){
      courtDecisions.push([courtDecisionDate,courtDecisionReference,courtDecisionEcli,courtDecisionUrl,"ABRS"]);
    }
  }  
  if(courtDecisions[0]){
    sheet.getRange(sheet.getLastRow()+1,1,courtDecisions.length,courtDecisions[0].length).setValues(courtDecisions); 
  };
}

Line 10-12
With UrlFetchApp.fetch(url).getContentText() GAS get access to the html-code of the webpage on which the court decisions are published. You can view the html-code by logging it (Logger.log(sourceText)) but a better way to view the html-code is via your browser.

The court decisions can be seperated (and thereafter manipulated) by splitting the html-code with the string class=siteLink href="https://www.raadvanstate.nl/uitspraken/@. View the html-code of the court decisions webpage and search for the above mentioned string.

Line 40-55
Of each court decision I need the date, the reference, the ECLI, the url and the summary. After splitting here and splitting there the data are pushed into the array courtDecisions.

Line 56-58
Finally the array courtDecisions is imported into the Google spreadsheet.