Tai sao dung Google Sheets quan ly nhan su?

Quan ly nhan su la mot trong nhung cong viec phuc tap nhat cua doanh nghiep - tu theo doi ho so nhan vien, cham cong, nghi phep, den danh gia KPI va quan ly hop dong lao dong. Nhieu doanh nghiep nho va vua (SMEs) tai Viet Nam dang tim kiem giai phap quan ly nhan su hieu qua nhung khong muon dau tu hang chuc trieu dong cho phan mem HR chuyen dung nhu SAP SuccessFactors, BambooHR hay Base HRM.

Google Sheets la giai phap hoan hao cho cac doanh nghiep tu 5-100 nhan vien. Voi uu diem mien phi, de su dung, cong tac realtime va kha nang tuy chinh cao, Google Sheets co the thay the hoan toan cac phan mem HR co ban voi chi phi bang 0. Theo khao sat nam 2025, hon 45% doanh nghiep nho tai Viet Nam van su dung Excel hoac Google Sheets de quan ly nhan su - nhung phan lon chua co template chuan, dan den sai sot va mat thoi gian.

Bai viet nay se cung cap cho ban mot template quan ly nhan su hoan chinh voi 6 sheets, day du cong thuc tu dong, data validation, conditional formatting va ca Apps Script tu dong hoa - tat ca deu mien phi va san sang su dung ngay.

Template nay bao gom:

6 sheets lien ket voi nhau: Ho so NV, Cham cong, Nghi phep, Dao tao, KPI, Dashboard
15+ cong thuc tu dong: VLOOKUP, COUNTIFS, DATEDIF, SUMIFS, IF lồng nhau
Data Validation: Dropdown phong ban, chuc vu, loai phep, trang thai
Conditional Formatting: Canh bao hop dong het han, NV moi, di muon qua nhieu
Apps Script: Tu dong gui email nhac hop dong sap het, tinh phep nam, bao cao thang
Mau data 10 nhan vien de ban tham khao cau truc va test cong thuc

So sanh Google Sheets voi phan mem HR chuyen dung

Tieu chi	Google Sheets	Phan mem HR (Base, SAP...)
Chi phi	Mien phi	500K - 5M/thang
Quy mo phu hop	5-100 nhan vien	50-10,000+ nhan vien
Thoi gian setup	30 phut (copy template)	1-4 tuan
Tuy chinh	Tu do hoan toan	Gioi han theo plan
Cong tac	Realtime (nhieu nguoi cung edit)	Co nhung can license
Cham cong tu dong	Thu cong/ban tu dong	Tu dong (may cham cong)
Bao cao	Can tu tao cong thuc	Co san, da dang
Bao mat	Google security + chia se gioi han	Enterprise-grade

Ket luan: Google Sheets phu hop khi...

Doanh nghiep co duoi 100 nhan vien
Ngan sach cho HR software con han che hoac bang 0
Can giai phap linh hoat, tuy chinh theo dac thu cong ty
Team HR quen dung Google Sheets/Excel (khong can dao tao lai)
Chua can tich hop may cham cong, payroll tu dong

Phan 2: Cau truc 6 Sheets trong Template

Template nay gom 6 sheets duoc thiet ke de lien ket voi nhau qua cac cong thuc VLOOKUP va COUNTIFS. Du lieu nhap o sheet nay se tu dong cap nhat o cac sheet khac va Dashboard tong hop.

Sheet 1: Ho So Nhan Vien (Master Data)

Day la sheet chinh chua toan bo thong tin nhan vien. Tat ca cac sheet khac deu tham chieu ve sheet nay qua Ma NV. Gom 15 cot:

STT	Ten cot	Kieu du lieu	Ghi chu
1	Ma NV	Text	VD: NV001, NV002... (unique, khong trung)
2	Ho ten	Text	Ho va ten day du
3	Ngay sinh	Date	Format: dd/mm/yyyy
4	CCCD	Text	So can cuoc cong dan (12 so)
5	Dia chi	Text	Dia chi thuong tru
6	SDT	Text	So dien thoai (format text de giu so 0 dau)
7	Email	Email	Email ca nhan hoac cong ty
8	Phong ban	Dropdown	Kinh doanh, Ke toan, IT, Marketing, Nhan su, San xuat
9	Chuc vu	Dropdown	Giam doc, Truong phong, Pho phong, Nhan vien, Thuc tap
10	Ngay vao lam	Date	Format: dd/mm/yyyy
11	Loai HD	Dropdown	Thu viec, 1 nam, 2 nam, Khong thoi han
12	Ngay het HD	Date	Tinh tu dong theo loai HD. De trong neu Khong thoi han.
13	Luong	Number	Luong gross (VND). Format: #,##0
14	Tai khoan ngan hang	Text	So TK + ten ngan hang. VD: 1234567890 - Vietcombank
15	Tinh trang	Dropdown	Dang lam viec, Nghi thai san, Nghi khong luong, Da nghi viec

