How can I create a HTML dialog box that contains a dropdown list?

Question

I want to record user input under specific categories - for this I wish to have the user select a category from a drop down list, and also have another text cell where they can enter text.

My goal is to populate the drop down with specific criteria, then record the users selection and additional text string into variables that are then written into the spreadsheet.

EDIT: I have been able to create the drop down and populate it - however the selected input from the drop down is only returning to the spreadsheet very sporadically (it has worked twice in about 30 attempts).

EDIT2: If I remove google.script.host.close(); from the code then the var is passed back to the spreadsheet. It appears that the dialog box is closing too quickly, but sleep() in the functionToRunOnFormSubmit(fromInputForm) function is not delaying the code at all.

Code.js:

function fncOpenMyDialog() {
  //Open a dialog
  var htmlDlg = HtmlService.createHtmlOutputFromFile('addDeck')
      .setSandboxMode(HtmlService.SandboxMode.IFRAME)
      .setWidth(200)
      .setHeight(150);
  SpreadsheetApp.getUi()
      .showModalDialog(htmlDlg, 'Add a New Deck');
};


function functionToRunOnFormSubmit(fromInputForm) {

Logger.log(fromInputForm);

  var ss = SpreadsheetApp.getActive();
  ss.getSheetByName("test").getRange(2, 1, 1, 1).setValue(fromInputForm);
};

addDeck.html

<!DOCTYPE html>
<html>
<body>
<form>

<select name="Class" id="class-selector" autofocus="autofocus" autocorrect="off" autocomplete="off">

  <option value="" selected="selected">Class</option>

  <option value="Druid">Druid</option>
  <option value="Hunter">Hunter</option>
  <option value="Mage">Mage</option>
  <option value="Paladin">Paladin</option>
  <option value="Priest">Priest</option>
  <option value="Rogue">Rogue</option>
  <option value="Shaman">Shaman</option>
  <option value="Warlock">Warlock</option>
  <option value="Warrior">Warrior</option>
</select>

<input type="submit" value="Submit" onclick="myFunction()">
</form>
<p id="addDeck"></p>

<script>
function myFunction() {
var x = document.getElementById("class-selector").value;
document.getElementById("addDeck").innerHTML = x;

  google.script.run
    .functionToRunOnFormSubmit(x);

  google.script.host.close();

}
</script>

</body>
</html>

Code sourced primarily from Using an HTML drop-down menu with Google Apps Script on Google Sheets - also from How to create a drop down list in (App Script) Spreadsheet Input Box?

I still need to add a text cell for user input and return that value to the spreadsheet.

Any help would be greatly appreciated, thank you!

score 3 · Accepted Answer · answered Apr 05 '19 at 06:55

3

You want to retrieve the value of the dropdown list and put the value to a sheet of "test" on the active Spreadsheet.
You want to put new text input field to HTML and retrieve the inputted value. Then you want to put the value together with the value of the dropdown list to the Spreadsheet.

If my understanding is correct, how about this modification?

Modified script

Google Apps Script:

From:

ss.getSheetByName("test").getRange(2, 1, 1, 1).setValue(fromInputForm);

To:

ss.getSheetByName("test").getRange(2, 1, 1, 2).setValues([[fromInputForm.Class, fromInputForm.sample]]);

HTML:

There are 2 modification parts.

From:

<input type="submit" value="Submit" onclick="myFunction()">

To:

<input name="sample" type="text"> <!-- Added -->
<input type="submit" value="Submit" onclick="myFunction(this.parentNode)"> <!-- Modified -->

And

From:

function myFunction() {
  var x = document.getElementById("class-selector").value;
  document.getElementById("addDeck").innerHTML = x;
  google.script.run
    .functionToRunOnFormSubmit(x);
  google.script.host.close();
}

To:

withSuccessHandler() is used for this.

function myFunction(obj) { // Modified
  var x = document.getElementById("class-selector").value;
  document.getElementById("addDeck").innerHTML = x;
  google.script.run // Modified
    .withSuccessHandler(() => google.script.host.close())
    .functionToRunOnFormSubmit(obj);
}

Reference:

withSuccessHandler()

If I misunderstood your question and this was not the result you want, I apologize.

answered Apr 05 '19 at 06:55

Tanaike

181,128
11
97
165

1

<3 <3 <3 IT WORKS!!!! <3 <3 <3 Thank you SO MUCH! Your explanation and how you set out the code was PERFECT for me to understand what changes you have made. You're amazing! (On going through your updated code I realised I'd put `;` at the end of each function in the code.js - which I just now realised is called code.gs) O_O – Aaron Irvine Apr 05 '19 at 07:25
2

@Aaron Irvine I'm glad your issue was resolved. Thank you, too. – Tanaike Apr 05 '19 at 12:32
@Tanaike, why do you pass back `this.parentNode` to `functionToRunOnFormSubmit()`? – Andrew Kirna Aug 02 '20 at 01:42
@Andrew Kirna In this case, I think that the form object including the inputted values is sent to Google Apps Script side. By this, at Google Apps Script side, the inputted values can be retrieved. About this, I think that you can confirm the reason of that by checking the log at `functionToRunOnFormSubmit(fromInputForm)`. If this was not the replying you expect, I apologize. – Tanaike Aug 02 '20 at 02:23
@Tanaike, thanks for the quick reply! I was getting a ScriptError with permission denied but I just fixed it by logging out of all my accounts and logging back in. The only thing I can't get working is `.withSuccessHandler(() => google.script.host.close())`. Any idea what I could do? – Andrew Kirna Aug 02 '20 at 03:31
@Tanaike, in other words, how do I tell `google.script.run` that my function succeeded? – Andrew Kirna Aug 02 '20 at 03:33
@Andrew Kirna Thank you for replying. I have to apologize for my poor skill. From your replying, I cannot understand about your situation. I deeply apologize for this. So in order to correctly understand about your current situation, can you post it as new question by including the detail information for replicating your issue? By this, it will help users including me think of the solution. When you can cooperate to resolve your issue, I'm glad. – Tanaike Aug 02 '20 at 04:13
1

@Tanaike, my script is working now! Thanks for replying! – Andrew Kirna Aug 02 '20 at 04:26
@Andrew Kirna Thank you for replying. I'm glad for your issue was resolved. – Tanaike Aug 02 '20 at 06:50

How can I create a HTML dialog box that contains a dropdown list?

Code.js:

addDeck.html

1 Answers1

Modified script

Google Apps Script:

HTML:

Reference:

Linked