I was looking for a way to multi select items from a dropdown in google sheets due to a client request and I’ve found several tutorials, one in particular i’ve found it very helpful but incomplete at the same time: https://www.lido.app/tutorials/google-sheets-multi-select-dropdown
The script included in this tutorial wasn’t 100% completed, so i took 15 min of my time to make it work in a way i thought it was convenient at that moment. What I needed was to remove an option if was already selected, same behaviour of a select2 dropdown or kinda.
So I’ve changed the code from the tutorial to the following:
function onEdit(e) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var activeCell = ss.getActiveCell();
if (
(activeCell.getColumn() == 8 || activeCell.getColumn() == 11)
&& ss.getActiveSheet().getName() == "Sheet1"
)
{
var newValue = e.value;
var oldValue = e.oldValue;
if (!newValue) {
activeCell.setValue("");
} else {
if (!oldValue) {
activeCell.setValue(newValue);
} else {
let opts = oldValue.split(', ')
if( opts.includes(newValue) ){
console.log('value is in array. Remove then.')
// new validation
var list = [];
opts.forEach(function(item, index){
if( item !== newValue ){
list.push(item)
}
})
console.log('list:', list)
// end new validation
console.log('newValue', newValue)
newValue = list.join(', ')
}else{
newValue = oldValue + ', ' + newValue;
}
activeCell.setValue(newValue);
}
}
}
}Now if i select an option that is already selected, would be removed from the selection.
Hope this helps!.