Data Validation Nâng Cao Trong Google Sheets: Custom Formula, Checkbox Và Dropdown Động

1. Data Validation Cơ Bản Vs Nâng Cao #

Data Validation cơ bản: dropdown list, number range. Nâng cao: custom formulas, regex validation, dependent dropdowns, dynamic lists. Google Sheets hỗ trợ custom formula validation mạnh hơn Excel — kết hợp REGEXMATCH, COUNTIF, và Apps Script.

2. Custom Formula Validation #

Data → Data validation → Custom formula is. Nhập formula trả về TRUE/FALSE. Cell chỉ accept giá trị khi formula = TRUE. Ví dụ: =REGEXMATCH(A1, "^0\d{9}$") validate số điện thoại VN 10 chữ số.

# Validate email format
=REGEXMATCH(A1, "^[\w.-]+@[\w.-]+\.[a-zA-Z]{2,}$")

# Validate date format DD/MM/YYYY
=REGEXMATCH(TO_TEXT(A1), "^\d{2}/\d{2}/\d{4}$")

# No duplicates in column
=COUNTIF(A:A, A1) <= 1

# Value must be in reference list
=NOT(ISNA(MATCH(A1, Sheet2!A:A, 0)))

3. Dependent Dropdown (Dropdown Phụ Thuộc) #

Dropdown B thay đổi dựa theo giá trị dropdown A. Ví dụ: chọn Tỉnh → Dropdown Huyện tự cập nhật. Dùng INDIRECT + Named Ranges hoặc FILTER function.

# Cell B1 validation formula (dropdown phụ thuộc A1):
=FILTER(Data!B:B, Data!A:A = A1)

# Hoặc dùng INDIRECT với Named Range:
=INDIRECT(A1)  # A1 chứa tên Named Range

4. Dynamic Dropdown Từ UNIQUE + SORT #

Tạo dropdown tự động cập nhật khi thêm dữ liệu mới: validation source = UNIQUE(SORT(A2:A)). Khi thêm giá trị mới vào cột A, dropdown tự có thêm option. Kết hợp FILTER để tạo cascading dropdowns.

5. Color Coding + Conditional Formatting #

Kết hợp Data Validation với Conditional Formatting: tô đỏ cell invalid, xanh cell valid. Custom formula CF: =NOT(REGEXMATCH(A1, "^0\d{9}$")) → tô đỏ phone không hợp lệ. Giúp user nhận ra lỗi ngay lập tức.

6. Apps Script: OnEdit Validation #

Với logic phức tạp (validate API, cross-sheet check), dùng Apps Script onEdit trigger. Script chạy tự động khi user edit cell, kiểm tra và reject giá trị không hợp lệ.

function onEdit(e) {
  const sheet = e.source.getActiveSheet();
  if (sheet.getName() !== "Data") return;
  const col = e.range.getColumn();
  if (col === 3) {  // Column C = Email
    const email = e.value;
    if (!/^[\w.-]+@[\w.-]+\.[a-zA-Z]{2,}$/.test(email)) {
      e.range.setValue("").setNote("Email không hợp lệ!");
      SpreadsheetApp.getUi().alert("Email không đúng format!");
    }
  }
}

7. Best Practices #

1) Luôn add input message hướng dẫn user format đúng. 2) Show warning thay reject để không chặn workflow. 3) Dùng Named Ranges cho dropdown source — dễ maintain. 4) Test với edge cases: empty, special chars, very long text. 5) Document validation rules trong sheet riêng.

8. Kết Luận #

Data Validation nâng cao biến Google Sheets thành form nhập liệu chuyên nghiệp. Custom formula + REGEXMATCH validate gần như mọi format. Dependent dropdowns + FILTER tạo UX smooth. Apps Script xử lý logic phức tạp. Kết quả: data sạch từ đầu, giảm 90% lỗi nhập liệu.