donderdag 14 november 2019

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



This is the result.

The labels, something like the following:
  • A
    • A1
      • A11
    • A2
      • A21
      • A22
    • A3
  • B
    • B1
      • B11
      • B12
      • B13
  • C
    • C1
    • C2
      • C21
        • C211
      • C22
      • C23
      • C24


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


  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
123
124
125
126
127
128
129
130
131
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);
}
Gepost door op

Geen opmerkingen:

Een reactie posten

Abonneren op: Reacties posten (Atom)