Skip to content

Instantly share code, notes, and snippets.

@arthurattwell
Last active August 1, 2022 09:54
  • Star 71 You must be signed in to star a gist
  • Fork 20 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save arthurattwell/aa5afc178624bdd6f46c2d0d28d06136 to your computer and use it in GitHub Desktop.
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(', '));
}
@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

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

Works like a charm. Thank you!!

@berrythemaker
Copy link

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

@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

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

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

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!

@forjoyilive
Copy link

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

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

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

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

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

@joshuajohnsont
Copy link

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

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

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