Skip to content

Instantly share code, notes, and snippets.

@Fauntleroy
Last active April 25, 2023 05:19
Show Gist options
  • Save Fauntleroy/6f441442ef4d1c7e138837c00bcbf0f7 to your computer and use it in GitHub Desktop.
Save Fauntleroy/6f441442ef4d1c7e138837c00bcbf0f7 to your computer and use it in GitHub Desktop.
A basic "Multiselect" Apps Script for Google Sheets

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:

image

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.

image

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.

image

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

image

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:

image

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.

image

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:

image

// 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}`);
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment