I have begun using google apps script in google sheets, and i want to create a dialogue box where the user will write an input that I will use later. the dialogue box shold have a drop list that will make suggestions or complete the input.

CodePudding user response：

For anyone arriving here through a Google Search:

Drop downs in Google Sheets can be achieved with data validation. Create a column of entries. Let's say like this:

A4 = Apples  
A5 = Tigers  
A6 = Coriander  
A7 = Forest

Then select a cell, say, B4. Now go to Data in the top menu. Choose Data validation. A module will open with options. Choose "List from a range." as criteria, then enter A4:A7 as the range. You will have the option to reject other input. Now hover over B4 and click the arrow. You will see that you now have an inline dropdown menu. It may be useful to know that you can add list sources an another tab and even hide that tab to users to keep the interface clean.

Now, to answer your actual question.

You want a dropdown to appear in a popup. This can be done! It's not as fast as using an inline dropdown with data validation, but it's much fancier.

Assuming you know at least the basics of Google Apps Script, here's the code:

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Custom Menu')
    .addItem('Multiple choice', 'dropDownModal')
    .addToUi();
}

function dropDownModal() {
  var htmlDlg = HtmlService.createHtmlOutputFromFile('dropdown.html')
    .setSandboxMode(HtmlService.SandboxMode.IFRAME)
    .setWidth(350)
    .setHeight(175);
    
  SpreadsheetApp.getUi()
    .showModalDialog(htmlDlg, 'A Title Goes Here');
};

function writeChoice(selection) {
  const writeResponseLocation = "B4";

  SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheets()[0]
    .getRange(writeResponseLocation)
    .setValue(selection);
}

Then create a file called dropdown.html (in addition to the code.gs file above) and input the following:

<!DOCTYPE html>
<html>

<head>
  <base target="_top">
</head>

<script>
  function onSuccess() {
    google.script.host.close();
  }

  function submit() {
    const choice = document.getElementById('choice').value;

    google.script.run
      .withSuccessHandler(onSuccess)
      .writeChoice(choice);
  }
  
  function setup() {
    const button = document.getElementById('submitbutton');
    button.addEventListener("click", submit)
  }
</script>

<body onl oad="setup()">
  <p>
    There will be a slight delay on submission.
  </p>
  <form>
    <select id="choice">
      <option value="apple">Apple</option>
      <option value="banana">Banana</option>
      <option value="coriander">Coriander</option>
      <option value="monkey">Monkey</option>
    </select>
    <button id="submitbutton">Submit</button>
  </form>
</body>

</html>

Now save everything and reload the sheet. A menu will appear at the end of the menu bar called Custom Menu. Select that and choose Multiple choice. You'll have to give yourself permission to the run the code you entered for this to work (then choose the menu option again). That'll do it. Tweak the code to suit your needs.