-
-
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) | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
@simon88pl
Thank you! I think I tried that but I will check again.
It would be brilliant if you could add two buttons:
Any chance you may be able to do that?