Xác thực dữ liệu trong Google Sheet

Nếu chưa biết cách tạo menu, danh sách thả xuống trong Google Sheets như thế nào? Bạn đọc tham khảo tiếp bài viết dưới đây để tìm hiểu chi tiết các bước thực hiện.

Hướng dẫn tạo danh sách thả xuống trong Google Sheets

1. Tạo menu, danh sách thả xuống trong Google Sheets

Các bước để tạo menu, danh sách thả xuống trong Google Sheets không quá phức tạp. Thực hiện theo các bước dưới đây:

Bước 1: Đầu tiên mở file Google Sheets và chọn các ô mà bạn muốn tạo, thêm menu thả xuống.
Bước 2: Trên thanh công cụ trang tính, click chọn Data (dữ liệu) => lệnh Data Validation.

Bước 3: Trên cửa sổ Data Validation, tại menu Criteria (tiêu chí), bạn chọn tùy chọn List From a Range (danh sách từ một dải ô) hoặc List of Items (danh sách mục).

Trong đó:

- Tùy chọn List From a Range (danh sách từ một dải ô): là danh sách các giá trị chọn từ các ô trong cùng trang tính hoặc trang tính khác.
- Tùy chọn List of Items (danh sách mục): danh sách các mục dữ liệu được xác định trước, có thể văn bản hoặc số, và bạn phải nhập từng giá trị bằng tay. Sử dụng dấu phẩy (không có dấu cách) để phân tác các giá trị. Ngoài ra tùy chọn này không cho phép bạn chèn dữ liệu trực tiếp từ các ô trên trang tính khác.

Trong hướng dẫn này Taimienphi.vn sử dụng tùy chọn List of Items và nhập các giá trị.

Bước 4: Sau khi nhập xong các dữ liệu, giá trị mà bạn muốn hiển thị trong menu, danh sách thả xuống. Bước tiếp theo đánh tích chọn hộp nằm kế bên tùy chọn Show Drop-Down List In Cell (hiển thị danh sách thả xuống trong ô) để kích hoạt tùy chọn.

Ngoài ra tại đây bạn có thể đánh tích chọn tùy chọn Show Warning (hiển thị cảnh báo) để hiển thị cảnh báo khi người dùng nhập các dữ liệu không hợp lệ. Hoặc tùy chọn Reject Input (từ chối nhập dữ liệu) để ngăn không cho nhập, chỉnh sửa dữ liệu trang tính của bạn.

Bước 5: Cuối cùng, đánh tích chọn tùy chọn Show validation help text (hiển thị văn bản trợ giúp xác thực) để gợi ý cho người dùng những gì họ có thể chọn trong các ô và nhập hướng dẫn bất kỳ.

Bước 6: Click chọn Save (lưu) để hoàn tất quá trình.

2. Cách sử dụng menu, danh sách thả xuống trong Google Sheets

Sau khi tạo menu, danh sách thả xuống trong Google Sheets, bạn có thể click chọn biểu tượng mũi tên hướng xuống và chọn giá trị bất kỳ từ danh sách trong các ô bảng tính.

Nếu đã kích hoạt tùy chọn Show validation help text (Hiển thị văn bản trợ giúp xác thực), văn bản sẽ hiển thị bất kỳ lúc nào người dùng chọn một trong các ô đã xác thực.

Trong trường hợp nếu có ai đó nhập các giá trị không hợp lệ, và bạn đã kích hoạt tùy chọn Show Warning (hiển thị cảnh báo), dữ liệu không hợp lệ sẽ được đánh dấu trong ô. Di chuột qua đó để xem đầy đủ nguyên nhân gây lỗi.

Hoặc nếu đã kích hoạt tùy chọn Reject Input (từ chối nhập dữ liệu), trên màn hình người dùng nhập dữ liệu không hợp lệ sẽ hiển thị cảnh báo như dưới đây:

Nếu cần xóa hoặc sửa đổi mục bất kỳ từ menu thả xuống, chỉ cần truy cập Data (dữ liệu) =>Data Validation (xác thực dữ liệu) để chỉnh sửa mục bất kỳ từ danh sách, menu thả xuống mà bạn đã tạo.

Cuối cùng, để xóa menu, danh sách thả xuống, chỉ cần click chọn nút Remove Validation (xóa xác thực) nằm góc dưới cùng cửa sổ Data Validation.

Đến đây bạn đã biết cách tạo menu, danh sách thả xuống trong Google Sheets? Đừng quên chia sẻ ý kiến, đánh giá của bạn cho Taimienphi.vn nhé.

Link tải Google Sheets cho Android và iOS:

=> Link tải Google Sheets cho Android

