Last active
May 7, 2024 10:26
-
-
Save LouDnl/fb55dd1b74fe1d11d943ea6ab838edc8 to your computer and use it in GitHub Desktop.
Google Sheets Apps Script for multiple selections in a dropdown selector
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// V4 ~ Still slow damnit | |
function onEdit(e) { | |
let triggeringSheets = new Array( // only in these sheets | |
"sheet1", | |
"sheet2", | |
"sheet3" | |
); | |
let triggeringColumns = new Array( // only in these columns | |
// BrondataFlevoland | |
15, // Column O == 15 | |
25, // Column Y == 25 | |
32, // Column AF == 32 | |
36 // Column AJ == 36 | |
); | |
let excludingRows = new Array( // not in these rows | |
1 | |
); | |
let activeSheet = e.source.getActiveSheet() // get the active sheet | |
let triggeredSheet = activeSheet.getName(); // get the name of the active sheet | |
if (triggeringSheets.includes(triggeredSheet)) // only if current sheet is in the triggeringSheets array | |
{ | |
let currentCell = e.range; // get the current cell from the current selection | |
let currentColumn = currentCell.getColumn(); // get the current column number | |
let currentRow = currentCell.getRow(); // get the current row number | |
if (triggeringColumns.includes(currentColumn) // only if current column is in the triggeringColumns array | |
&& !excludingRows.includes(currentRow)) // and only if current row is **not** in the excludingRows array | |
{ | |
let newValue = e.value; // get the new cell value ~ stores the newValue | |
let oldValue = e.oldValue; // get the old cell value ~ stores the oldValue | |
if (!newValue) // if no newValue e.g. newValue is empty | |
{ | |
currentCell.setValue(""); // set currentCell to an empty string | |
} else { | |
if (!oldValue) // if no oldValue e.g. oldValue is empty | |
{ | |
currentCell.setValue(newValue); // set the current cell to the newValue e.g. the selected option | |
} else { | |
if(!oldValue.includes(newValue)) // if oldValue does not include newValue | |
{ | |
currentCell.setValue(oldValue + ', ' + newValue); // append newValue to oldValue preceded by a comma | |
} else { // else if the oldValue already includes the newValue it should be a deselection/ removal | |
let regex = new RegExp("(, " + newValue + "|" + newValue + ", )", 'gi'); // create a Regex constructor | |
// example: | |
// oldValue: Procesregie, Verklarende analyse | |
// newValue: Procesregie | |
// RegExp: /(, Procesregie|Procesregie, )/gi | |
// replacedValue: Verklarende analyse | |
let replacedValue = oldValue.replace(regex, ''); // create a new value for oldData | |
currentCell.setValue(replacedValue); // set currentCell value with replacedValue | |
} | |
} | |
} | |
} | |
} | |
} | |
// V3 ~ SLOW +/- 1 second delay in change | |
function onEdit(e) { | |
var triggeringSheets = new Array("sheet1", "sheet2", "sheet3"); // only in these sheets | |
var activeSheet = e.source.getActiveSheet() // get the active sheet | |
var triggeredSheet = activeSheet.getName(); // get the name of the active sheet | |
if (triggeringSheets.includes(triggeredSheet)) // only if current sheet is in the triggeringSheets | |
{ | |
var currentCell = e.range; // get the current cell from the current selection | |
var currentColumn = currentCell.getColumn(); // get the current column number | |
var currentRow = currentCell.getRow(); // get the current row number | |
if((currentColumn == 15 // Column O == 15 | |
|| currentColumn == 25 // Column Y == 25 | |
|| currentColumn == 36) // Column AJ == 36 | |
&& currentRow != 1) // currentCell is not in row 1 | |
{ | |
var newValue = e.value; // get the new cell value ~ stores the newValue | |
var oldValue = e.oldValue; // get the old cell value ~ stores the oldValue | |
if (!newValue) // if newvalue is empty | |
{ | |
currentCell.setValue(""); // set currentCell to an empty string | |
} else { | |
if (!oldValue) // if no oldValue e.g. oldValue is empty | |
{ | |
currentCell.setValue(newValue); // set the current cell to the newValue e.g. the selected option | |
} else { | |
if(!oldValue.includes(newValue)) // if oldValue does not include newValue | |
{ | |
currentCell.setValue(oldValue + ', ' + newValue); // append oldValue with newValue preceded by a comma | |
} else { // else the oldValue already includes the newValue so it should be a deselection | |
var regex = new RegExp("(, " + newValue + "|" + newValue + ", )", 'gi'); // Regex constructor | |
var replacedData = oldValue.replace(regex, ''); // replace text with regex in oldValue | |
// Logger.log("nested old: " + oldValue + " new: " + newValue + " regex: " + regex + " regextest: " + replacedData); | |
// example: nested old: Procesregie, Verklarende analyse new: Procesregie regex: /(, Procesregie|Procesregie, )/gi regextest: Verklarende analyse | |
currentCell.setValue(replacedData); // set currentCell value with replacedData | |
} | |
} | |
} | |
} | |
} | |
} | |
// V2 ~ SLOW +/- 1 second delay in change | |
function onEdit(e) { | |
var triggeringSheets = new Array("sheet1", "sheet2", "sheet3"); // only in these sheets | |
var activeSheet = e.source.getActiveSheet() // get the active sheet | |
var triggeredSheet = activeSheet.getName(); // get the name of the active sheet | |
if (triggeringSheets.includes(triggeredSheet)) // only if current sheet is in the triggeringSheets | |
{ | |
var currentCell = e.range; // get the current cell from the current selection | |
if((currentCell.getColumn() == 15 // Column O == 15 | |
|| currentCell.getColumn() == 25 // Column Y == 25 | |
|| currentCell.getColumn() == 36) // Column AJ == 36 | |
&& currentCell.getRow() | |
!= 1) // currentCell is not in row 1 | |
{ | |
var newValue = e.value; // get the new cell value ~ stores the newValue | |
var oldValue = e.oldValue; // get the old cell value ~ stores the oldValue | |
if (!newValue) // if newvalue is empty | |
{ | |
currentCell.setValue(""); // set currentCell to an empty string | |
} else { | |
if (!oldValue) // if no oldValue e.g. oldValue is empty | |
{ | |
currentCell.setValue(newValue); // set the current cell to the newValue e.g. the selected option | |
} else { | |
if(!oldValue.includes(newValue)) // if oldValue does not include newValue | |
{ | |
currentCell.setValue(oldValue + ', ' + newValue); // append oldValue with newValue preceded by a comma | |
} else { // else the oldValue already includes the newValue so it should be a deselection | |
var regex = new RegExp("(, " + newValue + "|" + newValue + ", )", 'gi'); // Regex constructor | |
var replacedData = oldValue.replace(regex, ''); // replace text with regex in oldValue | |
// Logger.log("nested old: " + oldValue + " new: " + newValue + " regex: " + regex + " regextest: " + replacedData); | |
// example: nested old: Procesregie, Verklarende analyse new: Procesregie regex: /(, Procesregie|Procesregie, )/gi regextest: Verklarende analyse | |
currentCell.setValue(replacedData); // set currentCell value with replacedData | |
} | |
} | |
} | |
} | |
} | |
} | |
// V1 ~ SLOW +/- 1 second delay in change | |
function onEdit(e) { | |
var selection = SpreadsheetApp.getActiveSpreadsheet().getSelection(); // get the current selection | |
var currentCell = selection.getCurrentCell(); // get the current cell from the current selection | |
if((currentCell.getColumn() == 15 // Column O == 15 | |
|| currentCell.getColumn() == 25 // Column Y == 25 | |
|| currentCell.getColumn() == 36) // Column AJ == 36 | |
&& currentCell.getRow() | |
!= 1 // currentCell is not in row 1 | |
&& selection.getActiveSheet().getName() | |
== "myFancySheetName" ) // and only in this sheet | |
{ | |
var newValue = e.value; // stores the newValue | |
var oldValue = e.oldValue; // stores the oldValue | |
if (!newValue) // if newvalue is empty | |
{ | |
currentCell.setValue(""); // set currentCell to an empty string | |
} else { | |
if (!oldValue) // if no oldValue e.g. oldValue is empty | |
{ | |
currentCell.setValue(newValue); // set the current cell to the newValue e.g. the selected option | |
} else { | |
if(!oldValue.includes(newValue)) // if oldValue does not include newValue | |
{ | |
currentCell.setValue(oldValue + ', ' + newValue); // append oldValue with newValue preceded by a comma | |
} else { // else the oldValue already includes the newValue so it should be a deselection | |
var regex = new RegExp("(, " + newValue + "|" + newValue + ", )", 'gi'); // Regex constructor | |
var replacedData = oldValue.replace(regex, ''); // replace text with regex in oldValue | |
// Logger.log("nested old: " + oldValue + " new: " + newValue + " regex: " + regex + " regextest: " + replacedData); | |
// example: nested old: Procesregie, Verklarende analyse new: Procesregie regex: /(, Procesregie|Procesregie, )/gi regextest: Verklarende analyse | |
currentCell.setValue(replacedData); // set currentCell value with replacedData | |
} | |
} | |
} | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment