Google Sheets Multi Select Dropdown List

Can a Google Sheets drop-down list in a cell allow you to do multiple select?

Google Sheets doesn’t natively support the ability to select more than one item on a drop-down list in a cell, but there is a way where you can click an item in the drop-down list and have the clicked item populate the active cell.

Here’s an example of what this would look like:

Create Your Drop Down List

First, create your drop-down list of items by selecting the cell, or range of cells, where the drop-down list will be available.

In this example, I’ve selected the entire A column and upon clicking on the Data menu item I then clicked on Data validation, which displayed the following modal window where I changed the Criteria to a List of items and inserted the values Apple,Banana,Carrot.

Select your range of cells and then set the data validation to a list of items or a range

Once you’ve clicked on the Save button you will see the drop-down list arrow in each of the cells you had originally highlighted (in my case the entire A column).

You can double-check everything is working as it should for those cells which have been nominated to have the data validation:

Check to make sure your data validation cells show the desired drop-down list of items

Create Named Range For Data Validation Area

To make the subsequent coding a little easier to manage it will help if the same range you’ve used for data validation is also given a named range.

Therefore, highlight the same area where the cells have the data validation, and then click the main menu Data followed by the Named ranges option, then provide a name for the range, which I have labelled as ddFruitVeges as seen below:

Create a named range from the same data validation range

Once you’ve clicked on the Done button you should confirmation of your named range in the Named Range list afterwards:

Confirmation of the named range listing

Google Apps Script onEdit Function

The last part to get this functionality to work is to add some code. I’ll step through each of the functions added and the purpose behind each function in case you need to change them for your own use case.

One big benefit of the code below is that it doesn’t need special permissions for it to work. This may help when sharing the spreadsheet with others as they will not have to do any individual authentication for your spreadsheet to do what it needs.

First, open up the Google App Script page by clicking on Extensions and then on the option Apps Script this will open up a new coding editor tab where you will see on the left side a Files area with Code.gs inside, and on the right-side a blank editor area with an empty function called myFunction.

You can delete this in the editor and begin by writing an onEdit function like so:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

/**
 * @param {Object} e - edit event object
 */
function onEdit(e) {
    /**
     * @typedef {Object} MultiDropDown
     * @property {String} namedRange
     * @property {Number} handlingDuplicates
     * @property {String} separator
     * @property {Boolean} sortResult
     */
    const ddRanges = [{'namedRange': 'ddFruitVeges', 'handlingDuplicates': 1, 'separator': ", ", 'sortResult': true}];
    multiSelectDropDownList(e, ddRanges);
}

This code is fairly simple for the main reason that the onEdit function may be used to write other code as it’s a generic function that captures all other code operating on an edit event in your Google spreadsheet.

Therefore, try to keep your tasks as separate functions within the onEdit function and try to make the name of the functions in here as detailed as possible so that when you come back months or years later you can easily discern what’s happening.

Next, define an object array ddRanges that contains important properties needed for all the various ways you would want your multiple drop-down list select cells to work.

Here is a list of what each mean:

namedRange is the property this simply contains the name of each range you would want the multi-select script to operate on.

handlingDuplicates is the property that helps to define how you would want to handle when a user clicks on a drop-down list that is already in the active cell. There are three options: 1 is to remove the existing item from the list; 2 is to ignore the input (so there will only ever be 1 entry for each drop-down list item); and 3 to keep adding it as though it were new. You may have different use cases for each named range, therefore this is handy as a property that can be set for each unique named range.

separator is the property that contains the string of how the items in the cell will be separated and displayed to the user. A common separator is the comma character (with a space to help readability) ", " but ensure that whatever characters you use here that it is unique and not found in the drop-down list of items, otherwise it will cause havoc.

sortResult is the last property that contains a boolean value on whether you would like to sort the result of the items in the cell.

Once all these properties are set in the array you can then proceed to pass the entire array along with the edit event object e into the function multiSelectDropDownList.

Active Cell Intersection With Named Range

The first objective with the function multiSelectDropDownList is to fail quickly. There are likely many edit events happening on your spreadsheet and you want to have your script exit quickly if the edit is on a range that does not apply to the drop-down range.

Therefore, within the multiSelectDropDownList function I am going to start by writing another function called isIntersection to quickly exit the script if the edit event doesn’t apply.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

/**
 * @param {SheetsOnEdit} e
 * @param {MultiDropDown[]} ddRanges
 * @returns {void}
 */
function multiSelectDropDownList(e, ddRanges) {
  ddRanges.forEach((nr) => {
    if (!isIntersection(e, nr.namedRange)) return;
  });
}

In the code above I want to know if there is not and intersection happening and if there isn’t to fail fast and return nothing out of the function. Notice also, that the event object passed into the isIntersection function is the event object itself as you will be accessing both the range property and the source property in this object.

The array.forEach() method helps to quickly loop through each MultiDropDown object in the array.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

/**
 * @param {SheetsOnEdit} e - edit event object
 * @param {String} ddNamedRange
 * @returns {Boolean}
 */
