-
-
Save paulorenanmelo/f656800e7b97e3f4489bec733b52dd94 to your computer and use it in GitHub Desktop.
| <!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> |
| 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) | |
| } |
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)
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!
Hi Everyone! I've tried this script and the forks: they're wonderful.
I've tried it into a worksheet with two sheets: the first where I run the script in column C which contains my friends' surname and name, the second which collects the same range of values in column A. I need to improve the script in order to search and match for each of the multiple values selected in the first sheet (friend's names) by the box, with the values associated and correspondant in the same line in column B of the second sheet (email addresses).
For a better exemplification:
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
The goal is to complete Sheet1 Column C by all the values selected through a menu with checkboxes (ex: Mary Johnson, Jonny Richards....) and for each value selected (the range of values is contained in Column A of Sheet2) autocomplete Sheet1 Column D by auto searching and copying the correspondand values of Sheet2 Column B in Sheet1 Column D (ex: mary.johnson@gmail.com;johny.richards@gmail.com).
I need to improve the script found here (fork by Paulo Renan Melo) in order to search and match for each of the multiple values selected in the first sheet (friend's names) by the box, with the values associated and correspondant at the same line in column B of the second sheet (email addresses). I think I t needs another array.
I've found some working code here with the function search and match: Selecting Multiple Values from a Dropdown List in Google Spreadsheet
I'm not so good in codying so I'm not able to merge these two scripts can anyone help me?
I think I need another array. I've found some code here with the function search and match: https://stackoverflow.com/questions/42044903/compare-two-columns-and-if-match-paste-matching-value
https://stackoverflow.com/questions/41968207/google-apps-script-performing-index-match-function-between-two-separate-google but i'm not able to adapt it to this script.
Can anyone improve this script?Even if as it is It just works perfectly, I think it's useful a workaround also for other applications, if it's possible.