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.

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();
  .setTitle('Enter a New Invoice');

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] != "") {
        return valueArray;
    catch (err) {
        return ["no data in column:" + header];
<!DOCTYPE html>
<b>Add Invoice: </b><br />
<form autocomplete="on" >
Status: <br />    
    <select id='status' name='status'>
    <br />
<script src=""></script>
// The code in this function runs when the page is loaded.
$(function() {

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


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 *