Can I use HTML sidebar to call a function in Google Appscript?-CodePudding

I need to use a sidebar to get some variables and print then in the last line of a sheet, using AppScript. So, I was trying to use this code:

Sidebar.HTML

<!DOCTYPE html>
<html>

<head>
  <base target="_top">
</head>

<body>
  <p>Name: <input type="text" name="txtName" /></p>
  <p>Date: <input type="text" name="txtDate" /></p>
  <p>Value: <input type="text" name="txtValue" /></p>
  <button onclick="doSomething()"> submit </button>
  <script>
    function doSomething() {
      google.script.run.withFailureHandler(myFunction(document.getElementById("txtName", "txtDate", "txtValue").value))
    }
  </script>

</body>

</html>

Code.js

function myFunction(name = "", date = "", value = "") {
  var ss = SpreadsheetApp.getActive()
  var sheet = ss.getSheetByName("1")

  var lr = sheet.getLastRow()   1

  sheet.getRange(lr, 1).setValue(name)
  sheet.getRange(lr, 2).setValue(date)
  sheet.getRange(lr, 3).setValue(value)
}

function openDialog() {
  var html = HtmlService.createHtmlOutputFromFile("Sidebar");
  html.setTitle("Form");
  SpreadsheetApp.getUi().showSidebar(html);
}

But it don't worked. When I click on the button nothing happen. I'm new in HTML, so, what can I do to fixit?

CodePudding user response：

Looks like you got almost everything right except for 2 things, both in the Sidebar.HTML file —

Given that you're using getElementById, you also need to add an id tag in the input fields

The implementation of google.script.run.withFailureHandler seems to be a bit off. You can read through the

You can call many server side functions from the sidebar. I have a spreadsheet that I use for answering questions on SO and most of those scripts are found in five files and they are found in my globals hash table under the key sbfiles. Currently they are ag1,ag2,ag3,ag4,zkeepers.

The names in the select drop downs are dynamic and are read everytime one reloads the sidebar.

When I load my sidebar with the following function:

With this function:

function showToolsSideBar() {
  var userInterface = HtmlService.createTemplateFromFile('toolsSideBar').evaluate().setTitle('SO Tools');
  SpreadsheetApp.getUi().showSidebar(userInterface);
}

This loads toolsSideBar.html which has these four buttons:

 <br /><strong>Test Buttons</strong>
   <br /><input type="button" value="run1()" onClick="execFunc1();" /><select id="func1"></select>
   <br /><input type="button" value="run2()" onClick="execFunc2();" /><select id="func2"></select>
   <br /><input type="button" value="run3()" onClick="execFunc3();" /><select id="func3"></select>
   <br /><input type="button" value="run4()" onClick="execFunc4();" /><select id="func4"></select>
   <hr />

This is a templated html file which contains this line:

<?!= include('sbscript') ?>

And sbscript.html contains:

$(function(){
    google.script.run
    .withSuccessHandler(function(vA){
      let idA=["func1","func2","func3","func4"];
      idA.forEach(function(id){
        updateSelect(vA,id);
      });
    })
    .getProjectFunctionNames();
    var elem = document.getElementById("permnotes1");
    var v = localStorage.getItem(elem.name);
    if(v) {elem.value = v;}
    elem.addEventListener("change",saveText);
    //console.log('elem.name: %s',elem.name);
  })

Which calls this server side function getProjectFunctionNames():

function getProjectFunctionNames() {
  const vfilesA=getGlobal('sbfiles').split(',');
  const scriptId="script id";
  const url = "https://script.googleapis.com/v1/projects/"   scriptId   "/content?fields=files(functionSet,name)";
  const options = {"method":"get","headers": {"Authorization": "Bearer "    ScriptApp.getOAuthToken()}};
  const res = UrlFetchApp.fetch(url, options);
  let html=res.getContentText();
  //SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutput(html), "Project Functions");
  let data=JSON.parse(res.getContentText());
  let funcList=[];
  let files=data.files;
  files.forEach(function(Obj){
    if(vfilesA.indexOf(Obj.name)!=-1) {
      if(Obj.functionSet.values) {
        Obj.functionSet.values.forEach(function(fObj){
          funcList.push(fObj.name);
        });
      }
    }
  });      //SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutput(funcList.join(', ')), "Project Functions");
  return funcList;
}

This function uses the apps script api to read all of the files in my global hash table key sbfiles. That list is returned to the success handler of the javascript function and using this function it loads the select boxes for each of the four buttons in the html file using this script:

function updateSelect(vA,id){
    var id=id || 'sel1';
    var select = document.getElementById(id);
    select.options.length = 0; 
    vA.unshift("");
    for(var i=1;i<vA.length;i  ){
      select.options[i] = new Option(vA[i],vA[i]);
    }
  }

Which returns us back to this:

 <br /><strong>Test Buttons</strong>
   <br /><input type="button" value="run1()" onClick="execFunc1();" /><select id="func1"></select>
   <br /><input type="button" value="run2()" onClick="execFunc2();" /><select id="func2"></select>
   <br /><input type="button" value="run3()" onClick="execFunc3();" /><select id="func3"></select>
   <br /><input type="button" value="run4()" onClick="execFunc4();" /><select id="func4"></select>
   <hr />

You may select any of the function names via any of the four select dropdowns and then press the button to the left and you will call the function in the select drop down to the right of the button:

function execFunc1() {
    var funcname=$('#func1').val();
    google.script.run.executeFunctionByName(funcname);
  }
  function execFunc2() {
    var funcname=$('#func2').val();
    google.script.run.executeFunctionByName(funcname);
  }
  function execFunc3() {
    var funcname=$('#func3').val();
    google.script.run.executeFunctionByName(funcname);
  }
  function execFunc4() {
    var funcname=$('#func4').val();
    google.script.run.executeFunctionByName(funcname);
  }

which will then call this function:

function executeFunctionByName(func) {
  this[func]();
}

Which in turn calls the functions by name in the files found in the hash table key of sbfiles.

This makes it possible for me to call any of the functions I am developing in any of this files from the side bar while I'm developing them by simply selecting each one of the them for each of four possible buttons which ends up calling the function whose name appears in the select box associated with that function. Which makes it convenient for testing the code that I'm currently working on.

This is a really handy feature when developing new code to be able to have access to the most recent code right in the side. It's really handy when developing new dialog code because you can reissue a call to the function you are working on from the sidebar:

Demo: