Get list from a google sheet to populate a dialog box in html

Question

I have a list of items in a Google Sheet. I want to use the data from that sheet to inform a drop down list allowing a user to select an item from that list. I can get the list to popluate in the script, but can't figure out how to then return that into the html dialog box. Here is my .gs code:

function getPoList(){
  var fpa = SpreadsheetApp.openById('__WORKING ID___');
  var poSheet = fpa.getSheetByName('testSheet');
  var poVendor = poSheet.getRange('testVendor');
  var poPo = poSheet.getRange('testPo');
  var poVendorList = poVendor.getValues();
  var poPoList = poPo.getValues();     

  var poList = '';
  for (var i = 0; i < poPo.getNumRows(); i++) {
     poList += "<option value=" + poVendorList[i] + ": " + poPoList[i] + ">" + poVendorList[i] + ": " + poPoList[i] + "</option>";
     return poList;    
  }            
}

Here is my .html code:

   <script type="text/javascript">
google.script.run.withSuccessHandler(onSuccess).getPoList(poList);
  var div = document.createElement('div');
  div.className = 'newClass';
  div.innerHTML = '<select>' + poList + '</select>';
  document.body.appendChild(div); 
</script>

Alan Wells Alan Wells · Accepted Answer · 2015-03-09T03:14:48

Looks like you haven't written a function for the withSuccessHandler(onSuccess).

Should look like this:

<script type="text/javascript">
  google.script.run
    .withSuccessHandler(onSuccess)
    .getPoList(poList);

  function onSuccess(argReturnedHTML) {
    var div = document.createElement('div');
    div.className = 'newClass';
    div.innerHTML = '<select>' + argReturnedHTML + '</select>';
    document.body.appendChild(div);
  }
</script>

Also, .getValues() returns a two dimensional array. I don't see that you are dealing with that.

Two dimensional array:

[ ['row one cell one value', 'row one cell two value'], ['row two cell one value', 'row two cell two value'] ]

To get the inner values of the inner arrays, you need to use two index numbers:

poVendorList[i][j]

Where i is the index of each inner array that represents each row in the spreadsheet, and j is the value of each cell in the row.

poVendorList[rowIndex][cellInRowIndex]

You don't need to use both indexes at the same time, but the code needs to somehow first get an inner array, then get values out of the inner array.

Get list from a google sheet to populate a dialog box in html

1 Answers