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(', '));
}
@lodigro
Copy link

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
Copy link

jemoreto commented Dec 1, 2016

Thanks for this, working like a charm ;)

@keertipandey30
Copy link

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.

Copy link

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
Copy link

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
Copy link

nhim175 commented Aug 7, 2017

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

@spras
Copy link

spras commented Aug 10, 2017

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

@nicklee
Copy link

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
Copy link

Timosay commented Aug 29, 2017

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

@melrose
Copy link

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
Copy link

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
Copy link

kcho55555 commented Jan 23, 2018

Works like a charm. Thank you!!

@berrythemaker
Copy link

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
Copy link

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
Copy link

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
Copy link

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
Copy link

Tolano8 commented Dec 11, 2018

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

@Lalasusu
Copy link

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
Copy link

mieras commented Apr 15, 2019

Does this still work?

@coinsandsteeldev
Copy link

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
Copy link

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
Copy link

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
Copy link

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
Copy link

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?

@MeesLorch
Copy link

MeesLorch commented Feb 16, 2021

Everything works UNTIL I click on "select" and nothing happens :-( I am so grateful for this solution, I need to make this work... I would really appreciate any help you can give!

@IamNotaDevSamuel
Copy link

IamNotaDevSamuel commented Apr 9, 2021

Hello ! Thanks a lot for your code and time. Any idea to improve filters based on multi-criteria selection?

@joshuajohnsont
Copy link

joshuajohnsont commented Jun 24, 2021

Everything works UNTIL I click on "select" and nothing happens :-( I am so grateful for this solution, I need to make this work... I would really appreciate any help you can give!

I'm having this same problem. Select just does nothing when it's suppose to set the values.

@KierstenMc
Copy link

KierstenMc commented Jul 7, 2021

I started a code using this method about a month ago and it worked then. Logged back into the spreadsheet today and now when I click Select nothing happens :(. Was there an update? Anyone have any other coding solutions to achieve this?

Thank you!

Edit: Resolved by:

  1. Opening the Script Editor (Tools > Script Editor).
  2. Go to Project Settings (left menu).
  3. Uncheck 'Enable Chrome V8 runtime'

@KierstenMc
Copy link

KierstenMc commented Jul 7, 2021

Everything works UNTIL I click on "select" and nothing happens :-( I am so grateful for this solution, I need to make this work... I would really appreciate any help you can give!

I'm having this same problem. Select just does nothing when it's suppose to set the values.

@joshuajohnsont and @MeesLorch. I found this resolution on a different page and it fixed the issue for me!

  1. Open the Script Editor (Tools > Script Editor).
  2. Project Settings (left menu).
  3. Uncheck 'Enable Chrome V8 runtime'

Hope that helps! :)

@shriaviator
Copy link

shriaviator commented Sep 22, 2021

Is it possible to modify this script to give the user a option of entering free Text ???????

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment