Using an HTML drop-down menu with Google Apps Script on Google Sheets

Question

I am running a function on Google Sheets that requires a user to pick from a (rather lengthy) list of options. As UI service is deprecated, I thought I'd try with HTML, but I know nothing about this. I need the HTML User interface to pop up, have the user pick a name from the list, then go away, after passing the name back to the apps script function. I have tried to cobble together some code, but I can't seem to always get the drop-down menu to pop up, and I just can't seem to figure out how to send the choice back to the original function. Help?

function genDiscRep(){
var ss=SpreadsheetApp.getActive();
var dontTouch=ss.getSheetByName("Do Not Touch");
var studentNamesArrayLength=dontTouch.getLastRow()-1000+1
var studentNames=dontTouch.getRange(1000,3,studentNamesArrayLength,1).getValues();
var test=HtmlService.createHtmlOutputFromFile('index')
  .setSandboxMode(HtmlService.SandboxMode.IFRAME);
Browser.msgBox(test);
}

And then my html code (most choices removed for clarity)

<!DOCTYPE html>
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=utf-8" />
<title>Select to Autocomplete</title>
<script src="jquery-1.11.1.min.js"></script>
<script src="jquery-ui.min.js"></script>
<script src="jquery.select-to-autocomplete.js"></script>
<script>
  (function($){
    $(function(){
      $('select').selectToAutocomplete();
      $('form').submit(function(){
        alert( $(this).serialize() );
        return false;
      });
    });
  })(jQuery);
</script>
<link rel="stylesheet" href="jquery-ui.css">
<style>
  body {
    font-family: Arial, Verdana, sans-serif;
    font-size: 13px;
  }
.ui-autocomplete {
  padding: 0;
  list-style: none;
  background-color: #fff;
  width: 218px;
  border: 1px solid #B0BECA;
  max-height: 350px;
  overflow-x: hidden;
}
.ui-autocomplete .ui-menu-item {
  border-top: 1px solid #B0BECA;
  display: block;
  padding: 4px 6px;
  color: #353D44;
  cursor: pointer;
}
.ui-autocomplete .ui-menu-item:first-child {
  border-top: none;
}
.ui-autocomplete .ui-menu-item.ui-state-focus {
  background-color: #D5E5F4;
  color: #161A1C;
}
</style>
</head>
<body>
<form>
<select name="Student" id="name-selector" autofocus="autofocus"     autocorrect="off" autocomplete="off">

<option value="" selected="selected">Select Student</option>

<option value="Abercrombie, Amber">Abercrombie, Amber(Gr 11)</option>
<option value="Yupa, Jason">Yupa, Jason(Gr 9)</option>
</select>

<input type="submit" value="Submit" onclick="myFunction()">
</form>
<p id="demo"></p>
<script>
function myFunction() {
var x = document.getElementById("name-selector").value;
document.getElementById("demo").innerHTML = x;
var ss=SpreadsheetApp.getActive();
Browser.msgBox(ss.getSheetName());

}
</script>

</body>
</html>

I apologize for the length of the html code. I wasn't sure what I could omit and still provide sufficient information. The beginning of the html is an attempt to utilize Jamie Appleseed's open-source code that allows auto-complete and auto-correction of a drop-down menu. (That part doesn't seem to be working either, but one thing at a time, I suppose).

Alan Wells · Accepted Answer · 2015-11-14 00:58:59Z

You can't use Browser.msgBox(test); Use SpreadsheetApp.getUi(). etc

function genDiscRep() {
  var ss = SpreadsheetApp.getActive();
  var dontTouch = ss.getSheetByName("Do Not Touch");
  var studentNamesArrayLength=dontTouch.getLastRow()-1000+1;
  var studentNames=dontTouch.getRange(1000,3,studentNamesArrayLength,1).getValues();

  var test = HtmlService.createHtmlOutputFromFile('index')
    .setSandboxMode(HtmlService.SandboxMode.IFRAME);

  SpreadsheetApp.getUi()
    .showModalDialog(test, 'User Input')
};

You must use google.script.run.myFunction() to communicate back to the server side code:

google.script.run
  .functionToRunOnFormSubmit(x);

Use google.script.host.close() to close the dialog box.

Index.html

<!DOCTYPE html>
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=utf-8" />
<title>Select to Autocomplete</title>
<script src="jquery-1.11.1.min.js"></script>
<script src="jquery-ui.min.js"></script>
<script src="jquery.select-to-autocomplete.js"></script>
<script>
  (function($){
    $(function(){
      $('select').selectToAutocomplete();
      $('form').submit(function(){
        alert( $(this).serialize() );
        return false;
      });
    });
  })(jQuery);
</script>
<link rel="stylesheet" href="jquery-ui.css">
<style>
  body {
    font-family: Arial, Verdana, sans-serif;
    font-size: 13px;
  }
</style>
</head>
<body>
<form>
<select name="Student" id="name-selector" autofocus="autofocus" autocorrect="off" autocomplete="off">

<option value="" selected="selected">Select Student</option>

<option value="Abercrombie, Amber">Abercrombie, Amber(Gr 11)</option>
<option value="Yupa, Jason">Yupa, Jason(Gr 9)</option>
</select>

<input type="submit" value="Submit" onclick="myFunction()">
</form>
<p id="demo"></p>
<script>
function myFunction() {
var x = document.getElementById("name-selector").value;
document.getElementById("demo").innerHTML = x;

  google.script.run
    .functionToRunOnFormSubmit(x);

  google.script.host.close();

}
</script>

</body>
</html>

There needs to be a second function to handle the return from the user input:

Code.gs

function functionToRunOnFormSubmit(fromInputForm) {
  var ss = SpreadsheetApp.getActive();
  ss.getSheetByName("Do Not Touch").getRange(2, 2, 1, 1).setValue(fromInputForm);
};

Collectives™ on Stack Overflow

Using an HTML drop-down menu with Google Apps Script on Google Sheets

1 Answer 1

Index.html

Code.gs

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Index.html

Code.gs

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related