Is it possible to implement data validation programmatically in Google Sheets?

Other Questions Google Sheets
1

I’m trying to set up data validation in Google Sheets using Apps Script. My code runs on an ‘onEdit’ trigger and works fine until I try to set the validation criteria. Here’s what I’ve got so far:

function validateOnEdit() {
  let spreadsheet = SpreadsheetApp.getActive();
  let activeSheet = spreadsheet.getActiveSheet();
  let editedCell = spreadsheet.getActiveRange();

  if (activeSheet.getName() === 'Schedule' && editedCell.getColumn() === 2 && editedCell.getRow() > 3) {
    let validOptions = ['Low', 'Medium', 'High'];
    let validationRule = activeSheet.getRange(editedCell.getRow(), editedCell.getColumn() + 1).getDataValidation();
    validationRule.setAllowInvalid(false);
    validationRule.setHelpText('Please select a priority level');
    
    // This line causes issues:
    validationRule.setValues(validOptions);
    
    activeSheet.getRange(editedCell.getRow(), editedCell.getColumn() + 1).setDataValidation(validationRule);
  }
}

The script won’t save because of the setValues method. It seems Google might have changed how this works. Does anyone know the current way to set up data validation in code for Google Sheets?

2

I’ve encountered similar issues when working with data validation in Google Sheets. The solution lies in using the correct methods for creating and applying validation rules. Instead of modifying an existing rule, create a new one using SpreadsheetApp.newDataValidation(). Then, use requireValueInList() to set your valid options and apply the rule to your target range. This approach resolves the issues faced with the setValues method. Make sure to integrate and test the new validation rule carefully in your script to ensure it functions as expected.

3

Yes, it’s definitely possible to implement data validation programmatically in Google Sheets. I’ve done this quite a bit in my own projects. The issue in your code is with the setValues method - it’s not the correct way to set up validation criteria.

Instead, you should use SpreadsheetApp.newDataValidation() to create a new validation rule. Here’s how I’d modify your code:

let validationRule = SpreadsheetApp.newDataValidation()
  .requireValueInList(validOptions)
  .setAllowInvalid(false)
  .setHelpText('Please select a priority level')
  .build();

activeSheet.getRange(editedCell.getRow(), editedCell.getColumn() + 1).setDataValidation(validationRule);

This creates a dropdown list with your valid options. The requireValueInList method is what you need to set the acceptable values.

Also, make sure your onEdit trigger is set up correctly in the project triggers. Hope this helps solve your issue!

4

hey samuel, i’ve dealt with this before. the trick is to use newDataValidation() instead of getDataValidation(). try this:

let rule = SpreadsheetApp.newDataValidation()
.requireValueInList(validOptions)
.setAllowInvalid(false)
.setHelpText(‘Pick a priority level’)
.build();

editedCell.offset(0, 1).setDataValidation(rule);

this should work for ya. let me know if u need more help!