Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Google Sheets script to allow multi-select in cells with data-validation (adapted from https://www.youtube.com/watch?v=dm4z9l26O0I)
<div style="font-family: sans-serif;">
<? var data = valid(); ?>
<form id="form" name="form">
<? if(Object.prototype.toString.call(data) === '[object Array]') { ?>
<? for (var i = 0; i < data.length; i++) { ?>
<? for (var j = 0; j < data[i].length; j++) { ?>
<input type="checkbox" id="ch<?= '' + i + j ?>" name="ch<?= '' + i + j ?>" value="<?= data[i][j] ?>"><?= data[i][j] ?><br>
<? } ?>
<? } ?>
<? } else { ?>
<p>This cell has no <a href="https://support.google.com/drive/answer/139705?hl=en">Data validation</a>.</p>
<? } ?>
<input type="button" value="Select" onclick="google.script.run.fillCell(this.parentNode)" />
<input type="button" value="Refresh validation" onclick="google.script.run.showDialog()" />
</form>
</div>
function onOpen(e) {
SpreadsheetApp.getUi()
.createMenu('Scripts')
.addItem('Multi-select for this cell...', 'showDialog')
.addToUi();
}
function showDialog() {
var html = HtmlService.createTemplateFromFile('dialog').evaluate();
SpreadsheetApp.getUi()
.showSidebar(html);
}
var valid = function(){
try{
return SpreadsheetApp.getActiveRange().getDataValidation().getCriteriaValues()[0].getValues();
}catch(e){
return null
}
}
function fillCell(e){
var s = [];
for(var i in e){
if(i.substr(0, 2) == 'ch') s.push(e[i]);
}
if(s.length) SpreadsheetApp.getActiveRange().setValue(s.join(', '));
}
@arthurattwell

This comment has been minimized.

Copy link
Owner Author

@arthurattwell arthurattwell commented May 18, 2016

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
  6. Name the file dialog.html and paste in the contents of dialog.html. File > Save.
  7. Back in your spreadsheet, you should now have a new menu called 'Scripts'. Refresh the page if necessary.
  8. Select the cell you want to fill with multiple items from your validation range.
  9. Go to Scripts > Multi-select for this cell... and the sidebar should open, showing a checklist of valid items.
  10. Tick the items you want and click the 'Select' 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.

Many thanks to Alexander Ivanov for creating the original script.

@lodigro

This comment has been minimized.

Copy link

@lodigro lodigro commented Dec 1, 2016

hello,

thanks for your code

I've followed your instruction and used your script.... with no result !! After options checked, I "Select" but no data is going to cell,

any suggestion

Thanks

@jemoreto

This comment has been minimized.

Copy link

@jemoreto jemoreto commented Dec 1, 2016

Thanks for this, working like a charm ;)

@keertipandey30

This comment has been minimized.

Copy link

@keertipandey30 keertipandey30 commented Mar 15, 2017

Hi,

Thanks a lot.
I was wondering if we could have a 'select all' feature in it, that would be useful.

@ghost

This comment has been minimized.

Copy link

@ghost ghost commented Apr 6, 2017

Thanks - works well. Two questions:

  1. I have an error on my spreadsheet cell "Invalid:
    Input must fall within specified range" When I set up the data validation for a range as noted in step 1, what range should I use? I assumed it was a range on another sheet where my list of items is. As the script is pulling in my data, this appears right but is generating that message.

  2. Is there a way to have the Apps Script Application pop up only show when needed (i.e. when someone clicks on a cell) as opposed to all the time?

@ScreechingHalt

This comment has been minimized.

Copy link

@ScreechingHalt ScreechingHalt commented Jun 15, 2017

It works. However, the script runs really s-l-o-w. My list only has 6 items in it. Any thoughts to speed it up?
And, I also have the "Invalid: Input..." warning on my cells. Any way to disable that?
Looking forward to suggestions :)

@nhim175

This comment has been minimized.

Copy link

@nhim175 nhim175 commented Aug 7, 2017

Also it would be nice to have a search function when the list is long

@spras

This comment has been minimized.

Copy link

@spras spras commented Aug 10, 2017

no way to disable validation alert when we select more than one item ?

@nicklee

This comment has been minimized.

Copy link

@nicklee nicklee commented Aug 29, 2017

Can't seem to see the menu item "Scripts", does this still exist or has it been changed in the latest version of sheets? I'm not the owner of the spreadsheet, only an editor, wonder if this makes a difference?

@Timosay

This comment has been minimized.

Copy link

@Timosay Timosay commented Aug 29, 2017

This was great and just what I needed, thank you!

@melrose

This comment has been minimized.

Copy link

@melrose melrose commented Oct 12, 2017

Nice code, Arthur - terse but readable. And it works great too! A big improvement over Alexander's. Many thanks.

