-
-
Save OnodOfTheNorth/fdaeb3c3b49e246b9fae8aefe9c30448 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 (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() { | |
| 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 += '<input type=\'checkbox\' name=\''+data[i][0]+'\' value=\''+data[i][0]+'\''+ checked +' >' | |
| output += data[i][0] | |
| output += '<br>' | |
| } else if (data[i].length > 1) { | |
| if (data[i][0] == '') continue; | |
| // left will be used as value | |
| // the rest is title | |
| output += '<input 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 += '<br>' | |
| } | |
| } | |
| } 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() | |
| } | |
| </script> | |
| </head> | |
| <body> | |
| <div style='position:fixed; padding-top: 10px; background-color: white; height: 30px; width: 100%; top: 0;'> | |
| <input type="button" value="Set" onclick="set()" /> | |
| <input type="button" value="Update" onclick="update()" /> | |
| <input type="button" value="Reset" onclick="reset()" /> | |
| <input type="button" value="Refresh validation" onclick="google.script.run.showDialog()" /> | |
| </div> | |
| <div style="font-family: sans-serif; padding-top: 30px;"> | |
| <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 for this 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.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) | |
| } |
Thanks for your revision! Like some others, I'm having trouble getting this set of scripts to work (no options appear)
Incidentally, with
// function onOpen(e) { // SpreadsheetApp.getUi() // .createMenu('Scripts') // .addItem('Multi-select for this cell...', 'showDialog') // .addToUi(); // }how does the 'Scripts' menu appear on the menu strip?
Would you mind sending me a screen shot of how you have your script file set up?
Hello ! Thanks a lot for your code and time. Any idea to improve filters based on multi-criteria selection?
Hello! Thanks a lot for your code and time. Any idea to improve filters based on multi-criteria selection?
Hey sorry for the year late reply! I'm not super familiar with the google plugin developer libraries anymore. You should be able to use the range, value, and values variables, declared in function setValues_(e, update), to write a function that collapses specified/unspecified cells. That's about the extent of my thought on it though; I switched from google sheets to a MediaWiki-based database about a month after I published this fork so it's been a while. I hope this finds you well; though I'm sure you've found a better solution by now!
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Thanks for your revision! Like some others, I'm having trouble getting this set of scripts to work (no options appear)
Incidentally, with
how does the 'Scripts' menu appear on the menu strip?