CODEWITHSUNDEEP
htmlformsgoogle-apps-script
Jatinder
Jatinder

Reputation: 43

Passing value of a selection from Dynamic Dropdown List in HTML form created from a separate Google Sheet using Google App Script

I am unable to Pass the value of a selection from the Dynamic Dropdown List in HTML form created from a another Google Sheet within same file using Google App Script.

I am a newbie and make Google Sheets to automate / organize my daily workflow for personal use. I take help from videos / scripts from other users through internet & edit them to achieve my objective.

I am developing a small Google Sheet for one of my close friend so that to create a Client List in Google Sheet for his startup business using app script. Please Help. 

function getSelectList() 
         {
          var sheet = SpreadsheetApp.openById(".......").getSheetByName('Client_List_Dropdown');
          var lastRow = sheet.getLastRow();
          var myRange = sheet.getRange("A3:A" + lastRow);
          var data = myRange.getValues();
          Logger.log("Data = " + data); 
          return data;
         };




function openInputDialog_New_ClientContact() {
  var html = HtmlService.createHtmlOutputFromFile('Add_ClientContact').setSandboxMode(HtmlService.SandboxMode.IFRAME);
  SpreadsheetApp.getUi()
       .showModalDialog(html, 'Add Record');
}




// Add Single Item & Close on Submit
function itemAdd_New(form) {

  // Select Sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Add_ClientSheet");

  sheet.appendRow([form.Title, form.Full_Name, "=CONCATENATE(INDIRECT(ADDRESS(ROW(),COLUMN()-2)), ,INDIRECT(ADDRESS(ROW(),COLUMN()-1)))", form.mySelectList, form.Designation, form.Email, form.Per_Email, form.Mobile, form.STD_Code, form.Landline, form.Extn, form.Fax]);

  return true;
  sheet.getRange('A6').activate();
}

HTML File: Add_ClientContact.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
        <script>
      (function () {
        google.script.run.withSuccessHandler(
          function (selectList) {
            var select = document.getElementById("mySelectList");
            for( var i=0; i<selectList.length; i++ ) {
              var option = document.createElement("option");
              option.text = selectList[i][0];
              select.add(option);
            }
          }
        ).getSelectList();
      }());

    </script>    
  </head>

<form id="myForm" onsubmit="google.script.run
            .withSuccessHandler(google.script.host.close)
            .itemAdd_New(this)">

<p style="font-family:verdana;font-size:10pt;">
    Title:  
     <select name="Title" required>
     <option value="Mr.">Mr.</option>
     <option value="Ms.">Ms.</option>
     <option value="Sir/Madam">Sir/Madam</option>
     </select>
    <br><br>
    Full Name: <input type="text" name="Full_Name" >
    <br><br>
    Friendly Company Name: 
    <select id="mySelectList" value="mySelectList" >
    </select>
    <br><br>
    Designation: <input type="text" name="Designation">
    <br><br>
    Quotation Email: <input type="email" name="Email" >
    <br><br>
    Technical / Personal Email: <input type="email" name="Per_Email">
    <br><br>
    Mobile: <input type="text" name="Mobile">
    <br><br>
    STD Code: <input type="text" name="STD_Code">
    <br><br>
    Landline 1: <input type="text" name="Landline">
    <br><br>
    Extn: <input type="text" name="Extn">
    <br><br>
    Fax: <input type="text" name="Fax">
    <br><br>

    </p>
     <input type="submit" value="SUBMIT and Re-open BLANK FORM" />

</font> 
</form>
</html>

Screenshot of Google Sheet to capture Form Results

Thank you ALL in advance for Help!

Upvotes: 3

Views: 1123

Answers (1)

Cooper
Cooper

Reputation: 64100

Perhaps this simple example will be of some help:

html:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  <script>
    window.onload=function(){
        google.script.run
          .withSuccessHandler(updateSelect)
          .getSelectOptions();
      }

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

    function savSelect(){
      var selected=document.getElementById('sel1').value;
      google.script.run
      .withSuccessHandler(function(emails){
        console.log(emails);
        document.getElementById('emails').innerHTML='Send Emails to the following: '+emails;
      })
      .getEmailsForChoice(selected);
    }
    console.log('My Code');
  </script>
  </head>
  <body>
  <div id="emails"></div>
  <select id="sel1" onChange="savSelect();"></select>
  </body>
  </html>

GS:

function onOpen(){
  SpreadsheetApp.getUi().createMenu('My Tools')
  .addItem('Test', 'showFormDialog')
  .addItem('Show Choice Dialog','showMyDialog')
  .addToUi();
}

function buildForm(){
  var s='';
  for(var i=0;i<5;i++){
    s+=Utilities.formatString('<br /><input class="jim" type="text" value="" id="%s" />%s',"txt" + i,"text" + Number(i+1));
  }
  s+='<br /><input type="button" value="Submit" onClick="getValues();" /><input type="button" value="Close" onClick="google.script.host.close();" />';
  return s;
}
function saveValues(A){
  for(var i=0;i<A.length;i++){
    Logger.log('\nid=%s\nvalue=%s',A[i].id,A[i].value);
  }
}
function showFormDialog(){
  var ui=HtmlService.createHtmlOutputFromFile('form')
  SpreadsheetApp.getUi().showModelessDialog(ui,'My Form');
}

function getSelectOptions(){
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Selections')
  var rg=sh.getDataRange();
  var selections=[];
  var vA=rg.getValues();
  for (var i=0;i<vA.length;i++){
    selections.push(vA[i][0]);
  }
  return selections;
}

function getEmailsForChoice(choice){
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Selections')
  var rg=sh.getDataRange();
  var vA=rg.getValues();
  for (var i=0;i<vA.length;i++){
    if(choice==vA[i][0]){
      var emails=Utilities.formatString('%s;%s;%s',vA[i][1],vA[i][2],vA[i][3]);
      break;
    }                 
  }
  return emails;
}

function showMyDialog(){
  var ui=HtmlService.createHtmlOutputFromFile('choices')
  SpreadsheetApp.getUi().showModelessDialog(ui, 'Choices');
}

Upvotes: 1

Related Questions