google sheet generated html form not "lining up"

Question

How do I get dynamic content from multiple google sheets (within one google sheet) into the same html form?

In the code below, I dynamically create the html form from a main sheet. I want to add a form select, from a supplemental sheet. I cannot get the form.select to be part of one html form.

EDIT: (1) updated var html to include div's for each row. (2) moved form and table html into the html page directly. Form values are still not lining up correctly. Should I be using HTML Template instead?

  <head>
  <script type="text/javascript">  
    function setPageValues () {
      //this is working as intended
      google.script.run.withSuccessHandler(displayForm).getValuesFromSS(); 
      }
   
    function displayForm (values) {   
      var colsUsed = [4,5,0,13,15,16,17,18];        //sheet cols for form
      var html = "<form><table border=0 width=90%>"; //start the form
     
     //HERE IS THE ISSUE: gets range no problem, but html form is wrong.
     //setting return value "html +=" doesn't work!
google.script.run.withSuccessHandler(displayPick).getValuesForRngName('CategoryRole');
   
     //make the rest of the table (this all works fine)
      for (var j = 0; j < colsUsed.length; j++){
          colUsed = colsUsed[j]
          html += "<div id=row><tr><th align=right>" + values[0][colUsed] + ":*&nbsp;</th>";
          html += "<td><input></input type=text></td></tr></div>";
      }
      
      //close out the table and set the value (this all works fine)
      html += "<tr><td></td><td><input type=submit></td></tr></form></table>";
      html += "<i>* required form data.</i>";
      document.getElementById("forminfo").innerHTML = html;
    }  

  //building the pick list works just fine. results not correct in html
  //tried to "return html;" but this didn't work!
  function displayPick (values) {
    var rows = 10000;
    var catUsed = "34-31 21: Supplier";

    //find limit of column values
    for (var i = 0; i < rows; i++) {
      if (values[i] == '') { 
        var rows = i;
        break;
       } 
    }
    
    //build the select field description, and set selected option
    html = "<div id=row><tr><th align=right>" + values[0] + ":*&nbsp;</th><td><select>";
    for (var i = 1; i < rows; i++) {
      if (values[i] == catUsed) {
        html += "<option selected>" + values[i] + "<option>";
      } else {
        html += "<option>" + values[i] + "<option>";
      }
    }
    html += "</select></td></tr></div>";
    document.getElementById("formpick").innerHTML = html;
  }
  </script>
</head>

<body onload="setPageValues()">
  <form><table border=0 width=90%>
    <div id="formpick"></div>
    <div id="forminfo"></div>
  </table></form>
</body>

Cooper Cooper · Accepted Answer · 2017-11-15T17:09:40

Put a function in your project that opens up other Spreadsheets with "openById"; Then return the data to your form with google.script.run.withSuccessHandler(functionName);

google sheet generated html form not "lining up"

1 Answers