CODEWITHSUNDEEP
formsgoogle-apps-scriptgoogle-sheets
Mahmoud Bayoumi
Mahmoud Bayoumi

Reputation: 63

A solution to dynamically update the option list by a column in google sheet

In the below form: https://script.google.com/macros/s/AKfycbwTGqZqLTAsOpSweMn0xgHP0sOJPsFg5ZShC1HqzVoDoNi5h5Y/exec

I'm trying to dynamically update the option list from a column in this sheet: https://docs.google.com/spreadsheets/d/1_LSdBkvw5Z6L4ZQP5qZccLEfekNVRCeKQRLLb9Vm4eM/edit#gid=285745421

I'm using The solution provided by "Ziganotschka" here: How to auto populate form options based on a column in the attached spreadsheet

The code is not giving any errors, still for some reason it gives an empty list of options in the form

Thanks in advance

Code.gs

function createInnerHTML()
{
var ss = SpreadsheetApp.getActive();
var names = ss.getSheetByName("CHOICES");
var namesValues = names.getRange(2, 2, 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;
}

index.html

<div class="ss-q-title">JOINT
<span class="ss-required-asterisk" aria-hidden="true">*</span></div>

<? var innerHTML= createInnerHTML(); ?>  
<div>
<select name="JOINT" id="JOINT" aria-label="JOINT  " aria-required="true" required="">
<option value=""></option>
//HERE the inner HTML will be inserted
<?= innerHTML?>
</select>
</div>

Upvotes: 0

Views: 355

Answers (2)

0Valt
0Valt

Reputation: 10355

Problem

By printing scriplets you just add your HtmlStrings with options as text content (if you inspect the element in console, you will see that all the options are present). UPD: btw, TheMaster's solution is easier to implement.

Solution

One of the many possible solutions is to return the Array of option objects to template and loop through each one, forming tags dynamically:

Server-side

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

Client-side

Please, note that this sample omits <div> element and any other wrappers for clarity.

<? var innerHTML= createInnerHTML(); ?>  
<select name="JOINT" id="JOINT" aria-label="JOINT" aria-required="true" required="">
<option value=""></option>
<? innerHTML.forEach(function(option) { ?>
  <option value="<?= option.value ?>"><?= option.text ?></option>
<? }); ?>
</select>

Additional modifications

I would suggest switching from getMaxRows() to getLastRow() as your script returns 296 options, most of which are blank and only have values set due to rows being empty.

Upvotes: 3

TheMaster
TheMaster

Reputation: 50624

Issues/Solution:

  • Using printing scriplets(<?=>) to append html instead of force-printing scriplets(<?!=>)
  • Missing closing quotes(") in each option's html

Snippet(s):

  • Html: 

    <?!= innerHTML?>

  • Code.gs:

    InnerHTML.push('<option value="OPTION '+(i+1)+'">' + namesValues[i][0]+'</option>'); //Note added "

References:

Upvotes: 2

Related Questions