How to auto populate form options based on a column in the attached spreadsheet

Question

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_9eM4eN9knUtBm5Co/exec

The above form has a multiple choice question that I need to auto populate using a specific column in the attached spreadsheet

I used he below code, but for some reason the update function is not reading the form options or something

When I run the update form function, it gives "TypeError: Cannot find function getItemById in object"

Is there any solution to update the form options by the sheet column

    function doGet() {
    return HtmlService.createTemplateFromFile('form.html')
        .evaluate() // evaluate MUST come before setting the Sandbox mode
        .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
    }


    function updateForm(){
    // call your form and connect to the drop-down item
    var form = ("https://script.google.com/macros/s/AKfycbz6uGmXfSiXg4lYseeX0IN7Qv_9eM4eN9knUtBm5Co/exec");

    var namesList = form.getItemById("890244015").asListItem();


    // identify the sheet where the data resides needed to populate the drop-down
    var ss = SpreadsheetApp.getActive();
    var names = ss.getSheetByName("CHOICES");

    // grab the values in the first column of the sheet - use 2 to skip header row 
    var namesValues = names.getRange(2, 16, names.getMaxRows() - 1).getValues();

    var studentNames = [];

    // convert the array ignoring empty cells
    for(var i = 0; i < namesValues.length; i++)    
    if(namesValues[i][0] != "")
      studentNames[i] = namesValues[i][0];

     // populate the drop-down with the array data
     namesList.setChoiceValues(studentNames);

     }

This is the form.html code:














 


Test
WELD DATE 
00

* 
Required









 

  




JOINT
 

*










REP NO.
 

*







 






WELD DATE
 

*









WELDER
 

*








WPS
 

*

ziganotschka · Accepted Answer

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


...
  



...

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

//HERE the inner HTML will be inserted



...

How to auto populate form options based on a column in the attached spreadsheet

Answers (2)

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

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

Related Questions

How to auto populate form options based on a column in the attached spreadsheet

Answers (2)

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

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

Related Questions

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