CODEWITHSUNDEEP
htmlgoogle-apps-scriptgoogle-sheetsgoogle-sheets-api
Vasim
Vasim

Reputation: 3143

HTML select option to be captured from Google Spreadsheet

I have been spending days to get over this but no luck.

I have a html page in Google script whereby I want a dropdown list - this is achieved as follows

<div>
<select name="source">
  <option value="volvo">Volvo</option>
  <option value="saab">Saab</option>
  <option value="opel">Opel</option>
  <option value="audi">Audi</option>
</select>
</div>

Now instead of the manual inputs I want the option fields to be captured from a spreadsheet, if the coloumn range increase or decrease the dropdown list should update accordingly.

Any workarounds please...

Regards,

Upvotes: 2

Views: 9278

Answers (3)

Mogsdad
Mogsdad

Reputation: 45750

This is an adaptation of the technique demonstrated in the Html Service: Best Practices documentation.

Code.gs

function doGet() {
  var template = HtmlService
                 .createTemplateFromFile('DynamicList');

  var htmlOutput = template.evaluate()
                   .setSandboxMode(HtmlService.SandboxMode.NATIVE);

  return htmlOutput;
}

function getListOptions() {
  // In production code, get an array of options by
  // reading a spreadsheet.
  var options = ['Saab','Opel','Audi'];

  return( options );
}

DynamicList.html

<div>
  <select id="optionList">
    <option>Loading...</option>    
  </select>

</div>

<script src="//ajax.googleapis.com/ajax/libs/jquery/1.10.1/jquery.min.js">
</script>
<script>
// This code in this function runs when the page is loaded.
$(function() {
  google.script.run.withSuccessHandler(buildOptionList)
      .getListOptions();
});

function buildOptionList(options) {
  var list = $('#optionList');
  list.empty();
  for (var i = 0; i < options.length; i++) {
    list.append('<option value="' + options[i].toLowerCase() + '">' + options[i] + '</option>');
  }
}
</script>

Upvotes: 3

Neel
Neel

Reputation: 459

To display the elements in the array instead of the array object itself, you need to include the DATA array inside Scriptlet tags.And I see no requirement to use a 2-dimensional array. The correct syntax should be:

<?
 var sheet   =  SpreadsheetApp.openById(0Avt7ejriwlxudGZfV2xJUGJZLXktQ2RhQU1ugtgtaXc").getSheetByName("MRF Tab");
  var lastRow = sheet.getLastRow();  
  var myRange = sheet.getRange("C3:C"+lastRow); 
  var data    = myRange.getValues();
  ?>
<div>
  <select>
    <? for (var i = 0; i < data.length; ++i) { ?>
      <option><?!= data[i] ?></option>
    <? } ?>
  </select>
</div>

And its really a bad idea to mix data-fetching code & html UI code within a template. You should use separate gs and html files & apply the optimizations as Mogsdad has suggested.

Upvotes: 2

wchiquito
wchiquito

Reputation: 16551

You can take a look at the documentation, specifically, Pushing section variables to templates, insurance can be useful.

Upvotes: 2

Related Questions