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.
Add a comment
|
1 Answer
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 onload="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.
1 Comment
atultw
If it's opening a blank page change the button type to "button" (it's submit by default)