Sheet 2: Cham Cong Tong Hop

Sheet cham cong theo thang, tong hop so ngay cong, nghi phep, OT cua tung nhan vien. Gom 8 cot:

Cot	Kieu	Mo ta
Thang	Text	VD: 01/2026, 02/2026
Ma NV	Text	Lien ket voi sheet Ho So NV qua VLOOKUP
Ho ten	Formula	=VLOOKUP(B2,'Ho So NV'!A:B,2,FALSE) - Tu dong dien
Ngay cong	Number	So ngay di lam thuc te trong thang (0-31)
Nghi phep	Number	So ngay nghi phep co luong
Nghi KL	Number	So ngay nghi khong luong
OT (gio)	Number	Tong so gio lam them (overtime)
Di muon (lan)	Number	So lan di muon trong thang

Sheet 3: Nghi Phep

Quan ly chi tiet tung don xin nghi phep cua nhan vien, tu nghi phep nam, nghi om, den nghi viec rieng. Gom 9 cot:

Cot	Mo ta
Ma NV	Lien ket Ho So NV
Ho ten	=VLOOKUP tu Ho So NV
Loai phep	Dropdown: Phep nam, Nghi om, Viec rieng, Thai san, Khong luong
Ngay bat dau	Date format dd/mm/yyyy
Ngay ket thuc	Date format dd/mm/yyyy
So ngay	=E2-D2+1 (tu dong tinh)
Ly do	Text tu do
Nguoi duyet	Ten truong phong/quan ly
Trang thai	Dropdown: Cho duyet, Da duyet, Tu choi, Da huy

Sheet 4: Dao Tao

Theo doi cac khoa dao tao cua nhan vien - mot phan quan trong cua quan ly nhan su hien dai. Gom 6 cot:

Cot	Mo ta
Ma NV	Lien ket Ho So NV
Ho ten	=VLOOKUP tu Ho So NV
Khoa dao tao	Ten khoa: Ky nang mem, An toan LĐ, Nghiep vu chuyen mon, Ngoai ngu...
Ngay dao tao	Date format dd/mm/yyyy
Ket qua	Dropdown: Dat, Khong dat, Dang hoc
Chung chi	Ten chung chi dat duoc (neu co)

Sheet 5: KPI & Danh Gia

Danh gia hieu suat nhan vien theo tung ky (thang/quy/nam). Gom 10 cot:

Cot	Mo ta
Ma NV	Lien ket Ho So NV
Ho ten	=VLOOKUP tu Ho So NV
Ky danh gia	Dropdown: Thang 1, Thang 2..., Quy 1, Quy 2..., Ca nam
KPI 1: Hoan thanh CV	Diem 1-10 - Muc do hoan thanh cong viec duoc giao
KPI 2: Chat luong	Diem 1-10 - Chat luong cong viec
KPI 3: Teamwork	Diem 1-10 - Lam viec nhom, ho tro dong nghiep
KPI 4: Sang tao	Diem 1-10 - De xuat y tuong, cai tien quy trinh
KPI 5: Ky luat	Diem 1-10 - Di lam dung gio, tuan thu noi quy
Diem TB	=AVERAGE(D2:H2) - Trung binh 5 KPI
Xep loai	=IF(I2>=9,"Xuat sac",IF(I2>=7,"Tot",IF(I2>=5,"Dat",IF(I2>=3,"Can cai thien","Khong dat"))))

Sheet 6: Dashboard (Tong hop tu dong)

Dashboard tu dong tong hop du lieu tu 5 sheet con lai, hien thi cac chi so quan trong nhat giup HR va ban lanh dao ra quyet dinh nhanh chong.

Dashboard bao gom:

Tong quan nhan su

Tong so nhan vien dang lam viec
NV moi trong thang
NV nghi viec trong thang
Ty le bien dong nhan su (%)

Phan bo theo phong ban

So NV moi phong ban
Ty le phan tram
Bieu do tron tu dong

Canh bao hop dong

HD het han trong 30 ngay
HD het han trong 60 ngay
HD het han trong 90 ngay
Danh sach ten NV can gia han

Thong ke cham cong

Tong ngay cong binh quan
Tong gio OT trong thang
So NV di muon > 3 lan/thang
Tong nghi phep da dung

Phan 3: Cong Thuc Tu Dong - Chi tiet tung cong thuc

