I have been struggling for a couple days to build a simple interface for my colleagues to record every customer interaction. They will input the following: Name of Customer (Autocomplete feature, from a superset of all names in a column in the spreadsheet) Date of Interaction Summary of the Interaction Prospects (Hot, Warm, Tepid, Cold) My problem is in getting

use an array from Google Sheets to create a data list in html that can be served to a Google Apps Script for Autocomplete a Text Input

I have been struggling for a couple days to build a simple interface for my colleagues to record every customer interaction. They will input the following:

Name of Customer (Autocomplete feature, from a superset of all names in a column in the spreadsheet)
Date of Interaction
Summary of the Interaction
Prospects (Hot, Warm, Tepid, Cold)

My problem is in getting the Autocomplete working.

I have seen threads where @Tanaika has beautifully laid down the Server Side, HTML+JS, etc. but I am unable to get this to work. My files are attached. Thanks for your time!

HTML+JS

<!DOCTYPE html>
<html>
  <head>
    <style>
     label {
    display: inline-block;
    width: 150px;
    }   
    </style>
    
  <base target="_top">
  <script>
    function submitForm() {
      google.script.run.appendRowFromFormSubmit(document.getElementById("feedbackForm"));
      document.getElementById("form").style.display = "none";
      document.getElementById("thanks").style.display = "block";
    }
  </script>
  </head>
  <body>
  <datalist id="datalist">
    <?! 
    var url = "https://docs.google.com/spreadsheets/d/13Ms0Cny3f-XaXS26s5AnrDT4H9c8p8OKRfwxPIQ9_CU/edit#gid=16760772"; 
    var ss = SpreadsheetApp.openByUrl(url);
    var ws = ss.getSheetByName("Pipeline");
    var rng = ws.getRange('D2:D')
    var rangeArray = rng.getValues();
    var filArray = rangeArray.filter(function (el) {return el[0] != ""}).flat();  // Modified
    console.info("hello read the data");
    for (var i = 0; i < datalist.length; i++) { !?>
    <option value="<?= datalist[i] ?>">
    <?! } !?>
  </datalist>
  <div>
  <div id="form">
  <h1>Record Interaction</h1>
  <form id="feedbackForm">
    <label for="name">Parent Name</label>
    <input type="text" id="name" name="name" list="datalist"><br><br>

    <label for="doi">Date of Interaction</label>
    <input id="today" type="date" name="doi"><br><br>

    <label for="feedback">Interaction Summary</label>
    <textarea rows=4 cols=35 id="feedback" name="feedback">Enter Interaction Summary Here...
         </textarea><br><br>

    <div>
      <label for="temperature">Likely Candidate?</label><br>
      <input type="radio" id="Hot" name="temperature" value="Hot">
      <label for="yes">Hot</label><br>
      <input type="radio" id="Warm" name="temperature" value="Warm">
      <label for="yes">Warm</label><br>
      <input type="radio" id="Tepid" name="temperature" value="Tepid">
      <label for="yes">Tepid</label><br>
      <input type="radio" id="Cold" name="temperature" value="Cold">
      <label for="no">Cold</label><br><br>

      <input type="button" value="Submit Interaction" onclick="submitForm();">
  </form>
  </div>
  </div>
  <div id="thanks" style="display: none;">
    <p>Thank you for speaking to our customers!</p>
  </div>
  </body>
</html>

JavaScript
​x
 
<!DOCTYPE html>
<html>
  <head>
    <style>
     label {
    display: inline-block;
    width: 150px;
    }   
    </style>
    
  <base target="_top">
  <script>
    function submitForm() {
      google.script.run.appendRowFromFormSubmit(document.getElementById("feedbackForm"));
      document.getElementById("form").style.display = "none";
      document.getElementById("thanks").style.display = "block";
    }
  </script>
  </head>
  <body>
  <datalist id="datalist">
    <?! 
    var url = "https://docs.google.com/spreadsheets/d/13Ms0Cny3f-XaXS26s5AnrDT4H9c8p8OKRfwxPIQ9_CU/edit#gid=16760772"; 
    var ss = SpreadsheetApp.openByUrl(url);
    var ws = ss.getSheetByName("Pipeline");
    var rng = ws.getRange('D2:D')
    var rangeArray = rng.getValues();
    var filArray = rangeArray.filter(function (el) {return el[0] != ""}).flat();  // Modified
    console.info("hello read the data");
    for (var i = 0; i < datalist.length; i++) { !?>
    <option value="<?= datalist[i] ?>">
    <?! } !?>
  </datalist>
  <div>
  <div id="form">
  <h1>Record Interaction</h1>
  <form id="feedbackForm">
    <label for="name">Parent Name</label>
    <input type="text" id="name" name="name" list="datalist"><br><br>
