How to Create a Multiple-selection Drop-down in Google Sheets

Learn how to create multiple-selection drop-downs in Google Sheet using Apps Script.

Updated on 11/04/2024: The new code helps create multiple-selection drop-downs in multiple columns and sheets:

A drop-down is an interactive element that presents a list of options to the user. There are two main types of drop-downs: single selection and multiple selection. The difference lies in the number of options that can be chosen at once. As the names suggest, a single selection drop-down allows the user to pick only one option, while a multiple selection drop-down lets the user choose several options.

So, when do we need a multiple selection drop-down in Google Sheets? The answer lies in the context of use. For instance, consider a scenario where we have an order sheet containing orders from our clients and a list of services we provide. A client can order several services. Consequently, it would be beneficial to have a mechanism to add multiple items to the corresponding cell. This is where a multiple-selection drop-down becomes handy.

While Google Sheets provides a built-in feature for drop-downs, it does not natively support the multiple selection feature. This is where Google Apps Script comes into play. By using Google Apps Script, we can extend the functionality of Google Sheets to cater to our specific needs.

In our use case, copying from the list of services and pasting into the cell would be a time-consuming process, especially if we have many clients and orders. Creating a multiple-select drop-down list makes this task simpler and faster. The short video below summarizes the whole process. At the end of it, you can see a demo of what the final result looks like.

In the following sections, we’ll explore how to create such a drop-down using Google Apps Script.

1. Open your spreadsheet

First, get a copy of the sample spreadsheet that already contains the required code. You can then start testing this feature right away.

But if you want to use this feature in one of your Google sheets, then continue reading this post. The short video above also summarizes how to make the script work in a new spreadsheet.

2. Creating a multiple-selection drop-down

We will need to add to our spreadsheet the Google script that will enable the multiple-select drop-down functionality. After creating the layout of the checkboxes as shown in the image below, open the code editor of the Google Sheet by clicking on  Extensions > Apps Script. Then replace the content of the code.gs file with the following script.

This is the updated version of the script, different from what is shown in the video. But the configuration is pretty much the same.

/**
 * MULTIPLE-SELECTION DROPDOWNS IN GOOGLE SHEETS
 *
 * Written by Joseph Asinyo (MIT License)
 *
 **/


// Specify the exact names of the sheets and the columns where the drop-downs are located
// The dropdowns of each columns can have different options
const SHEETS_DROPDOWNS_COLUMNS = {
  "Orders": [2,3], // The multiple-selection will work in column B and C of the sheet named "Orders"
  "Another sheet with dropdowns": [1], // The multiple-selection will work in column A and C of the sheet named "Another sheet with dropdowns"
}


// Specify what should separate the options in cells. 
// This can be a character like " , " or text like " and "
const SEPARATOR = "\n"   // Return to new line    



function onEdit(e) {
  var oldValue;
  var newValue;
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var activeCell = ss.getActiveCell();
  var activeSheetName = ss.getActiveSheet().getName()

  if(activeSheetName in SHEETS_DROPDOWNS_COLUMNS){
    if(SHEETS_DROPDOWNS_COLUMNS[activeSheetName].includes(activeCell.getColumn())){
      newValue=e.value;
      oldValue=e.oldValue;

      if(!e.value) {
        activeCell.setValue("");
      }
      else {
        if (!e.oldValue) {
          activeCell.setValue(newValue);
        } else {
          if(oldValue.includes(newValue)){
            activeCell.setValue(oldValue);
          } else {
            activeCell.setValue(oldValue+SEPARATOR+newValue);
          }
        } 
      }
    } 
  }
}

Start by configuring the script. These constants make the script flexible and easily adaptable to different sheets and columns.

1. SHEETS_DROPDOWNS_COLUMNS: Specify in this object the exact names of the sheets and the columns where the drop-downs are located.
2. SEPARATOR: This is the character or text that separates the options in cells.

3. How the script works

The Google Apps Script is designed to be applied to a specific sheet and a specific column within your spreadsheet. The script works by responding to any edit event within the specified column of the specified sheet.

The onEdit(e) function is triggered each time a cell is edited. This function captures the previous and new values of the active cell. The function then confirms if the edited cell is in the specified sheet and column. If it’s not, the function exits and no further action is taken.

If the edited cell is in the specified sheets and columns, the function examines the new value of the cell. If the new value is empty, the cell is cleared. If the new value isn’t empty and there was no previous value, the new value is set in the cell.

If there was a previous value, the function checks whether the new value is already included in the old value. If it is, the old value is restored to prevent duplication. If it isn’t, the new value is appended to the old value, separated by the specified separator.

That’s it! Congratulations on successfully reaching the end of this tutorial! You’ve just taken a significant step in enhancing your Google Sheets skills by learning to create a multiple-selection drop-down using Google Apps Script.

Joseph Asinyo

Google Workspace Developer

I’m Joseph. I love building applications and writing Google scripts to automate Google Workspace for greater productivity. Learn more about me.

• YouTube
• LinkedIn
• Twitter