Xác thực dữ liệu trong Google Sheet

=> Link tải Google Sheets cho iPhone

Xác thực dữ liệu trong Google Sheet

Ngoài ra bạn đọc có thể tham khảo thêm một số bài viết đã có trên Taimienphi.vn để tìm hiểu thêm cách sử dụng bộ lọc trong Sheets như thế nào nhé.

Xem thêm: Cách sử dụng bộ lọc trong Google Sheets.

Tạo menu, danh sách thả xuống trong Google Sheets là giải pháp lý tưởng giúp bạn vừa tiết kiệm thời gian và công sức cũng như hạn chế lỗi có thể xảy ra, thay vì đi nhập các giá trị.

Cách tính thời gian trong Google Sheets Cách xóa hàng và cột trống trong Google Sheets Cách vẽ biểu đồ cột trong Google Sheets Cách chèn bảng tính Google Sheets vào Google Docs So sánh các cột trong Google Sheets Phím tắt tạo trang Google Sheets mới

Trong bài này, chúng ta sẽ thực hiện mục tiêu như sau: Tạo xác thực dữ liệu cho một ô phụ thuộc vào giá trị của một ô khác

File tham khảo

https://docs.google.com/spreadsheets/d/1es8agzxldicKw6rlWd1UN3CBO7XcUMCHd93xfK6G1fM/edit?usp=sharing

Bạn hãy tạo bản sao về Drive để xem và chỉnh sửa nhé

Mô tả ví dụ

  • Cho file có 2 sheet: “sheet” và “Database”
  • Dải ô A3:A13 được xác thực dữ liệu cho các giá trị: “iPhone”, “Samsung”, và “Nokia” (hàng 1 sheet Database)
  • Khi chọn giá trị bất kì ở A3:A13 thì sẽ xuất hiện xác thực dữ liệu tương ứng ở sheet Database. Ví dụ, chọn iPhone, thì sẽ hiện ra X, XS, và XS max
Xác thực dữ liệu trong Google Sheet

Code hoàn chỉnh

function onEdit() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheetByName('sheet'); var dbSheet = ss.getSheetByName('Database'); var activeCell = ss.getActiveCell(); if (activeCell.getColumn() == 1 && activeCell.getRow() > 2) { activeCell.offset(0, 1).clearContent().clearDataValidations(); var col = dbSheet.getRange(1,1,1,dbSheet.getLastColumn()).getValues(); var tam = col[0].indexOf(activeCell.getValue())+1; var luachon = dbSheet.getRange(3, tam, dbSheet.getLastRow()); var rule = SpreadsheetApp.newDataValidation().requireValueInRange(luachon).build(); activeCell.offset(0, 1).setDataValidation(rule); } }

function onEdit() {

   var ss = SpreadsheetApp.getActiveSpreadsheet();

   var sheet = ss.getSheetByName('sheet');

   var dbSheet = ss.getSheetByName('Database');

   var activeCell = ss.getActiveCell();

   if (activeCell.getColumn() == 1 && activeCell.getRow() > 2) {

     activeCell.offset(0, 1).clearContent().clearDataValidations();

     var col = dbSheet.getRange(1,1,1,dbSheet.getLastColumn()).getValues();

     var tam = col[0].indexOf(activeCell.getValue())+1;    

     var luachon = dbSheet.getRange(3, tam, dbSheet.getLastRow());

     var rule = SpreadsheetApp.newDataValidation().requireValueInRange(luachon).build();

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

   }

}

Các bước thực hiện

  1. Lấy số thứ tự cột của activeCell
  2. Dùng getRange để lấy dải ô tương ứng với cột cần lấy
  3. Viết Xác thực dữ liệu ra ô ở cột B “Model”

Ví dụ, khi ta chọn Nokia. Bước 1 sẽ lấy số thứ tự cột của Nokia trong Database (là 3). Tiếp theo (bước 2), lấy tiếp dải ô bên dưới Nokia, là các model N70, N71, N72. Cuối cùng (bước 3), viết những model này vào Xác thực dữ liệu cho cột B.

Bước 1 – Lấy số thứ tự cột

  • Sử dụng trigger onEdit để thực thi các câu lệnh mỗi khi thay đổi giá trị trong file Google Sheet
  • sheet đại diện cho sheet “sheet”
  • dbSheet đại diện cho sheet “Database”
  • activeCell là ô chúng ta đang bấm vào

