Try a script that runs on a simple trigger, like this:
/**
* Simple trigger that runs each time the user hand edits the spreadsheet.
*
* @param {Object} e The onEdit() event object.
*/
function onEdit(e) {
if (!e) {
throw new Error('Please do not run the script in the script editor window. '
+ 'It runs automatically when you hand edit the spreadsheet.');
}
multiSelectDropDownList_(e);
}
/**
* Enables clumsy but workable multi-select drop-down lists.
*
* @param {Object} e The onEdit() event object.
*/
function multiSelectDropDownList_(e) {
// version 1.3, written by --Hyde, 20 April 2023
if (e.value === undefined || e.oldValue === undefined) {
return;
}
try {
const parameters = [
////////////////////////////////
// [START modifiable parameters]
{
sheetsToWatch: /^(Sheet1)$/i,
columnsToWatch: [4, 5,],
separator: ', ',
},
{
sheetsToWatch: /^(Sheet2|Sheet3)$/i,
columnsToWatch: [5, 6,],
separator: ', ',
},
// [END modifiable parameters]
////////////////////////////////
];
let sheet;
const settings = parameters.filter(par =>
par.columnsToWatch.includes(e.range.columnStart)
&& (sheet = sheet || e.range.getSheet()).getName().match(par.sheetsToWatch)
)[0];
if (!settings) {
return;
}
let newValue = e.oldValue;
if (e.oldValue !== e.value
&& !e.oldValue.startsWith(e.value + settings.separator)
&& !e.oldValue.endsWith(settings.separator + e.value)
&& !e.oldValue.includes(settings.separator + e.value + settings.separator)) {
newValue += settings.separator + e.value;
}
const rule = e.range.getDataValidation();
if (!rule.getAllowInvalid()) {
e.range.setDataValidation(rule.copy().setAllowInvalid(true).build());
}
e.range.setValue(newValue);
} catch (error) {
e.source.toast(`${error.message} ${error.stack}`, 'Error in multiSelectDropDownList_()', 30)
}
}
To install the script, paste the code in Tools > Script editor, completely replacing the myFunction()
placeholder there. Configure it to the column where you have the drop-down lists by modifying the columnsToWatch
arrays and the sheetsToWatch
regexes.