Skip to content

Instantly share code, notes, and snippets.

@coccoinomane
Forked from arthurattwell/dialog.html
Last active July 1, 2020 03:42
Show Gist options
  • Star 6 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save coccoinomane/b2acb5b5fbc023da3b6725f9181f4c46 to your computer and use it in GitHub Desktop.
Save coccoinomane/b2acb5b5fbc023da3b6725f9181f4c46 to your computer and use it in GitHub Desktop.
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
Copy link
Author

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

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

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