if (activeCell.getColumn() == 1 && activeCell.getRow() > 2) {

if (activeCell.getColumn() == 1 && activeCell.getRow() > 2) {

Nếu ô đang bấm vào nằm ở cột A và nằm từ dòng 3 trở đi thì thực hiện lệnh (vì dải ô xác thực là A3:A13)

activeCell.offset(0, 1).clearContent().clearDataValidations();

activeCell.offset(0, 1).clearContent().clearDataValidations();

Dòng lệnh này để xóa xác thực ở ô cùng hàng với activeCell trên cột B (tức cột “Model”). Mục đích là khi ta thay đổi giá trị iPhone, Samsung, hay Nokia thì ô bên cột B sẽ tự động “làm sạch”. Đây là câu lệnh không bắt buộc, chỉ thêm vào để đẹp thôi

var col = dbSheet.getRange(1,1,1,dbSheet.getLastColumn()).getValues();

var col = dbSheet.getRange(1,1,1,dbSheet.getLastColumn()).getValues();

Biến col dùng để để lấy các giá trị cho Smartphone, ở đây là dòng 1 trong sheet Database

var tam = col[0].indexOf(activeCell.getValue())+1;

var tam = col[0].indexOf(activeCell.getValue())+1;

“tam” sử dụng hàm indexOf để lấy số thứ tự của activeCell trong biến col. Ví dụ activeCell = iPhone, thì biến tam sẽ trả về số 1, vì iPhone nằm ở cột 1 (A) trong Database

Trong biến tam, có 2 thành phần cần lưu ý, thứ nhất là col[0] và thứ hai là +1 ở cuối

Nếu các bạn thử ghi ra file log biến col thì sẽ được kết quả như sau:

col = [[iPhone, Samsung, Nokia]]

Vì ở đây chúng ta dùng indexOf để lấy số thứ tự của biến cần tìm, nên ta phải đi trực tiếp vào phần tử mẹ của biến col để tìm. Để hiểu rõ, mời các bạn đọc thêm Bài viết về mảng

col[0] = [iPhone, Samsung, Nokia]

Chúng ta còn phải +1 vào cuối biến tam bởi vì khi dùng indexOf, số đếm bắt đầu từ 0. iPhone là 0, Samsung là 1, và Nokia là 2

Bước 2 – Lấy dải ô xác thực

var luachon = dbSheet.getRange(3, tam, dbSheet.getLastRow());

var luachon = dbSheet.getRange(3, tam, dbSheet.getLastRow());

Sau khi đã có số cột cần tìm, thì ta chỉ cần lấy dải ô có số cột tương ứng và gán vào biến luachon

Bước 3 – Ghi xác thực dữ liệu

var rule = SpreadsheetApp.newDataValidation().requireValueInRange(luachon).build(); activeCell.offset(0, 1).setDataValidation(rule);

var rule = SpreadsheetApp.newDataValidation().requireValueInRange(luachon).build();

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

Biến rule dùng để tạo Quy tắc xác thực dữ liệu. Ở đây, quy tắc chúng ta cần là dải ô ở biến luachon

Phần SpreadsheetApp.newDataValidation(). ở đầu và .build(); ở cuối là bắt buộc phải có. Còn các bạn có thể thay thế requireValueInRange() bằng các hàm tùy ý muốn. Xem các lựa chọn khác tại đây:

https://developers.google.com/apps-script/reference/spreadsheet/data-validation-builder

Câu hỏi thường gặp / Sửa code cho đúng thực tế

Áp dụng cho các cột khác?

Ở dòng:

if (activeCell.getColumn() == 1 && activeCell.getRow() > 2) {

if (activeCell.getColumn() == 1 && activeCell.getRow() > 2) {

activeCell.getColumn() == 1 thì 1 là số thứ tự của cột A. Nếu bạn muốn đặt điều kiện cho cột B thì hãy đổi số này thành 2, tương tự với C là 3, D là 4,…

Áp dụng xác thực phụ thuộc cho nhiều cột khác nhau

if (activeCell.getColumn() == 1 && activeCell.getRow() > 2) || (activeCell.getColumn() == 5 && activeCell.getRow() > 2) {

if (activeCell.getColumn() == 1 && activeCell.getRow() > 2) || (activeCell.getColumn() == 5 && activeCell.getRow() > 2) {

Ở câu lệnh điều kiện if, mình đã thêm điều kiện xác thực cho cột E. Bây giờ cột A-B và E-F đều sẽ cho có xác thực phụ thuộc giống nhau. Nếu muốn tiếp tục thêm các cặp cột này, thì bạn hãy tiếp tục viết thêm điều kiện hoặc: || (activeCell.getColumn()…)

Bạn cũng có thể viết thế này cho gọn:

if (activeCell.getRow() > 2) && (activeCell.getColumn() == 1 || activeCell.getColumn() == 5) {

if (activeCell.getRow() > 2) && (activeCell.getColumn() == 1 || activeCell.getColumn() == 5) {

Ngoài ra, còn có thể dùng switch…case… trong trường hợp có quá nhiều điều kiện. Tuy nhiên trong phạm vi bài này, mình sẽ không nói về nó.

Cột phụ thuộc không phải là cột B (giống ví dụ)?

Ở 2 câu lệnh sau:

activeCell.offset(0, 1).clearContent().clearDataValidations();

activeCell.offset(0, 1).clearContent().clearDataValidations();

và…

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

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

Hàm offset(x, y) sẽ quyết định cột phụ thuộc là cột nào. Giá trị y là cột tính từ cột hiện tại. Ở ví dụ trên, cột hiện tại là A, nên khi offset(0, 1) thì 1 sẽ mang ý nghĩa 1 cột tính từ cột A, tức là cột B. Nếu muốn không phải cột B mà là cột D, thì vì D cách A 3 cột nên bạn hãy sửa 1 thành 3. Lưu ý, hãy sửa cùng lúc ở 2 câu lệnh trên.

Thêm nhiều xác thực phụ thuộc khác nhau

Với ví dụ trên thì bạn chỉ làm được xác thực phụ thuộc cho cột A-B với dữ liệu gốc nằm ở sheet Database. Trong trường hợp bạn muốn thêm xác thực phụ thuộc cho các cặp cột khác nhau, và hơn cả là dữ liệu gốc nằm ở sheet khác (không phải Database) thì hãy tham khảo đoạn code sau:

function onEdit() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheetByName('sheet'); var dbSheet = ss.getSheetByName('Database'); var dbSheet2 = ss.getSheetByName('Database 2'); var activeCell = ss.getActiveCell(); if (activeCell.getColumn() == 1 && activeCell.getRow() > 2) { activeCell.offset(0, 1).clearContent().clearDataValidations(); var col = dbSheet.getRange(1,1,1,dbSheet.getLastColumn()).getValues(); var tam = col[0].indexOf(activeCell.getValue())+1; var luachon = dbSheet.getRange(3, tam, dbSheet.getLastRow()); var rule = SpreadsheetApp.newDataValidation().requireValueInRange(luachon).build(); activeCell.offset(0, 1).setDataValidation(rule); } else if (activeCell.getColumn() == 3 && activeCell.getRow() > 2) { activeCell.offset(0, 1).clearContent().clearDataValidations(); var col = dbSheet2.getRange(1,1,1,dbSheet2.getLastColumn()).getValues(); var tam = col[0].indexOf(activeCell.getValue())+1; var luachon = dbSheet2.getRange(3, tam, dbSheet2.getLastRow()); var rule = SpreadsheetApp.newDataValidation().requireValueInRange(luachon).build(); activeCell.offset(0, 1).setDataValidation(rule); } }

0

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

function onEdit() {

   var ss = SpreadsheetApp.getActiveSpreadsheet();

   var sheet = ss.getSheetByName('sheet');

   var dbSheet = ss.getSheetByName('Database');

   var dbSheet2 = ss.getSheetByName('Database 2');

   var activeCell = ss.getActiveCell();

   if (activeCell.getColumn() == 1 && activeCell.getRow() > 2) {

     activeCell.offset(0, 1).clearContent().clearDataValidations();

     var col = dbSheet.getRange(1,1,1,dbSheet.getLastColumn()).getValues();

     var tam = col[0].indexOf(activeCell.getValue())+1;    

     var luachon = dbSheet.getRange(3, tam, dbSheet.getLastRow());

     var rule = SpreadsheetApp.newDataValidation().requireValueInRange(luachon).build();

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

   } else if (activeCell.getColumn() == 3 && activeCell.getRow() > 2) {

     activeCell.offset(0, 1).clearContent().clearDataValidations();

     var col = dbSheet2.getRange(1,1,1,dbSheet2.getLastColumn()).getValues();

     var tam = col[0].indexOf(activeCell.getValue())+1;    

     var luachon = dbSheet2.getRange(3, tam, dbSheet2.getLastRow());

     var rule = SpreadsheetApp.newDataValidation().requireValueInRange(luachon).build();

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

   }

}

Mình đã thêm vào phần khai báo biến dbSheet2 nhằm gọi ra sheet có tên Database 2. Còn ở đoạn else if, mình đã thêm điều kiện cho cột C. Bây giờ, file sẽ có 2 chỗ có xác thực phụ thuộc: (1) cột A-B với dữ liệu gốc ở sheet Database, (2) cột C-D với dữ liệu gốc ở sheet Database 2.