​
    <label for="doi">Date of Interaction</label>
    <input id="today" type="date" name="doi"><br><br>
​
    <label for="feedback">Interaction Summary</label>
    <textarea rows=4 cols=35 id="feedback" name="feedback">Enter Interaction Summary Here...
         </textarea><br><br>
​
    <div>
      <label for="temperature">Likely Candidate?</label><br>
      <input type="radio" id="Hot" name="temperature" value="Hot">
      <label for="yes">Hot</label><br>
      <input type="radio" id="Warm" name="temperature" value="Warm">
      <label for="yes">Warm</label><br>
      <input type="radio" id="Tepid" name="temperature" value="Tepid">
      <label for="yes">Tepid</label><br>
      <input type="radio" id="Cold" name="temperature" value="Cold">
      <label for="no">Cold</label><br><br>
​
      <input type="button" value="Submit Interaction" onclick="submitForm();">
  </form>
  </div>
  </div>
  <div id="thanks" style="display: none;">
    <p>Thank you for speaking to our customers!</p>
  </div>
  </body>
</html>

CODE.GS

function onOpen() {
  SpreadsheetApp.getUi() // Or DocumentApp or SlidesApp or FormApp.
      .createMenu('Customer Engagement')
      .addItem('Record Interaction', 'showDialog')
      .addToUi();
}

function showDialog() {
  var html = HtmlService.createHtmlOutputFromFile('RecordInteraction.html')
      .setWidth(400)
      .setHeight(600);
  SpreadsheetApp.getUi() // Or DocumentApp or SlidesApp or FormApp.
      .showModalDialog(html, 'Please Enter Details');
}
function readData() {
  var url = "https://docs.google.com/spreadsheets/d/13Ms0Cny3f-XaXS26s5AnrDT4H9c8p8OKRfwxPIQ9_CU/edit#gid=16760772"; 
  var ss = SpreadsheetApp.openByUrl(url);
  var ws = ss.getSheetByName("Pipeline");
  var rng = ws.getRange('D2:D')
  var rangeArray = rng.getValues();
  var filArray = rangeArray.filter(function (el) {return el[0] != ""}).flat();  // Modified
  console.info("hello read the data")
  return filArray;
}

function activateSheetById(sheetId) {
 
  //Access all the sheets in the Google Sheets spreadsheet
  var sheets = SpreadsheetApp.getActive().getSheets();

  //Filter out sheets whose Ids do not match
  var sheetsForId = sheets.filter(function(sheet) {
    return sheet.getSheetId() === sheetId;
  });

  //If a sheet with the Id was found, activate it
  if(sheetsForId.length > 0)
    sheetsForId[0].activate();
}

function appendRowFromFormSubmit(form) {
  var row = [form.name, form.doi, form.feedback, form.temperature];
  console.info("Appending Row");
  activateSheetById(2059810756);
  SpreadsheetApp.getActiveSheet().appendRow(row);
}

function makeUL(array) {
      // Create the list element:
    var namelist = document.createElement('ul');

    for (var i = 0; i < array.length; i++) {
        // Create the list item:
        var item = document.createElement('li');

        // Set its contents:
        item.appendChild(document.createTextNode(array[i]));

        // Add it to the list:
        list.appendChild(item);
    }

    // Finally, return the constructed list:
    return namelist;
}

JavaScript
 
