Skip to content Skip to sidebar Skip to footer
Home / Google Apps Script / Google Sheets / Javascript

How To Auto Populate Form Options Based On A Column In The Attached Spreadsheet

Post a Comment
I created a new google sheet for collecting user information. In my sheet script editor, I created this form: https://script.google.com/macros/s/AKfycbz6uGmXfSiXg4lYseeX0IN7Qv_9eM4

Solution 1:

On a previous thread you already got an answer about how to create an HTML form.

On the code of this question you are trying to use getItemById over a string value but this method is from Class Form. In other words, this method should be used only on Google Forms, not on web forms. Something similar happens with other methods used like setChoiceValues.

To set the option list from a web form by using client side code, you should use DOM methods, like querySelectorAll, getElementsByTagName, etc. If you need to get the options from a spreadsheet you could use server side code and could call that code from the client side by using google.script.run.

For an overview of how Google Apps Script web applications works, please read https://developers.google.com/apps-script/guides/web

Related

Solution 2:

Consider using <?= ?> and <? ?>scriptlets

This allows you to access Apps SCript functionalities within the HTML file.

.gs file

function doGet() {  
  return HtmlService
      .createTemplateFromFile('index')
      .evaluate();
}

function createInnerHTML(){
    var ss = SpreadsheetApp.getActive();
    var names = ss.getSheetByName("CHOICES");
    var namesValues = names.getRange(2, 16, names.getMaxRows() - 1).getValues(); 
   return namesValues;
}

HTML file

<body>
...
<?var namesValues= createInnerHTML(); ?><div><selectname="entry.890244015"id="entry_890244015"aria-label="JOINT  "aria-required="true"required=""><optionvalue=""></option><optionvalue="OPTION 01"><?= namesValues[0][0]?></option><optionvalue="OPTION 02"><?= namesValues[1][0]?></option><optionvalue="OPTION 03"><?= namesValues[2][0]?></option><optionvalue="OPTION 04"><?= namesValues[3][0]?></option></select></div>
...
</body>

This is an easy solution if your HTML frame is predetermined (you know in advance that your dropdown menu will have 4 options).

If you want to adjust the amount of options dynamically, you a more elegant solution would be:

.gs file

function doGet() {  
  return HtmlService
      .createTemplateFromFile('index')
      .evaluate();
}

function createInnerHTML()
{
    var ss = SpreadsheetApp.getActive();
    var names = ss.getSheetByName("CHOICES");
    var namesValues = names.getRange(2, 16, names.getMaxRows() - 1).getValues(); 
    var InnerHTML = [];
    for (var i=0;i<namesValues.length;i++){
      InnerHTML.push('<option value="OPTION '+(i+1)+'>' + namesValues[i][0]+ '</option>'; 
    }); 
   InnerHTML.join('');
   return InnerHTML;
  }

HTML file

<body>
...
<?var innerHTML= createInnerHTML(); ?><div><selectname="entry.890244015"id="entry_890244015"aria-label="JOINT  "aria-required="true"required=""><optionvalue=""></option>
//HERE the inner HTML will be inserted
<?= innerHTML?></select></div>
...
</body>

Post a Comment for "How To Auto Populate Form Options Based On A Column In The Attached Spreadsheet"