Data Validation in Google Sheets

Data validation rules prevent invalid entries from being added to your sheet. Apps Script lets you create dropdown lists, enforce number ranges, restrict dates, and write custom formula rules — all programmatically using SpreadsheetApp.newDataValidation().

Adding a Dropdown to the Status Column

function addStatusDropdown() {
  var sheet = SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheetByName("Sales Tracker");

  var statusRange = sheet.getRange(2, 7, sheet.getMaxRows() - 1, 1); // Column G

  var rule = SpreadsheetApp.newDataValidation()
    .requireValueInList(["Closed", "In Progress", "Pending", "Lost"], true)
    .setAllowInvalid(false)
    .setHelpText("Select a valid deal status.")
    .build();

  statusRange.setDataValidation(rule);
  Logger.log("Status dropdown applied.");
}

The true argument shows values as a dropdown. Setting setAllowInvalid(false) rejects any entry not in the list.

Adding a Dropdown from a Named Range

If your valid options live in a separate Config sheet:

function addProductDropdownFromRange() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Sales Tracker");
  var configSheet = ss.getSheetByName("Config");

  // Config sheet has product names in A1:A3
  var productRange = configSheet.getRange("A1:A3");

  var rule = SpreadsheetApp.newDataValidation()
    .requireValueInRange(productRange, true)
    .setAllowInvalid(false)
    .setHelpText("Select a product from the list.")
    .build();

  sheet.getRange(2, 3, sheet.getMaxRows() - 1, 1).setDataValidation(rule); // Column C
  Logger.log("Product dropdown from range applied.");
}

Enforcing a Number Range on the Amount Column

function addAmountValidation() {
  var sheet = SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheetByName("Sales Tracker");

  var amountRange = sheet.getRange(2, 4, sheet.getMaxRows() - 1, 1); // Column D

  var rule = SpreadsheetApp.newDataValidation()
    .requireNumberBetween(1, 100000)
    .setAllowInvalid(false)
    .setHelpText("Amount must be between $1 and $100,000.")
    .build();

  amountRange.setDataValidation(rule);
  Logger.log("Amount validation applied.");
}

Adding a Region Dropdown from Sheet Data

This reads unique regions already in the Sales Tracker and builds the dropdown list dynamically:

function addRegionDropdownFromData() {
  var sheet = SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheetByName("Sales Tracker");

  var data = sheet.getRange(2, 5, sheet.getLastRow() - 1, 1).getValues();
  var regions = [...new Set(data.flat().filter(String))];

  var rule = SpreadsheetApp.newDataValidation()
    .requireValueInList(regions, true)
    .setAllowInvalid(false)
    .setHelpText("Select a valid region.")
    .build();

  sheet.getRange(2, 5, sheet.getMaxRows() - 1, 1).setDataValidation(rule); // Column E
  Logger.log("Region dropdown applied: " + regions.join(", "));
}

With the sample data, the dropdown shows: North, East, South.

Adding a Date Constraint

Prevent future dates in the Deal Date column:

function addDateValidation() {
  var sheet = SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheetByName("Sales Tracker");

  var dateRange = sheet.getRange(2, 8, sheet.getMaxRows() - 1, 1); // Column H

  var rule = SpreadsheetApp.newDataValidation()
    .requireDateOnOrBefore(new Date())
    .setAllowInvalid(false)
    .setHelpText("Deal date cannot be in the future.")
    .build();

  dateRange.setDataValidation(rule);
  Logger.log("Date validation applied.");
}

Removing Data Validation

function clearAllValidation() {
  var sheet = SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheetByName("Sales Tracker");

  sheet.getRange(2, 1, sheet.getMaxRows() - 1, 8).clearDataValidations();
  Logger.log("All validation rules cleared.");
}