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://gist.github.com/arthurattwell/aa5afc178624bdd6f46c2d0d28d06136, original credits to Alexander Ivanov 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{
var data = SpreadsheetApp.getActiveRange().getDataValidation().getCriteriaValues()[0].getValues();
return data.filter(function(v){return v[0]!=='';});
}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(', '));
}
@coccoinomane

This comment has been minimized.

Copy link
Owner Author

@coccoinomane coccoinomane commented Oct 26, 2017

I have updated arthurattwell gist to exclude empty values from the multi-select choices.

TODO:

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

This comment has been minimized.

Copy link

@SantaHey SantaHey commented Nov 12, 2017

Hello,
I was looking for this function too but I found an easier solution
Replace
var data = SpreadsheetApp.getActiveRange().getDataValidation().getCriteriaValues()[0].getValues(); return data.filter(function(v){return v[0]!=='';});
By
return SpreadsheetApp.getActiveRange().getDataValidation().getCriteriaValues()[0].getValues().filter(String);

@berrythemaker

This comment has been minimized.

Copy link

@berrythemaker berrythemaker commented Apr 17, 2018

Hi arthurattwell were you able to fix the invalid warning for data validation? It happens to me on each cell when I use multi-select using your script. I saw a few people mention they used a code to ignore the dv errors but I cannot seem to find it.

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