Last active
August 1, 2022 09:54
-
-
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)
This file contains 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 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{ | |
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(', ')); | |
} |
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!
- Open the Script Editor (Tools > Script Editor).
- Project Settings (left menu).
- Uncheck 'Enable Chrome V8 runtime'
Hope that helps! :)
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
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: