Google Sheets Automation

1_ Apps Script là gì?
Apps Scripts là một ngôn ngữ lập trình do Google phát triển để người dùng có thể tạo ra các kịch bản, tự động hóa các tác vụ cho các ứng dụng của Google như Google Sheets, Google Docs, Gmail,...
Ví dụ: Bạn có danh sách 10000 email khách hàng trên 1 file Sheet, trong đó có 3000 khách hàng đã sản phẩm A, 3000 khách hàng đã mua sản phẩm B và 4000 khách hàng đã mua sản phẩm C
Thay vì tạo filter để lọc từng loại khách hàng, sau đó export ra email để gửi, bạn có thể viết 1 đoạn Script để Google tự động quét qua từng dòng trong file Sheet đó và đọc cột sản phẩm mà khách hàng đã mua và gửi email tương ứng với sản phẩm đó, tiếp theo thì chỉ cần ngồi đợi Google xử lí phần còn lại 😃

2_ Hàm (function), Trigger trong Apps Script và Bot API
Hàm (function) là 1 đoạn code để thực hiện 1 hành động cụ thể. Hàm có thể nhận vào các tham số và trả về kết quả sau khi thực hiện hàm
Trigger là 1 đoạn code để thực hiện 1 hoặc nhiều hàm khi có 1 sự kiện nào đó xảy ra
👉 Hàm chỉ là hành động, nhưng để hành động đó xảy ra thì cần có Trigger
Bot API là 1 trung gian phần mềm, cho phép Chatbot có thể tương tác với khách hàng
Ví dụ đơn giản về hàm, trigger và Bot API như sau:
Trong nhà hàng sẽ có khách hàng, phục vụ và đầu bếp. Đầu bếp sẽ có hành động là nấu ăn (hàm nấu ăn 😃), chủ nhà hàng sẽ cài đặt Trigger cho đầu bếp như sau: Khi khách hàng đến và order món ăn thì đầu bếp sẽ bắt đầu nấu ăn (hành động nấu ăn khi có sự kiện khách hàng order, nếu khách hàng không order thì đầu bếp không nấu ăn 😃 giống y như định nghĩa). Để có thể nấu ăn được thì đầu bếp cần biết khách hàng order món gì để chuẩn bị nguyên liệu (tham số nhận vào của hàm chính là các nguyên liệu) và trả về kết quả là món ăn
Tuy nhiên thì khách hàng không thể đến nhà hàng và đi thẳng vào trong bếp để gọi món với đầu bếp được, và cần order qua phục vụ, phục vụ sẽ mang yêu cầu của khách hàng đến nói với đầu bếp và cầm món ăn sau khi làm xong mang ra cho khách hàng 👉 Phục vụ chính là API trung gian, cho phép đầu bếp và khách hàng tương tác qua lại với nhau

Trong Apps Scripts, sẽ có rất nhiều Trigger và hàm được sử dụng, ví dụ như:

• Trigger onOpen(e) sẽ thực hiện 1 hành động khi có người dùng mở file Sheet
• Trigger onEdit(e) sẽ thực hiện 1 hành động khi có người dùng thay đổi giá trị 1 ô bất kỳ trên file Sheet
• Hàm getRow() sẽ trả về dòng của 1 ô trong file Sheet
• Hàm getColumn() sẽ trả về cột của 1 ô trong file Sheet

Bạn có thể tham khảo thêm về các Trigger của Google tại đây 

3_ Hướng dẫn tạo Apps Scripts trên Google Sheet để thực hiện Automation
3.1_ Chuẩn bị file Sheet
Trước tiên, bạn cần chuẩn bị 1 file Sheet (có thể là thông tin khóa học, thông tin đơn hàng,...)
Lưu ý: Bắt buộc phải có cột ID của khách hàng trên SmaxBot (ví dụ như file phía dưới)
Tư duy của Automation này rất đơn giản 😃: Khi một ô bị thay đổi giá trị, chúng ta sẽ yêu cầu Google gửi về dữ liệu của ô vừa bị thay đổi, dòng và cột của ô đó
Nếu giá trị thay đổi và cột thay đổi trùng với điều kiện chúng ta đặt ra, tiếp tục yêu cầu Google tìm đến ô ID cùng dòng với ô vừa bị thay đổi và gửi block tới khách hàng có ID đó
Ví dụ: Điều kiện đặt ra cho Automation như sau: Nếu ô thay đổi nằm ở cột số 7 (Trạng thái đơn hàng) và giá trị thay đổi là Giao không thành thì thực hiện các hành động phía sau
Nếu bạn thay đổi giá trị tại cột Địa chỉ, Google sẽ trả về cột có giá trị thay đổi là cột 4 (không phù hợp với điều kiện 👉 Không chạy)


