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>