function isIntersection(e, ddNamedRange) {
    /** @type {Range} */
    const activeCell = e.range;
    // is the Active Cell a single cell?
    if (activeCell.getWidth() * activeCell.getHeight() > 1) return false;
    // loop through the array of drop down list named ranges
    const rngNR = e.source.getRangeByName(ddNamedRange);
    // is the Active Cell on the same sheet?
    const shtIdAC = activeCell.getSheet().getSheetId();
    const shtIdNR = rngNR.getSheet().getSheetId();
    if (shtIdAC !== shtIdNR) return false;
    // is the Active Cell intersecting within the boundaries of the named range?
    const colAC = activeCell.getColumn();
    const rowAC = activeCell.getRow();
    const colNR = rngNR.getColumn();
    const rowNR = rngNR.getRow();
    const colNRLast = rngNR.getLastColumn();
    const rowNRLast = rngNR.getLastRow();
    return colAC >= colNR && colAC <= colNRLast && rowAC >= rowNR && rowAC <= rowNRLast;
}

In the isIntersection function I want to again fail quickly as scripts on the onEdit() function cannot run for any longer than 30 seconds. So I try to find ways I can quickly check how to exit out of the code. The first check I perform is determining if the active cell is a single cell range rather than a range of cells. If it’s a range of cells then I exit.

Next is to check if the sheet of the active cell is the same as the sheet of the named range.

Finally, if none of the previous two checks have triggered a false return then I check the boundaries of the named range and see if it is within the boundaries of the single active cell.

Once you’ve then confirmed the active cell is within the boundaries of the named range you can proceed to the next step of updating the contents of the cell.

I’ve created another function to handle this too, therefore, the multiSelectDropDownList function is amended with the next function call:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

/**
 * @param {SheetsOnEdit} e
 * @param {MultiDropDown[]} ddRanges
 * @returns {void}
 */
function multiSelectDropDownList(e, ddRanges) {
  ddRanges.forEach((nr) => {
    if (!isIntersection(e, nr.namedRange)) return;
    updateDropDownListCell(e, nr.handlingDuplicates, nr.separator, nr.sortResult);
  });
}

The added line of code passes through the entire edit event object as the edit event object contains the oldValue as well as the new value which are needed to assess how to handle the change.

The function updateDropDownList will then perform the concatenation or removal of the values in the list:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

/**
 * @param {SheetsOnEdit} e
 * @param {Number} handlingDuplicates
 * @param {String} separator
 * @param {Boolean} sortResult
 * @returns {Range}
 */
function updateDropDownListCell(e, handlingDuplicates, separator, sortResult) {
    // if cell has been cleared or is a new cell
    if (!e.value || !e.oldValue) return e.range.setValue(e.value);
    const oldItems = e.oldValue.split(separator);
    const idx = oldItems.indexOf(e.value);
    // How do you want to handle duplicate entries?
    if (idx > -1) {
      // Option #1: a click on an item that already exists in the cell will
      // have it removed.
      if (handlingDuplicates === 1) {
        oldItems.splice(idx, 1);
        if (sortResult && oldItems.length > 1) oldItems.sort();
        return e.range.setValue(oldItems.join(separator));
      }
      // Option #2: ignore duplicates completely
      if (handlingDuplicates === 2) return e.range.setValue(oldItems.join(separator));
      // Option #3: treat it as though it were a new entry...
    }
    // Add new value to existing items
    oldItems.push(e.value);
    if (sortResult) oldItems.sort();
    return e.range.setValue(oldItems.join(separator));
}

This function above first checks if the contents of the cells are being deleted, or if the value of the cell has been newly set (i.e. does not contain an oldValue). If so the process exits quickly by just setting the new e.value into the active cell.

The next series of checks relate to the existing content in the active cell. First I split the cell contents by the designated separator and then I find out if the newly inserted value already exists in the array.

If it does I then check how this is to be handled according to the handlingDuplicates argument of the function.

If the item is to remove the array.splice(idx, deleteNumberItems) is used to remove the entry, or the entry is ignored, or the entry is added. Amongst all this there is also the option to sort the entries so that the active cell contains an ordered delimited list.

Therefore, the entire code in your App Script for your Google spreadsheet will be as follows:

The result of the multi-select feature using the data validation drop down lists in Google Sheets

Once you’ve pasted all the code into your App Script and saved the code into your project (you may need to give your project a name) you can skip on over to the sheet with your drop-down lists and start playing around with selecting the items in the menu.

When handlingDuplicates is set to 1 you should notice the disappearance of an item that already exists in your list. For example, when I click on the drop-down item Apple, then click on Banana, then click on Banana again the cell only contains Apple - having removed the Banana option. If I happen to click on Apple again though Apple is not removed: this is because the cell’s contents have not changed - you’ve selected an item from a drop-down list that already exists in the active cell, therefore the onEdit function does not trigger.

To remove Apple from the list you would just need to delete the contents of the cell.

When handlingDuplicates is set to 2 you should notice that no matter how many times you click on the same drop-down item nothing happens. This is its intended purpose.

Lastly, when handlingDuplicates is set to 3 you should notice the same drop-down items being added into the cell. You may need to apply the cell wrap so that the contents of the cell can easily be read.

One other final thing to note is that each cell where 2 or more items exist will automatically flag with Invalid: Input must be an item on the specified list. Unfortunately, there is no way around removing this warning, so this will be a continual nuisance.

Summary

While Google Sheets doesn’t provide an option for multi-select drop down menus through an App Script you can easily replicate the behavior of a multi-select by adding the selections from the drop-down menu each time a user clicks on an item.

This does mean for each time a user wants to add an item from the list they need to click on the drop-down list icon and select one item at a time.

The code above would best be suited for simple use cases, but where bigger use cases are needed I would look at developing a sidebar interface.