-
-
Save simon88pl/609cf5ee665117fc8597a7dee4afb598 to your computer and use it in GitHub Desktop.
Google Sheets script to allow multi-select in cells with data-validation, and auto check previous options selected in current cell, and sorting values alphabetically, and clickable options, and a bit more pleasant look & feel (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
| <!DOCTYPE html> | |
| <html> | |
| <head> | |
| <script> | |
| var data | |
| var formId = 'form' | |
| var content | |
| var checked | |
| function drawForm() { | |
| console.log('drawForm'); | |
| if (!data) return | |
| var outputEl = document.getElementById(formId); | |
| var output = '' | |
| if (Object.prototype.toString.call(data) === '[object Array]') { | |
| for (var i = 0; i < data.length; i++) { | |
| if (data[i].length == 1 ) { | |
| if (data[i][0] == '') continue; | |
| checked = '' | |
| if ( content.includes( data[i][0] ) ) checked = 'checked' | |
| output += '<label class="_cs_multiple-select__form__label"><input class=\'_cs_multiple-select__form__checkbox\' type=\'checkbox\' name=\''+data[i][0]+'\' value=\''+data[i][0]+'\''+ checked +' >' | |
| output += data[i][0] | |
| output += '</label>' | |
| } else if (data[i].length > 1) { | |
| if (data[i][0] == '') continue; | |
| // left will be used as value | |
| // the rest is title | |
| output += '<label class="_cs_multiple-select__form__label"><input class=\'_cs_multiple-select__form__checkbox\' type=\'checkbox\' name=\''+data[i][0]+'\' value=\''+data[i][0]+'\'>' | |
| output += data[i][0] + ' – ' | |
| for (var j = 1; j < data[i].length; j++) { | |
| if (data[i][j] == '') continue | |
| output += data[i][j] + '; ' | |
| } | |
| output += '</label>' | |
| } | |
| } | |
| } else { | |
| output += '<p>This cell has no <a href="https://support.google.com/drive/answer/139705?hl=en">Data validation</a>.</p>'; | |
| } | |
| outputEl.innerHTML = output | |
| } | |
| var onData = function(result) { | |
| data = result | |
| google.script.run.withSuccessHandler(onCheck).getValidationDataCheck(); | |
| } | |
| var onCheck = function(result) { | |
| content = result | |
| drawForm() | |
| } | |
| google.script.run.withSuccessHandler(onData).getValidationData(); | |
| function set() { | |
| google.script.run.withSuccessHandler(x=>{ | |
| }).fillCell(document.getElementById(formId)) | |
| } | |
| // function update() { | |
| // google.script.run.withSuccessHandler(x=>{ | |
| // }).updateCell(document.getElementById(formId)) | |
| // } | |
| function reset() { | |
| drawForm() | |
| } | |
| function refresh() { | |
| google.script.run.withSuccessHandler(onCheck).getValidationDataCheck(); | |
| } | |
| </script> | |
| <style> | |
| ._cs_multiple-select__buttons { position:fixed; top: 0; left:0; padding: 10px 0 0 10px; width: 100%; height: 40px; background-color: white; box-shadow: 0 4px 8px -3px rgb(60 64 67 / 15%); } | |
| ._cs_multiple-select__buttons__element { border: 1px solid transparent!important; border-radius: 4px; box-sizing: border-box; font-family: "Google Sans",Roboto,RobotoDraft,Helvetica,Arial,sans-serif; font-weight: 500; font-size: 14px; height: 30px; letter-spacing: 0.25px; line-height: 16px; padding: 6px 14px 8px 14px; background: white; border: 1px solid #dadce0!important; color: #188038; cursor:pointer; } | |
| ._cs_multiple-select__buttons__element:hover { background: #f8fcf9; border: 1px solid #c8e7d1!important; } | |
| ._cs_multiple-select__buttons__element--h { color: #fff; background: #2a8947; } | |
| ._cs_multiple-select__buttons__element--h:hover { color: #fff; background: #2b8a48; } | |
| ._cs_multiple-select__buttons__element--a { color: #fff; background: #1a73e8; } | |
| ._cs_multiple-select__buttons__element--a:hover { color: #fff; background: #1b66ca; } | |
| ._cs_multiple-select__form-container { font-family: sans-serif; padding-top: 50px; } | |
| ._cs_multiple-select__form__label { display: block; } | |
| ._cs_multiple-select__form__checkbox { cursor: pointer; } | |
| </style> | |
| </head> | |
| <body> | |
| <div class="_cs_multiple-select__buttons"> | |
| <input class="_cs_multiple-select__buttons__element _cs_multiple-select__buttons__element--a" type="button" value="Load" onclick="refresh()" /> | |
| <input class="_cs_multiple-select__buttons__element _cs_multiple-select__buttons__element--h" type="button" value="Set" onclick="set()" /> | |
| <!-- <input class="_cs_multiple-select__buttons__element" type="button" value="Update" onclick="update()" /> --> | |
| <input class="_cs_multiple-select__buttons__element" type="button" value="Reset" onclick="reset()" /> | |
| </div> | |
| <div class="_cs_multiple-select__form-container" style=""> | |
| <form id="form" name="form"> | |
| </form> | |
| </div> | |
| </body> | |
| <html> |
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 in cell', 'showDialog') | |
| .addToUi(); | |
| } | |
| function showDialog() { | |
| var html = HtmlService.createHtmlOutputFromFile('dialog').setSandboxMode(HtmlService.SandboxMode.IFRAME); | |
| SpreadsheetApp.getUi() | |
| .showSidebar(html); | |
| } | |
| function getValidationData(){ | |
| try { | |
| return SpreadsheetApp.getActiveRange().getDataValidation().getCriteriaValues()[0].getValues(); | |
| } catch(e) { | |
| return null | |
| } | |
| } | |
| function getValidationDataCheck(){ | |
| try { | |
| return SpreadsheetApp.getActiveSheet().getActiveCell().getValue(); | |
| } catch(e) { | |
| return null | |
| } | |
| } | |
| function setValues_(e, update) { | |
| var selectedValues = []; | |
| for (var i in e) { | |
| selectedValues.push(i); | |
| } | |
| var separator = ', ' | |
| var total = selectedValues.length | |
| if (total > 0) { | |
| var range = SpreadsheetApp.getActiveRange() | |
| var value = selectedValues.sort((a, b) => a.localeCompare(b)).join(separator) | |
| if (update) { | |
| var values = range.getValues() | |
| // check every cell in range | |
| for (var row = 0; row < values.length; ++row) { | |
| for (var column = 0; column < values[row].length; ++column) { | |
| var currentValues = values[row][column].split(separator);//typeof values[row][column] === Array ? values[row][column].split(separator) : [values[row][column]+''] | |
| // find same values and remove them | |
| var newValues = [] | |
| for (var j = 0; j < currentValues.length; ++j) { | |
| var uniqueValue = true | |
| for(var i = 0; i < total; ++i) { | |
| if (selectedValues[i] == currentValues[j]) { | |
| uniqueValue = false | |
| break | |
| } | |
| } | |
| if (uniqueValue && currentValues[j].trim() != '') { | |
| newValues.push(currentValues[j]) | |
| } | |
| } | |
| if (newValues.length > 0) { | |
| range.getCell(row+1, column+1).setValue(newValues.join(separator)+separator+value) | |
| } else { | |
| range.getCell(row+1, column+1).setValue(value); | |
| } | |
| } | |
| } | |
| } else { | |
| range.setValue(value); | |
| } | |
| } | |
| } | |
| function updateCell(e) { | |
| return setValues_(e, true) | |
| } | |
| function fillCell(e) { | |
| setValues_(e) | |
| } |
@mary-loulou removing the sorting in line #37 should help you achieve the effect you want:
var value = selectedValues.join(separator)
But I haven't checked it now, because it's been a long time since I used the script and I don't have it used anywhere at the moment. Test it :).
@simon88pl
Thank you! I think I tried that but I will check again.
It would be brilliant if you could add two buttons:
- one to sort ABC
- one to read the current cell content and preselect it.
Any chance you may be able to do that?
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Hi @simon88pl,
It's been a while since you posted the code. It works brilliantly.
The only thing I would like to change is the sorting. It would be amazing if the elements were not sorted alphabetically but in the original order of the selection list.
Unfortunately, all scripts I have tested seem to sort them randomly, yours does a much nicer job, but for our purposes the original order as in the column specified in the validation range would be brilliant.
Could anyone help me adapt the script?