Day la phan quan trong nhat - cac cong thuc giup template hoat dong tu dong. Ban chi can nhap du lieu o 1 noi, cac sheet khac se tu cap nhat.

3.1 VLOOKUP - Lien ket sheets

VLOOKUP duoc dung de lay thong tin tu sheet Ho So NV sang cac sheet khac dua tren Ma NV:

// Lay Ho ten tu Ma NV (dung o tat ca cac sheet con)
=VLOOKUP(A2, 'Ho So NV'!A:B, 2, FALSE)

// Lay Phong ban
=VLOOKUP(A2, 'Ho So NV'!A:H, 8, FALSE)

// Lay Chuc vu
=VLOOKUP(A2, 'Ho So NV'!A:I, 9, FALSE)

// Lay Luong (de tinh luong thuc nhan)
=VLOOKUP(A2, 'Ho So NV'!A:M, 13, FALSE)

// Lay Email (de gui email thong bao)
=VLOOKUP(A2, 'Ho So NV'!A:G, 7, FALSE)

// Neu khong tim thay Ma NV, hien "Khong tim thay"
=IFERROR(VLOOKUP(A2, 'Ho So NV'!A:B, 2, FALSE), "Khong tim thay")

3.2 COUNTIFS - Thong ke Dashboard

// Dem so NV dang lam viec
=COUNTIFS('Ho So NV'!O:O, "Dang lam viec")

// Dem NV theo phong ban
=COUNTIFS('Ho So NV'!H:H, "Kinh doanh", 'Ho So NV'!O:O, "Dang lam viec")
=COUNTIFS('Ho So NV'!H:H, "Ke toan", 'Ho So NV'!O:O, "Dang lam viec")
=COUNTIFS('Ho So NV'!H:H, "IT", 'Ho So NV'!O:O, "Dang lam viec")
=COUNTIFS('Ho So NV'!H:H, "Marketing", 'Ho So NV'!O:O, "Dang lam viec")
=COUNTIFS('Ho So NV'!H:H, "Nhan su", 'Ho So NV'!O:O, "Dang lam viec")
=COUNTIFS('Ho So NV'!H:H, "San xuat", 'Ho So NV'!O:O, "Dang lam viec")

