How to add multiple pre-created values to the same cell in google sheets

Question

With google sheets if you add data validation, you can select a single value from a predefined list of values (or from another specified range of values). This makes data entry a lot faster and more precise.

However I would like to do the same but without being constrained to a single value. So suppose I got this list of values

healthy
cheap
expensive
trendy
mexican
fast food

And I would like the ability to quickly select from the above list _multiple values to the same cell, so that it can end up having for example: expensive, mexican

Answer 1

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.