0

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

1

1 Answer 1

3

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.4, written by --Hyde, 22 January 2024
  //  - add rowStart, rowEnd
  if (e.value === undefined || e.oldValue === undefined) return;
  try {
    const parameters = [

      ////////////////////////////////
      // [START modifiable parameters]
      {
        sheetsToWatch: /^(Sheet1)$/i,
        columnsToWatch: [4, 5,],
        rowStart: 1,
        rowEnd: +Infinity,
        separator: ', ',
      },
      {
        sheetsToWatch: /^(Sheet2|Sheet3)$/i,
        columnsToWatch: [5, 6,],
        rowStart: 20,
        rowEnd: 30,
        separator: ', ',
      },
      {
        sheetsToWatch: /./i, // matches all sheets
        columnsToWatch: [26,],
        rowStart: 1,
        rowEnd: 2,
        separator: ' - ',
      },
      // [END modifiable parameters]
      ////////////////////////////////

    ];
    let sheet;
    const settings = parameters.filter(par =>
      par.columnsToWatch.includes(e.range.columnStart)
      && par.rowStart <= e.range.rowStart
      && par.rowEnd >= e.range.rowEnd
      && (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 && !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 parameters.

1
  • The function as shown will do its thing in any cell in column 5, i.e., column E in the sheets Sheet1, Sheet2 and Sheet3. Edit the code to set another column and a different set of sheets as necessary. Oct 4, 2021 at 19:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.