// Dem NV moi trong thang (ngay vao lam trong thang hien tai)
=COUNTIFS('Ho So NV'!J:J, ">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1), 'Ho So NV'!J:J, "<="&EOMONTH(TODAY(),0))

// Dem don nghi phep cho duyet
=COUNTIFS('Nghi Phep'!I:I, "Cho duyet")

// Dem NV di muon > 3 lan trong thang
=COUNTIFS('Cham Cong'!A:A, TEXT(TODAY(),"MM/YYYY"), 'Cham Cong'!H:H, ">"&3)

3.3 DATEDIF - Tinh tham nien va tuoi

// Tinh tham nien (so nam lam viec)
=DATEDIF(J2, TODAY(), "Y") & " nam " & DATEDIF(J2, TODAY(), "YM") & " thang"
// Ket qua: "3 nam 5 thang"

// Tinh tuoi tu ngay sinh
=DATEDIF(C2, TODAY(), "Y")
// Ket qua: 28

// Tinh so ngay con lai den khi het hop dong
=IF(L2="", "KTH", IF(L2-TODAY()<0, "DA HET HAN", L2-TODAY() & " ngay"))
// KTH = Khong thoi han
// DA HET HAN neu ngay het HD da qua
// "45 ngay" neu con 45 ngay

// Tinh so ngay phep con lai trong nam
// (12 ngay/nam, tru di so ngay da nghi loai "Phep nam" va "Da duyet")
=12 - SUMPRODUCT(
  ('Nghi Phep'!A:A=A2) *
  ('Nghi Phep'!C:C="Phep nam") *
  ('Nghi Phep'!I:I="Da duyet") *
  ('Nghi Phep'!F:F)
)

3.4 Cong thuc tinh luong thuc nhan

// Luong co ban theo ngay cong (26 ngay cong tieu chuan)
=M2/26 * D3
// M2: Luong gross (tu Ho So NV)
// D3: So ngay cong thuc te (tu Cham Cong)

// Tien OT (150% luong gio binh thuong, 200% cuoi tuan, 300% le)
// Don gian hoa: OT binh thuong 150%
=M2/26/8 * G3 * 1.5
// G3: So gio OT

// Tru nghi khong luong
=M2/26 * F3
// F3: So ngay nghi khong luong

// Luong thuc nhan (don gian hoa, chua tru BHXH/thue)
=M2/26 * D3 + M2/26/8 * G3 * 1.5 - M2/26 * F3

// Phuong an nang cao hon: co BHXH, thue TNCN
// Luong dong BHXH = Luong gross (gioi han tran 36,000,000)
// NLĐ dong: 10.5% (BHXH 8% + BHYT 1.5% + BHTN 1%)
// Giam tru ban than: 11,000,000
// Giam tru phu thuoc: 4,400,000/nguoi
// Thue TNCN: theo bieu thue luy tien

3.5 Cong thuc canh bao hop dong sap het

// Danh sach NV co HD het han trong 30 ngay toi
=FILTER(
  'Ho So NV'!A:B,
  ('Ho So NV'!L:L >= TODAY()) *
  ('Ho So NV'!L:L <= TODAY()+30) *
  ('Ho So NV'!O:O = "Dang lam viec")
)

// Dem so HD het han trong 30/60/90 ngay
=COUNTIFS('Ho So NV'!L:L, ">="&TODAY(), 'Ho So NV'!L:L, "<="&TODAY()+30, 'Ho So NV'!O:O, "Dang lam viec")
=COUNTIFS('Ho So NV'!L:L, ">="&TODAY(), 'Ho So NV'!L:L, "<="&TODAY()+60, 'Ho So NV'!O:O, "Dang lam viec")
=COUNTIFS('Ho So NV'!L:L, ">="&TODAY(), 'Ho So NV'!L:L, "<="&TODAY()+90, 'Ho So NV'!O:O, "Dang lam viec")

// Ty le bien dong nhan su thang nay (%)
// = (NV moi + NV nghi) / Tong NV * 100
=((COUNTIFS('Ho So NV'!J:J,">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1),'Ho So NV'!J:J,"<="&EOMONTH(TODAY(),0)) + COUNTIFS('Ho So NV'!O:O,"Da nghi viec",'Ho So NV'!J:J,">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1))) / COUNTIFS('Ho So NV'!O:O,"Dang lam viec") * 100

Phan 4: Data Validation - Dropdown & Rang buoc du lieu

Data Validation giup dam bao du lieu nhap vao dung format, giam sai sot va giup thong ke chinh xac. Sau day la cac validation can thiet cho tung sheet:

4.1 Cach tao Data Validation

Chon vung o can validation (VD: cot H - Phong ban)
Vao menu Data → Data validation
Chon Criteria: "List of items" hoac "List from a range"
Nhap danh sach (cach nhau boi dau phay) hoac chon range
Tick "Show dropdown list in cell"
Chon "Reject input" de khong cho nhap ngoai danh sach
Click Save

4.2 Danh sach Dropdown cho tung cot

Sheet	Cot	Danh sach Dropdown
Ho So NV	Phong ban	Kinh doanh, Ke toan, IT, Marketing, Nhan su, San xuat
Ho So NV	Chuc vu	Giam doc, Truong phong, Pho phong, Nhan vien, Thuc tap
Ho So NV	Loai HD	Thu viec, 1 nam, 2 nam, Khong thoi han
Ho So NV	Tinh trang	Dang lam viec, Nghi thai san, Nghi khong luong, Da nghi viec
Nghi Phep	Loai phep	Phep nam, Nghi om, Viec rieng, Thai san, Khong luong
Nghi Phep	Trang thai	Cho duyet, Da duyet, Tu choi, Da huy
Dao Tao	Ket qua	Dat, Khong dat, Dang hoc
KPI	Ky danh gia	Thang 1, Thang 2, ..., Thang 12, Quy 1, Quy 2, Quy 3, Quy 4, Ca nam

Meo: Dung sheet an lam nguon dropdown

Thay vi nhap truc tiep "List of items", hay tao 1 sheet ten "DanhMuc" chua tat ca cac danh sach (phong ban, chuc vu, loai HD...). Khi can thay doi, chi can sua o sheet DanhMuc - tat ca dropdown se tu dong cap nhat. Dung "List from a range" tro ve sheet DanhMuc.

Phan 5: Conditional Formatting - To mau tu dong

Conditional Formatting giup ban nhanh chong nhan ra cac muc can chu y bang cach tu dong to mau cells theo dieu kien. Sau day la 5 rule quan trong nhat cho template HR:

Rule 1: Hop dong sap het han (Vang) - trong 30 ngay

Chon cot L (Ngay het HD) tren sheet Ho So NV
Menu Format → Conditional formatting
Chon "Custom formula is"
Nhap: =AND(L2-TODAY()>0, L2-TODAY()<=30, L2<>"")
Mau nen: Vang nhat (#FFF9C4)
Click Done

Rule 2: Hop dong da qua han (Do) - het han roi chua gia han

Chon cot L (Ngay het HD) tren sheet Ho So NV
Custom formula: =AND(L2<TODAY(), L2<>"")
Mau nen: Do nhat (#FFCDD2)
Text: Do dam (#C62828)

Rule 3: Nhan vien moi (Xanh) - vao lam chua qua 3 thang

Chon cot J (Ngay vao lam)
Custom formula: =AND(TODAY()-J2<=90, J2<>"")
Mau nen: Xanh la nhat (#C8E6C9)

Rule 4: Di muon qua nhieu (Do) - tren 3 lan/thang

Chon cot H (Di muon) tren sheet Cham Cong
Custom formula: =H2>3
Mau nen: Cam nhat (#FFE0B2)
Text: Cam dam (#E65100)

Rule 5: KPI xep loai "Khong dat" (Do dam)

Chon cot J (Xep loai) tren sheet KPI
Custom formula: =J2="Khong dat"
Mau nen: Do (#EF5350), Text: Trang
Them 1 rule nua cho "Xuat sac": =J2="Xuat sac" - Mau xanh la (#4CAF50), Text trang

Phan 6: Apps Script - Tu dong hoa

Cac doan Apps Script duoi day giup tu dong hoa 3 tac vu quan trong nhat: gui email nhac hop dong sap het, tinh phep nam con lai, va tao bao cao thang tu dong.

6.1 Tu dong gui email nhac hop dong sap het han

// Script: Gui email nhac HD sap het han (30 ngay toi)
// Dat trigger chay moi sang 8h: Edit > Current project triggers > Add trigger
function notifyExpiringContracts() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Ho So NV');
  var data = sheet.getDataRange().getValues();

  var today = new Date();
  var thirtyDaysLater = new Date(today.getTime() + 30 * 24 * 60 * 60 * 1000);

  var expiringList = [];

  // Duyet tu dong 2 (bo header)
  for (var i = 1; i < data.length; i++) {
    var maNV = data[i][0];      // Cot A: Ma NV
    var hoTen = data[i][1];     // Cot B: Ho ten
    var ngayHetHD = data[i][11]; // Cot L: Ngay het HD
    var tinhTrang = data[i][14]; // Cot O: Tinh trang

    // Chi xet NV dang lam viec va co ngay het HD
    if (tinhTrang !== 'Dang lam viec' || !ngayHetHD) continue;

    var hetHanDate = new Date(ngayHetHD);
    if (hetHanDate >= today && hetHanDate <= thirtyDaysLater) {
      var soNgayConLai = Math.ceil((hetHanDate - today) / (24 * 60 * 60 * 1000));
      expiringList.push({
        maNV: maNV,
        hoTen: hoTen,
        ngayHet: Utilities.formatDate(hetHanDate, 'Asia/Ho_Chi_Minh', 'dd/MM/yyyy'),
        soNgay: soNgayConLai,
      });
    }
  }

  if (expiringList.length === 0) {
    Logger.log('Khong co HD nao sap het han trong 30 ngay toi');
    return;
  }

  // Tao noi dung email
  var emailBody = 'CANH BAO HOP DONG SAP HET HAN

';
  emailBody += 'Ngay kiem tra: ' + Utilities.formatDate(today, 'Asia/Ho_Chi_Minh', 'dd/MM/yyyy') + '
';
  emailBody += 'So HD sap het: ' + expiringList.length + '

';
  emailBody += '-------------------------------------------
';

  for (var j = 0; j < expiringList.length; j++) {
    var nv = expiringList[j];
    emailBody += (j + 1) + '. ' + nv.maNV + ' - ' + nv.hoTen + '
';
    emailBody += '   Ngay het HD: ' + nv.ngayHet + ' (con ' + nv.soNgay + ' ngay)

';
  }

  emailBody += '-------------------------------------------
';
  emailBody += 'Vui long lien he nhan vien de thoa thuan gia han hop dong.
';
  emailBody += 'Link file: ' + ss.getUrl();

  // Gui email cho HR va quan ly
  var hrEmail = 'hr@congty.com';  // Thay bang email thuc
  MailApp.sendEmail({
    to: hrEmail,
    subject: '[HR] Canh bao: ' + expiringList.length + ' hop dong sap het han',
    body: emailBody,
  });

  Logger.log('Da gui email canh bao ' + expiringList.length + ' HD sap het han');
}

6.2 Tu dong tinh phep nam con lai

// Script: Tinh va cap nhat so ngay phep con lai cua moi NV
// Chay vao ngay 1 hang thang
function updateLeaveBalance() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var hoSoSheet = ss.getSheetByName('Ho So NV');
  var nghiPhepSheet = ss.getSheetByName('Nghi Phep');
  var currentYear = new Date().getFullYear();

  var hoSoData = hoSoSheet.getDataRange().getValues();
  var nghiPhepData = nghiPhepSheet.getDataRange().getValues();

  // Tinh phep nam theo luat LĐ VN:
  // - 12 ngay/nam (lam viec binh thuong)
  // - Cong them 1 ngay cho moi 5 nam tham nien
  // - NV chua du 12 thang: tinh theo ty le thang

  for (var i = 1; i < hoSoData.length; i++) {
    var maNV = hoSoData[i][0];
    var ngayVaoLam = new Date(hoSoData[i][9]);  // Cot J
    var tinhTrang = hoSoData[i][14];            // Cot O

    if (tinhTrang !== 'Dang lam viec') continue;

    // Tinh tham nien (nam)
    var thamNien = (new Date() - ngayVaoLam) / (365.25 * 24 * 60 * 60 * 1000);
    var thamNienNam = Math.floor(thamNien);

    // Tinh so ngay phep nam duoc huong
    var phepNamDuocHuong = 12 + Math.floor(thamNienNam / 5);

    // Neu chua du 12 thang, tinh ty le
    if (thamNien < 1) {
      var soThangLamViec = Math.floor(thamNien * 12);
      phepNamDuocHuong = Math.round(12 * soThangLamViec / 12);
    }

    // Tinh so ngay da nghi (loai "Phep nam" + "Da duyet" trong nam nay)
    var daNghi = 0;
    for (var j = 1; j < nghiPhepData.length; j++) {
      if (nghiPhepData[j][0] === maNV &&
          nghiPhepData[j][2] === 'Phep nam' &&
          nghiPhepData[j][8] === 'Da duyet') {
        var ngayBatDau = new Date(nghiPhepData[j][3]);
        if (ngayBatDau.getFullYear() === currentYear) {
          daNghi += Number(nghiPhepData[j][5]) || 0;
        }
      }
    }

    var phepConLai = phepNamDuocHuong - daNghi;
    Logger.log(maNV + ': Duoc huong ' + phepNamDuocHuong + ' ngay, da nghi ' + daNghi + ', con lai ' + phepConLai);
  }
}

// Tao menu tuy chinh de chay thu cong
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('HR Tools')
    .addItem('Kiem tra HD sap het', 'notifyExpiringContracts')
    .addItem('Cap nhat phep nam', 'updateLeaveBalance')
    .addItem('Tao bao cao thang', 'generateMonthlyReport')
    .addToUi();
}

6.3 Tu dong tao bao cao thang

// Script: Tu dong tao bao cao tong hop thang
function generateMonthlyReport() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var hoSoSheet = ss.getSheetByName('Ho So NV');
  var chamCongSheet = ss.getSheetByName('Cham Cong');
  var nghiPhepSheet = ss.getSheetByName('Nghi Phep');

  var today = new Date();
  var thang = today.getMonth() + 1;
  var nam = today.getFullYear();
  var tenThang = 'Thang ' + thang + '/' + nam;

  var hoSoData = hoSoSheet.getDataRange().getValues();

  // Thong ke tong quan
  var tongNV = 0;
  var nvMoi = 0;
  var nvNghi = 0;
  var phongBanCount = {};

  for (var i = 1; i < hoSoData.length; i++) {
    var tinhTrang = hoSoData[i][14];
    var ngayVaoLam = new Date(hoSoData[i][9]);
    var phongBan = hoSoData[i][7];

    if (tinhTrang === 'Dang lam viec') {
      tongNV++;
      if (!phongBanCount[phongBan]) phongBanCount[phongBan] = 0;
      phongBanCount[phongBan]++;

      // NV moi trong thang
      if (ngayVaoLam.getMonth() + 1 === thang && ngayVaoLam.getFullYear() === nam) {
        nvMoi++;
      }
    }
    if (tinhTrang === 'Da nghi viec') {
      nvNghi++;
    }
  }

  // Tao bao cao
  var report = 'BAO CAO NHAN SU ' + tenThang.toUpperCase() + '
';
  report += '==========================================

';
  report += '1. TONG QUAN
';
  report += '   - Tong nhan vien dang lam: ' + tongNV + '
';
  report += '   - Nhan vien moi trong thang: ' + nvMoi + '
';
  report += '   - Nhan vien nghi viec: ' + nvNghi + '
';
  report += '   - Ty le bien dong: ' + ((nvMoi + nvNghi) / tongNV * 100).toFixed(1) + '%

';

  report += '2. PHAN BO THEO PHONG BAN
';
  var phongBanKeys = Object.keys(phongBanCount);
  for (var k = 0; k < phongBanKeys.length; k++) {
    var pb = phongBanKeys[k];
    report += '   - ' + pb + ': ' + phongBanCount[pb] + ' nguoi (' + (phongBanCount[pb] / tongNV * 100).toFixed(1) + '%)
';
  }

  report += '
==========================================
';
  report += 'Bao cao tu dong tao luc: ' + Utilities.formatDate(today, 'Asia/Ho_Chi_Minh', 'dd/MM/yyyy HH:mm');

  // Gui email bao cao
  MailApp.sendEmail({
    to: 'hr@congty.com',
    subject: '[HR] Bao cao nhan su ' + tenThang,
    body: report,
  });

  Logger.log('Da tao va gui bao cao thang ' + thang);
  Logger.log(report);
}

Cach dat Trigger tu dong chay script

Trong Apps Script Editor, click bieu tuong dong ho (Triggers) o thanh ben trai
Click "+ Add Trigger"
Chon function: notifyExpiringContracts
Event source: Time-driven
Type: Day timer → Chon 8am to 9am
Click Save
Lam tuong tu cho generateMonthlyReport: chay vao ngay 1 hang thang

Phan 7: Mau Data 10 Nhan Vien

De ban hieu ro cau truc template va test cong thuc, day la mau data 10 nhan vien mau:

Ma NV	Ho ten	Phong ban	Chuc vu	Ngay vao lam	Loai HD	Luong	Tinh trang
NV001	Nguyen Van An	Kinh doanh	Truong phong	15/03/2020	Khong thoi han	25,000,000	Dang lam viec
NV002	Tran Thi Binh	Ke toan	Nhan vien	01/06/2021	2 nam	15,000,000	Dang lam viec
NV003	Le Minh Cuong	IT	Nhan vien	10/01/2022	2 nam	20,000,000	Dang lam viec
NV004	Pham Thu Dung	Marketing	Pho phong	20/08/2019	Khong thoi han	22,000,000	Dang lam viec
NV005	Hoang Van Em	San xuat	Nhan vien	05/04/2023	1 nam	12,000,000	Dang lam viec
NV006	Vo Thi Phuong	Nhan su	Truong phong	12/02/2018	Khong thoi han	28,000,000	Dang lam viec
NV007	Dang Quoc Gia	Kinh doanh	Nhan vien	18/09/2024	1 nam	13,000,000	Dang lam viec
NV008	Bui Thi Huong	Ke toan	Nhan vien	01/12/2025	Thu viec	10,000,000	Dang lam viec
NV009	Ngo Duc Tai	IT	Pho phong	01/07/2020	Khong thoi han	30,000,000	Dang lam viec
NV010	Ly Thi Kim	Marketing	Thuc tap	15/01/2026	Thu viec	7,000,000	Dang lam viec

Mau data tren bao gom cac truong hop dien hinh:

NV001, NV004, NV006, NV009: HD Khong thoi han (nhan vien lau nam, level truong phong tro len)
NV002, NV003: HD 2 nam (sap het han - de test canh bao)
NV005, NV007: HD 1 nam (nhan vien moi hon)
NV008, NV010: Thu viec (moi vao, luong thap hon)
Da dang phong ban: 6 phong ban khac nhau
Da dang chuc vu: tu Thuc tap den Truong phong
Muc luong: tu 7M (thuc tap) den 30M (pho phong IT)

Phan 8: Meo su dung template hieu qua

1. Bao ve sheet cau truc

Sau khi setup xong template, bao ve (protect) cac o chua cong thuc va header de tranh nhan vien vo tinh xoa. Vao Data → Protected sheets and ranges → Chon vung can bao ve → Chi cho Admin edit.

2. Tao sheet "DanhMuc" an

Tao sheet rieng chua tat ca danh sach dropdown (phong ban, chuc vu, loai HD...). Click chuot phai vao tab → "Hide sheet". Sheet van hoat dong cho Data Validation nhung nguoi dung khong nhin thay, tranh sua nham.

3. Phan quyen truy cap

Khong share toan bo file cho moi nguoi. Truong phong chi can xem data phong minh - tao filtered view rieng. Nhan vien khong nen truy cap sheet Ho So NV (co thong tin luong). Chi HR va Ban Giam Doc co quyen edit toan bo.

4. Backup dinh ky

Du Google Sheets tu dong luu version history, van nen tao ban sao backup moi thang: File → Make a copy → Dat ten "HR_Backup_02_2026". Luu vao folder rieng tren Drive.

5. In an va bao cao

Khi can in bao cao, chon vung du lieu → File → Print → Chon "Selected cells". Dat landscape orientation cho cac bang nhieu cot. Dung Ctrl+P de preview truoc khi in.

Phan 9: FAQ - Cau hoi thuong gap

Template nay phu hop cho cong ty bao nhieu nguoi?

Template hoat dong tot nhat voi 5-100 nhan vien. Voi quy mo lon hon, Google Sheets co the cham khi co nhieu cong thuc tinh toan tren hang nghin dong. Neu cong ty co tren 100 NV, ban nen can nhac phan mem HR chuyen dung hoac nang cap len Google Sheets ket hop BigQuery de xu ly du lieu lon.

Lam sao tich hop voi may cham cong?

Hau het may cham cong deu ho tro export du lieu ra file CSV/Excel. Ban co the import file nay vao sheet Cham Cong hang thang. Hoac neu may cham cong co API (nhu ZKTeco, Ronald Jack), ban co the viet Apps Script goi API de tu dong dong bo du lieu cham cong vao Google Sheets moi ngay.

Co the tinh luong tren Google Sheets khong?

Co, nhung chi nen tinh luong co ban (luong theo ngay cong, tru nghi KL, cong OT). Viec tinh BHXH, thue TNCN theo bieu luy tien 7 bac kha phuc tap tren Sheets va de sai. Neu can tinh luong chinh xac, nen dung phan mem chuyen dung hoac them sheet tinh luong rieng voi cong thuc chuyen sau hon.

Lam sao bao mat thong tin luong cua nhan vien?

Co 3 cach: (1) Protected ranges: Bao ve cot Luong chi cho HR va CEO xem. (2) Tach sheet rieng: De thong tin luong o 1 sheet rieng, hide sheet do va chi share cho nguoi co quyen. (3) File rieng: Tao file Google Sheets rieng chi chua thong tin luong, lien ket voi file chinh qua IMPORTRANGE. Chi HR co quyen truy cap file luong.

Co the dung tren dien thoai khong?

Co, Google Sheets co app tren ca iOS va Android. Tuy nhien, trai nghiem tren dien thoai khong tot bang may tinh do man hinh nho va bang nhieu cot. Khuyen nghi: Dung dien thoai de xem nhanh Dashboard va duyet don nghi phep. Dung may tinh de nhap lieu va chinh sua.

Tong ket

Template quan ly nhan su tren Google Sheets la giai phap mien phi, nhanh chong va hieu qua cho cac doanh nghiep nho va vua. Voi 6 sheets lien ket, cong thuc tu dong, conditional formatting va Apps Script, ban co the:

Quan ly ho so nhan vien tap trung, de tim kiem
Theo doi cham cong, nghi phep chinh xac
Danh gia KPI nhan vien khach quan, co he thong
Nhan canh bao tu dong khi hop dong sap het han
Xem Dashboard tong hop de ra quyet dinh nhanh
Tu dong gui bao cao hang thang qua email

Cach su dung template:

Tao Google Sheets moi va dat ten "Quan Ly Nhan Su 2026"
Tao 6 sheets theo cau truc da huong dan (Ho So NV, Cham Cong, Nghi Phep, Dao Tao, KPI, Dashboard)
Nhap header va thiet lap Data Validation (dropdown)
Nhap cong thuc VLOOKUP, COUNTIFS, DATEDIF theo huong dan
Thiet lap Conditional Formatting (5 rules da huong dan)
Copy 3 doan Apps Script vao Script Editor (Extensions → Apps Script)
Nhap data thu (dung mau 10 NV ben tren) de test cong thuc
Share file cho team HR voi quyen phu hop

Neu ban muon tham khao them cac template khac cho Google Sheets, doc bai 15 Template Google Sheets Mien Phi Cho Doanh Nghiep Nho hoac Template Quan Ly Kho Hang Hoan Chinh.

Ban can giai phap nhan su chuyen nghiep hon?

Khi doanh nghiep phat trien va can nhieu tinh nang nang cao hon (cham cong tu dong, tinh luong chinh xac, quan ly tuyen dung...), SheetStore cung cap giai phap phan mem quan ly toan dien tich hop voi Google Sheets:

Dong bo du lieu giua phan mem va Google Sheets - team HR van lam viec tren Sheets quen thuoc
Bao cao tu dong chuyen nghiep hon voi chart, bieu do va tu dong gui email
Phan quyen chi tiet den tung sheet, tung cot du lieu
Mobile app cho nhan vien tu cham cong, xin nghi phep

Xem cac giai phap SheetStore Lien he tu van mien phi

📚 Bài Viết Liên Quan