javascript – Dynamic option list in HTML function in Google Apps Script — can't get items to load into HTML-ThrowExceptions

Exception or error:

I am trying to generate a list of options for a dropdown menu in a function run from the sidebar. I want the list to be dynamic. I have the list in a ‘data’ sheet in the spreadsheet, and I can run a script from outside the HTML that pulls the data I need just fine, but I can’t figure out how to load this into an option list. I have worked on this for hours today, and I am now SO CONFUSED. I am not even sure if the scripts I’m showing you here make any sense because I have tried so many different approaches to get this to work.

Code.gs:

function createNewInv() {
   // var values = getColValues('DataVal',1);
//    var listValues = getListValues(values);
  //  Logger.log(values);
  
    ss = SpreadsheetApp.getActiveSpreadsheet();
  var html = HtmlService.createHtmlOutputFromFile('Invoice Form.html')
 // html.status = status;
//  var evhtml = html.evaluate();
  .setSandboxMode(HtmlService.SandboxMode.IFRAME)
  .setHeight(450)
  .setWidth(300)
  .setTitle('Enter a New Invoice')
  ss.show(html);

function getColValues(sheet, col) {
    var sheet = ss.getSheetByName(sheet);
    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
    //var col = headers.indexOf(header) + 1;
    var col = col;
    try {
        var values = sheet.getRange(2, col, sheet.getLastRow() - 1, 1).getValues();
        var valueArray = [];
        for (var i = 0; i < values.length; i++) {
            if (values[i][0] != "") {
                valueArray.push(values[i][0]);
            }
        }
        return valueArray;
    }
    catch (err) {
        return ["no data in column:" + header];
    }
}
<!DOCTYPE html>
<html>
<div>
<b>Add Invoice: </b><br />
<form autocomplete="on" >
Status: <br />    
    <select id='status' name='status'>
    <option>...</option>
    
    </select>
    <br />
    
        </form>
    
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script>
// The code in this function runs when the page is loaded.
$(function() {
  google.script.run.withSuccessHandler(showThings)
      .getColValues('DataVal',1);
        Logger.log(values);
});

function showThings(things) {
  google.script.run.withSuccessHandler(function(getColValues('DataVal',1))){
  var list = $('#status');
  list.empty();
  
  for (var i = 0; i < things.length; i++) {
    list.append(new Option(things[i],things[i]));
  }
 
  return list;
}
}

</script>

Can anyone help me sort out this mess I’ve made??

How to solve:

Leave a Reply

Your email address will not be published. Required fields are marked *