-
-
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) | |
| } |
@megaroeny The script, as is, is currently only written to allow for the criteria of "List from a range." I'm currently trying to rewrite a version to fit what you're trying to do...
So, if you want to use this, you'll need to set up a range of values somewhere in your sheets file from which to draw the checkboxes.
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Do I need to uncheck
V8 runtimefor this? When I do, it throws an errorAlso, not seeing anything appear in the sidebar (dialog). The three buttons are at the top, and that's it. Trying to use a list of values for the buttons. Is that possible?