firenemus firenemus - 4 months ago 8
Javascript Question

Building / Populating a Drop Down Menu on Web Page from Google Spreadsheet Data

I'm using a Google visualization query to pull column headings from this spreadsheet. Currently I must include the row and column indexes for each cell I want in the menu. What I'd like is a script that'll populate this menu dynamically with data from the cells in row 1 of the spreadsheet. In other words, make the menu as big or as small as what exists on the spreadsheet.

I believe I need something like the following, but I'm unsure how to implement it. Perhaps I need server side script (GAS) to accomplish this??

<select>
for (var i = 0; i < data.length; ++i) {
<option>!= data[i] </option>
}
</select>


I've found plenty of documentation to accomplish this using Google's HTMLService, but I need this menu to be hosted using a different service.

Any help would be greatly appreciated. Thanks!

My current code follows...



google.load('visualization', '1', {'packages':['corechart']});
google.setOnLoadCallback(ValIDS);

function ValIDS() {
var queryValIDS = new google.visualization.Query('https://docs.google.com/spreadsheet/ccc?key=1HpHMfoEnPgESb2XPVCgb7XyGwRAvrq3EoQj4WHj4vhA&sheet=QUERY2');

queryValIDS.send(handleQueryValIDResponse);
}

function handleQueryValIDResponse(response) {
if (response.isError()) {
alert('Error in ID Validation Query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
return;
}

var datatable = response.getDataTable();
var cat1 = datatable.getValue(0,0);
var cat2 = datatable.getValue(0,1);
var cat3 = datatable.getValue(0,2);
var cat4 = datatable.getValue(0,3);
var cat5 = datatable.getValue(0,4);
var cat6 = datatable.getValue(0,5);
var cat7 = datatable.getValue(0,6);
var cat8 = datatable.getValue(0,7);
var cat9 = datatable.getValue(0,8);
var cat10 = datatable.getValue(0,9);



document.getElementById('cat1').innerHTML = cat1;
document.getElementById('cat2').innerHTML = cat2;
document.getElementById('cat3').innerHTML = cat3;
document.getElementById('cat4').innerHTML = cat4;
document.getElementById('cat5').innerHTML = cat5;
document.getElementById('cat6').innerHTML = cat6;
document.getElementById('cat7').innerHTML = cat7;
document.getElementById('cat8').innerHTML = cat8;
document.getElementById('cat9').innerHTML = cat9;
document.getElementById('cat10').innerHTML = cat10;


}

function myFunction() {
document.getElementById("myDropdown").classList.toggle("show");
}

window.onclick = function(event) {
if (!event.target.matches('.dropbtn')) {

var dropdowns = document.getElementsByClassName("dropdown-content");
var i;
for (i = 0; i < dropdowns.length; i++) {
var openDropdown = dropdowns[i];
if (openDropdown.classList.contains('show')) {
openDropdown.classList.remove('show');
}
}
}
}

.dropbtn {
background-color: #4CAF50;
color: white;
padding: 16px;
font-size: 16px;
border: none;
cursor: pointer;
}

.dropbtn:hover, .dropbtn:focus {
background-color: #3e8e41;
}

.dropdown {
position: relative;
display: inline-block;
}

.dropdown-content {
display: none;
position: absolute;
background-color: #f9f9f9;
min-width: 160px;
overflow: auto;
box-shadow: 0px 8px 16px 0px rgba(0,0,0,0.2);
}

.dropdown-content a {
color: black;
padding: 12px 16px;
text-decoration: none;
display: block;
}

.dropdown a:hover {background-color: #f1f1f1}

.show {display:block;}

.dropbtn {
background-color: #4CAF50;
color: white;
padding: 16px;
font-size: 16px;
border: none;
cursor: pointer;
}

.dropbtn:hover, .dropbtn:focus {
background-color: #3e8e41;
}

.dropdown {
position: relative;
display: inline-block;
}

.dropdown-content {
display: none;
position: absolute;
background-color: #f9f9f9;
min-width: 160px;
overflow: auto;
box-shadow: 0px 8px 16px 0px rgba(0,0,0,0.2);
}

.dropdown-content a {
color: black;
padding: 12px 16px;
text-decoration: none;
display: block;
}

.dropdown a:hover {background-color: #f1f1f1}

.show {display:block;}

<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
<script type="text/javascript" src="https://www.google.com/jsapi"></script>


<div class="dropdown">
<button onclick="myFunction()" class="dropbtn">Dropdown</button>
<div id="myDropdown" class="dropdown-content">
<a href="#"><div id="cat1"></div></a>
<a href="#"><div id="cat2"></div></a>
<a href="#"><div id="cat3"></div></a>
<a href="#"><div id="cat4"></div></a>
<a href="#"><div id="cat5"></div></a>
<a href="#"><div id="cat6"></div></a>
<a href="#"><div id="cat7"></div></a>
<a href="#"><div id="cat8"></div></a>
<a href="#"><div id="cat9"></div></a>
<a href="#"><div id="cat10"></div></a>

</div>
</div>




Answer

you can use client-side JavaScript to build the drop-down dynamically
using the data from the datatable

there are a number of ways, but this should accomplish what you need

you can use getNumberOfColumns rather than hard-coding each drop-down item
this will allow additional columns to be added to the spreadsheet,
without having to change the code / html

for (var i = 0; i < datatable.getNumberOfColumns(); i++) {
  var ddItem = document.getElementById('myDropdown').appendChild(document.createElement('A'));
  ddItem.href = '#';
  var ddItemContent = ddItem.appendChild(document.createElement('SPAN'));
  ddItemContent.id = 'cat' + (i + 1);
  ddItemContent.innerHTML = datatable.getValue(0, i);
}

(i + 1) was used on the id above,
just so the id would be the same as what you had hard-coded

since anchors (<a>) are inline elements,
a <span> was used instead of <div>

you may not even need id, or the SPAN for that matter,
since the drop-down items are being created dynamically

see following working snippet...

google.load('visualization', '1', {'packages':['corechart']});
google.setOnLoadCallback(ValIDS);

function ValIDS() {
  var queryValIDS = new google.visualization.Query('https://docs.google.com/spreadsheet/ccc?key=1HpHMfoEnPgESb2XPVCgb7XyGwRAvrq3EoQj4WHj4vhA&sheet=QUERY2');
  queryValIDS.send(handleQueryValIDResponse);
}

function handleQueryValIDResponse(response) {
  if (response.isError()) {
    alert('Error in ID Validation Query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
    return;
  }

  var datatable = response.getDataTable();
  for (var i = 0; i < datatable.getNumberOfColumns(); i++) {
    var ddItem = document.getElementById('myDropdown').appendChild(document.createElement('A'));
    ddItem.href = '#';
    var ddItemContent = ddItem.appendChild(document.createElement('SPAN'));
    ddItemContent.id = 'cat' + (i + 1);
    ddItemContent.innerHTML = datatable.getValue(0, i);
  }
}

function myFunction() {
  document.getElementById('myDropdown').classList.toggle('show');
}

window.onclick = function(event) {
  if (!event.target.matches('.dropbtn')) {
    var dropdowns = document.getElementsByClassName('dropdown-content');
    var i;
    for (i = 0; i < dropdowns.length; i++) {
      var openDropdown = dropdowns[i];
      if (openDropdown.classList.contains('show')) {
        openDropdown.classList.remove('show');
      }
    }
  }
}
.dropbtn {
    background-color: #4CAF50;
    color: white;
    padding: 16px;
    font-size: 16px;
    border: none;
    cursor: pointer;
}

.dropbtn:hover, .dropbtn:focus {
    background-color: #3e8e41;
}

.dropdown {
    position: relative;
    display: inline-block;
}

.dropdown-content {
    display: none;
    position: absolute;
    background-color: #f9f9f9;
    min-width: 160px;
    overflow: auto;
    box-shadow: 0px 8px 16px 0px rgba(0,0,0,0.2);
}

.dropdown-content a {
    color: black;
    padding: 12px 16px;
    text-decoration: none;
    display: block;
}

.dropdown a:hover {background-color: #f1f1f1}

.show {display:block;}

.dropbtn {
    background-color: #4CAF50;
    color: white;
    padding: 16px;
    font-size: 16px;
    border: none;
    cursor: pointer;
}

.dropbtn:hover, .dropbtn:focus {
    background-color: #3e8e41;
}

.dropdown {
    position: relative;
    display: inline-block;
}

.dropdown-content {
    display: none;
    position: absolute;
    background-color: #f9f9f9;
    min-width: 160px;
    overflow: auto;
    box-shadow: 0px 8px 16px 0px rgba(0,0,0,0.2);
}

.dropdown-content a {
    color: black;
    padding: 12px 16px;
    text-decoration: none;
    display: block;
}

.dropdown a:hover {background-color: #f1f1f1}

.show {display:block;}
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>

<div class="dropdown">
  <button onclick="myFunction()" class="dropbtn">Dropdown</button>
  <div id="myDropdown" class="dropdown-content"></div>
</div>

EDIT

the getValue method takes two arguments

getValue(rowIndex, columnIndex)

both rowIndex and columnIndex are zero-based,
meaning the first cell value would be getValue(0, 0)

if you want to pull values from the first column of each row...

use i as rowIndex and 0 as columnIndex

for (var i = 0; i < datatable.getNumberOfRows(); i++) {
  var test = datatable.getValue(i, 0);
}