0

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>
Mahmoud Bayoumi
  • 63
  • 3
  • 8

2 Answers2

3

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.

Oleg Valter is with Ukraine
  • 9,619
  • 8
  • 37
  • 59
  • NP, glad we could help! Do take a look at TheMaster's solution - it is a quick and easy fix if you don't expect (untrusted) user input to be passed to such a scriplet. Btw, you should probably think of moving to asynchronous loading as well (see BP) – Oleg Valter is with Ukraine Aug 18 '19 at 21:39
  • About "moving to asynchronous loading", would you please suggest a reference on how to do so. Thanks again Oleg – Mahmoud Bayoumi Aug 22 '19 at 11:30
  • Hi Mahmoud! I refered to [best practices](https://developers.google.com/apps-script/guides/html/best-practices#load_data_asynchronously_not_in_templates) reference provided by TheMaster, but if you need more in-depth look, take a look at [client-server documentation](https://developers.google.com/apps-script/guides/html/communication) official docs - loading `createInnerHTML()` like this is discouraged in apps script (although not prohibited, you are limiting yourself to one-time load only [it can be enough for the task at hand, ofc] whereas async loading could be reused) – Oleg Valter is with Ukraine Aug 22 '19 at 12:43
2

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:

TheMaster
  • 45,448
  • 6
  • 62
  • 85