90

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?

Dmitry Pavliv
  • 35,333
  • 13
  • 79
  • 80
user3084196
  • 925
  • 1
  • 9
  • 7

6 Answers6

47

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

parishodak
  • 4,506
  • 4
  • 34
  • 48
  • 1
    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://stackoverflow.com/a/57071543/3154274 – MagTun Jul 17 '19 at 08:18
  • 1
    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`.* – JinSnow Jul 17 '19 at 09:31
  • This was really very helpful. Thanks a lot. – Anand Sep 05 '19 at 06:15
  • 1
    @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. – J.Mapz Nov 15 '19 at 13:21
  • 1
    @J.Mapz. you might have to implement your own javascript autocomplete search algorithm and link it to search box. For instance https://www.w3schools.com/howto/howto_js_autocomplete.asp – parishodak Nov 16 '19 at 00:16
  • 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. – Mukesh Ghatiya Feb 10 '23 at 02:39
  • Hi, I refactored the linked scripts and I'm using it in a spreadsheet so it works. Find my version here: https://gist.github.com/zslim/a4c6f2042d48c240802805d9122088e1 – zslim Apr 16 '23 at 15:20
14

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

AlexHalkin
  • 1,088
  • 14
  • 33
8

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.

enter image description here

Hope this is clear!!

Tohid
  • 6,175
  • 7
  • 51
  • 80
Paul Chumak
  • 105
  • 1
  • 1
  • 1
    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! – Adrian May 22 '19 at 01:58
  • 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 =) – Blue Bear May 24 '20 at 04:56
  • 1
    the picture really not helping me to do this at all, can u do step by step and and the result ? – Mark Sparrow May 25 '21 at 08:06
1

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.

Carson
  • 6,105
  • 2
  • 37
  • 45
Peter Noges
  • 68
  • 5
  • Would it be possible to implement it in a TamperMonkey script (so it could change the way google sheet works)? Did you try? – JinSnow Jul 17 '19 at 06:40
-4

If the answers must be constrained to Google Sheets, this answer works but it has limitations and is clumsy enough UX that it may be hard to get others to adopt. In trying to solve this problem I've found that, for many applications, Airtable solves this by allowing for multi-select columns and the UX is worlds better.

chenware
  • 851
  • 8
  • 20
-11

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.

Rebekah Hynson
  • 9
  • 1
  • 3
    this only lets them select one item at a time. The OP asked for an option to select multiple items from one dropdown. – BlueCaret Mar 29 '16 at 17:37
  • 1
    Answer doesn't fit with the question -multiselect is needed, not single select from a dropdown. – GeorgeC Apr 25 '16 at 07:27
  • Answer doesn't relate to the question as others have identified. Additionally, the link provided doesn't work. – Luke Mar 24 '21 at 23:06