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.
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:
- Create a drop-down list using a list of items
- Add the function in the Script Editor that will enable multiple selections in the drop-down.
Let’s look at each of these steps in detail
Creating the drop-down list
Suppose 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:
- Select the cell where you want the drop-down list
- Navigate to Data >Data validation
- In Criteria, select Dropdown (from a range) and then select the range that has the items that you want to show in the drop-down.
- Open the Advanced options and make sure Show a warning is selected instead of Reject input (this is part of allowing multiple inputs, you don’t normally have to do this)
- Click on Save
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 Selections
Below 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:
- Navigate to Extensions > Apps script
- In the Code.gs window, remove anything that is already there and copy and paste the above macro code
- Click on the Save button in the toolbar (or use the keyboard shortcut Control + S)
- Click Run
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).
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.
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. 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 forth column and second row.
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:
if(activeCell.getColumn() == 3 && ss.getActiveSheet().getName()=="Sheet1")
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:
if(activeCell.getColumn() == 3 || 6 && ss.getActiveSheet().getName()=="Sheet1")
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!
Want to become a pro in Google Forms and Sheets? Take our masterclass!
Other Google Sheets tutorials you may find useful:
133 thoughts on “How to Make Multiple Selection in Drop-down Lists in Google Sheets”
One important problem I just ran into with the “no repeats” version, is that it is comparing strings instead of comparing values. e.g. if I am selecting multiple clothing sizes, and I select “XS”, then “S” – it won’t allow “S” to be added because “XS” has the string “S” in it…
I suppose it needs to break the oldValue up by comma and compare each whole string individually.
Thank you for posting this. I have been looking for a solution to this problem for a while and couldn’t find anything that worked as well. I also appreciate the detailed explanation. It’s very helpful since I am trying to teach myself google apps scripts.
Very usefull info. Thank you for the knowledge
This was super helpful – exactly what I needed. Thanks!
Hi Sumit,
This is my first time using google scripts. It works great. Thank you very much.
Thank you for this very helpful tutorial. How would I apply this script to an entire column, minus the top two header rows?
thanks, it worked!
Excellent article and exactly what I was looking for. Thank you!
Thanks a bunch
It helps me a lot
Thank you for this, it worked perfectly for me.
Appreciate this tutorial, didn’t understand all of the code but your explanation was very helpful.
This was super helpful and easy to follow. Thank you for putting this together.
Would like to clarify, actually not only the second row will disappear as long as the copied text is only pasted in one cell (instead of over multiple rows in a column), it disappears too.
I’m attempting to make this work for the whole column. I’ve replaced the code as instructed such that it only looks like a particular column. (In my case, column Q, which is column 17.) However, it only works on one row, row 33.
Here’s the code I’m using:
function onEdit(e) {
var oldValue;
var newValue;
var ss=SpreadsheetApp.getActiveSpreadsheet();
var activeCell = ss.getActiveCell();
if(activeCell.getColumn() == 17 && ss.getActiveSheet().getName()==”Master List of Prospects”) {
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);
}
}
}
}
}
What am I missing?
Nevermind. I figured it out. You must set the data validation to warning only, not reject input.
You just solved my issue after hours of searching the net.
In your version without repetition you made a slight typo in what is line 18 on my version:
activeCell.setValue(oldValue+’,’+newValue);
should be
activeCell.setValue(oldValue+’, ‘+newValue);
I have since rectified it for myself, but others might not notice this if in a rush!
sorry don’t know how to delete the comment above, but got it working
These look the same to me? What difference am I missing?
The space after the comma 🙂
I FREAKING LOVE YOUR TUTORIALS ! So easy, so to-the-point, so detailed and perfect for people who have zero knowledge! Made it really easy for me.
Thank you, to the people who wrote these and/or run the website.
Amazing!
very good, and very well explained step by step.
Congratulations on your teaching to explain.
it was very useful for me
what if the data validation is coming from another tab(sheet)?
Include those tabs in the 6th line of Apps Script
Hi there. I tried this but it shows the red alert sign stating:
Invalid: Input must fall within specified range.
Is there a way to remove that? Also, with this script, can I still sort/filter the date with no problems?
No, there isn’t.
Hey there! Your script is very cool and useful. Thanks for this. But would there be a way to make this function applicable to any other active sheet, not just Sheet1 (indicated in the script code)? It would also be easier if we can simply copy and paste this data validation to make it applicable to any other cell in any given sheet, but I guess the script limits this function to only Column C or F in Sheet1. Lastly, would there be a code that would allow the user to get the data validation from another sheet besides Sheet1?
Thanks!
Change the code on the 6th line to include the cell values or Sheets you’d like to include joined by the OR operator ||
Many thanks for your tutorial, was very useful. I’ll make a snake view of your other posts.
Hello! Just wanted to point out an error here:
In the section “Multiple Selection in Drop Down (Whole Column or Multiple Cells)”
there is a suggestion to replace a line of code with the below:
if(activeCell.getColumn() == 3 && ss.getActiveSheet().getName()==”Sheet1″)
However, as a novice coder (I’m a customer success manager :P) I did not realize that the bracket { was left off of the end of code snippet so I did not include it in my Script.
This caused a tricky issue because it DID in fact enable the multi-select on the dropdown, but it also made it so that any other text I entered into other columns of the spreadsheet disappeared.
Just wanted to flag this to hopefully save someone else the heartache!
This comment saved me!!!!! THANK YOU 🙂
This tutorial was amazing but this kept happening to me – after I entered the code, I had multi-select on the drop down but everything else I put on the sheet after that disappeared. But adding the { at the end of the if line of code made it work THANK YOU!
Hey – Thanks so much for this. I have this working across multiple columns, and multiple sheets; however, I am encountering the following error:
TypeError: Cannot read property ‘value’ of undefined
onEdit @ Code.gs:7
While it says there is an error, the code is actually working in the sheet…so I am very confused. One thing that is odd, however, I used the following line:
if(activeCell.getColumn() == 3 || 5 && ss.getActiveSheet().getName()==”ATTEMPT” || “ATTEMPT 2”){
The intent was to have the multi-select work for columns C & E, but, it is *also* working in G in the workbook (“ATTEMPT”), and in Column B in the second sheet in the workbook (“ATTEMPT 2”).
Additionally, is there a way of correcting the data validation so that it doesn’t throw the error “Input must fall within specified range”?
Thanks so much for all the help!
In troubleshooting, the error is coming in whenever I add to the logic of the primary “if statement.” I am getting the same error whenever I try to expand the logic beyond a single cell to multiple cells, the entire column, etc.
if(activeCell.getColumn() == 3 && activeCell.getRow() == 1 || 4 && ss.getActiveSheet().getName()==”Sheet1″)
Is my current modification, but this is actually allowing the mutli-select dropdown in any data validation done within the worksheet, not limited to the column.
Thank you so much! This is super helpful. How could you get this script to work on every sheet in the spreadsheet?
Change Sheet1 to include the other sheets in your workbook with the or operator ||
This is such a helpful script! And as a total AppScript newbie I really appreciate the way you break down how each part of the script functions!
I am trying to make 7 columns of dropdown options that all allow multiple selections with the output producing commas and spaces between, and no repetitions allowed, as the last part of your article provides. However, I am running into a couple problems:
Firstly, I cannot clear any of the cells. When I delete the contents (by selecting and hitting “delete” key), it adds the text “[object Object] into the output (e.g. the output becomes “Apple, Banana, [object Object]”.
Secondly, the script seems to be affecting the entire worksheet rather than the columns specified. When I hit “Run” in the AppScript editor it is also coming up with this error, so maybe that is part of the problem but I really don’t know:
TypeError: Cannot read property “value” from undefined.
onEdit @ Code.gs:7
Here is the script:
function onEdit(e) {
var oldValue;
var newValue;
var ss=SpreadsheetApp.getActiveSpreadsheet();
var activeCell = ss.getActiveCell();
if(activeCell.getColumn() == 18 || 19 || 20 || 21 || 22 || 23 || 24 && ss.getActiveSheet().getName()==”IMAGERY Basic”) {
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);
}
}
}
}
}
Would really love some help if possible! Thanks again so much for the excellent article.
When I use this script Allowing Multiple Selections in a Dropdown list (without repetition), I keep getting this error.
11:01:41 AM Error
TypeError: Cannot read property ‘value’ of undefined
onEdit @ Code.gs:7
Advice please
function onEdit(e) {
var oldValue;
var newValue;
var ss=SpreadsheetApp.getActiveSpreadsheet();
var activeCell = ss.getActiveCell();
if(activeCell.getColumn() == 20 && activeCell.getRow() == 6 && ss.getActiveSheet().getName()==’Master’) {
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);
}
}
}
}
}
You have make sure you saved the data validation first. Then make sure this line of code is referring to the correct cell. if(activeCell.getColumn() == 20 && activeCell.getRow() == 6 && ss.getActiveSheet().getName()==’Master’) {
Thanks for this! Very clear and extremely helpful!
Hi,
Hope you are well. What to do if I want to apply vlookup to the adjacent cell to which multi-select is already present and want that adjacent cell to put exactly the same thing over there.
For ex: I have three columns: Country, Continent, Region. So lets say if I select India in the country, the vlookup looks from the list and identifies the continent as Asia and region as south asia.
Now if I want to select India and USA in the country, the vlookup shows both Asia and Americas in the continent and South Asia and North America in the region.
Hope you can help.
This one doesn’t seem to work for me.
if(activeCell.getColumn() == 3 && ss.getActiveSheet().getName()==”Sheet1″)
Hi I think I got it. Activespreadsheet instead of activesheet
if(activeCell.getColumn() == 3 && ss.getActivespreadSheet().getName()==”Sheet1″)
Thanks much!!
Hi! So i used the code for Multiple Selection in Drop Down (Whole Column) but it’s not applying the multiselect to just those specified columns. when I type into a non-drop down cell outside of the columns I listed in the code, click out, then type over that cell it fills the cell with the first and second text separated by a comma. how do I get this code to only apply to the specified columns and not the whole sheet? below is the code I’m using.
function onEdit(e) {
var oldValue;
var newValue;
var ss=SpreadsheetApp.getActiveSpreadsheet();
var activeCell = ss.getActiveCell();
if(activeCell.getColumn() == 18 || 19 || 20 || 21 || 22 || 23 || 24 || 25 || 26 || 28 || 29 || 30 && ss.getActiveSheet().getName()==”Monthly Trends”) {
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);
}
}
}
}
}
Hello,
I am using this tutorial and was able to figure out how to successfully create a multi select drop down for an entire column, which is great! However, whenever I attempt to edit any other cell on the google excel doc it automatically deletes when I hit enter or click out of the cell. Any ideas on how to correct this?
I am using this for multiple values with columns 2 and 3 indicated on a specific sheet in the workbook. but it seems to do it for every cell in every workbook.
sorry I mean every cell in every sheet in the workbook.
It happened to me as well 🙁
Anyone knows how to fix it?
It works perfectly on the selected / defined sheet (meaning the code will affect only the selected cells) but in all other sheets every cell is affected even if it doesn’t contain a drop-down menu. So whenever you add something to previous cell content, the content will multiply. <.-<8
Hi, try to use the OR operator for the following script row:
if(activeCell.getColumn() == 3 || activeCell.getColumn() == 6 || activeCell.getColumn() == 11 && ss.getActiveSheet().getName()==”Finished/Watched”) {
Thanks Cristian, your tweek to the code helped me!
The OR operator restricts the code to apply else where on the spreadsheet.
Thank you Cristian for sharing this tip with the community!
So i tried using this today and found an issue with
if(activeCell.getColumn() == 3 || 6 && ss.getActiveSheet().getName()==”Sheet1″)
I found that it was causing an issue where all cells on the sheet and other sheets were having this applied. I solved it by using the following command for an AND
//for easy reference column 13=M
var activecolumn = activeCell.getColumn();
if((activecolumn >= 13 && activecolumn <= 16) && ss.getActiveSheet().getName()=='Sheet1')
if you wanted to still use the OR instead you can use something like
var active = activeCell.getColumn();
if((active == 13 || active == 14 || active == 18 || active == 22) && ss.getActiveSheet().getName()=='Sheet1')
Ahh thank you!! I was really struggling, but this worked. 🙂
There is an issue with this script. While it works smoothly for the above specific task, but when I am copying and pasting any other column (which I haven’t included in the script) into a different sheet tab, some values are getting deleted automatically.
I wondered why this is happening. Then I tried removing the script and did the pasting again, then it is working fine and all values are pasting without any deletion. So I am sure there is something wrong with the script, but unable to find out what is. Please help.
When I use the code that “Allows Multiple Selections in a Dropdown list (without repetition)”, I’m noticing that every cell in the sheet has weird logic applied to it. If I select a cell that doesn’t have the drop down menu and populate it with anything, and then try to overwrite it with something new it will update after a second and combine the original value with the new value. How do I prevent this from happening as it is very annoying to delete the cell before entering a new value. Copy and pasting is also a nightmare as well.
Hi Sumit, thanks for this, it’s really useful. I’ve copied the code and adapted it for my spreadsheet and it works fine when I apply it to one column (column N). I wanted to add the option to a second column (column O) and replaced the line of code as suggested:
if(activeCell.getColumn() == 14 && ss.getActiveSheet().getName()==”Research”)
with
if(activeCell.getColumn() == 14 || 15 && ss.getActiveSheet().getName()==”Research”)
however it has applied it to the whole spreadsheet, rather than just the two columns. Am I doing something wrong?
I’m not able to get the multi select to work. My list is short (just four items) and I’ve tried applying the script to a single cell and also tried applying to multiple cells. I had this working in Excel and am now trying to duplicate in Google Sheets so our team can have easy access cross-platform. My drop-down lists are working, just not the multi-select.
Any advice would be greatly appreciated as I’ve tried other options from different sites already.
Thank you Sumit!
That’s very good content! Thanks for sharing
THANK YOU SUMIT, THIS HELPED ME A LOT!!
This was great! How do you write in a specific number of rows?
I want this to apply to all the rows in column C except the first row (title row) how do I write that in?
Hi,
thanks for the good explanation, it is great for beginners 🙂
But I still have one question … how do I enter a specific ” row-range”. Like: it should beginn at row 2, and end at row 20.
Thanks,
Sarah
Hi,
With using the ‘without repetitions code’, how do you take the source data from another sheet like Sheet 2 A2:A8 while populating Sheet 1 Column C? The code works fine if the data is on the existing/active sheet but what if the data is on another sheet?
Thank you!
If I need to have multiple selection for one column for 5 different sheets, do I need to enter the code for 5 times?
Any other methods which is faster?
Thank you.
I’m using the following code it doesn’t seem to work properly.
var activeCell = ss.getActiveCell();
if(activeCell.getColumn() == 3 || 6 || 9 && ss.getActiveSheet().getName()==”Sheet1″)
Because “Multiple Selection in Drop Down” is also applied not only to columns C, F, I also to all columns.
Then I looked for references and found code that worked.
var activeCell = ss.getActiveCell();
var active = activeCell.getColumn();
if((active == 3 || active == 6 || active == 9) && ss.getActiveSheet().getName()==”Sheet1″)
I don’t know if this method also works in other cases. But this way worked in my case which needed more than two columns.
I’m getting the below Error if I try to add your code
Exception: Please select an active sheet first.
onEdit @ MS Column 12.gs:5
Trying to achieve : Different Multi Select for multiple columns in the same Google sheet. I tried adding a new script and it always disables the previous one.
Code below:
function onEdit(e) {
var oldValue;
var newValue;
var ss=SpreadsheetApp.getActiveSpreadsheet();
var activeCell = ss.getActiveCell();
var active = activeCell.getColumn();
if((active == 12 || active == 13 || active == 14 || active == 15 || active == 16 || active == 17 || active == 18 || active == 19) && ss.getActiveSheet().getName()==”Requirement Request”) {
newValue=e.value;
oldValue=e.oldValue;
if(!e.value) {
activeCell.setValue(“”);
}
else {
if (!e.oldValue) {
activeCell.setValue(newValue);
}
else {
activeCell.setValue(oldValue+’, ‘+newValue);
}
}
}
}
Is it possible to make clicked output vertical per word (like creating a new line) instead of horizontal?
Thank you for sharing this script. It is incredibly helpful. I can get it to work perfectly if the range of drop down options are on the same sheet as the drop down, but how do I modify the code to multi-select the options when the selection range is in a different sheet in the workbook?
This was so helpful. The only small glitch I have run into is that it does this for every cell in the workbook, regardless of whether it is a drop down or not. How do I limit it to just the cells with a drop down?
Hi Sumit! This was very helpful. I came up with a simplified version of what you created, which works on any cell edited in a sheet, so long as that cell is part of a range that has “data validation” turned on to “select from a list”. It also alleviates the need to figure out what cell you’re in, since e.range is that. We just need to validate that e.value and e.oldValue exist, because otherwise it is not a single cell.
So anyway, here’s what I came up with:
const separator = ‘, ‘
function onEdit(e) {
if (e.value != null && e.oldValue != null && e.value !== “”) {
var dataValidation = e.range.getDataValidation();
if(dataValidation != null && dataValidation.getCriteriaType() == SpreadsheetApp.DataValidationCriteria.VALUE_IN_RANGE) {
if(e.value.indexOf(separator) < 0 && e.oldValue.indexOf(e.value) < 0) {
e.range.setValue(e.oldValue + separator + e.value);
}
}
}
}
I dumped this into a Gist as well:
https://gist.github.com/sschwartzman/ed34dec66231acd9092051170d0ed6dc
THIS IS AWESOMEEEEEEEEEEEEE.
Many more EEEEEEE
This is exactly I needed, a code that should work for all cells, in whole SHEEETTTTT.
YiiiPiiiiiiiiiiiiiiiiiii
Thanks a Ton SETH and SUMIT
I just used this code and thought I was doing something wrong but IT WORKED!! Thank you so much for this simplified version. I am not techy at all and am ecstatic I got this so quickly.
Thank you for your code, sir. This really worked for me. Just a question, how should this be tweaked if you want it for a specific tab only? Thank you.
Change the code in line 6 from
to whichever tab you’d like to use
Thank you so much! This was exactly what I was looking for.
I’m trying to make this work for two columns so I have this:
if(activeCell.getColumn() == 4 || 17 && ss.getActivespreadSheets().getName()==”Sheet 1″)
but it only applies it to column 4 and it doesn’t work for column 17, any ideas?
You have to add “activeCell.getColumn() ==” the code thinks you mean “is column 4 or the number 17 and is sheet 1”. The number 17 will always evaluate to true. You will also need parentheses because it defaults to “value || ( value && value)” which is not what you want here.
if((activeCell.getColumn() == 4 || activeCell.getColumn() == 17 ) && ss.getActivespreadSheets().getName()==”Sheet 1″)
This has been helpful information for a non-coder, but I’m stuck on what to do if I want to apply the multiple selection code to different columns in multiple Sheets in my workbook. Any direction welcome.
Thank you so much for the tutorial! I was wondering what I would have to add to link my dropdown box with a question on Google Forms? Thanks again 🙂
Could you do this so the selections are listed (stacked) within the cell via line breaks instead of being separated by commas?
I know next to nothing about writing code, but am trying to gain some basics as needed. This was a very helpful write-up. Im going to do some additional studying and experimenting. In case this yields a solution sooner, does anyone know how to add to or modify this so that selecting an option that is already displayed removes it from the displayed items in the cell? I would like to be able to remove a single item from the list without having to erase all and start over without losing the ability to select and display multiple items.
This is very helpful, thank you.
Is there a way I can delete the variable once already selected?
Hi,
Very helpful. Thanks!
Any ideas on how we can replace the comma separator by a line break, within the cell?
Thanks!
Me too. Can’t figure it out.
Is it possible to avoid free text entry in the cell? for example, selecting: Apple, Banana, and Kiwi would yield the cell (i.e. C1) to show: Apple, Banana, Kiwi
I could then return to C1 and type Strawberry; then C1 would show: Apple, Banana, Kiwi, Strawberry.
Is there code that can be added here to override typed text not coming from the Data validation?
How do you use this with 2 or more sheets with different columns affected per sheet? I tried adding a new script and it always disables the previous one.
Hi RJ,
Did you find a solution ? If yes, I’m interesting to know how you fixed this issue.
Thanks
Francois
Thank you so much. Was very helpful. Not only the article itself but the comment section as well 🙂
Nice ! Thanks for sharing.
Hi Sumit… This was really helpful but can you help me with a code which I can use on a file which has multiple tabs(minimum) and multiple drop down selection is in atleast 3 columns on every tab?
When I’m trying to implement Multiple Selection in Drop Down (Whole Column) I’m getting the following error “TypeError: Cannot read property ‘value’ of undefined
onEdit @ Multi Select Column 12.gs:7” — Please note that my picklist values are in sheet 2. I tried having the values in the ‘Requirement Request’ itself too, but alas it doesn’t work. Can please someone help?
I my pasting my code below:
function onEdit(e) {
var oldValue;
var newValue;
var ss=SpreadsheetApp.getActiveSpreadsheet();
var activeCell = ss.getActiveCell();
if(activeCell.getColumn() == 12 && ss.getActiveSheet().getName()==’Requirement Request’) {
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);
}
}
}
}
}
Thank you!
I have this plugged in to my sheet and the drop down multi selection is working but say I want to unselect an answer it will not deselect? Any ideas?
function onEdit(e) {
var oldValue;
var newValue;
var ss=SpreadsheetApp.getActiveSpreadsheet();
var activeCell = ss.getActiveCell();
if(activeCell.getColumn() == 13 && ss.getActiveSheet().getName()==’Form Responses 1′) {
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);
}
}
}
}
}
worked as described for a whole column pulling from a range in a different sheet, no repetitions, effortless, copy/paste, 2 minutes later… done – you rock!
Thank you
Thanks, this worked for me, i wish learned this one day too 🙂
A cleaner version:
function onEdit(e) {
var oldValue;
var newValue;
var ss=SpreadsheetApp.getActiveSpreadsheet();
var activeCell = ss.getActiveCell();
if(activeCell.getColumn() != 2 || activeCell.getRow() == 1 || ss.getActiveSheet().getName() != “Sheet1”) {
return
}
newValue = e.value;
oldValue = e.oldValue;
if(!e.value) {
activeCell.setValue(“”);
return
}
if (!e.oldValue) {
activeCell.setValue(newValue);
return
}
activeCell.setValue(oldValue+’, ‘+newValue);
}
Much appreciated. Is there a correction you could suggest to the issue that you reference yourself about not being able to manually edit a cell that you’ve already added content to? Example, I’ve added ‘apple, banana’, but then when i try to add a manual entry I get ‘apple, banana, apple, banana, manual entry’. This is with your extra code to avoid duplication.
Thanks.
How to do this with the new Google Sheets interface: Script editor is gone, replaced with Apps Script, which is such a headache to use or doesn’t really work right now, who knows ?
For the moment, you can still use the old editor through a selection when you first enter the screen
Hello: This worked perfect for the one sheet, however I need to be able to duplicate the sheet and the script still work. How do I accomplish that without having to manually go in and change the script every time I duplicate it?
I am using following code for Multiple Drop drown for entire coloumn.
function onEdit(e) {
var oldValue;
var newValue;
var ss=SpreadsheetApp.getActiveSpreadsheet();
var activeCell = ss.getActiveCell();
if(activeCell.getColumn() == 2 && 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);
}
}
}
}
Hi! Is it possible to allow copy and paste in these multi-select cells?
I have used the code successfully for multi-select columns and would like to be able to batch update cells with via copy and paste (instead of using the drop-down for each column x row)
Thanks in advance for your help!
I can’t get this to work and I think it is because there’s an issue using .setValue() in an onEdit function. Everything else is working.
Any ideas how to resolve this?
I worked out what the error was. This is the code I used to create the data validation
let ruleNewOwners = SpreadsheetApp.newDataValidation()
.requireValueInRange(listValues)
.setAllowInvalid(false)
.setHelpText(‘Select group owner from list’)
.build();
I had to change to .setAllowInvalid(true), and then it worked instantly and perfectly.
*sigh
How do I find this code to .setAllowInvald(true)?
I made the data validation dropdown via this tutorial, not via code, originally
This was a great guide, thank you!
I’ve been able to apply this Script to multiple sheets by adding ,’sheet_name’) after the first sheet name.
Does anyone here know of a way to apply the script to all sheets? I have to manually add the name as I duplicate the sheet to reuse.
How do I get this code to apply to multiple sheets?
I tried it this way:
if(activeCell.getColumn() == 6 && ss.getActiveSheet().getName()==”Taylor.TS”||”Taylor.TS.7/18″)
It still works BUT i get issues with the other cells on the sheet not holding their formatting after data is deleted and reentered. It (the “123”) always switches back to automatic. Dates are getting changed to decimals. Only occurs when I try to get this to apply to multiple sheets within the file.
Any help is much appreciated.
Great tutorial!
I have just one issue: I explicitely want to be able to select the same option multiple times but it doesn’t seem to work if i select the same option directly after having selected the same one. The onEdit seems not to be triggered if the value wasn’t actually changed first.
So I can select ‘apple, banana, apple’ but not ‘apple, apple’ because no real ‘edit’ was triggered.
Is there a workaround?
This is very helpful, thank you! It would be great if you could add a line that would make the items sort by alphabetic order, otherwise if one clicks several items in different order for different rows, when we filter it, it’ll come as different items, for example:
Apple, Pear, Orange
Pear, Apple, Orange
Orange, Apple, Pear
These are essentially the same (if order doesn’t matter) but this will look like 3 separate things when you set a filter for the column. Can you suggest a line that would help sort these out in alphabetic order, so it doesn’t matter in which order the person selects the dropdown?
The code works, but when I overwrite the value in the cell, the old value remains in the cell. What part of the code should I change to prevent this from happening?
I keep getting an error – It says attempted to execute onEdit, but could not save. Now what?
Is there a way to have this running on multiple columns? I have 3 columns in a sheet and can’t seem to get it to work across columns. I was able to get to work across multiple rows just not columns
Sumit, thank you so much for this info (and other tutorials as well)! I’m NOT a coder but have really appreciated your instructions as I’ve been trying to make Google Sheets more functional whilst constrained to it for team-editing reasons. Truly–between your instructions and the comments I’ve been successful and amazed it’s working!
I put this function:
function onEdit(e) {
var oldValue;
var newValue;
var ss=SpreadsheetApp.getActiveSpreadsheet();
var activeCell = ss.getActiveCell();
if(activeCell.getColumn() == 19 && activeCell.getRow() == 2 && 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);
}
}
}
}
}
It is telling me that my error is in line 5 : var activeCell = ss.getActiveCell();
I can't find the issue could you help
I put this function:
function onEdit(e) {
var oldValue;
var newValue;
var ss=SpreadsheetApp.getActiveSpreadsheet();
var activeCell = ss.getActiveCell();
if(activeCell.getColumn() == 19 && activeCell.getRow() == 2 && 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);
}
}
}
}
}
It is telling me that my error is in line 5 : var activeCell = ss.getActiveCell();
I can't find the issue could you help
Hi there,
I am so sorry for bothering you all, I am a newbee and a bit lost. I’m attempting to make this work for the whole column “G”. I have been trying to adapt it reading the comments, still I am not able to select multiple options. Probably it is simple beginner’s mistake. Any advice? 🙂
function onEdit(e) {
var oldValue;
var newValue;
var ss=SpreadsheetApp.getActiveSpreadsheet();
var activeCell = ss.getActiveCell();
if(activeCell.getColumn() == 6 && activeCell.getLastRow && 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);
}
}
}
}
if(activeCell.getColumn() == 6
switch to
if(activeCell.getColumn() == 7
6 is column f, 7 is column g 🙂
Just in case you want remove newValue if it already exists in oldValue :
function onEdit(e) {
var oldValue;
var newValue;
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var activeCell = spreadsheet.getActiveCell();
if (activeCell.getColumn() == 6 && activeCell.getRow() > 1 && spreadsheet.getActiveSheet().getName() == “Draft”) {
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 {
var array = oldValue.split(", ");
array = array.filter(function(element) {
return element !== newValue;
})
activeCell.setValue(array.join(", "));
}
}
}
}
}
How would I edit the code if my item list is on a separate sheet to where I want the drop-down list to be? For example, my drop down is on a sheet called Customer List and is in Column D. My list of items is on a separate sheet called App List in Column A. Thanks for your help!
Has anyone had issues with this since Google Sheets updated their drop-down chips? It worked for me previously, but not anymore! 🙁
Make sure you change the advanced options to Show warning instead of reject input
Thanks! That’s very useful and actually is working. P.s. few notes for the future readers:
– Script Editor now is under the Extensions -> Apps Script
– When setting up the dropdown values make sure you use the setting ‘show a warning’ if the data is invalid (under Advanced options), otherwise you won’t be able to select multiple options.
Thanks for this comprehensive tutorial and everyone collaborating in the comment section, it helped a LOT!
With the help of everyone I created the following code where I am running in the issue that the Multi Select is NOT working in column 27 (column AA). I researched and found that I am right with AA = 27 but I can’t solve the issue at hand. Here is my code
function onEdit(e) {
var oldValue;
var newValue;
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var activeCell = ss.getActiveCell();
if(activeCell.getColumn() == 13 ||activeCell.getColumn() == 14 ||activeCell.getColumn() == 15 ||activeCell.getColumn() == 18 ||activeCell.getColumn() == 21 ||activeCell.getColumn() == 24 ||activeCell.getColumn() == 27 && spreadsheet.getActiveSheet().getName()==”Kostentabelle”) {
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 {
var array = oldValue.split(", ");
array = array.filter(function(element) {
return element !== newValue;
})
activeCell.setValue(array.join(", "));
}
}
}
}
}
Any help would be highly appreciated!
Many thanks, Dom
If the code does not work for you try to Data Validation and change “on invalid data” to “show warning” instead of “reject input”.
Hi. Thanks so much for your tutorials. Weirdly, I don’t see being able to List from a Range under the Criteria. Google might have switched it out. I only can do DROPDOWN FROM A RANGE. So weird. Is there a new way to do this with the (assuming) changes to Data Validation?
thx!
Yes, Google just changed the wording with the April Update. It’s the same thing. We’ve updated the article now.
I am able to select multiple items for a single cell, but whenever more than one item appears in the cell the cell has an error comment: “Input must be an item on the specified list”
How do I get rid of this error?
There is no way to do that, unfortunately.
Thank you – Made me look good to my boss!
This was really helpful, thank you. I had used another similar method that did not work properly, so this was excellent to find.
One thing I noticed that I managed to fix was running multiple scripts for this on the same sheet made it function such that the dropdowns were fine, but anything I typed in other blank cells disappeared. I eventually fixed this by just running all the versions of this script in one, with different cell ranges specified (as per the instructions). It seemed to fix the glitch. I don’t know enough about how this works to figure out how the glitch was functioning.
But all that being said, I’m curious if there is a way to add some minor formatting to this. Is there a way to trigger the list of multiple items to appear as a list in the cell, instead of separated by commas with no spaces? I’d even take the comma, as long as it added a line break to put each item on a different line in the cell.
Thanks again for all the help.
This does’t appear to work with the changes to Googlesheets that went live in April 2023
Yes, you just have to make sure Show a warning is selected under the advanced options of the data validation
Hey there,
Can I get support from someone with that? It seems like the article isn’t updated.
I wasn’t able to implement it following the instructions (even not step 1).
It seems like the screenshots aren’t relevant.
Thank you for your help.
Hi,
The code and everything still works the same but the menu path is a little different
Navigate to Data > Data validation > Dropdown from a range and enter the data
Make sure “Show a warning” is selected in the advanced settings. Then follow the guide for adding the code.