Lưu ý nữa là nên sử dụng attribute {{id}} chứ không dùng {{messenger user id}} nhé 😃 vì Google sẽ tự động làm tròn số {{messenger user id}} nên sẽ không hoạt động đâu
Cách lưu thì bạn có thể sử dụng thẻ Attribute to Sheet trên Bot hoặc tham khảo khóa học của anh Giáp Đức Thắng để biết cách mapping dữ liệu đơn hàng từ tin nhắn chốt đơn về Google Sheets nhé


3.2_ Setup Apps Script cho file Sheet
Trước tiên, tại file Sheet muốn tạo Automation bạn chọn Extensions, sau đó chọn Apps Script


Truy cập để lấy code tại đây, sau đó copy toàn bộ và paste vào Code.gs


Giải thích cho từng đoạn code 😃

function getValueAt(row, column) {
  const ss = SpreadsheetApp.getActiveSpreadsheet(); // Lấy file sheet hiện tại đang thao tác
  const sheet = ss.getSheets()[0] // Lấy bảng tính đầu tiên trong file sheet, các bảng tính sẽ bắt đầu đếm từ số 0
  const range = sheet.getRange(row, column) // Lấy ô có số dòng là row và số cột là column
  const value = range.getValue() // Lấy giá trị tại ô range
  return value // Trả về giá trị tại ô range
}

Hàm getValueAt(row, column) sẽ nhận vào 2 tham số là số dòng (row) và số cột (column), sau đó trả về dữ liệu và giá trị tại ô có dòng và cột đó
Ví dụ, bạn muốn lấy giá trị tại ô A3, bạn cần truyền vào hàm getValueAt(row, column) số dòng là 3 và số cột là 1 (cột A = 1, cột B = 2,...) ⏩ getValueAt(3, 1) sẽ trả về giá trị tại ô A3

 

function fetchAPI(id, blockId) {
    const data = {
      "customer_id": id,
      "block_id": blockId
    }
    const options = {
      'method': 'post',
      'headers': {
        'Authorization': 'Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjY1NTZjZDFhNDU0MTJjNjlkN2UwYTYyNiIsIm5hbWUiOiJCTEFOSyBCT1QgLSBDb3B5IiwiaWF0IjoxNzAwMTg3NDE4LCJleHAiOjIwMTU1NDc0MTh9.DWaPT7dTPVXnoX08SFBILkWbUZtuzTBWehLfWpIS91g' // Thay giá trị Bot Token vào đây (sau chữ Bearer)
      },
      'contentType': 'application/json',
      'payload': JSON.stringify(data)
    }
    const response = UrlFetchApp.fetch('https://api.smax.bot/public/bots/6556cd1a45412c69d7e0a626/send', options) // Thay giá trị Bot ID vào đây
    return response // Trả về mã code sau khi call API, nếu mã code = 200 => Call API thành công
}

Hàm fetchAPI(id, blockId) sẽ nhận 2 tham số là id của khách hàng (nằm tại cột ID) và ID của block muốn gửi tới khách hàng (cái này cần setup thủ công cho từng trường hợp)
Khi sử dụng bạn cần thay giá trị Bot Token và Bot ID vào dãy 'Bearer {{bot Token}}' và https://api.smax.bot/public/bots/{{bot ID}}/send vì mỗi bot sẽ có ID và token riêng

 

function onEdit(e) {
  const range = e.range; // Lấy ô vừa có giá trị bị thay đổi
  const value = range.getValue() // Lấy giá trị tại ô vừa bị thay đổi
  const row = range.getRow() // Lấy dòng của ô vừa bị thay đổi
  const column = range.getColumn() // Lấy cột của ô vừa bị thay đổi
  if (column == 7 && value == 'Đang giao') { // Kiểm tra điều kiện: Nếu cột của ô vừa bị thay đổi = 7 (là cột Trạng thái đơn hàng) và giá trị của ô vừa bị thay đổi = Đang giao thì...
    const id = getValueAt(row, column - 6) // Gọi hàm getValueAt(row, column - 6)
    const response = fetchAPI(id, '660684fe035c95dfa748210f') // Gọi hàm fetchAPI(ID, blockID)
    Logger.log(response.getResponseCode())
  } else if (column == 7 && value == 'Giao không thành') { // Kiểm tra điều kiện: Nếu cột của ô vừa bị thay đổi = 7 (là cột Trạng thái đơn hàng) và giá trị của ô vừa bị thay đổi = Giao không thành thì...
    const id = getValueAt(row, column - 6)
    const response = fetchAPI(id, '66068aab53fe594af83fbda4')
    Logger.log(response.getResponseCode())
  } else if (column == 7 && value == 'Đã nhận') {
    const id = getValueAt(row, column - 6)
    const repsone = fetchAPI(id, '66068b2173d7adacdd5e3bb3')
    Logger.log(repsone.getResponseCode())
  }
}

