-
-
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)
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<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> |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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(', ')); | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
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.