<!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 | |
} | |
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() | |
} | |
</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> |
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 = ',' | |
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) | |
} |
This comment has been minimized.
This comment has been minimized.
Possible workaround with validation errors - provide validation range for dialog from special cell which contains validation data. If someone wants this approach to be implemented - just select special cell which is validated, then run the script. Opened form you can you anywhere you want. Select other cell with validation data and click refresh validation to update form. |
This comment has been minimized.
This comment has been minimized.
Set button will override whole cell with new value |
This comment has been minimized.
This comment has been minimized.
works great! thanks |
This comment has been minimized.
This comment has been minimized.
This is awesome. Thanks, @coinandsteeldev |
This comment has been minimized.
This comment has been minimized.
Great script! Thanks |
This comment has been minimized.
This comment has been minimized.
how do you set the new values for this script? I edit the first cell in the doc, but the values still don't show up. |
This comment has been minimized.
This comment has been minimized.
Hi! Here is good instruction for setting up the script https://stackoverflow.com/a/53310932/1223007 |
This comment has been minimized.
This comment has been minimized.
Works great! Adding data validation to multiple cells and adding additional columns of input options works great: Thanks! |
This comment has been minimized.
This comment has been minimized.
Hi everyone, |
This comment has been minimized.
This comment has been minimized.
Some idea to improve this script: ● we need a way to recognize cells with data validation. We can't keep the arrow from Google Sheet (since it points to the wrong dropmenu limited to single selection, and will delete the entries added via the script). So it's better to hide the data validation arrow, but then you don't know where the cells with the data validation are. ● each time you want to edit a cell with different data validation, you must on refresh data validation. I would be better if the list is updated automatically, according to the list linked to the selected cell |
This comment has been minimized.
This comment has been minimized.
This comment has been minimized.
This comment has been minimized.
@DaTigerYT I believe I have setup that screen above as you have but I cannot get the Side Panel to show me a Multi-select (showing options and checkboxes) after Saving it. Is there another step to do that? If you know. Thanks |
This comment has been minimized.
This comment has been minimized.
Thanks a lot for your wonderful scripts, they work perfectly |
This comment has been minimized.
This comment has been minimized.
This is a LIFE SAVER! Thank you |
This comment has been minimized.
This comment has been minimized.
Been doing some workarounds on google sheets, this solution made my day. |
This comment has been minimized.
This comment has been minimized.
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). 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, i've explained better here: https://gist.github.com/paulorenanmelo/f656800e7b97e3f4489bec733b52dd94 .... but i'm not able to adapt it to this script! Can anyone improve this script? As it is It just works perfectly, but if somebody can help me It will work better. |
This comment has been minimized.
This comment has been minimized.
In addition to my last commentation, I've also found this script in order to search and match: https://stackoverflow.com/questions/41968207/google-apps-script-performing-index-match-function-between-two-separate-google (similar to a VLookup function). |
This comment has been minimized.
This comment has been minimized.
Better if we add Adding this would display the form elements below the button. |
This comment has been minimized.
This comment has been minimized.
If I'm reading through this correctly, then the only way for the script to pick up the List of options from Data Validation is if it's reading from a designated cell range and can't be a List of Items, in the Criteria section of Data Validation? DaTigerYT posted an image that that shows the Criteria being set as a List of items instead of List from range -- and that's why the script isn't working as intended, right? because the Criteria needs to be "List from range" can the script also allow for multiple selections if pulling options from this List of items? |
This comment has been minimized.
This comment has been minimized.
It works handsomely! Thanks |
This comment has been minimized.
This comment has been minimized.
I also have a similar issue :( wondering if any of you manage to find a solution yet! edit: typo |
This comment has been minimized.
This comment has been minimized.
Tried shifting to "list from range" still didn't work. Having the same problem with @DaTigerYT |
This comment has been minimized.
This comment has been minimized.
@lianlaughsalot and @coinsandsteeldev- this script had been working happily for me for a few months and only recently stopped working. Turns out, the script above is not compatible with V8 runtime. https://developers.google.com/apps-script/guides/v8-runtime
|
This comment has been minimized.
This comment has been minimized.
@coinsandsteeldev @lestroud Sorry, I'm a noob and don't even know where to start... :-\ |
This comment has been minimized.
This comment has been minimized.
@VforVenique |
This comment has been minimized.
This comment has been minimized.
@Lartsch One more question for you: is it possible to change the text that reads "Apps Script application" label at the top of the Scripts sidebar? |
This comment has been minimized.
This comment has been minimized.
@coinsandsteeldev Follow-up question: I'd like to use this script at the nonprofit I work for and we have some pretty sensitive client data in our sheets. Does this script give you or anyone else outside of our Google Suite domain access to our Google Sheets/Drive (and therefore our client data)? The Authorization prompts to run the script make me a little nervous about data privacy/security. But I'm hoping that it's just a standard Authorization prompt for third-party apps/add-ons and that it doesn't really apply to the multi-select script you've created. Thanks again for all of your help! Many Thanks, |
This comment has been minimized.
This comment has been minimized.
This comment has been minimized.
This comment has been minimized.
You may need to disable V8 and revert back to Rhino.
…On Tue, Feb 2, 2021 at 7:54 PM msmichls ***@***.***> wrote:
***@***.**** commented on this gist.
------------------------------
I am having trouble seeing any of the validation values. Also, I am not
seeing any activity when I click the buttons. I am working on Google
Chrome. Is there something I am missing?
—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
<https://gist.github.com/4c67dfa5411e8add913273fc5a30f5e7#gistcomment-3617773>,
or unsubscribe
<https://github.com/notifications/unsubscribe-auth/ARZZQ5A6QT4UJJJUO2ZICALS5CUEZANCNFSM4HXS23VQ>
.
|
This comment has been minimized.
This comment has been minimized.
I've updated this script so that when you navigate back to a cell that already has multiple entries selected, the UI displays these options as checked so that its easier to work with large lists. https://gist.github.com/GeorgeNavarre/fdaeb3c3b49e246b9fae8aefe9c30448 |
This comment has been minimized.
This comment has been minimized.
@VforVenique |
This comment has been minimized.
Selection support several rows - very first (left) row is an actual value which will be used in the cell, the rest will create checkbox label.