The main purpose of drop-down lists in Google Sheets is to offer options that a user can choose from. It gives users a clear look at all the available options and also makes sure the user selects only the items allowed. Show
A drop-down list also ensures that there are fewer errors as the user can now choose from a pre-defined list instead of manually typing the cell content. Google sheets let us use this functionality with ease. With just a few clicks, you can create either a single-cell dropdown or populate a whole row or column with dropdown lists. However, you will notice that the default Google Sheets dropdown list allows the user to only select a single item from the list. Often times you may need to select more than one option in a drop-down list. For example, when there’s a collection of colors for you to choose from, you might like more than one color. Or might want to get a list of coding languages the user is proficient in. In such cases, it’s possible that the user knows more than one and there is a need to select multiple options from the drop-down. Therefore, multiple selections in dropdown lists can be quite useful. Unfortunately, this option is not traditionally allowed in Google Sheets. You are only allowed one option at a time. The good news is that there’s a way around this. It is possible to make your dropdown list allow multiple selections by using Google AppScript. In this article, I will show you how to create a drop-down list that allows multiple selections (something as shown below).  But first, let’s start from scratch. Table of Contents
Let’s begin by creating a fresh dropdown list from a list of color options. Click here to get a copy of the Google Sheets that has multiple selections enabled (make a copy to use it). Allowing Multiple Selections in a Dropdown list (with repetition)For this tutorial, I will use the following dataset of items and will create the drop-down in cell C1 To create a drop-down list that allows multiple selections, you need to do two things:
Let’s look at each of these steps in detail Creating the drop-down listSuppose I have a dataset of items as shown below and I want to create a drop-down list in cell C1. Below are the steps to so this:
Your dropdown will now appear in your designated cell (C1 in this example). When you click on the arrow you will see your list of options. Notice now that you are only allowed to select one option at a time. Now let me show you how to convert this drop-down (which allows only one item to be displayed in the cell) to the one that allows multiple selections. And to do that, you need to add the function script in the Google Sheets script editor. Adding the Google Apps Script to Enable Multiple SelectionsBelow is the script code that you would have to copy and paste in the script editor (steps mentioned below section 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);
}
}
}
}Below are the steps to add this script code in the Google Sheets backend so that the drop-down we created in cell C1 can allow selecting more than one option:
Now come back to the worksheet and try making multiple selections in the drop-down. For example, first, select Apple and then select Banana. You will notice that it takes a second (sometimes two seconds) and will then show you both the selected items (separated by a comma). Note: You would see a red triangle at the top-right part of the cell. It may look like an error (which it is as the value you have in the cell is not what it expects). You can safely ignore this. Also note that with this code, it will allow you to select the same item twice. For example, if you select Apple and then select Apple again, it will show it twice in the cell. If you want to create a drop-down list that allows multiple selections without repetition, I have provided the code later 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 on Google Sheets. It is also known as an event handler. This function is triggered every time there is a change in your spreadsheet. We want our multiple selection code to run every time an item is selected from the dropdown list, so it makes sense to put our code in the onEdit() function. Now, the AppScript passes this function as an event object as an argument. Typically, the event object is called e. This event object contains information about the event triggered. If you know the basics of AppScript, you will find the first four lines quite 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 variable activeCell 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 CA1 of Sheet1 is edited. So we make sure of that by using an if statement: if(activeCell.getColumn() == 3 && activeCell.getRow() == 1 && ss.getActiveSheet().getName()=="Sheet1") The above code checks row and column number of the active cell and the sheet name. Since out drop-down is in cell C1, it checks whether the row number is 1 or not and whether the column number is 3 or not. Only when all these three conditions are met that the code within the IF statement is executed. Below is the code that is executed when we are 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. This holds the previous value of the active cell. In our case, this would be the value before we make the drop-down selection We want to assign this to the variable oldValue. e.value is a property of the event object, e. This holds the current value of the active cell. We want to assign this to the variable newValue. First, let us consider what happens if no option is selected. In that case, e.value will be undefined. When this happens, we do not want anything displayed in cell A1. So we put a blank value on the cell. This will also be the case if the user decides to delete all previous selections and restart from scratch. if(!e.value) {
activeCell.setValue("");
}If the user does select an option, then the lines following the else statement will be executed. We now want to specify what to do if an option is selected for the first time from the drop-down list. That means e.oldValue is undefined. When this happens, we want only the selected option(newValue) to be displayed in cell A1. if (!e.oldValue) {
activeCell.setValue(newValue);Finally, we specify what to do the next time onwards that an option is selected. That means when both e.value and e.oldValue hold specific values. else {
activeCell.setValue(oldValue+', '+newValue);
}Once you are one typing the code, save it and then try making multiple selections from your dropdown list. You will find all your selected options displayed one by one, separated by commas. If you make a mistake, you can always clear the cell and start over. When this happens, we want to display both the previous values and the newly selected value in cell A1, all separated by commas. Note: When you use the above code, it will not allow you to go back and edit part of the string. For example, if you want to manually edit the item string or want to delete a part of it, you won’t be able to do this. You will have to delete all the cell content and start over if you want to make any changes. There is, however, a small problem with this. Notice that if you select an item more than once, it will again be entered in your list of selections. In other words, repetition is allowed. But usually, we do not want that. Below, I have provided details of how you can make changes to your code to make sure an item can only be selected once so that there are no repetitions. Allowing Multiple Selections in a Dropdown list (without repetition)Below is the code that will allow multiple selections in the drop-down 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 am again using the cell C1 in the worksheet Sheet1 as an example. In case your drop-down is in a different cell (or sheet), you need to adjust the code accordingly. The below part of the code makes it possible for us to ignore any repeated value in the drop-down: if(oldValue.indexOf(newValue) <0) {
activeCell.setValue(oldValue+', '+newValue);
}
else {
activeCell.setValue(oldValue);
}The indexof() function here checks if the string in oldValue contains the string in newValue. If it does, then it will return the index of the string in oldValue. If not, it will return a value less than 0. If the newly selected option does exist in our list, we want to leave the list as it is (so we populate cell C1 with the previous value). If not, then we want to add the newly selected option to the list with a comma (‘, ’) and display it in cell C1. Multiple Selection in Drop Down (Whole Column or Multiple Cells)In the above examples, I have shown you how to get a multi-selection drop-down in a cell. But what if you want to get this in an entire column or multiple cells. You can easily get this done with some minor modifications in the code. If you want the drop-down to allow selecting multiple items 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: function onEdit(e)1 When you do this, we are only checking whether the column is 3 or not. Any cells which are in Sheet1 and in Column 3 would satisfy this IF criteria and there any drop-down in this would allow multiple selections. Similarly, if you want this to be available for entire column C and F, use the below line instead: function onEdit(e)2 The above line uses an OR condition within the IF statement where it checks whether the column number is 3 or 6. In case a cell that has the drop-down is in column C or F, multiple selections would be enabled. Similarly, if you want this to be enabled for multiple cells, you can do that as well by modifying the code. So this is how you can enable multiple selections in a drop-down in Google Sheets. While this is not available as an in-built feature, you can easily do this with some Google Apps Script magic. Hope you found this tutorial useful! Other Google Sheets tutorials you may find useful:
Most Popular Posts
5 Simple Ways to Highlight Duplicates in Google SheetsIF CONTAINS Google Sheets Formulas [2 Clever Options]How to Apply Formula to Entire Column in Google SheetsHow to VLOOKUP From Another Sheet in Google SheetsSumitGoogle Sheets and Microsoft Excel Expert. 81 thoughts on “How to Make Multiple Selection in Drop-down Lists in Google Sheets”
|
How do I select multiple selections in dropdown?
Pos Terkait
Copyright © 2024 apacode Inc.
