How to populate dropdown list at selection element with values from Google sheet?

Question

I have a Google sheet with custom HTML form. The form contains <selection> element. Like this

<select id="category_name"  name="category_name" class="control" style="width:150px;height:20px;margin:10px 0 10px 0;">
<option value="" selected></option>
</select>

I'm getting values from the sheet

function getCategory() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sh = ss.getSheetByName(SHEET_NAME);  
  let list = sh.getRange(2, 1, sh.getLastRow() - 1).getValues();
  return list;  
}

And then I'm populating this selection with expected values in HTML file

(function () {
        google.script.run.withSuccessHandler(
          function (selectList) {
            var select = document.getElementById("category_name");
            for( var i=0; i<selectList.length; i++ ) {
              var option = document.createElement("option");
              option.val = selectList[i][0];
              option.text = selectList[i][0];
              select.add(option);
            }
          }
        ).getCategory();
      }());

It looks like list was populated well, but when I choice some item from selection it returns blank value after form submitting. Where I'm wrong and how to fix it?

Iamblichus · Accepted Answer · 2020-05-18 09:39:09Z

1

Issue:

You are not setting the <option> value correctly: val is not a valid attribute. Because of this, no value is added to each <option> and they are not submitted.

Solution:

Set the option value like this:

option.value = selectList[i][0];

Using Option constructor:

Of course, using the Option constructor would also work:

var option = new Option(selectList[i][0], selectList[i][0]);

Reference:

answered May 18, 2020 at 9:39

Iamblichus

19.5k2 gold badges14 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Cooper · Accepted Answer · 2020-05-15 17:00:08Z

0

I use this a lot:

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

new option

answered May 15, 2020 at 17:00

Cooper

64.2k7 gold badges29 silver badges65 bronze badges

Collectives™ on Stack Overflow

How to populate dropdown list at selection element with values from Google sheet?

2 Answers 2

Issue:

Solution:

Using Option constructor:

Reference:

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Issue:

Solution:

Using Option constructor:

Reference:

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related