@coccoinomane

This comment has been minimized.

Copy link

@coccoinomane coccoinomane commented Oct 26, 2017

Hi Arthur, thank you for sharing this code!

I have forked the gist and added the feature to prevent empty choices to be shown in the sidebar => my gist link.

I am happy to delete my fork if you plan to implement this feature.

I am also thinking of ways to:

  • Avoid the "invalid" warning when the user selects more than one choice.
  • Speed up the script.

I'll update this thread if I make progress.

Thanks,
Guido

@kcho55555

This comment has been minimized.

Copy link

@kcho55555 kcho55555 commented Jan 23, 2018

Works like a charm. Thank you!!

@berrythemaker

This comment has been minimized.

Copy link

@berrythemaker berrythemaker commented Apr 17, 2018

Hello, is anyone able to provide an additional script I could use to ignore the data validation errors? I receive a dv error on each cell when using Arthur's script. Thank you.

@xlaxplaya

This comment has been minimized.

Copy link

@xlaxplaya xlaxplaya commented May 3, 2018

@berrythemaker I'm assuming the script NEEDS to check if a cell has data validation before running, which is why you're getting those errors. But you can try deleting lines 10, 11, and 12 in the dialog.html script, that's the part that throws up the error message "This cell has no data validation".

I'm no expert, but nobody else is answering you, so just give that a shot. If it doesn't work, you should probably revert the changes (put those lines back in).

@coinsandsteeldev

This comment has been minimized.

Copy link

@coinsandsteeldev coinsandsteeldev commented May 16, 2018

Here is better version with fixed bar on top, reset button which reset checkboxes and ignoring empty validation cells, using multiple cells to create checkboxes' titles, validation errors workaround and Set/Update cell feature.
https://gist.github.com/coinsandsteeldev/4c67dfa5411e8add913273fc5a30f5e7

@bhahumanists

This comment has been minimized.

Copy link

@bhahumanists bhahumanists commented Nov 1, 2018

Note that since the user above has changed their username since their last post, their link above will not work. It should be accessible using their new user name at:
https://gist.github.com/coinsandsteeldev/4c67dfa5411e8add913273fc5a30f5e7

@Tolano8

This comment has been minimized.

Copy link

@Tolano8 Tolano8 commented Dec 11, 2018

Hey mate, many thanks. You have saved a lot's of time to me ;)

@Lalasusu

This comment has been minimized.

Copy link

@Lalasusu Lalasusu commented Feb 16, 2019

Hi there, when I try to save the file dialog.html, it gives me an error message: syntax error (line 10, "dialog.html")

Line 10 states this: if (!data) return

Is this no longer valid ?
Thank you otherwise, this would be really saving me loads of time.

@mieras

This comment has been minimized.

Copy link

@mieras mieras commented Apr 15, 2019

Does this still work?

@coinsandsteeldev

This comment has been minimized.

Copy link

@coinsandsteeldev coinsandsteeldev commented Jun 20, 2019

Note that since the user above has changed their username since their last post, their link above will not work. It should be accessible using their new user name at:
https://gist.github.com/coinsandsteeldev/4c67dfa5411e8add913273fc5a30f5e7

Thanks, fixed my comment!

@nabhacosley

This comment has been minimized.

Copy link

@nabhacosley nabhacosley commented Sep 19, 2019

This still works for me — but to get the 'Scripts' menu to appear I had to do Run -> Run function -> onOpen() in the Script Editor. Then the menu showed up. I had to click around a bit to figure out how it works but it wasn't that hard!

Presumably I could also have closed and reopened the sheet?

@AppsScriptUser

This comment has been minimized.

Copy link

@AppsScriptUser AppsScriptUser commented Sep 24, 2019

It does work well, but I would like to make the whole thing that appears on the right disappear, once the selection is made... I can make the HTML form disappear, I can make the div part of HTML disappear, but I don't know how to make the whole thing disappear, meaning the frame on the right that has the title: "Apps Script application". I can manually click on the "x" and close it, but I would like to do it programmatically...

Does anybody know how to do it?

@eamocha

This comment has been minimized.

Copy link

@eamocha eamocha commented Dec 9, 2019

hello,

thanks for your code

I've followed your instruction and used your script.... with no result !! After options checked, I "Select" but no data is going to cell,

any suggestion

Thanks

From the menu, select Data->Data Validation and then On Invalid Data select Show warning. Then click save

@soberman99

This comment has been minimized.

Copy link

@soberman99 soberman99 commented Jan 16, 2020

Thank you! Thank you! Thank you! All worked perfectly and did exactly what I wanted (Multi-select!). However, I did have to go into an Incognito Window to make the script editor work and to be able to do multi-select. When I left the incognito window and went into my normal window, it doesn't work! Any thoughts on this?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.