How to Make Multiple Selection in Drop-down Lists in Google Sheets

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).

Drop Down List with multiple selection demo

But first, let’s start from scratch.

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

Drop Down Dataset and cell where it needs to be added

To create a drop-down list that allows multiple selections, you need to do two things:

  1. Create a drop-down list using a list of items
  2. 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.

Drop Down Dataset and cell where it needs to be added

Below are the steps to so this:

  1. Select cell C1 (the one where you want the drop-down)
  2. Click the Data option in the menuClick the Data tab
  3. Click on Data ValidationClick the Data Validation option in the menu
  4. In the Data Validation dialog box, make sure the ‘Cell range’ refers to the cell where you want the drop-downCell range where you want the in-cell drop down
  5. 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.Criteria is Data Validation
  6. 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:

  1. Click the Tools option in the menu
  2. Click on Script Editor. This will open the Script Editor in a new windowClick on Script Editor in the Tools options
  3. In the Code.gs window, remove anything that is already there and copy and paste the above macro codeCopy and Paste the code in the code editor
  4. Click on the Save button in the toolbar (or use the keyboard shortcut Control + S)Click the Save button in the toolbar
  5. Give the project a name (you only need to do this once)Give the Project a Name
  6. 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.

Drop Down List with multiple selection demo

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:

if(activeCell.getColumn() == 3 && ss.getActiveSheet().getName()=="Sheet1")

When you do this, we are only checking whether the column is 3 or not. Any cells which are in Sheet1 and in Column 3 would satisfy this IF criteria and there any drop-down in this would allow multiple selections.

Similarly, if you want this to be available for entire column C and F, use the below line instead:

if(activeCell.getColumn() == 3 || 6 && ss.getActiveSheet().getName()=="Sheet1")

The above line uses an OR condition within the IF statement where it checks whether the column number is 3 or 6. In case a cell that has the drop-down is in column C or F, multiple selections would be enabled.

Similarly, if you want this to be enabled for multiple cells, you can do that as well by modifying the code.

So this is how you can enable multiple selections in a drop-down in Google Sheets. While this is not available as an in-built feature, you can easily do this with some Google Apps Script magic.

Hope you found this tutorial useful!

Other Google Sheets tutorials you may find useful:

Most Popular Posts

Sumit

Sumit

Google Sheets and Microsoft Excel Expert.

81 thoughts on “How to Make Multiple Selection in Drop-down Lists in Google Sheets”

  1. 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.

    Reply
  2. 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.

    Reply
  3. Thank you for this very helpful tutorial. How would I apply this script to an entire column, minus the top two header rows?

    Reply
  4. 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?

    Reply
  5. 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!

    Reply
  6. 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!

    Reply
  7. very good, and very well explained step by step.

    Congratulations on your teaching to explain.

    it was very useful for me

    Reply
  8. 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!

    Reply
    • 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
  9. This one doesn’t seem to work for me.

    if(activeCell.getColumn() == 3 && ss.getActiveSheet().getName()==”Sheet1″)

    Reply
  10. Hi I think I got it. Activespreadsheet instead of activesheet

    if(activeCell.getColumn() == 3 && ss.getActivespreadSheet().getName()==”Sheet1″)

    Thanks much!!

    Reply
  11. 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.

    Reply
        • 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
      • 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”) {

        Reply
  12. 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')

    Reply
  13. 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?

    Reply
  14. 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

    Reply
  15. 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!

    Reply
  16. 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.

    Reply
  17. 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.

    Reply
    • 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);
      }
      }
      }
      }

      Reply
  18. 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?

    Reply
  19. 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?

    Reply
  20. 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

    Reply
    • 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

      Reply
    • 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
  21. 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?

    Reply
    • 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
  22. 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.

    Reply
  23. 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 🙂

    Reply
  24. 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.

    Reply
  25. 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?

    Reply
  26. 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.

    Reply
  27. 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

    Reply
  28. 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
  29. 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 ?

    Reply
  30. 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?

    Reply
  31. 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);
    }
    }
    }
    }

    Reply
  32. 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!

    Reply
  33. 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?

    Reply
  34. 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

    Reply
  35. 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.

    Reply
  36. 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?

    Reply
  37. 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!

    Reply
  38. 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(", "));
    }
    }
    }
    }
    }

    Reply
  39. 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!

    Reply

Leave a Comment