Hàm onEdit(e) sẽ nhận vào giá trị là sự kiện của ô vừa thay đổi. Khi có 1 ô giá trị bị thay đổi thì:

const range = e.range ⏩ Lưu ô vừa bị thay đổi giá trị vào biến range
const value = range.getValue() ⏩ Lưu giá trị của ô vừa bị thay đổi vào biến value
const row = range.getRow() và const column = range.getColumn() ⏩ Lưu dòng và cột của ô vừa bị thay đổi vào 2 biến row và column

Tiếp theo sẽ kiểm tra các điều kiện:

if (column == 7 && value == 'Đang giao') ⏩ Nếu cột của ô vừa bị thay đổi là 7 (cột G - Trạng thái đơn hàng, cái này các bạn có thể thay đổi custom tùy theo file Sheet của mình có bao nhiêu cột và cột đó nằm ở vị trí nào nhé 😃) và giá trị của ô vừa bị thay đổi là Đang giao

Nếu thỏa mãn điều kiện thì

const id = getValueAt(row, column - 6) ⏩ Gọi hàm getValueAt, sau đó truyền vào giá trị row và column - 6 của ô vừa bị thay đổi giá trị, nếu ô vừa thay đổi giá trị của bạn nằm ở dòng 2 và cột 7 thì hàm getValueAt sẽ nhận vào 2 tham số là 2 và 1 (column - 6 = 7 - 6 = 1, vì chỗ này mình để cột trạng thái ở cột số 7 và cột ID ở cột số 1 nên khi truy vấn lại cột ID thì cần trừ đi 6, chỗ này cũng tùy file Sheet của bạn để thay đổi giá trị nhé), sau đó lưu ID của khách hàng vừa truy vấn ở ô dòng 2 cột 1 vào biến id

Tiếp theo, bạn cần chuẩn bị block muốn gửi cho khách, ví dụ với trạng thái đơn hàng 'Đang giao' (Ship đã nhận hàng và đang giao cho khách) mình sẽ tạo 1 block trên bot như sau và copy lại id của block đó


Sau khi đã có ID truy vấn từ ô thay đổi giá trị và block ID, tiếp tục sử dụng hàm fetchAPI

const response = fetchAPI(id, '660684fe035c95dfa748210f') // Gọi hàm fetchAPI(ID, blockID)

Copy block ID tạo phía trên và thay vào fetchAPI(id, '{{block ID}}')

Làm tương tự với các điều kiện khác, ví dụ đơn hủy, đơn giao không thành công, đơn giao thành công,...

Sau khi hoàn thiện phần code, đặt tên cho project và chọn Save để lưu lại


Tiếp theo vào phần Triggers và chọn Add Trigger


Phần cấu hình Trigger chọn như ảnh dưới
Hàm chính sẽ chạy khi có sự thay đổi của ô là onEdit
Event Type (loại sự kiện) sẽ kích hoạt Trigger là onEdit (khi có sự thay đổi)
Failure notification settings: Thông báo qua email khi trigger chạy lỗi, chọn immediately để Google thông báo ngay khi bị lỗi


Sau khi chọn Save, Google sẽ yêu cầu người dùng đăng nhập vào tài khoản để xác minh Apps Script vừa tạo, chọn Advance và chọn Go to onEdit function


Trigger sau khi được thêm thành công


Phần Excutions này để dùng cho việc xem lại dữ liệu Log của các trigger khi hoạt động, bạn có thể theo dõi trong đây xem Trigger nào hoạt động hoặc bị lỗi và Google báo lỗi ở dòng nào để khắc phụ nhé


Lưu ý ở đây sẽ có 2 loại Trigger: Simple Trigger do Google tự động và Trigger do bạn định nghĩa, chỉ Trigger do bạn định nghĩa mới có thể sử dụng được API còn Simple Trigger thì không, nên Simple Trigger sẽ báo lỗi Failed do không call được API, còn Trigger do bạn tự tạo sẽ Completed do bạn đã đăng nhập xác minh và cấp quyền cho Apps Script nhé