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.
*
* When a value is selected in a drop-down list, it is appended
* to the end of the list, unless it already appears in the list,
* in which case it is removed from the list.
* Converts 'Dropdown (from a range)' lists to 'Dropdown' lists.
*
* @param {Object} e The onEdit() event object.
*/
function multiSelectDropDownList_(e) {
  // version 1.6, written by --Hyde, 2 June 2024
  //  - see https://webapps.stackexchange.com/a/159321/269219
  'use strict';
  if (e.value === undefined || e.oldValue === undefined) return;
  try {
    const parameters = [

      ////////////////////////////////
      // [START modifiable parameters]
      {
        sheetsToWatch: /^(Sheet1)$/i,
        columnsToWatch: [4, 5,], // columns D, E
        rowStart: 1,
        rowEnd: +Infinity,
        separator: ', ',
      },
      {
        sheetsToWatch: /^(Sheet2|Sheet3)$/i,
        columnsToWatch: [5, 6,], // columns E, F
        rowStart: 2,
        rowEnd: 30,
        separator: ', ',
      },
      {
        sheetsToWatch: /./i, // matches any sheet
        columnsToWatch: [26,], // column Z
        rowStart: 1,
        rowEnd: 2,
        separator: ', ',
      },
      // [END modifiable parameters]
      ////////////////////////////////

    ];
    let sheet, sheetName;
    const settings = parameters.find(p =>
      p.columnsToWatch.includes(e.range.columnStart)
      && p.rowStart <= e.range.rowStart
      && p.rowEnd >= e.range.rowEnd
      && (sheetName = sheetName || (sheet = sheet || e.range.getSheet()).getName()).match(p.sheetsToWatch)
    );
    if (!settings) return;
    const rule = e.range.getDataValidation();
    if (!rule) return;
    const type = String(rule.getCriteriaType());
    if (!['VALUE_IN_LIST', 'VALUE_IN_RANGE'].includes(type)) return;
    const oldValues = e.oldValue.split(settings.separator);
    const newValues = oldValues.includes(e.value)
      ? oldValues.filter(v => v !== e.value) // remove value when it is re-selected
      : oldValues.concat(e.value);
    const newValuesString = newValues.join(settings.separator);
    let criteriaValues = rule.getCriteriaValues()[0];
    if ('VALUE_IN_RANGE' === type) criteriaValues = criteriaValues.getValues().flat()
      .filter((e, i, a) => String(e) && a.indexOf(e) === i);
    const newCriteriaValues = criteriaValues
      .filter(v => !v.includes(settings.separator))
      .concat(newValuesString);
    const newRule = SpreadsheetApp.newDataValidation().requireValueInList(newCriteriaValues).build();
    e.range.setDataValidation(newRule);
    e.range.setValue(newValuesString);
  } catch (error) {
    e.source.toast(`${error.message} ${error.stack}`, 'Error in multiSelectDropDownList_()', 30);
    throw error;
  }
}

To install the script, paste the code in Extensions > Apps Script, completely replacing the myFunction() placeholder there. Configure it to the column where you have the drop-down lists by modifying the parameters.