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>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;
}
Solution 1:
Modification points:
- In your HTML, the template is used. In this case, please use
createTemplateFromFileinstead ofcreateHtmlOutputFromFile. - The scriptlet of
<?!= ... ?>isForce-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');
}
To:
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>
To:
<datalist id="datalist">
<? data.forEach(e => { ?>
<option value="<?= e ?>">
<? }); ?>
</datalist>
Reference:
- HTML Service: Templated HTML