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
- Allowing Multiple Selections in a Dropdown list (with repetition)
- Creating the drop-down list
- Adding the Google Apps Script to Enable Multiple Selections
- Allowing Multiple Selections in a Dropdown list (without repetition)
- Multiple Selection in Drop Down (Whole Column or Multiple Cells)
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 cell C1 (the one where you want the drop-down)
- Click the Data option in the menu
- Click on Data Validation
- In the Data Validation dialog box, make sure the ‘Cell range’ refers to the cell where you want the drop-down
- In Criteria, select ‘List from a range’ and then select the range that has the items that you want to show in the drop-down.
- 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:
- Click the Tools option in the menu
- Click on Script Editor. This will open the Script Editor in a new window
- 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)
- Give the project a name (you only need to do this once)
- Close the Script Window (if you want to)
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)1When 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)2The 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:
- How to Apply a Formula to an Entire Column in Google Sheets
- How to Combine Cells in Google Sheets (using simple Formulas)
Most Popular Posts
5 Simple Ways to Highlight Duplicates in Google Sheets
IF CONTAINS Google Sheets Formulas [2 Clever Options]
How to Apply Formula to Entire Column in Google Sheets
How to VLOOKUP From Another Sheet in Google Sheets
Sumit
Google Sheets and Microsoft Excel Expert.
81 thoughts on “How to Make Multiple Selection in Drop-down Lists in Google Sheets”
dog
April 25, 2020 at 11:30 am
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.
ReplyChris
April 30, 2020 at 6:21 pm
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.
Replyarbin kumar
May 6, 2020 at 9:10 am
Very usefull info. Thank you for the knowledge
ReplyDRT
July 17, 2020 at 8:05 pm
This was super helpful – exactly what I needed. Thanks!
ReplyAli
July 21, 2020 at 9:08 pm
Hi Sumit,
This is my first time using google scripts. It works great. Thank you very much.
ReplyElliott
July 26, 2020 at 2:32 am
Thank you for this very helpful tutorial. How would I apply this script to an entire column, minus the top two header rows?
ReplyRam
August 24, 2020 at 4:05 am
thanks, it worked!
ReplyChad
October 7, 2020 at 10:11 pm
Excellent article and exactly what I was looking for. Thank you!
ReplyRamlan
October 8, 2020 at 8:26 am
Thanks a bunch
Reply
It helps me a lotLeslie Coster
November 11, 2020 at 8:17 am
Thank you for this, it worked perfectly for me.
ReplyEli
December 2, 2020 at 9:21 pm
Appreciate this tutorial, didn’t understand all of the code but your explanation was very helpful.
Replymark
January 3, 2021 at 10:22 pm
This was super helpful and easy to follow. Thank you for putting this together.
ReplyMichael
January 30, 2021 at 12:15 am
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?
ReplyMichael
January 30, 2021 at 12:18 am
Nevermind. I figured it out. You must set the data validation to warning only, not reject input.
Reply
Cameron
February 21, 2021 at 9:12 pm
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!
ReplyFAdi
September 15, 2021 at 4:52 am
sorry don’t know how to delete the comment above, but got it working
ReplyLeslie
November 11, 2021 at 8:58 pm
These look the same to me? What difference am I missing?
ReplyManuel
February 3, 2022 at 7:47 pm
The space after the comma 🙂
Reply
Veronica Lin
March 15, 2021 at 11:59 am
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!
ReplyOrivaldo
March 27, 2021 at 8:29 pm
very good, and very well explained step by step.
Congratulations on your teaching to explain.
it was very useful for me
ReplyRicardo Quiroz
April 20, 2021 at 1:48 am
Many thanks for your tutorial, was very useful. I’ll make a snake view of your other posts.
ReplyTyler
April 28, 2021 at 2:12 am
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!
ReplyHolly
June 21, 2022 at 10:14 am
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!
Reply
S
May 10, 2021 at 3:06 pm
Thanks for this! Very clear and extremely helpful!
ReplyRosa
May 20, 2021 at 12:28 am
This one doesn’t seem to work for me.
if(activeCell.getColumn() == 3 && ss.getActiveSheet().getName()==”Sheet1″)
ReplyRosa
May 20, 2021 at 12:34 am
Hi I think I got it. Activespreadsheet instead of activesheet
if(activeCell.getColumn() == 3 && ss.getActivespreadSheet().getName()==”Sheet1″)
Thanks much!!
ReplyHeidi
May 28, 2021 at 1:26 am
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.
ReplyHeidi
May 28, 2021 at 1:27 am
sorry I mean every cell in every sheet in the workbook.
ReplyEran
January 2, 2022 at 7:06 pm
It happened to me as well 🙁
Reply
Anyone knows how to fix it?Maria
February 7, 2022 at 5:31 pm
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
Reply
Cristian
March 21, 2022 at 2:34 pm
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”) {
ReplyMJ
September 29, 2022 at 2:17 pm
Thanks Cristian, your tweek to the code helped me!
The OR operator restricts the code to apply else where on the spreadsheet.
Reply
Dannie
June 3, 2021 at 5:49 pm
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();
Reply
if((active == 13 || active == 14 || active == 18 || active == 22) && ss.getActiveSheet().getName()=='Sheet1')JaLisa
July 22, 2022 at 9:31 pm
Ahh thank you!! I was really struggling, but this worked. 🙂
Reply
Leonardo
June 25, 2021 at 1:29 pm
That’s very good content! Thanks for sharing
ReplyNabiha
July 29, 2021 at 11:29 pm
THANK YOU SUMIT, THIS HELPED ME A LOT!!
ReplyHannah
August 9, 2021 at 11:15 pm
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?
ReplySarah
August 23, 2021 at 11:53 am
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,
Reply
SarahJG
September 21, 2021 at 10:14 am
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!
ReplyJennifer
September 21, 2021 at 2:00 pm
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.
ReplyJokowi
September 21, 2021 at 10:04 pm
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.
ReplyBindu
February 1, 2022 at 9:52 pm
I’m getting the below Error if I try to add your code
Reply
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);
}
}
}
}
Jokowi
September 21, 2021 at 10:13 pm
Is it possible to make clicked output vertical per word (like creating a new line) instead of horizontal?
ReplyDM
September 24, 2021 at 1:56 am
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?
ReplyDM
September 25, 2021 at 2:57 am
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?
ReplySeth
September 30, 2021 at 10:24 pm
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:
Reply
//gist.github.com/sschwartzman/ed34dec66231acd9092051170d0ed6dcYogesh
April 10, 2022 at 1:31 pm
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
ReplySTEPHANIE
September 21, 2022 at 4:06 am
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.
Reply
Taryn
October 5, 2021 at 9:25 pm
Thank you so much! This was exactly what I was looking for.
ReplyEllie
October 21, 2021 at 10:25 pm
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?
ReplyAmanda
February 23, 2022 at 1:18 am
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″)
Reply
Liz
October 27, 2021 at 8:28 pm
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.
Replylaoise
October 28, 2021 at 6:32 pm
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 🙂
ReplySB
October 29, 2021 at 2:10 am
Could you do this so the selections are listed (stacked) within the cell via line breaks instead of being separated by commas?
ReplyRyan Hammer
November 14, 2021 at 10:16 am
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.
ReplyLex
December 13, 2021 at 7:06 am
This is very helpful, thank you.
Is there a way I can delete the variable once already selected?
ReplyValérie
December 22, 2021 at 11:05 pm
Hi,
Reply
Very helpful. Thanks!
Any ideas on how we can replace the comma separator by a line break, within the cell?
Thanks!Valérie
January 5, 2022 at 1:52 am
Me too. Can’t figure it out.
ReplyAle
January 5, 2022 at 4:26 am
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?
ReplyRJ
January 7, 2022 at 7:22 am
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.
ReplyNeringa
January 19, 2022 at 1:20 pm
Thank you so much. Was very helpful. Not only the article itself but the comment section as well 🙂
ReplyFrançois
January 27, 2022 at 9:11 pm
Nice ! Thanks for sharing.
ReplyNik
February 4, 2022 at 9:06 am
Thank you!
Replymacca
February 15, 2022 at 9:00 pm
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!
Reply
Thank youTayná
February 20, 2022 at 6:23 am
Thanks, this worked for me, i wish learned this one day too 🙂
ReplyAmanda
February 23, 2022 at 1:40 am
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);
Reply
}Malina
February 26, 2022 at 7:01 pm
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 ?
ReplyChristopher Daniel
November 24, 2022 at 4:01 am
For the moment, you can still use the old editor through a selection when you first enter the screen
Reply
Asha J
April 5, 2022 at 7:39 am
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?
ReplyPramod
May 22, 2022 at 8:10 pm
I am using following code for Multiple Drop drown for entire coloumn.
Reply
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);
}
}
}
}Nancy
June 9, 2022 at 2:33 pm
Hi! Is it possible to allow copy and paste in these multi-select cells?
Reply
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!David Miles
June 17, 2022 at 3:44 pm
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.
Reply
Any ideas how to resolve this?David Miles
June 18, 2022 at 1:30 am
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.
Reply
*sighjustin
November 15, 2022 at 1:25 am
How do I find this code to .setAllowInvald(true)?
I made the data validation dropdown via this tutorial, not via code, originally
Reply
Leo
June 29, 2022 at 10:13 am
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.
ReplyVictoria
August 10, 2022 at 10:33 am
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, PearThese 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?
ReplyNic
September 8, 2022 at 3:07 am
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!
ReplyHaro
October 14, 2022 at 7:33 pm
Just in case you want remove newValue if it already exists in oldValue :
function onEdit(e) {
Reply
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(", "));
}
}
}
}
}Ariana
November 24, 2022 at 2:37 am
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!
Can you select multiple items from a drop
Place the cursor in the form where you want the multi-selection list and then go to the Controls Task Pane (View > Design Tasks > Controls). Under the Repeating and Optional section, click Multiple-Selection List Box. Configure the multi-select list box as you would a standard List Box.How do I select multiple items from a drop
With the multiple attribute, you can allow the user to select multiple items from the dropdown. This makes 4 items visible by default. To select multiple items, the user has to hold down the shift or ctrl key, then select with the mouse.How can you select multiple items in a multi selection list?
These options work on Windows and Macintosh and are not specific to ConfTool but work in many applications. Hold the CTRL key and click the items in a list to choose them. Click all the items you want to select. They don't have to be next to each other.