Travel advice #GoogleAppsScript

 

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

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

function travelAdvice(arriveTime,labelOrigin,labelDestination){

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

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

return travelAdvice;
}

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

Updated: 31-01-2021

Dynamic dependent dropdown data validation lists #GoogleAppsScript

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

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

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

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


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

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

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