How to Use Google Apps Script to Build Custom Data Validation Rules

The Data Integrity Nightmare

If you build a Google Sheet to track company expenses and share it with 50 employees, chaos is inevitable. You might have a column for “Department Code” that must be exactly three letters long (e.g., “MKT”, “ENG”, “FIN”).

If an employee accidentally types “Marketing”, your downstream financial reporting dashboards will instantly break.

While Google Sheets has a built-in Data Validation tool (Data > Data Validation), it is heavily restricted. It can restrict users to a dropdown list or check if a number is between 1 and 10, but it cannot enforce complex, programmatic logic.

To build indestructible spreadsheets with custom, regex-based validation rules, developers use Google Apps Script and the DataValidationBuilder class.

Step 1: Building a Standard Rule Programmatically

Before diving into complex logic, you must understand the basic architecture of assigning a rule to a cell using Apps Script.

Open script.google.com and paste the following code to force a specific cell (A1) to only accept a number between 1 and 100.

function applyBasicValidation() {
  
  // 1. Connect to the active sheet and select Cell A1
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var cell = sheet.getRange("A1");
  
  // 2. Build the Validation Rule
  var rule = SpreadsheetApp.newDataValidation()
    .requireNumberBetween(1, 100)
    .setAllowInvalid(false) // CRITICAL: This physically blocks bad input
    .setHelpText("You must enter a number between 1 and 100.")
    .build();
    
  // 3. Apply the rule to the cell
  cell.setDataValidation(rule);
}

The .setAllowInvalid(false) method is the most important part of this script. If you omit it, Google Sheets will just display a tiny, ignorable red triangle when the user makes a mistake. Setting it to false forces a hard pop-up error, physically preventing the user from leaving the cell until they fix the data.

Step 2: The Custom Formula Rule (Regex Integration)

Let’s solve the Department Code problem. We must force the user to enter exactly three capital letters. No numbers, no lowercase letters, no spaces.

Because the built-in methods (like requireNumberBetween) cannot do this, we must use the incredibly powerful requireFormulaSatisfied() method.

We will apply this strict validation to the entire “Department” column (Column B, starting at Row 2).

function applyRegexValidation() {
  
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  
  // Select the entire Column B (excluding the header row)
  var columnRange = sheet.getRange("B2:B1000");
  
  // Build a custom formula rule using Google Sheets REGEXMATCH
  // The formula must evaluate to TRUE for the input to be accepted
  var rule = SpreadsheetApp.newDataValidation()
    .requireFormulaSatisfied('=REGEXMATCH(B2, "^[A-Z]{3}$")')
    .setAllowInvalid(false)
    .setHelpText("ERROR: Department Code must be exactly 3 UPPERCASE letters (e.g., ENG).")
    .build();
    
  // Apply the rule to all 999 cells instantly
  columnRange.setDataValidation(rule);
}

The Regex Formula:
The formula =REGEXMATCH(B2, "^[A-Z]{3}$") is a native Google Sheets function.

• ^[A-Z] forces it to be a capital letter.
• {3} forces it to be exactly three characters long.
• $ ensures there are no trailing spaces or hidden characters at the end.

Because Apps Script intelligently handles relative cell references, even though we wrote B2 in the formula, when the script applies the rule to cell B50, it will automatically update the formula to B50.

Conclusion

By leveraging the DataValidationBuilder class, Google Apps Script allows developers to bulletproof their spreadsheets. By injecting complex regular expressions and custom formula logic directly into the cells, organizations can completely eliminate human data-entry errors before they corrupt downstream financial models.