Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Create a Multi-Select Drop-Down for any cell in Google Sheets
const separator = ', ';
const dvType = SpreadsheetApp.DataValidationCriteria.VALUE_IN_RANGE;
function onEdit(e) {
if (e.value != null && e.oldValue != null && e.value !== "") {
var dataValidation = e.range.getDataValidation();
if(dataValidation != null && dataValidation.getCriteriaType() == dvType &&
e.value.indexOf(separator) < 0 && e.oldValue.indexOf(e.value) < 0) {
e.range.setValue(e.oldValue + separator + e.value);
}
}
}
@sschwartzman
Copy link
Author

sschwartzman commented Sep 30, 2021

Based on https://spreadsheetpoint.com/multiple-selection-drop-down-google-sheets/

Improvements:

  • Works on any cell, only depends on which cell was edited (not on which spreadsheet or cell is currently active)
  • Only proceeds if the edit was on a single cell and that the cell wasn't blanked out (aka reset)
  • Checks for if the cell has "data validation" enabled for "list from a range"
  • Only proceeds if the new value itself isn't just an edit of the existing value
  • Uses a global 'separator' variable for easy modification

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment