ASAppScriptExpert
Quote

Author: By Raj

Part of our Google Sheets Automation Expert guides. Need this built for your team? Hire a Google Apps Script developer.

Estimated reading time: 10 minutes

Dynamic Data Validation in Google Sheets with Apps Script

Data validation rules in Google Sheets can be static lists or driven by Apps Script for dependent dropdowns, API-backed options, and role-based choices.

Use Range.setDataValidation with SpreadsheetApp.newDataValidation() builders. Rebuild rules when source lists change on another tab or external JSON fetch completes.

Dependent lists (Region → Country) require clearing child cells when parents change, implement in an installable onEdit handler. See /blog/google-sheets-approval-workflow for status-driven validations.

Building validation rules in code

Create a rule with requireValueInList(['Open','Closed'], true) or requireValueInRange(listRange, true) for dynamic lists on a Lists tab.

Apply with range.setDataValidation(rule).setAllowInvalid(false) to block saves that fail validation.

Dependent dropdown pattern

Map parent values to child arrays in a hidden Lookup sheet or object loaded from getValues(). On edit of column B, rewrite column C validation.

Use SpreadsheetApp.flush sparingly; batch validation updates when importing many rows.

API-driven option lists

Nightly UrlFetchApp pulls SKU catalog into Lists!A:A, then refresh validations on OrderEntry!D:D. Cache ETag headers when supported.

Handle empty API responses by falling back to last-known list stored in Script Properties JSON.

User experience and errors

Show a note on the header cell explaining allowed values. Pair validation with conditional formatting for rows stuck in Invalid state.

Log validation rebuild duration if sheets are large, see /blog/google-apps-script-execution-time-limit.

Example code

function setStatusValidation() {
  const sh = SpreadsheetApp.getActive().getSheetByName('Requests');
  const rule = SpreadsheetApp.newDataValidation()
    .requireValueInList(['Draft', 'Submitted', 'Approved', 'Rejected'], true)
    .setAllowInvalid(false)
    .build();
  sh.getRange('E2:E500').setDataValidation(rule);
}
ApproachBest forTradeoff
Apps Script nativeGoogle Workspace-centric workflows6-min limit, quotas
Zapier / MakeNo-code, many connectorsPer-task cost, vendor lock-in
Python + CloudHeavy data / MLHosting cost, separate auth
Google Sheets automation expertProduction custom logicBuild cost, you own code

FAQ

Can validation reference another sheet?

Yes. Use requireValueInRange(SpreadsheetApp.getActive().getRange('Lists!A2:A')) for dynamic length lists.

Why does onEdit not update child dropdowns?

Simple triggers cannot call some services; use an installable onEdit trigger created via ScriptApp.newTrigger.

How do I clear validation?

Call range.clearDataValidations() before applying a new rule when migrating schemas.

Can checkboxes use data validation?

Use requireCheckbox() builder instead of lists for boolean columns.

Do validations count toward cell limits?

They consume metadata like formulas. Thousands of rows with unique per-row rules can slow edits, prefer column-level rules.

Need this done for you? I handle this as part of my consulting work, fixed-price quote within 24 hours.

Book a call with Raj →

Get the full Dynamic Data Validation in Google Sheets with Apps Script script template

I'll email you a production-ready, commented version you can deploy in 10 minutes.

Continue reading

Google Sheets Automation

Google Sheets CRM Automation: Triggers, Pipelines, and Follow-Ups

Google Sheets Automation

Google Sheets Dashboard Automation with Apps Script

Google Sheets Automation

Auto-Generate Reports from Google Sheets with Apps Script

From another topic

How to Automate Google Sheets with Apps Script (Beginner Guide) →

Need help with this? I handle this as part of my Google Sheets Automation Expert service.

CRM, dashboards, invoices, inventory, and report automation in Sheets.

See how it works →