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
//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
Code hoàn chỉnh
0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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
- Lấy số thứ tự cột của activeCell
- Dùng getRange để lấy dải ô tương ứng với cột cần lấy
- 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
0
1
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]
0
1
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
0
1
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
0
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
0
1
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
0
1
2
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:
//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:
0
1
if [activeCell.getColumn[] == 1 && activeCell.getRow[] > 2] {
activeCell.getColumn[] == 1thì1là 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ành2, 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
0
1
if [activeCell.getColumn[] == 1 && activeCell.getRow[] > 2] || [activeCell.getColumn[] == 5 && activeCell.getRow[] > 2] {
Ở câu lệnh điều kiệnif, 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:
0
1
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:
0
1
activeCell.offset[0, 1].clearContent[].clearDataValidations[];
và…
0
1
activeCell.offset[0, 1].setDataValidation[rule];
Hàmoffset[x, y]sẽ quyết định cột phụ thuộc là cột nào. Giá trịylà 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 khioffset[0, 1]thì1sẽ 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ửa1thành3. 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 ở sheetDatabase. 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ảiDatabase] thì hãy tham khảo đoạn code sau:
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ếndbSheet2nhằm gọi ra sheet có tênDatabase 2. Còn ở đoạnelse 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 ở sheetDatabase, [2] cột C-D với dữ liệu gốc ở sheetDatabase 2.