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.

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>