Selecting Multiple Values from a Dropdown List in Google Spreadsheet

Question

The Google Spreadsheet looks like can only select one value in the dropdown list.

Is there any way to select multiple values from a dropdown list in google spreadsheet?

Tyndareus

For anyone attempting to follow the answers, do note that all the answers are workarounds and this is a very bad idea to implement in spreadsheets as it doesn't fit the first-normal-form – Compact

Answer 1

To Add to AlexG's answer, a better and enhanced version of multi-select is found in this following link (which I tried and worked as expected):

https://gist.github.com/coinsandsteeldev/4c67dfa5411e8add913273fc5a30f5e7

For general guidance on setting up a script in Google Sheets, see this quickstart guide.

To use this script:

1. In your Google Sheet, set up data validation for a cell (or cells), using data from a range. In cell validation, do not select 'Reject input'.
2. Go to Tools > Script editor...
3. In the script editor, go to File > New > Script file
4. Name the file multi-select.gs and paste in the contents of multi-select.gs. File > Save.
5. In the script editor, go to File > New > Html file Name the file dialog.html and paste in the contents of dialog.html. File > Save.
6. Back in your spreadsheet, you should now have a new menu called 'Scripts'. Refresh the page if necessary.
7. Select the cell you want to fill with multiple items from your validation range.
8. Go to Scripts > Multi-select for this cell... and the sidebar should open, showing a checklist of valid items.
9. Tick the items you want and click the 'Set' button to fill your cell with those selected items, comma separated.

You can leave the script sidebar open. When you select any cell that has validation, click 'Refresh validation' in the script sidebar to bring up that cell's checklist.

The above mentioned steps are taken from this link

Riviera

The users, with whom you share your sheet, may get a red warning you do not have access to perform this action. Please ask the owner of this item to grant access to you. To fix this, you ned to deploy the script as a web app: https://mcmap.net/q/245931/-how-can-my-google-apps-script-be-run-by-others-the-sheet-is-shared-with – Beirut

Great work, but since it basically only copy past the list item, after playing few hours with it, I find it easier to simply copy past the cells I need from my list (if you don't have too much data, it's much more flexible and quicker). Don't forget to send this feature request to Google: please allow selecting multiple values from a dropdown list. – Maxia

This was really very helpful. Thanks a lot. – Kimble

@parishodak, this is amazing. Had I been the asker, I would have tagged this as the solution. Just to set the bar higher though, is it possible to include a search bar within the html body? Something to filter items from 1000+ selections? I've searched around google, but to no avail. – Cranial

@J.Mapz. you might have to implement your own javascript autocomplete search algorithm and link it to search box. For instance w3schools.com/howto/howto_js_autocomplete.asp – Riviera

Can these steps be updated with the latest way the App script works? I do not get Tools>Script Editor. Had to go to Apps Script, it created a project over there. And now I do not see the Scripts in the menu yet. – Fantastically

Hi, I refactored the linked scripts and I'm using it in a spreadsheet so it works. Find my version here: gist.github.com/zslim/a4c6f2042d48c240802805d9122088e1 – Endothermic

Answer 2

I have found solution at https://www.youtube.com/watch?v=dm4z9l26O0I

You would need to use Tools > Script Editor. Create .gs and .html files there. See example at http://goo.gl/LxGXfU (link can be also found under Youtube video). Just copy

Once you have .gs and .html files in place save them and reload your spreadsheet. You will see "Custom menu" as the last item of your top menu. Select cell you would like to manage and click on this menu item.

During the first time it will ask you to authorize application - go ahead and do this.

Note (1): make sure that your cell has "Data validation" defined before you click on "Custom menu".

Note (2): it appeared that solution works with "List from a range" criteria for Data validation (it does not work with "List of items")

Dumah

This works quite good, although it is not very user-friendly. There is a space to improve it using JS/GA/PHP. – Ivatts

Answer 3

I have found a great work-around for this. It really only works practically if you want to be able to select up to 4 or so options from your drop down list but here it is:

For each "item" create as many rows as drop-down items you'd like to be able to select. So if you want to be able to select up to 3 characteristics from a given drop down list for each person on your list, create a total of 3 rows for each person. Then merge A:1-A:3, B:1-B:3, C:1-C:3 etc until you reach the column that you'd like your drop-down list to be. Don't merge those cells, instead place the your Data Validation drop-down in each of those cells.

Hope this is clear!!

Brotherly

I know that this isn't as fancy an answer as the others, but it's pretty robust. It 1) doesn't require scripting, 2) can be used by others without training, 3) doesn't break (badly) if someone does it wrong, 4) easily supports filtering, searching, etc, 5) uses standard validation, so if Google changes something it will still work. Thanks! – Cyclamate

Filtering seems not work in this case due to the vertical merges in column A, B, D, and E? (although I still quite like this solution =) – Vignette

the picture really not helping me to do this at all, can u do step by step and and the result ? – Prana

Multiple columns for Selection1, Selection2, etc with dropdowns, or multiple columns for Value1, Value2 with checkboxes would work just as well and do not require merging – Passed

Answer 4

I see that you've tagged this question with the google-spreadsheet-api tag. So by "drop-down" do you mean Google App Script's ListBox? If so, you may toggle a user's ability to select multiple items from the ListBox with a simple true/false value.
Here's an example:

var lb = app.createListBox(true).setId('myId').setName('myLbName');

Notice that multiselect is enabled because of the word true.

Kucik

Would it be possible to implement it in a TamperMonkey script (so it could change the way google sheet works)? Did you try? – Maxia

Answer 5

You can use multiple columns for Selection1, Selection2, etc with dropdowns, or multiple columns for Value1, Value2 with checkboxes for this.

Passed

You can also use a Google Form to enter complex multi-values with data validation etc – Passed

Answer 6

You would use data validation for this. Click in the cell you want to have a multiple drop down > DATA > Validation > Criteria (List from a Range) - here you select form a list of items you want in the drop down. And .. you are good. I have included an example to reference.

Tamar

this only lets them select one item at a time. The OP asked for an option to select multiple items from one dropdown. – Pippy

Answer doesn't fit with the question -multiselect is needed, not single select from a dropdown. – Photodrama

Answer doesn't relate to the question as others have identified. Additionally, the link provided doesn't work. – Delanos