Google Sheets + Zalo OA: Tự Động Gửi Tin Nhắn Khách Hàng 2026

function refreshZaloToken() {
  const ss = SpreadsheetApp.getActiveSpreadsheet()
  const config = ss.getSheetByName("Config")
  const data = config.getDataRange().getValues()
  const tokenMap = {}
  const rowMap = {}
  for (let i = 0; i < data.length; i++) {
    tokenMap[data[i][0]] = data[i][1]
    rowMap[data[i][0]] = i + 1
  }

  const response = UrlFetchApp.fetch("https://oauth.zaloapp.com/v4/oa/access_token", {
    method: "POST",
    contentType: "application/x-www-form-urlencoded",
    payload: [
      "app_id=" + tokenMap["ZALO_APP_ID"],
      "grant_type=refresh_token",
      "refresh_token=" + tokenMap["ZALO_REFRESH_TOKEN"]
    ].join("&")
  })

  const result = JSON.parse(response.getContentText())
  if (result.access_token) {
    config.getRange(rowMap["ZALO_ACCESS_TOKEN"], 2).setValue(result.access_token)
    config.getRange(rowMap["ZALO_REFRESH_TOKEN"], 2).setValue(result.refresh_token)
    config.getRange(rowMap["TOKEN_EXPIRES_AT"], 2).setValue(new Date().getTime() + 86400000)
    console.log("Token refreshed successfully")
  }
}

// Cai trigger tu dong refresh moi 20 gio:
function setupRefreshTrigger() {
  ScriptApp.newTrigger("refreshZaloToken")
    .timeBased().everyHours(20).create()
}

// Trigger: Khi nguoi dung chinh sua sheet DonHang
function onEdit(e) {
  const sheet = e.range.getSheet()
  if (sheet.getName() !== "DonHang") return

  const row = e.range.getRow()
  const col = e.range.getColumn()

  // Col 8 = TrangThai don hang
  if (col !== 8 || row <= 1) return

  const rowData = sheet.getRange(row, 1, 1, 15).getValues()[0]
  const [maDH, maKH, ngay, sanPham, soTien, soLuong, dvt, trangThai] = rowData

  // Tim Zalo UID cua khach hang
  const ss = SpreadsheetApp.getActiveSpreadsheet()
  const khSheet = ss.getSheetByName("KhachHang")
  const khData = khSheet.getDataRange().getValues()
  let zaloId = ""
  let hoTen = ""
  for (let i = 1; i < khData.length; i++) {
    if (khData[i][0] === maKH) {
      zaloId = khData[i][2]  // Col 3 = Zalo ID
      hoTen = khData[i][1]   // Col 2 = Ho Ten
      break
    }
  }

  if (!zaloId) return

  // Tao noi dung tin nhan theo trang thai
  let message = ""
  if (trangThai === "XAC_NHAN") {
    message = "Xin chao " + hoTen + ",\n"
      + "Don hang #" + maDH + " da duoc xac nhan!\n"
      + "San pham: " + sanPham + "\n"
      + "Tong tien: " + Number(soTien).toLocaleString("vi-VN") + " dong\n"
      + "Chung toi se xu ly va lien he ban som nhat. Cam on ban!"
  } else if (trangThai === "DANG_GIAO") {
    message = "Don hang #" + maDH + " dang tren duong giao!\n"
      + "Du kien giao trong 1-3 ngay lam viec.\n"
      + "Lien he hotline neu can: 1900xxxx"
  } else if (trangThai === "DA_GIAO") {
    message = "Don hang #" + maDH + " da giao thanh cong!\n"
      + "Cam on " + hoTen + " da mua hang. Mong nhan duoc phan hoi cua ban!\n"
      + "Danh gia san pham: https://sheet.com.vn/review"
  } else if (trangThai === "HUY") {
    message = "Don hang #" + maDH + " da bi huy theo yeu cau.\n"
      + "Neu co thac mac, lien he: https://sheet.com.vn/support"
  }

  if (message) {
    sendZaloMessage(zaloId, message)
  }
}

// Chay moi ngay luc 8:00 sang de kiem tra sinh nhat
function sendBirthdayMessages() {
  const ss = SpreadsheetApp.getActiveSpreadsheet()
  const khSheet = ss.getSheetByName("KhachHang")
  const data = khSheet.getDataRange().getValues()

  const today = new Date()
  const todayMonth = today.getMonth() + 1
  const todayDay = today.getDate()

  for (let i = 1; i < data.length; i++) {
    const [maKH, hoTen, zaloId, sdt, ngaySinh] = data[i]
    if (!ngaySinh || !zaloId) continue

    const bd = new Date(ngaySinh)
    const bdMonth = bd.getMonth() + 1
    const bdDay = bd.getDate()

    if (bdMonth === todayMonth && bdDay === todayDay) {
      const tuoi = today.getFullYear() - bd.getFullYear()
      const maGiamGia = "BD" + maKH.toString().slice(-4) + today.getFullYear()
      const message = "Chuc Mung Sinh Nhat " + hoTen + "!\n"
        + "Nhan hang SheetStore gui tang ban mon qua dac biet:\n"
        + "GIAM 20% don hang trong hom nay voi ma: " + maGiamGia + "\n"
        + "Chuc ban tuoi " + tuoi + " that nhieu suc khoe va hanh phuc!\n"
        + "Xem san pham: https://sheet.com.vn/marketplace"

      const result = sendZaloMessage(zaloId, message)
      console.log("Birthday sent to " + hoTen + ":", result.error === 0 ? "OK" : result.message)

      // Luu ma giam gia vao sheet DiscountCodes de kiem tra sau
      const discountSheet = ss.getSheetByName("MaGiamGia") ||
        ss.insertSheet("MaGiamGia")
      discountSheet.appendRow([maGiamGia, maKH, 20, new Date(), new Date(today.getTime() + 86400000), "CHUA_DUNG"])
    }
  }
}