function onOpen() {
  SpreadsheetApp.getUi() // Or DocumentApp or SlidesApp or FormApp.
      .createMenu('Customer Engagement')
      .addItem('Record Interaction', 'showDialog')
      .addToUi();
}
​
function showDialog() {
  var html = HtmlService.createHtmlOutputFromFile('RecordInteraction.html')
      .setWidth(400)
      .setHeight(600);
  SpreadsheetApp.getUi() // Or DocumentApp or SlidesApp or FormApp.
      .showModalDialog(html, 'Please Enter Details');
}
function readData() {
  var url = "https://docs.google.com/spreadsheets/d/13Ms0Cny3f-XaXS26s5AnrDT4H9c8p8OKRfwxPIQ9_CU/edit#gid=16760772"; 
  var ss = SpreadsheetApp.openByUrl(url);
  var ws = ss.getSheetByName("Pipeline");
  var rng = ws.getRange('D2:D')
  var rangeArray = rng.getValues();
  var filArray = rangeArray.filter(function (el) {return el[0] != ""}).flat();  // Modified
  console.info("hello read the data")
  return filArray;
}
​
function activateSheetById(sheetId) {
 
  //Access all the sheets in the Google Sheets spreadsheet
  var sheets = SpreadsheetApp.getActive().getSheets();
​
  //Filter out sheets whose Ids do not match
  var sheetsForId = sheets.filter(function(sheet) {
    return sheet.getSheetId() === sheetId;
  });
​
  //If a sheet with the Id was found, activate it
  if(sheetsForId.length > 0)
    sheetsForId[0].activate();
}
​
function appendRowFromFormSubmit(form) {
  var row = [form.name, form.doi, form.feedback, form.temperature];
  console.info("Appending Row");
  activateSheetById(2059810756);
  SpreadsheetApp.getActiveSheet().appendRow(row);
}
​
function makeUL(array) {
      // Create the list element:
    var namelist = document.createElement('ul');
​
    for (var i = 0; i < array.length; i++) {
        // Create the list item:
        var item = document.createElement('li');
​
        // Set its contents:
        item.appendChild(document.createTextNode(array[i]));
​
        // Add it to the list:
        list.appendChild(item);
    }
​
    // Finally, return the constructed list:
    return namelist;
}
​

Answer

Modification points:

In your HTML, the template is used. In this case, please use createTemplateFromFile instead of createHtmlOutputFromFile.
The scriptlet of <?!= ... ?> is Force-printing scriptlets (like printing scriptlets except that they avoid contextual escaping.). Ref

I thought that these are the reasons for your issue. When these points are reflected to your script, it becomes as follows.

Modified script:

Google Apps Script side:

From:

function showDialog() {
  var html = HtmlService.createHtmlOutputFromFile('RecordInteraction.html')
      .setWidth(400)
      .setHeight(600);
  SpreadsheetApp.getUi() // Or DocumentApp or SlidesApp or FormApp.
      .showModalDialog(html, 'Please Enter Details');
}

JavaScript
 
function showDialog() {
  var html = HtmlService.createHtmlOutputFromFile('RecordInteraction.html')
      .setWidth(400)
      .setHeight(600);
  SpreadsheetApp.getUi() // Or DocumentApp or SlidesApp or FormApp.
      .showModalDialog(html, 'Please Enter Details');
}
​

To:

function showDialog() {
  var html = HtmlService.createTemplateFromFile('index.html');
  html.data = readData();
  SpreadsheetApp.getUi().showModalDialog(html.evaluate().setWidth(400).setHeight(600), 'Please Enter Details');
}

JavaScript
 
function showDialog() {
  var html = HtmlService.createTemplateFromFile('index.html');
  html.data = readData();
  SpreadsheetApp.getUi().showModalDialog(html.evaluate().setWidth(400).setHeight(600), 'Please Enter Details');
}
​

Here, your function of readData() is used.

HTML and Javascript side:

From:

<datalist id="datalist">
  <?! 
  var url = "https://docs.google.com/spreadsheets/d/###/edit#gid=16760772"; 
  var ss = SpreadsheetApp.openByUrl(url);
  var ws = ss.getSheetByName("Pipeline");
  var rng = ws.getRange('D2:D')
  var rangeArray = rng.getValues();
  var filArray = rangeArray.filter(function (el) {return el[0] != ""}).flat();  // Modified
  console.info("hello read the data");
  for (var i = 0; i < datalist.length; i++) { !?>
  <option value="<?= datalist[i] ?>">
  <?! } !?>
</datalist>

JavaScript
 
<datalist id="datalist">
  <?! 
  var url = "https://docs.google.com/spreadsheets/d/###/edit#gid=16760772"; 
  var ss = SpreadsheetApp.openByUrl(url);
  var ws = ss.getSheetByName("Pipeline");
  var rng = ws.getRange('D2:D')
  var rangeArray = rng.getValues();
  var filArray = rangeArray.filter(function (el) {return el[0] != ""}).flat();  // Modified
  console.info("hello read the data");
  for (var i = 0; i < datalist.length; i++) { !?>
  <option value="<?= datalist[i] ?>">
  <?! } !?>
</datalist>
​

To:

<datalist id="datalist">
<? data.forEach(e => { ?>
  <option value="<?= e ?>">
<? }); ?>
</datalist>

JavaScript
 
<datalist id="datalist">
<? data.forEach(e => { ?>
  <option value="<?= e ?>">
<? }); ?>
</datalist>
​

Reference:

HTML Service: Templated HTML

Advertisement

Answer

Modification points:

Modified script:

Google Apps Script side:

From:

To:

HTML and Javascript side:

From:

To:

Reference: