Create drop-down list in Sheets

Programming
,
1

Question: How can I create a drop down menu in a single cell using JavaScript in Google Sheets?

Requirements:

  • The drop down menu will be in cell A1.
  • The menu should contain three or more values from an array.
// Your code here
2 
function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuItems = [
    {name: 'Option 1', functionName: 'option1'},
    {name: 'Option 2', functionName: 'option2'},
    {name: 'Option 3', functionName: 'option3'}
  ];
  ss.addMenu('Dropdown Menu', menuItems);
}

function option1() {
  SpreadsheetApp.getActiveSheet().getRange("A1").setValue("Option 1");
}

function option2() {
  SpreadsheetApp.getActiveSheet().getRange("A1").setValue("Option 2");
}

function option3() {
  SpreadsheetApp.getActiveSheet().getRange("A1").setValue("Option 3");
}
  1. Open your Google Sheet and go to the Script Editor (Tools > Script Editor).
  2. Copy and paste the code above into the script editor.
  3. Save the script and go back to your sheet.
  4. Refresh the page if necessary.
  5. You should see a new menu called “Dropdown Menu” at the top of your sheet.
  6. Click on “Dropdown Menu” and select any of the options.
  7. The value of cell A1 will change to the selected option.