Skip to content

Instantly share code, notes, and snippets.

@bennettscience
Last active May 9, 2023 07:22
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
  • Save bennettscience/0530546722a48b1473329785e2460b95 to your computer and use it in GitHub Desktop.
Save bennettscience/0530546722a48b1473329785e2460b95 to your computer and use it in GitHub Desktop.
Dynamic data validation in Apps Script
function onEdit(e) {
var row = e.range.getRow();
var col = e.range.getColumn();
// If the edited column was 2 or 3, do the next part. Ignore other changes
if(col == 2 || col == 3) {
var value = sheet.getRange(row, col).getValue();
updateList(row,col, value);
}
}
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("ChoiceSheet");
var dates = ss.getSheetByName("Dates");
function setValidation() {
var cells = sheet.getRange("B2:C15");
var rules = dates.getRange("A:A");
var validation = SpreadsheetApp.newDataValidation().requireValueInRange(rules).build();
cells.setDataValidation(validation);
}
function updateValidation(row,col,value) {
var data = dates.getDataRange().getValues();
// Looping backwards lets you cleanly delete rows
for(var i=data.length-1; i>=0; i--) {
if(data[i].toString() == value) {
dates.deleteRow((i+1));
}
}
// Remove the validation from the edited cell so it doesn't show an error
sheet.getRange(row, col).setDataValidation(null);
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment