-
-
Save paulorenanmelo/f656800e7b97e3f4489bec733b52dd94 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). Edit1: break lines between selections, instead of comma. Edit2: Add Select All button, and change name from Reset to Clear selection. Sorry for bad copied code in html. Quick mindless solution
This file contains hidden or 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' | |
| 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; | |
| output += '<input type=\'checkbox\' name=\''+data[i][0]+'\' value=\''+data[i][0]+'\'>' | |
| 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 | |
| } | |
| function drawFormSelected() { | |
| 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; | |
| 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]+'\' checked >' | |
| 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 | |
| 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 selectAll() { | |
| drawFormSelected() | |
| } | |
| </script> | |
| </head> | |
| <body> | |
| <div style='position:fixed; padding-top: 10px; background-color: white; height: 50px; width: 100%; top: 0;'> | |
| <input type="button" value="Set" onclick="set()" /> | |
| <input type="button" value="Update" onclick="update()" /> | |
| <input type="button" value="Clear Selection" onclick="reset()" /> | |
| <input type="button" value="Select All" onclick="selectAll()" /> | |
| <input type="button" value="Refresh validation" onclick="google.script.run.showDialog()" /> | |
| </div> | |
| <div style="font-family: sans-serif; padding-top: 50px;"> | |
| <form id="form" name="form"> | |
| </form> | |
| </div> | |
| </body> | |
| <html> |
This file contains hidden or 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 setValues_(e, update) { | |
| var selectedValues = []; | |
| for (var i in e) { | |
| selectedValues.push(i); | |
| } | |
| var separator = '\n' | |
| 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) | |
| } |
Author
Hi Paulo,
Thank you so much for the reply!
I'm starting by a Google Spreadsheet with two worksheets the first one named Sheet1 which contains: Column A: Books' Author Column B: Books' Title Column C: My Friends Surname and Name Column D: My Friends' Email address.
The second one named Sheet2 which contains: Column A: My Friends Surname and Name Column B: My Friends' Email address Sheet2
Heres's the link to the file I'm working on: https://drive.google.com/open?id=1s7tcozGAvFTPivjGHlOeF3NczqftMF4AxdIow3CJ08k
The input is Sheet 1 Column C (my Friend's names, the same in Sheet 2 Column A) implemented with your script with checkboxes.
The result I'm lookiing for It's complete also Sheet1 Column D with the related e-mail addresses in Sheet2 Column B (please see the examples I've completed manually in Column D). I thought about array because I need to work with multiple values in one cell in column C (instead in Sheet 2 there are single values in column A) and also because I think it's more easy to update values.....but I'm not so able with google-(java)script!
I hope I explained more clearly.....Thank you so much for your work!
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Hi Eiseauton,
I'm glad the script can be of some help. I can't even remember why I needed it, it was a quick thing.
I am only on the phone for the next week, so can't work on it, but I can have a look after that, shouldn't take me long. Although I'd first try using available functions such as VLookup, HLookup, Filter and even just plain and simple array ={item1, item2} or ={item1; item2}
Just for that, could you give an example with input and expected output? (Even if it's just in an image)