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. Note that this differs from a dependent drop down list. Here, we focus on allowing the user to choose multiple items. A dependent drop down list changes what’s shown based on a user’s choice.
Ready to see how to use the Google Sheets multi select dropdown? Here’s how the data set looks right now.
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. Not sure how to use it? I covered how to use the Google Sheets script editor in more detail. It’s the next step in my guide on how to create drop down list in excel with multiple selections. If you already know how to use it, move on to this next step.
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. If you need additional guidance, I provide more steps in the 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.
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.
I hope you found this tutorial useful!
Want to become a spreadsheet expert? Take one of these Google Sheets courses.
Other Google Sheets tutorials you may find useful:
172 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
For a novice, is this what you mean?
if(activeCell.getColumn() == 6 && ss.getActiveSheet().getName()==”Sheet 1″||”Sheet 2″)
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.
got the same error and for me what fixed it is changing the data validation ‘If the data is invalid’ option from “Reject the input” to “Show a warning”
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 have the same problem. Can someone help, please? Thanks <3
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. 🙂
You are a lifesaver! thank you so much. There are a ton of comments relating to the script failing when trying to specify multiple columns.
Your AND solution worked for me.
One thing to note, when I specified two columns apart (3 & 5) the multiple entries functioned on columns 3,4, and 5.
For me this wasn’t a problem as I was happy to just have it set to two columns that where beside one another
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?
SB, This worked for me:
Change the line:
activeCell.setValue(oldValue + ‘, ‘ + newValue);
to:
activeCell.setValue(oldValue + ‘, ‘ + “\n” + newValue);
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);
}
}
}
}
}
I was getting this too. I then renamed the tab to a single word and used it in the code.
i.e. ss.getActiveSheet().getName()==”
It then started working for me
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);
}
}
}
}
I keep getting the following message:
Syntax error:
SyntaxError: Invalid or unexpected token line: 6 file: Code.gs
Here is line 6:
if(activeCell.getColumn() == 2 && ss.getActiveSheet().getName() == ”Sheet1”) {
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?
Did you ever find a solution to sorting the selections alphabetically?
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
Thank you Chris. This I was banging my head against the wall for why this used to work for me, but didn’t in a new file I created. Changing to ‘Show Warning’ did the trick.
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.
Ryan, This worked for me:
Change the line:
activeCell.setValue(oldValue + ‘, ‘ + newValue);
to:
activeCell.setValue(oldValue + ‘, ‘ + “\n” + newValue);
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.
Yes, you just have to make sure Show a warning is selected under the advanced options of the data validation
the script doesn’t work for me, not sure what’s wrong. i’m new to all this
Hi!
I’m proceeding as explained but the multiple choice doesn’t happen.
I did everything as suggested here, also “show a warning” is checked. Still, the C1 doesn’t change to a multiple choice thing….
Hi,
This is very helpful until you need to do so for the entire column. I removed the component about the row, but now it tells me “TypeError: Cannot read properties of undefined (reading ‘value’)
onEdit @ Code.gs:7”
I’ve copy pasted what has worked for there people here and renamed the sheets/column appropriately and it still doesnt work. Here is my code:
function onEdit(e) {
var oldValue;
var newValue;
var ss=SpreadsheetApp.getActiveSpreadsheet();
var activeCell = ss.getActiveCell();
if(activeCell.getColumn() == 3 && 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 Chris!
Thanks for this.
It is not working for me even though I followed the menu path you mentioned in your previous comment.
The error I get is:
Exception: Please select an active sheet first.
onEdit @ Code.gs:5
I tried to replace line 5 with
var ss=SpreadsheetApp.getActive().getSheetByName(“Shipment Information”);
The code then runs, but the dropdown does not bring in multiple selection.
This is the code I am using!
Would appreciate your help 🙂
function onEdit(e) {
var oldValue;
var newValue;
var ss=SpreadsheetApp.getActiveSpreadsheet();
var activeCell = ss.getActiveCell();
if(activeCell.getColumn() == 12 && activeCell.getRow() == 2 && ss.getActiveSheet().getName()==”Shipment Information”) {
newValue=e.value;
oldValue=e.oldValue;
if(!e.value) {
activeCell.setValue(“”);
}
else {
if (!e.oldValue) {
activeCell.setValue(newValue);
}
else {
activeCell.setValue(oldValue+’, ‘+newValue);
}
}
}
This really works and extremely helps for me as i am absolutely unaware coding and all.
So it works for me a particular cell for me.
Sheet name – Master
Column – 16 (P)
Row – 2
I want to apply it to whole column P
May you kindly help me with this.
Thanks a Lot.
This doesn’t work for me. I definitely have “Show Warning” selected. When I go to select the second value it just has that supersede the first selected value, even if I hold down command or control.
Hi tried this to choose multiple name from the list and it worked perfectly can we use same for selecting multiple dates
How can I add time format in Drop Down List. when add time format in drop down list cell value come in numerical format.
Hi folks, please note that I am very, VERY new to Google Apps Script, or coding whatsoever.
I’ve (hopefully) replaced all of the code where needed,
but when I run the program I get
Error
TypeError: Cannot read properties of undefined (reading ‘value’)
onEdit @ Code.gs:7
in my execution log. What does this mean? And how can I fix this?
Thanks a bunch in advance!
Hi Michelle,
If your data validation is in another sheet (like I did before – I create drop down list in 1 sheet and get data from another) then it won’t work. In that case you just need to put them into 1 sheet. After doing so, you may still get the same error but you can safely ignore it and it won’t affect the results. Just Save, Run and reload the sheet.
Hope this can be useful to you.
The script seems not to be working for my Google Sheets. I kept trying to select multiple items and nothing else is selected.
Hello!
I am getting an error:
TypeError: Cannot read properties of undefined (reading ‘getActiveSpreadsheet’)
onEdit @ Code.gs:5
line 5 reads:
var activeCell = ss.SpreadsheetApp.getActiveSpreadsheet();
I’m noting this app is no longer available on the google app store at the time of this posting. Is this the issue?
The code recommended above had a bug in it that applied the code to all cells. I asked GPT to modify the code so it only applies to the columns I specified and now it works. Check it out, cheers!
OLD CODE:
function onEdit(e) {
var oldValue;
var newValue;
var ss=SpreadsheetApp.getActiveSpreadsheet();
var activeCell = ss.getActiveCell();
if(activeCell.getColumn() == 11 || 12 && 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);
}
}
}
}
}
CODE MODIFIED BY GPT:
function onEdit(e) {
var oldValue;
var newValue;
var ss = SpreadsheetApp.getActiveSpreadsheet();
var activeCell = e.range; // Use the edited range from the event object.
// Check if the edited cell is in column 11 (K) or column 12 (L) and if the active sheet is "Sheet1".
if ((activeCell.getColumn() == 11 || activeCell.getColumn() == 12) && ss.getActiveSheet().getName() == "Sheet1") {
newValue = e.value;
oldValue = e.oldValue;
// If the edited value is empty, clear the cell.
if (!e.value) {
activeCell.setValue("");
} else {
// If there was no old value, set the new value in the cell.
if (!e.oldValue) {
activeCell.setValue(newValue);
} else {
// If the old value does not contain the new value, append it to the old value with a comma.
if (oldValue.indexOf(newValue) < 0) {
activeCell.setValue(oldValue + ', ' + newValue);
} else {
// If the old value already contains the new value, set the cell value to the old value.
activeCell.setValue(oldValue);
}
}
}
}
}
Will the multiple dropdown then work when trying to SUMIF data from another tab? Essentially, I would want to be able to add together the costs associated with multiple items (e.g. getting the cost of buying apples and oranges)
When I share the spreadsheet others are not able to use the multiple selection drop down list. Is it possible to have yhe google sheet shared with others so that they can choose multiple items in the drop down list
Great question, Karen! You can choose what level of “access” to provide when you share. If you want someone to check from a multiple-selection dropdown list, you’ll need to provide edit access instead of view- or comment-only access.
Here is my version of the code, which checks the validation rule itself instead of a hard coded cell or range of cells. In my case I am looking for a VALUE_IN_RANGE data validation rule to be in place, and that it’s set to show a dropdown. You might depending on your setup need to change it to look for a LIST_OF_ITEMS rule. For this range-based rule, I also have a flag forceValidValues which will ensure the existing values will be in the valid range (in case the range values are changed over time, or bad data got in there some other way). This will only operate on an actual changed value. Oh, and I’m using a newline character as my delimiter. I couldn’t find a way to hide that warning that the data is invalid if multiple are selected, but maybe google will one day support multiselect dropdowns natively. In the meantime, hopefully this revised script helps someone:
function onEdit(e) {
if(!e) {
Logger.log(‘onEdit exiting, e is null or undefined. Probably because you are running it in the script editor…’);
return;
}
/*
// THESE VARIABLES CAN BE USED TO SPECIFY THE RANGE – HARDER TO MAINTAIN, SO I’M GOING TO CHECK WHAT KIND OF DATA VALIDATION RULE IS IN PLACE INSTEAD…
var dataRowStart = 9;
var sheetName = ‘Variable selections’;
var columnsWithSelects = [11,13];
*/
var forceValidValues = true; //While it doesn’t get rid of the “Invalid: input must fall in specified range” warning , it will remove any values that had somehow been entered but are now invalid based on the current range so that we can be more certain that at least when a NEW selection is made the new value will be valid (despite the warning). To keep things tidy, it will also order the new values based on the order in the range.
var delimeter = “\n”; //you could use a comma, but for bigger lists with more text this was hard to read so I’m using a new line character
var oldValue;
var newValue;
var ss=SpreadsheetApp.getActiveSpreadsheet();
var activeCell = ss.getActiveCell();
var activeSheet = ss.getActiveSheet();
var currentColumn = activeCell.getColumn();
//var currentSheetName = activeSheet.getName();
var currentRow = activeCell.getRow();
// get the range and the data validation rule of the active cell
var range = activeSheet.getRange(currentRow, currentColumn);
var rule = range.getDataValidation();
/*
// You can check the logs to see what kind of rule you’re applying. For example, you might be using SpreadsheetApp.DataValidationCriteria.LIST_OF_ITEMS
Logger.log(‘Rule criteria type: ‘ + (rule ? rule.getCriteriaType() : ‘N/A’));
Logger.log(‘allow invalid: ‘ + (rule ? rule.getAllowInvalid() : ‘N/A’));
Logger.log(‘help text: ‘ + (rule ? rule.getHelpText() : ‘N/A’));
*/
//if(columnsWithSelects.includes(currentColumn) && activeCell.getRow() >= dataRowStart && currentSheetName==sheetName) {
if(rule && rule.getCriteriaType() == SpreadsheetApp.DataValidationCriteria.VALUE_IN_RANGE && rule.getAllowInvalid()){
// get the criteria values from the rule
var criteriaValues = rule.getCriteriaValues();
// get the boolean value that indicates whether to show a dropdown menu or not
var showDropdown = criteriaValues[1];
if(showDropdown){
newValue=e.value;
oldValue=e.oldValue;
if(!newValue) {
activeCell.setValue(“”);
}
else {
if (!e.oldValue) {
activeCell.setValue(newValue);
}
else {
if(oldValue.replace(“\r”, “”).trim() === newValue.replace(“\r”, “”).trim()){
activeCell.setValue(oldValue);
}
else if(oldValue.replace(“\r”, “”).split(delimeter).indexOf(newValue) < 0) {
var updatedValue = oldValue.replace("\r", "")+delimeter+newValue;
if(forceValidValues){
// get the range of cells that contain the valid values
var validRange = criteriaValues[0];
// get the values from the valid range as a 2D array
var validValues = validRange.getValues();
// flatten the 2D array into a 1D array
validValues = validValues.flat();
var updatedValueArray = updatedValue.split(delimeter);
var sanitizedUpdatedValueArray = [];
for(var i=0;i<validValues.length; i++){
if(updatedValueArray.indexOf(validValues[i]) !== -1) {
sanitizedUpdatedValueArray.push(validValues[i]);
}
else {
Logger.log('Rejected invalid value: ' + updatedValueArray[i]);
}
}
updatedValue = sanitizedUpdatedValueArray.join(delimeter);
}
activeCell.setValue(updatedValue);
}
else {
activeCell.setValue(oldValue);
}
}
}
}
}
}
Love this script! My only issue is that once I put it to use in 2 columns (C & D), my hyperlink function stopped working in column A. Any idea what’s going on here?
Hello,
I am new to this script writing, I love this concept but I am having issues getting this to apply to my document, I am creating a document where I need 13 separate sheets in the one document but I need column J & K to be consistent on each page by being able to select multiple options in each column and row. I have selected the data validation to show as error, then I place the script in, change column, row and sheet name in but the multiple options in each row does not work, can you please offer any assistance as to what I am doing wrong. I hope this makes sense and you understand the issue I am having.
This is great! I’m working on the multi select dropdown (no repetition) but (1) I like the item list on a separate sheet . I cannot make that work. Aside from that the dropdown range also had a (2) conditional dependency. ex. if I choose South Africa only South African countries are in range. Then I try to select the specific South African countries that has a specific activity (not all).
Hey Sumit, thanks for your solution. It works very well. I wanted to also delete previous selected values. So I’m edited your last “else” part there an oldValue exist to:
var oldValueArr = [{}];
oldValueArr = oldValue.split(delimiter);
var newValueInOld = oldValueArr.indexOf(newValue);
if(newValueInOld < 0) {
activeCell.setValue(oldValue +delimiter +newValue);
} else {
oldValueArr.splice(newValueInOld, 1);
activeCell.setValue(oldValueArr.join(delimiter));
}
Now the script checks if a value is in the oldValue and if so deletes it. Note I declared at the beginning of the script var delimiter = ", "; so I can easy change it if I want to. Feel free to use it.
Greetings
Hi Kai! Extremely new to coding… I want to apply this logic to my google sheet, if I remove an option from my list, I want it to be deleted from the previously selected rows. I tried your coding, but I am not implementing it correctly; below is my code – are you able to help? Also, I am currently getting an error message for line 7, but the code is still working. Does it look right to you? Appreciate any help!
1. function unit(e) {
2. var oldValue;
3. var newValue;
4. var ss=SpreadsheetApp.getActiveSpreadsheet();
5. var activeCell = ss.getActiveCell();
6. if(activeCell.getColumn() == 4 || 5 && ss.getActiveSheet().getName()==”Sheet1″) {
7. newValue=e.value;
8. oldValue=e.oldValue;
9. if(!e.value) {
10. activeCell.setValue(“”);
11. }
12. else {
13. if (!e.oldValue) {
14. activeCell.setValue(newValue);
15. }
16. else {
17. if(oldValue.indexOf(newValue) <0) {
18. activeCell.setValue(oldValue+', '+newValue);
19. }
20. else {
21. activeCell.setValue(oldValue);
}
}
}
}
}
Try this instead:
function onEdit(e) {
var newValue = e.value;
var oldValue = e.oldValue;
var ss = SpreadsheetApp.getActiveSpreadsheet();
var activeCell = e.range; // Use e.range to get the edited cell
if (
(activeCell.getColumn() == 4 || activeCell.getColumn() == 5) && // Use double equals (==) for comparisons
ss.getActiveSheet().getName() == “Sheet1”
) {
if (!newValue) {
activeCell.setValue(“”); // Clear the cell if the new value is empty
} else {
if (!oldValue) {
activeCell.setValue(newValue); // Set the cell value to the new value if there was no previous value
} else {
var oldValueArray = oldValue.split(‘, ‘); // Split the old value into an array
if (oldValueArray.indexOf(newValue) < 0) { oldValueArray.push(newValue); // Add the new value to the array if it's not already present activeCell.setValue(oldValueArray.join(', ')); // Update the cell value with the updated array } else { // If the new value is already in the old value, do nothing or handle it as you prefer } } } } }
Thank you Jmarkus! I copy and pasted exactly how you have it and I am getting an error message :Synxtax error: Invalid or unexpected token line: 9 file: Code.gs .
Which is:
ss.getActiveSheet().getName() == “Sheet1”
The name of the sheet is sheet1. (also, for context, when I say I am a beginner – I am extremely new, :/ apologies if I have incorrectly implemented the code you provided and thanks for your patience. Are you able to diagnose?
No problem! It looks like the issue might be the quotes. Those are curly quotes, but we need them to be regular double quotes. Make sure the double quotes around “Sheet1” are standard straight double quotes. If you copied and pasted the code, sometimes formatting issues might cause the quotes to be incorrect. Please try replacing line 9 with this:
ss.getActiveSheet().getName() == "Sheet1"Thank you for this! Is it possible to automatically fill out the drop down based on the contents of another cell? for example, if a cell with text lists a person’s favorite fruits, is there a formula that could read that cell, see it contains the word “banana”, and then in this dropdown select “banana”?
it runs fine on App Script but for some reason not on the sheets? What can be a fix for this?
It sounds like there might be a setup issue. Have you gone through our guide on setting up App Script to make sure it functions with your sheet?
This was very helpful, thank you!
Hello,
I have the below code in the Apps Script. When I run this code, I keep getting ‘9:58:55 AM Error An unknown error has occurred, please try again later.’
I am hoping for multiple selections with no repetition in column 11. Do you notice anything that I have incorrect? Thank you!
function onEdit(e) {
var oldValue;
var newValue;
var ss=SpreadsheetApp.getActiveSpreadsheet();
var activeCell = ss.getActiveCell();
if(activeCell.getColumn() == 11 && 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);
}
}
}
}
}
Maybe try something like this?
function onEdit(e) {var ss = SpreadsheetApp.getActiveSpreadsheet();
var activeCell = ss.getActiveCell();
// Ensure we are in the correct column and sheet
if (activeCell.getColumn() == 11 && ss.getActiveSheet().getName() == "Sheet1") {
var newValue = e.value;
var oldValue = e.oldValue || ""; // Ensure oldValue is a string if undefined
// If there's no new value, clear the cell
if (!newValue) {
activeCell.setValue("");
return;
}
// Split existing values into an array, trim spaces, and filter out empty strings
var oldValues = oldValue.split(',').map(function(value) { return value.trim(); }).filter(function(value) { return value !== ""; });
// Check if the new value is already in the array of old values
if (oldValues.indexOf(newValue) < 0) { // Add the new value and join the array back into a string oldValues.push(newValue); activeCell.setValue(oldValues.join(', ')); } // If the value already exists, we do nothing (or reset to oldValue if needed) // This else block can be uncommented if you wish to explicitly reset the oldValue, but it's redundant in this context // else { // activeCell.setValue(oldValue); // } } }