/**There must be a better way to do it but I do not yet know how*/: 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>

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

zondag 8 maart 2020

How to 'manage' templated spreadsheets from a standalone script with GAS

Geen opmerkingen:

Een reactie posten