Last active
May 9, 2023 07:22
-
-
Save bennettscience/0530546722a48b1473329785e2460b95 to your computer and use it in GitHub Desktop.
Dynamic data validation in Apps Script
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
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); | |
} | |
} |
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
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); | |
} |
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
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