A basic "Multiselect" Apps Script for Google Sheets

readme.md

A basic "Multiselect" Apps Script for Google Sheets

This is a basic, but functional implementation of a "Multiselect" mode for the "Dropdown" of Google Sheets. You can get a comma separated list of values, but a warning will always show up (due to limitations with Google Sheets). When running properly, it looks like this:

In order to use this script, you must first create a dropdown. To do so, select a cell, then choose Data -> Data Validation from the menu.

Then configure your dropdown. Your configuration should look like this. Pay special attention to "If the data is invalid: Show a warning". If this is not set, the script will not work.

After you have created your dropdown, select Extensions -> Apps Script from the menu.

You will be sent to the Google Apps Script dashboard. Click on everything that sounds like "internal development". Eventually, you should have a new project that looks like this:

Simply copy the contents of the Code.gs file in this gist, then update the MULTI_CHOICE_CELLS configuration variable accordingly. You will need to set which cells in which sheets the "Multiselect" functionality will be applied to.

This script has some minimal logging that can help you find issues. If you're having trouble, check out the "Executions" tab on the left:

Code.gs

// SCRIPT CONFIGURATION
// Change the configuration values below to make everything work.

// MULTI_CHOICE_CELLS
// This is a string in "A1 Notation". Simply specify which cells you would
// like to have "multiselect" functionality the same way you specify cells
// inside Google Sheets
//
// const MULTI_CHOICE_CELLS = 'Sheet1!A1:C3,Sheet2!D5:E6';

// Replace the contents of the single quotes below with your selection
const MULTI_CHOICE_CELLS = 'Sheet1!D1:E2';



// SCRIPT BODY
// Do not modify the code below. Everything you should work with is
// above, in the SCRIPT CONFIGURATION section

const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

// Util method
function binaryXor (arr1, arr2) {
  const a = arr1.filter(x => !arr2.includes(x))
  const b = arr2.filter(x => !arr1.includes(x))

  return Array.from(new Set([...a, ...b]))
}

function isCellWithinRange(rangeStr, cell) {
  const sheet = cell.getSheet();
  const range = sheet.getRange(rangeStr);
  const numRows = range.getNumRows();
  const numCols = range.getNumColumns();
  
  for (let i = 1; i <= numRows; i++) {
    for (let j = 1; j <= numCols; j++) {
      const currCell = range.getCell(i, j);
      if (cell.getRow() == currCell.getRow() && cell.getColumn() == currCell.getColumn()) {
        return true;
      }
    }
  }
  
  return false;
}

// Listen for cell value edits
function onEdit(e) {
  let oldValue;
  let newValue;
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const currentCell = spreadsheet.getCurrentCell();

  if (isCellWithinRange(MULTI_CHOICE_CELLS, currentCell)) {
    newValue = e.value;
    oldValue = e.oldValue;

    const currentCellA1Notation = currentCell.getA1Notation();
    Logger.log(`[TK MultiSelect]: CELL: ${currentCellA1Notation}, OLD: ${oldValue}, NEW: ${newValue}`);

    if (!newValue) {
      currentCell.setValue('');

      Logger.log(`[TK MultiSelect]: Value cleared because new value is empty.`);
    }
    else {
      const oldArray = oldValue.split(',').map(item => item.trim());
      const newArrayOfValues = binaryXor(oldArray, [newValue]);
      const updatedValue = newArrayOfValues.join(', ');
      currentCell.setValue(updatedValue);

      Logger.log(`[TK MultiSelect]: Value updated to: ${updatedValue}`);
    }
  }
}