22 đoạn code Apps Script "mì ăn liền" cho người mới bắt đầu
Tuyển tập các đoạn code ngắn, copy-dán-dùng ngay, không cần hiểu sâu về lập trình: định dạng dữ liệu, dọn dẹp Sheet, quản lý file/Sheet hàng loạt, xử lý ngày tháng, hộp thoại tương tác và các hàm tùy chỉnh hay dùng nhất trong Google Sheets.
Nhảy nhanh đến code
Mở Google Sheets → Extensions → Apps Script, dán đoạn code cần dùng
vào file Code.gs, bấm chạy (▶) và cấp quyền ở lần đầu tiên. Các hàm tùy
chỉnh (Nhóm E) dùng trực tiếp như công thức bình thường ngay trong ô tính.
Định dạng & làm sạch dữ liệu
Các thao tác lặp đi lặp lại khi nhập liệu: căn chỉnh, đổi chữ hoa/thường, dọn dòng trống, tìm trùng lặp.
Tự động căn lề, đổi font, bo viền cho vùng dữ liệu mới nhập
Gắn vào onEdit để mỗi khi nhập/sửa dữ liệu, ô đó tự được định dạng theo chuẩn chung.
function onEdit(e) {
var range = e.range;
range.setFontFamily("Arial")
.setHorizontalAlignment("center")
.setBorder(true, true, true, true, false, false);
}
onEdit khác, cần gộp chung logic vào một hàm duy nhất — Apps Script chỉ chạy một hàm onEdit đơn giản (simple trigger) cho mỗi lần sửa.Đổi chữ Hoa thành chữ thường (hoặc ngược lại) cho cột được chọn
Bôi đen vùng dữ liệu cần đổi rồi chạy hàm — không cần nhập tay từng ô.
function doiChuHoaThuong() {
var range = SpreadsheetApp.getActiveSheet().getActiveRange();
var values = range.getValues();
for (var i = 0; i < values.length; i++) {
for (var j = 0; j < values[i].length; j++) {
if (typeof values[i][j] === "string") {
values[i][j] = values[i][j].toUpperCase(); // doi .toLowerCase() neu can chu thuong
}
}
}
range.setValues(values);
}
Tìm và xóa toàn bộ dòng trống chỉ với 1 click
Duyệt ngược từ dòng cuối lên để tránh lỗi lệch chỉ số khi xóa dòng.
function xoaDongTrong() {
var sheet = SpreadsheetApp.getActiveSheet();
var data = sheet.getDataRange().getValues();
for (var i = data.length - 1; i >= 0; i--) {
var dongRong = data[i].every(function (o) { return o === "" || o === null; });
if (dongRong) sheet.deleteRow(i + 1);
}
}
Highlight các ô có giá trị trùng lặp
Đếm số lần xuất hiện của mỗi giá trị trong cột đầu tiên của vùng chọn, tô đỏ nếu trùng.
function highlightTrungLap() {
var range = SpreadsheetApp.getActiveSheet().getActiveRange();
var values = range.getValues();
var demXuatHien = {};
values.forEach(function (row) {
var val = row[0];
if (val !== "") demXuatHien[val] = (demXuatHien[val] || 0) + 1;
});
for (var i = 0; i < values.length; i++) {
var val = values[i][0];
range.getCell(i + 1, 1).setBackground(demXuatHien[val] > 1 ? "#ffcccc" : null);
}
}
Tự động ẩn/hiện cột dựa trên giá trị ô đầu tiên
Ví dụ: ẩn cột "Doanh thu" (cột D) nếu ô A1 ghi "Bảo mật".
function anHienCotTheoTrangThai() {
var sheet = SpreadsheetApp.getActiveSheet();
var trangThai = sheet.getRange("A1").getValue();
var cotDoanhThu = 4; // cot D
if (trangThai === "Bao mat") {
sheet.hideColumns(cotDoanhThu);
} else {
sheet.showColumns(cotDoanhThu);
}
}
Gắn thêm trigger onEdit gọi hàm này để tự chạy ngay khi ô A1 thay đổi.
Quản lý Sheet & File Drive
Thao tác hàng loạt với nhiều Sheet trong cùng file, và lấy thông tin file từ Google Drive.
Tự động tạo 12 Sheet mới tương ứng 12 tháng
Kiểm tra Sheet đã tồn tại chưa trước khi tạo, tránh bị lỗi trùng tên.
function taoSheetTheoThang() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var thangArr = ["Thang 01","Thang 02","Thang 03","Thang 04","Thang 05","Thang 06",
"Thang 07","Thang 08","Thang 09","Thang 10","Thang 11","Thang 12"];
thangArr.forEach(function (ten) {
if (!ss.getSheetByName(ten)) ss.insertSheet(ten);
});
}
Ẩn toàn bộ Sheet phụ, chỉ chừa lại Sheet "Trang chủ"
Hữu ích khi muốn chia sẻ file nhưng giấu bớt các Sheet dữ liệu thô.
function anTatCaSheetPhu() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
ss.getSheets().forEach(function (sheet) {
if (sheet.getName() !== "Trang chu") sheet.hideSheet();
});
}
Đổi tên hàng loạt Sheet theo danh sách có sẵn
Tạo một Sheet phụ tên "DanhSachTen" gồm 2 cột: tên cũ (A) và tên mới (B).
function doiTenHangLoatSheet() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var data = ss.getSheetByName("DanhSachTen").getDataRange().getValues();
for (var i = 1; i < data.length; i++) {
var sheet = ss.getSheetByName(data[i][0]);
if (sheet) sheet.setName(data[i][1]);
}
}
Lấy nhanh URL và ID của tất cả file trong một thư mục Drive
Kết quả được ghi ngay vào Sheet đang mở, gồm tên file, ID và link truy cập.
function layDanhSachFileTrongThuMuc() {
var folder = DriveApp.getFolderById("ID_THU_MUC_DRIVE");
var files = folder.getFiles();
var sheet = SpreadsheetApp.getActiveSheet();
var hang = 1;
sheet.getRange(hang++, 1, 1, 3).setValues([["Ten file", "ID", "URL"]]);
while (files.hasNext()) {
var file = files.next();
sheet.getRange(hang++, 1, 1, 3).setValues([[file.getName(), file.getId(), file.getUrl()]]);
}
}
Tự động cuộn đến dòng cuối cùng có dữ liệu khi vừa mở file
Dùng simple trigger onOpen — chạy tự động, không cần gắn trigger thủ công.
function onOpen() {
var sheet = SpreadsheetApp.getActiveSheet();
var dongCuoi = sheet.getLastRow();
var cotCuoi = Math.max(sheet.getLastColumn(), 1);
if (dongCuoi > 0) {
sheet.setActiveSelection(sheet.getRange(dongCuoi, cotCuoi));
}
}
Ngày tháng & thời gian
Chuẩn hóa định dạng ngày, tính khoảng cách ngày làm việc, và cảnh báo quá hạn.
Định dạng lại cột ngày tháng chuẩn DD/MM/YYYY
Bôi đen cột ngày tháng nhập sai định dạng rồi chạy — script tự parse và chuẩn hóa.
function chuanHoaCotNgayThang() {
var range = SpreadsheetApp.getActiveSheet().getActiveRange();
var values = range.getValues();
for (var i = 0; i < values.length; i++) {
var ngay = new Date(values[i][0]);
if (!isNaN(ngay.getTime())) values[i][0] = ngay;
}
range.setValues(values);
range.setNumberFormat("dd/MM/yyyy");
}
Tính số ngày làm việc giữa hai mốc thời gian (trừ Thứ 7, Chủ Nhật)
Có thể dùng trực tiếp như hàm trong ô tính.
function SO_NGAY_LAM_VIEC(ngayBatDau, ngayKetThuc) {
var start = new Date(ngayBatDau);
var end = new Date(ngayKetThuc);
var soNgay = 0;
while (start <= end) {
var thu = start.getDay(); // 0 = Chu Nhat, 6 = Thu Bay
if (thu !== 0 && thu !== 6) soNgay++;
start.setDate(start.getDate() + 1);
}
return soNgay;
}
Cách dùng trong ô tính: =SO_NGAY_LAM_VIEC(A2,B2)
Tự động điền Thứ trong tuần dựa vào cột Ngày tháng
Hàm tùy chỉnh, tự nhận diện thứ từ giá trị ngày trong ô.
function DIEN_THU(ngay) {
var thuArr = ["Chu Nhat","Thu Hai","Thu Ba","Thu Tu","Thu Nam","Thu Sau","Thu Bay"];
return thuArr[new Date(ngay).getDay()];
}
Cách dùng trong ô tính: =DIEN_THU(A2)
Kiểm tra ngày đã quá hạn (Past Due) so với hôm nay để đổi màu đỏ
Áp dụng cho vùng chọn chứa cột hạn chót (deadline).
function kiemTraQuaHan() {
var range = SpreadsheetApp.getActiveSheet().getActiveRange();
var values = range.getValues();
var homNay = new Date();
homNay.setHours(0, 0, 0, 0);
for (var i = 0; i < values.length; i++) {
var ngay = new Date(values[i][0]);
range.getCell(i + 1, 1).setBackground(ngay < homNay ? "#ff4d4d" : null);
}
}
Hộp thoại tương tác (UI)
Các hộp thoại đơn giản để thông báo, xác nhận, hoặc lấy dữ liệu nhập tay từ người dùng.
Hộp thoại thông báo (Alert) đơn giản khi chạy xong lệnh
function hienThongBao() {
SpreadsheetApp.getUi().alert("Da hoan tat xu ly du lieu!");
}
Hộp thoại xác nhận "Yes/No"
Ví dụ: xác nhận trước khi thực hiện thao tác xóa dữ liệu.
function xacNhanXoaDuLieu() {
var ui = SpreadsheetApp.getUi();
var phanHoi = ui.alert("Xac nhan", "Ban co chac muon xoa du lieu khong?", ui.ButtonSet.YES_NO);
if (phanHoi === ui.Button.YES) {
// thuc hien xoa du lieu tai day
ui.alert("Da xoa du lieu.");
} else {
ui.alert("Da huy thao tac.");
}
}
Hộp thoại bắt buộc nhập text (Prompt)
Ví dụ: yêu cầu nhập mật khẩu để xem một Sheet đang ẩn.
function nhapMatKhauXemSheetAn() {
var ui = SpreadsheetApp.getUi();
var phanHoi = ui.prompt("Nhap mat khau de xem Sheet an:", ui.ButtonSet.OK_CANCEL);
if (phanHoi.getSelectedButton() === ui.Button.OK) {
if (phanHoi.getResponseText() === "123456") {
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("DuLieuMat").showSheet();
} else {
ui.alert("Sai mat khau!");
}
}
}
Thông báo nhỏ (Toast) tự biến mất sau 3 giây
Không làm gián đoạn thao tác của người dùng như Alert.
function hienToast() {
SpreadsheetApp.getActiveSpreadsheet().toast("Da luu du lieu thanh cong!", "Thong bao", 3);
}
Hàm tùy chỉnh (Custom Function)
Dán code vào Apps Script, sau đó gõ trực tiếp vào ô tính như một hàm bình thường của Google Sheets.
=TINHTHUE(so_tien) — Tự động tính thuế VAT 8% hoặc 10%
Mặc định 8%, có thể truyền tham số thứ hai để đổi mức thuế suất.
function TINHTHUE(soTien, phanTramThue) {
var thue = phanTramThue || 8; // mac dinh 8%
return soTien * (1 + thue / 100);
}
Cách dùng: =TINHTHUE(A2) (mặc định 8%) hoặc =TINHTHUE(A2,10) (10%)
=DEMMAU(vung_du_lieu, mau_nen) — Đếm số ô có màu nền chỉ định
Hàm mặc định của Sheets không đọc được màu nền, nên phải viết riêng bằng Apps Script.
function DEMMAU(diaChiVung, diaChiOMauMau) {
var sheet = SpreadsheetApp.getActiveSheet();
var mauCanDem = sheet.getRange(diaChiOMauMau).getBackground();
var backgrounds = sheet.getRange(diaChiVung).getBackgrounds();
var dem = 0;
backgrounds.forEach(function (hang) {
hang.forEach(function (mau) {
if (mau === mauCanDem) dem++;
});
});
return dem;
}
Cách dùng: =DEMMAU("A1:A20","C1") — địa chỉ vùng và ô mẫu màu phải để trong dấu ngoặc kép.
=DOCSO(so_tien) — Chuyển số thành chữ tiếng Việt
Ví dụ dùng để ghi số tiền bằng chữ trên hóa đơn, phiếu thu.
function DOCSO(soTien) {
if (soTien === 0) return "Khong dong";
var so = Math.abs(Math.round(soTien));
var chuSo = ["khong","mot","hai","ba","bon","nam","sau","bay","tam","chin"];
var donVi = ["","nghin","trieu","ty"];
function docBaSo(so3) {
var tram = Math.floor(so3 / 100);
var chuc = Math.floor((so3 % 100) / 10);
var donVi1 = so3 % 10;
var kq = "";
if (tram > 0) {
kq += chuSo[tram] + " tram ";
if (chuc === 0 && donVi1 > 0) kq += "linh ";
}
if (chuc > 1) {
kq += chuSo[chuc] + " muoi ";
if (donVi1 === 1) kq += "mot ";
else if (donVi1 === 5) kq += "lam ";
else if (donVi1 > 0) kq += chuSo[donVi1] + " ";
} else if (chuc === 1) {
kq += "muoi ";
if (donVi1 === 5) kq += "lam ";
else if (donVi1 > 0) kq += chuSo[donVi1] + " ";
} else if (donVi1 > 0) {
kq += chuSo[donVi1] + " ";
}
return kq.trim();
}
var nhom = [];
while (so > 0) {
nhom.push(so % 1000);
so = Math.floor(so / 1000);
}
var ketQua = "";
for (var i = nhom.length - 1; i >= 0; i--) {
if (nhom[i] === 0) continue;
ketQua += docBaSo(nhom[i]) + " " + donVi[i] + " ";
}
ketQua = ketQua.trim().replace(/\s+/g, " ");
return ketQua.charAt(0).toUpperCase() + ketQua.slice(1) + " dong";
}
Cách dùng: =DOCSO(A2)
=KHOANGCACH(diem_A, diem_B) — Tính khoảng cách (km) giữa 2 địa chỉ
Dùng dịch vụ Maps tích hợp sẵn trong Apps Script (Directions), không cần đăng ký API key riêng.
function KHOANGCACH(diemA, diemB) {
var duong = Maps.newDirectionFinder()
.setOrigin(diemA)
.setDestination(diemB)
.setMode(Maps.DirectionFinder.Mode.DRIVING)
.getDirections();
if (duong.routes.length === 0) return "Khong tim thay duong di";
var met = duong.routes[0].legs[0].distance.value;
return Math.round((met / 1000) * 10) / 10; // km, lam tron 1 chu so thap phan
}
Cách dùng: =KHOANGCACH("TP.HCM","Vung Tau") hoặc truyền địa chỉ cụ thể để kết quả chính xác hơn.
Câu hỏi thường gặp
Hàm tùy chỉnh (Custom Function) khác gì với các hàm còn lại trong bài?
Hàm tùy chỉnh (Nhóm E) dùng trực tiếp trong ô tính như =SUM(), =VLOOKUP(). Các hàm còn lại phải chạy thủ công từ Apps Script hoặc gắn trigger, không gõ được vào ô tính.
Vì sao hàm tùy chỉnh không tự cập nhật khi dữ liệu thay đổi?
Google Sheets chỉ tự tính lại công thức khi các ô nó tham chiếu thay đổi giá trị. Với những hàm đọc thông tin không phải giá trị (như màu nền ở Code 20), cần chỉnh sửa lại ô công thức hoặc chạy lại thủ công để cập nhật.
Có thể gộp nhiều đoạn code trong bài vào cùng một file Code.gs không?
Được. Có thể đặt toàn bộ 22 hàm trong cùng một file, chỉ cần lưu ý các hàm trùng tên bảo lưu như onEdit hoặc onOpen chỉ nên có một phiên bản duy nhất, gộp chung logic nếu cần dùng nhiều tác vụ.
