-
-
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 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 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)