Nội dung
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

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:
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:
0 1 | if (activeCell.getColumn() == 1 && activeCell.getRow() > 2) { |
Á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ện
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àm
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
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
Cám ơn chủ web nhiều,
Mình đã theo dõi hết các bài về google app script của bạn thì phải nói là rất dễ chịu, dễ hiểu và gần gũi ngay với người chưa không chuyên về lập trình như mình. Rất hy vọng là bạn sẽ viết thêm các bài khác nữa để mình có thể tiếp tục theo dõi.
Hiện tại, trong bài này thì mình hỏi một chút là câu lệnh col[0] = [iPhone, Samsung, Nokia] thì số 0 ở đây có phải là đang nói về phần tử mẹ đầu tiên đúng không bạn?
Vậy nếu là một mảng 02 chiều nhiều dòng nhiều cột ví dụ col[1] = [[‘a’,’b’,’c’],[‘d’,’e’,’f’],[‘g’,’h’,’i’]], thì là mình đang hiểu xét cho phần tử mẹ thứ hai [‘d’,’e’,’f’] đúng ko bạn?
và nếu mình ghi là col[1][1] = [[‘a’,’b’,’c’],[‘d’,’e’,’f’],[‘g’,’h’,’i’]] thì là đang xét đến phần tử con ‘e’ đúng không ạ?
Cám ơn bạn rất nhiều
Cảm ơn bạn vì đã quan tâm đến các bài chia sẻ của HocGgSheet.com
Về câu hỏi về mảng ở trên thì bạn đã hiểu đúng rồi ạ
Chúc bạn thành công. Thân 🙂
Anh ơi cho Em hỏi E có 1 list databasa mà k dọc mà là hàng ngang VD : Em viết Iphone Gộp cột A1 A2 A3 sau đó cột B1 B2 B3 lần lượt t X XS Max – Samsung Cột A4 A5 A6 cột B4 B5 B6 Note 8 note 9 note 10….. thì em viết lệnh xổ xuống bên seet theo công thức nào. Em cảm ơn.
Chào bạn,
Về xác thực dữ liệu phụ thuộc theo hàng thì bạn tham khảo bài viết sau nhé, mình mới viết sáng nay =))
https://hocggsheet.com/help-tao-xac-thuc-du-lieu-phu-thuoc-theo-hang-ngang/
Thân
Cảm ơn tác giả, bạn hướng dẫn rất chi tiết, mình không biết gì về lập trình vẫn làm 1 lần chạy được luôn
đúng thứ mình cần
Bạn cho mình hỏi thêm chút mình cần chạy 2 nội dung validatinon này cùng trên một sheet ở 2 cột khác nhau ( tất nhiên là nội dung khác nhau) thì phải làm như thế nào nhỉ
Thật sự thì nếu bạn không biết lập trình thì có thể hơi khó để tinh chỉnh nhiều thứ như mong muốn. Nhưng mình vẫn chia sẻ, bạn cứ làm theo và suy nghĩ thêm 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);
}
// thêm xác thực cho 1 cột nữa
// bạn thay số cột vào ngay dòng code bên dưới nhé. Mình để là 3 - nghĩa là cột C
if (activeCell.getColumn() == 3 && activeCell.getRow() > 2) {
activeCell.offset(0, 1).clearContent().clearDataValidations();
// tiếp tục thay đổi biến col để lấy vùng lựa chọn ở dải ô khác nhé (dòng code ngay bên dưới)
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);
}
}
Thế thôi. Bạn cứ thử xem sao. Thân.
Cảm ơn tác giả rất nhiều vì bài viết này. Bạn hướng dẫn rất chi tiết và dễ hiểu. Tuy nhiên, mình có một số vấn đề cần áp dụng mà mình tự sửa code vẫn ko chạy dc, bạn có thể hỗ trợ ko.
– Dữ liệu nhập trong database có sử dụng tiếng việt có dấu và khoảng trắng ko.
– Mình muốn áp dụng Drop list phụ thuộc cho nhiều SHEET trên cùng một TRANG TÍNH (áp dụng cho sheet1; sheet2; sheet3;…sử dụng chung 1 database)
– Cột phụ thuộc của mình nằm trước cột drop list chính (vd: drop list chính cột 12 và drop list phụ thuộc cột 11)
Cảm ơn bạn nhiều.
Anh ơi cho em hỏi, nếu em không muốn trả kết quả vào cột B mà muốn trả vào cột D, E hoặc F thì sửa ở đâu được anh nhỉ. Anh hỗ trợ giúp em với ạ.
Em cảm ơn nhiều!
Bạn sửa dòng này nhé:
activeCell.offset(0, 1).setDataValidation(rule);
Chỗ “offset(0, 1)” thì “1” chính là số cột tính từ cột mà bạn đặt Xác thực dữ liệu
Ví dụ bạn đang ở cột D, muốn XTDLPThuoc cho cột F thì thay 1 bằng 3. Số dương là cột bên phải cột hiện tại, số âm là cột bên trái cột hiện tại, 0 là cột hiện tại
cảm ơn rất nhiều, rất bổ ích, chúc bạn sức khỏe và có nhiều video nữa để anh chị em nghiệp dư như mình có cái để học hỏi thêm.
Cảm ơn tác giả nhiều về bài viết, case của mình có 1 chút khac biệt, vì database nó setup hơi khác với guide, vì mình đang dùng để quản lí kho, nên format nó sẽ như sau:
Tên thuốc | Số lô
Thuốc A | Lô 1
Thuốc A | Lô 2
Thuốc A | Lô 3
thì cần tinh chỉnh code như nào nhỉ?
mình ko rành code lắm nên đọc hết guide rồi mà vẫn chưa biết chỉnh code sao cho phù hợp, nhờ tác giả nếu có thời gian giúp mình với.
Mình cảm ơn nhiều.
Nếu không rành code thì bạn có thể tạo 1 sheet mới, dùng để format lại database theo kiểu mình đã hướng dẫn rồi chạy code cho database mới là được nhé
Cảm ơn tác giả rất nhiều về bài viết, anh hướng dẫn rất dễ hiểu, em làm một lần chạy được luôn.
Tuy nhiên trong tình huống thực tế em đang sử dụng thì em đang để phần drop list phụ thuộc này ở cột F và G, ở cột K em có sử dụng drop list không phụ thuộc khác lấy dữ liệu theo Range, thì nó không hoạt động được, thì em cần sửa code như thế nào để phần drop list phụ thuộc này không ảnh hưởng đến các cột khác trong cùng sheet ạ?
Mình đã update thêm phần “Câu hỏi thường gặp” cho bài viết này. Bạn có thể tham khảo thêm để tìm ra phương hướng nhé. Thân <3
You must log in to post a comment.