// Cai trigger chay 8h sang moi ngay:
function setupBirthdayTrigger() {
  ScriptApp.newTrigger("sendBirthdayMessages")
    .timeBased().atHour(8).everyDays(1).create()
}

// Blast tin nhan khuyen mai toi nhom khach hang
// Tham so: nhomKH = "VIP" | "THUONG_XUYEN" | "TAT_CA"
function blastPromotion(tieudeTin, noiDungTin, nhomKH, gioiHanSoLuong) {
  const ss = SpreadsheetApp.getActiveSpreadsheet()
  const khSheet = ss.getSheetByName("KhachHang")
  const data = khSheet.getDataRange().getValues()

  let danhSachGui = []
  for (let i = 1; i < data.length; i++) {
    const [maKH, hoTen, zaloId, sdt, ngaySinh, nhomKHData] = data[i]
    if (!zaloId) continue
    if (nhomKH !== "TAT_CA" && nhomKHData !== nhomKH) continue
    danhSachGui.push({ maKH, hoTen, zaloId })
  }

  // Gioi han so luong neu can
  if (gioiHanSoLuong > 0) {
    danhSachGui = danhSachGui.slice(0, gioiHanSoLuong)
  }

  console.log("Sending to " + danhSachGui.length + " customers...")

  let thanhCong = 0, thatBai = 0
  for (const kh of danhSachGui) {
    // Personalize tin nhan voi ten khach
    const tinNhanCaNhan = noiDungTin.replace("{ten}", kh.hoTen)

    const result = sendZaloMessage(kh.zaloId, tinNhanCaNhan)

    if (result.error === 0) {
      thanhCong++
    } else {
      thatBai++
      console.log("Failed for " + kh.maKH + ":", result.message)
    }

    // Delay 100ms giua moi tin de tranh rate limit
    Utilities.sleep(100)
  }

  console.log("Done! Thanh cong: " + thanhCong + ", That bai: " + thatBai)
  return { thanhCong, thatBai, tongGui: danhSachGui.length }
}

// Vi du su dung:
function chayBlastTetNguyen() {
  blastPromotion(
    "Khuyen mai Tet 2026",
    "Chao {ten}! Mung nam moi 2026!\n"
    + "SheetStore giam 30% tat ca goi trong thang 1.\n"
    + "Ma: TET2026 - Han dung: 31/01/2026\n"
    + "Dang ky ngay: https://sheet.com.vn/bang-gia",
    "TAT_CA",
    0  // 0 = khong gioi han
  )
}

// Ham gui tin nhan tai kich hoat khach khong hoat dong 30+ ngay
function sendReactivationMessages() {
  const ss = SpreadsheetApp.getActiveSpreadsheet()
  const dhSheet = ss.getSheetByName("DonHang")
  const khSheet = ss.getSheetByName("KhachHang")

  const dhData = dhSheet.getDataRange().getValues()
  const khData = khSheet.getDataRange().getValues()

  // Tim ngay mua hang cuoi cua moi khach
  const lastPurchase = {}
  for (let i = 1; i < dhData.length; i++) {
    const maKH = dhData[i][1]
    const ngayDH = new Date(dhData[i][2])
    if (!lastPurchase[maKH] || ngayDH > lastPurchase[maKH]) {
      lastPurchase[maKH] = ngayDH
    }
  }

  const now = new Date()
  const threshold = 30 * 24 * 60 * 60 * 1000 // 30 ngay

  for (let i = 1; i < khData.length; i++) {
    const [maKH, hoTen, zaloId] = khData[i]
    if (!zaloId || !lastPurchase[maKH]) continue

    const daysSince = (now - lastPurchase[maKH]) / (24 * 60 * 60 * 1000)
    if (daysSince >= 30 && daysSince < 31) { // Chi gui dung ngay thu 30
      const message = "Chao " + hoTen + ", lau khong gap!\n"
        + "Da " + Math.round(daysSince) + " ngay ke tu lan mua cuoi.\n"
        + "Quay lai hom nay va nhan uu dai: COMEBACK15\n"
        + "Kham pha: https://sheet.com.vn/marketplace"
      sendZaloMessage(zaloId, message)
    }
  }
}