Are you tired of being limited to selecting only one option from a dropdown list in Google Sheets? Well, we’ve got some good news for you! With a little bit of magic using Google Apps Script, you can create a dropdown list that allows multiple selections.
Dropdown lists in Google Sheets provide users with a clear view of all the available options and ensure that they only select allowed elements. They also help reduce errors since users can choose from a predefined list instead of manually typing in cell content.
By default, Google Sheets only allows users to select one item from a dropdown list. But what if you need to select multiple options? For example, when choosing from a collection of colors, you might like more than one. Or maybe you want to get a list of programming languages in which the user is proficient.
Traditionally, this option is not available in Google Sheets. You are only allowed to select one option at a time. But fear not! There is a solution. By using Google Apps Script, you can create a dropdown list that allows for multiple selections.
In this article, I will show you how to create a dropdown list that allows for multiple selections. But first, let’s start from the beginning.
Creating a Dropdown List
To demonstrate how to create a dropdown list that allows for multiple selections, let’s use the following dataset and create the dropdown list in cell C1. Please note that this differs from a dependent dropdown list. Here, we focus on the ability to allow the user to choose multiple items. A dependent dropdown list changes what is displayed based on the user’s choice.
To create a dropdown list that allows for multiple selections, you need to do two things:
- Create a dropdown list using a list of items.
- Add the function to the Google Sheets script editor that enables multiple selections in the dropdown list.
Let’s take a closer look at each of these steps.
Creating the Dropdown List
Let’s assume we have a dataset of items as shown below, and we want to create a dropdown list in cell C1.
Here are the steps to follow:
- Select the cell where you want to place the dropdown list.
- Go to Data > Data validation.
- In the criteria, select List from a range, then select the range that contains the items you want to display in the dropdown list.
- Open the Advanced options and make sure Show warning is selected instead of Reject input (this is part of enabling multiple selections, you normally don’t need to do this).
- Click Save.
Your dropdown list will now appear in the designated cell (C1 in this example). When you click the arrow, you will see your list of options.
Now, you might notice that you are only allowed to select one option at a time.
Now, let me show you how to convert this dropdown list (which only allows one item to be displayed in the cell) into a list that allows for multiple selections.
For that, you need to add the function script in the Google Sheets script editor. Don’t know how to use it? I’ll explain how to use the Google Sheets script editor in more detail. It’s the next step in my guide on creating a dropdown list in Excel with multiple selections. If you already know how to use it, feel free to skip to the next step.
Adding the Google Apps Script to Enable Multiple Selections
Here’s the script code that you’ll need to copy and paste into the script editor. If you need additional help, I’ll provide further explanations in the section below (after the code):
function onEdit(e) {
var oldValue;
var newValue;
var ss=SpreadsheetApp.getActiveSpreadsheet();
var activeCell = ss.getActiveCell();
if(activeCell.getColumn() == 3 && activeCell.getRow() == 1 && ss.getActiveSheet().getName()=="Sheet1") {
newValue=e.value;
oldValue=e.oldValue;
if(!e.value) {
activeCell.setValue("");
}
else {
if (!e.oldValue) {
activeCell.setValue(newValue);
}
else {
activeCell.setValue(oldValue+', '+newValue);
}
}
}
}Here are the steps to add this script code to the background of Google Sheets so that the dropdown list we created in cell C1 can allow for multiple selections:
- Go to Extensions > Apps Script.
- In the Code.gs window, delete everything that is already there and copy-paste the above macro code.
- Click the Save button in the toolbar (or use the keyboard shortcut Ctrl + S).
- Click Run.
Now, go back to the spreadsheet and try selecting multiple options from the dropdown list. For example, select “Apple” first, then select “Banana”.
You will notice that it takes a second (sometimes two) and will then display both selected items (separated by a comma).
Please note that with this code, it will be possible to select the same item twice. For example, if you select “Apple” and then select “Apple” again, it will display twice in the cell.
If you want to create a dropdown list that allows for multiple selections without repetition, I’ve provided the code further in this tutorial.
How Does the Code Work?
Let’s try to understand this code part by part.
The code starts with the line:
function onEdit(e)onEdit() is a special function in Google Sheets. It’s also called an event handler. This function is triggered whenever there is a change in your spreadsheet.
We want our multiple selection code to run every time an item is selected in the dropdown list, so it makes sense to place our code inside the onEdit() function.
Now, AppScript passes this function as an event object as an argument. Typically, the event object is referred to as e. This event object contains information about the triggered event.
If you’re familiar with AppScript basics, you’ll find the first four lines fairly easy to understand:
var oldValue;
var newValue;
var ss=SpreadsheetApp.getActiveSpreadsheet();
var activeCell = ss.getActiveCell();I have declared two variables – one (oldValue) that will hold the old value of the cell, and another (newValue) that will hold the new value of the cell.
The activeCell variable will hold the currently active cell that has been edited.
Now, we don’t want the code to run every time any cell is edited. We only want it to run when cell C1 on Sheet1 is changed. So, we ensure that with an if statement:
if(activeCell.getColumn() == 3 && activeCell.getRow() == 1 && ss.getActiveSheet().getName()=="Sheet1")The above code checks the column and row number of the active cell and the name of the sheet. Since our dropdown list is in cell C1, it checks if the row number is equal to 1 and the column number is equal to 3.
Only when all three conditions are met will the code inside the if statement be executed.
Here’s the code that gets executed when we’re on the right cell (C1 in our example):
newValue=e.value;
oldValue=e.oldValue;e.oldValue is also a property of the event object, e. It contains the previous value of the active cell. In our case, it would be the value before we made the selection in the dropdown list.
We want to assign that to the oldValue variable.
e.value is a property of the event object, e. It contains the current value of the active cell. We want to assign that to the newValue variable.
First, let’s consider what happens if no option is selected. In that case, e.value will be undefined. When that happens, we don’t want to display anything in the cell.
The same will happen if the user decides to remove all previous selections and start from scratch.
if(!e.value) {
activeCell.setValue("");
}If the user selects an option, the following lines after the else statement will execute. Now we want to specify what to do if an option is selected for the first time in the dropdown list.
This means that e.oldValue is undefined. When that happens, we want only the selected option (newValue) to be displayed in the cell.
if (!e.oldValue) {
activeCell.setValue(newValue);
}Finally, we specify what to do next time an option is selected. This is when both e.value and e.oldValue contain specific values.
else {
activeCell.setValue(oldValue+', '+newValue);
}Once you’re done typing in the code, save it, and then try making multiple selections from your dropdown list. You’ll find all your selected options displayed one after the other, separated by commas.
If you make a mistake, you can always clear the cell and start over. When that happens, we want to display both the previous values and the newly selected value in the cell, all separated by commas.
Below, I have provided details on how you can make changes to your code to ensure that an item can only be selected once, so there are no repetitions.
Allowing Multiple Selections in a Dropdown List without Repetition
Here’s the code that will allow for multiple selections in the dropdown list without repetitions:
function onEdit(e) {
var oldValue;
var newValue;
var ss=SpreadsheetApp.getActiveSpreadsheet();
var activeCell = ss.getActiveCell();
if(activeCell.getColumn() == 3 && activeCell.getRow() == 1 && ss.getActiveSheet().getName()=='Sheet1') {
newValue=e.value;
oldValue=e.oldValue;
if(!e.value) {
activeCell.setValue("");
}
else {
if (!e.oldValue) {
activeCell.setValue(newValue);
}
else {
if(oldValue.indexOf(newValue) <0) {
activeCell.setValue(oldValue+', '+newValue);
}
else {
activeCell.setValue(oldValue);
}
}
}
}
}In the above code, I’m using cell C1 in Sheet1 as an example again. If your dropdown list is in a different cell (or sheet), you need to adjust the code accordingly. For example, if you were using D2, you would change the sixth line of code to:
if(activeCell.getColumn() == 4 && activeCell.getRow() == 2 && ss.getActiveSheet().getName()=='Sheet1') {D2 is the fourth column and the second row.
The code snippet below allows us to ignore any repeated value in the dropdown list:
if(oldValue.indexOf(newValue) <0) {
activeCell.setValue(oldValue+', '+newValue);
}
else {
activeCell.setValue(oldValue);
}The indexOf() function checks if the string in oldValue contains the string in newValue.
If it does, it will return the index of the string in oldValue. Otherwise, it will return a value less than 0.
If the newly selected option exists in our list, we want to leave the list as it is (so we fill cell C1 with the previous value). Otherwise, we want to add the newly selected option to the list with a comma (‘,’) and display it in cell C1.
Multiple Selection in Dropdown List (Entire Column or Multiple Cells)
In the examples above, I showed you how to get a dropdown list with multiple selections for a single cell.
But what if you want to achieve this in an entire column or multiple cells?
You can easily do that with a few minor modifications in the code.
If you want the dropdown list to allow for multiple selections in the entire column C, you need to replace the following line of code:
if(activeCell.getColumn() == 3 && activeCell.getRow() == 1 && ss.getActiveSheet().getName()=="Sheet1")with the following line of code:
if(activeCell.getColumn() == 3 && ss.getActiveSheet().getName()=="Sheet1")By doing this, we’re only checking if the column number is equal to 3 or not. Any cells in Sheet1 and in column 3 will satisfy these IF criteria, and all dropdown lists will have multiple selections enabled.
Similarly, if you want this to be available for the entire column C and column F, use the following line instead:
if(activeCell.getColumn() == 3 || 6 && ss.getActiveSheet().getName()=="Sheet1")The above line uses an OR condition in the IF statement where it checks if the column number is 3 or 6. If a cell containing the dropdown list is in either column C or F, multiple selections will be enabled.
Likewise, if you want this to be enabled for multiple cells, you can also do that by modifying the code.
That’s how you can enable multiple selections in a dropdown list in Google Sheets. Although it’s not available as a built-in feature, you can easily achieve it with a little bit of magic using Google Apps Script.
I hope you found this tutorial helpful!
Want to become an expert in spreadsheets? Check out one of these Google Sheets courses.
Other Google Sheets tutorials that might be helpful:
