So sánh 2 cell trong excel app script năm 2024

Google Script là một ứng dụng của Google, thường dùng kết hợp với Google Sheets, nó được ví như VBA của Excel. Google Script có thể làm được hầu như tất cả mọi thứ mà bạn có thể làm bằng VBA với Excel, và còn hơn thế nữa.

Khả năng mở rộng của Google Script rất lớn khi nó liên kết với toàn bộ ứng dụng của Google chứ không chỉ là Google Sheets thôi. Chính vì vậy Google tạo Script là một áp riêng biệt. Xtea thường dùng Google Script liên kết với

• Google Sheets để điều khiển Sheets theo ý mình khi mà các hàm sẵn có không làm tốt hơn được
• Google Mail khi cần gởi mail tự động
• Google Maps khi cần tìm địa chỉ hành chính
• Google Forms khi tạo những Form dài, Form thay đổi nội dung theo thời gian
• Google Docs khi cần tạo văn bản từ Sheets
• Google Calendar khi cần tạo lịch tự động
• API của bên thứ 3 như Mailgun
• Tạo Google Web Apps
• ...

Và những Script đó sẽ cùng hoạt động với nhau chứ không phải là riêng lẽ. Tuyệt vời hơn là ứng dụng nền web nên bạn chỉ cần có quyền truy cập và có internet là được, không cần máy cấu hình mạnh, không có phiên bản cũ hay phiên bản mới hơn mà chỉ có duy nhất phiên bản hiện hành. Và cái hay nhất là nó chạy tự động hoàn toàn theo ngữ cảnh.

Cấu trúc ngôn ngữ của Google Script tương đồng với JavaScript. Cửa sổ code của Google Script cũng có nhiều tính năng gợi ý, đưa ra hướng dẫn ngắn, thông báo lỗi và công cụ gỡ lỗi để bạn code trơn tru dễ dàng hơn.

Một số đặc điểm của Googles Script và SpreadsheetApp:

• Googles Script có phân biệt chữ hoa chữ thường.
• Có 4 phân lớp trong SpreadsheetApp
  • SpreadsheetApp: gọi SpreadsheetApp (chính là Google Sheets)
  • Spreadsheet: gọi file Google Sheets cụ thể
  • Sheet: gọi đến Sheet cụ thể trong file
  • Range: gọi đến địa chỉ cụ thể trong sheet
• Bạn muốn xử lý đến lớp nào thì gọi lớp đó ra để thực hiện, thông thường xử lý đến lớp cuối cùng "Range" là nhiều nhất.
• Các Google App khác cũng có các lớp tương tự, bạn có thể tìm hiểu đầy đủ nhất tại trang tài liệu hỗ trợ của Google (Spreadsheet Service | Apps Script | Google Developers)

Xtea chia sẻ thêm về cách lưu trữ thông tin dịch vụ Xtea bằng Google Sheets - Forms - Scripts, tóm tắt cơ bản nhất nhé:

• Khi khác hàng lần đầu điền thông tin vào Form sẽ liên kết với Sheet để lưu trữ, đồng thời gởi mail thông báo đăng ký thành công theo template đã soạn.
• Khi khách hàng sử dụng dịch vụ thì submit Form bắt đầu/ kết thúc, dựa trên thời gian submit sẽ tính toán trên Sheet thời gian sử dụng dịch vụ và chi phí.
• Kết nối với trang thông báo trạng thái để minh bạch đồng hồ đếm giờ.
• Khi khách hàng nộp tiền vào tài khoản hoặc kiểm tra tài khoản thì script tự động gởi mail theo yêu cầu từ data đã lưu.

Các bài sau Xtea sẽ chia sẻ những bài viết về Google Script cụ thể hơn, hy vọng sẽ giúp ích cho các bạn.

Google Apps Script - là 1 ngôn ngữ lập trình dựa trên ngôn ngữ lập trình gốc là Javascript. Với công cụ này bạn có thể lập trình để thao tác, can thiệp trực tiếp đến các dịch vụ của Google, giúp tự động hóa đơn các quá trình làm thủ công.

* Các liên kết cần biết Apps Script Google: https://www.google.com/script/start - Quản lý dự án https://docs.google.com/spreadsheets - Quản lý google sheet (như excel online)

2. Apps Script Google có thể làm được những gì?

Thêm menu, dialogs, và thanh sidebar tùy chỉnh vào Google Docs, Sheets và Forms. Viết các hàm mở rộng hoặc các macros cho Google Sheets. Xuất bản Web Apps - độc lập hoặc tích hợp vào trang web của Google Sites. Tương tác với các dịch vụ khác của Google, bao gồm AdSense, Analytics, Lịch, Drive, Gmail và Bản đồ. Xây dựng các tiện ích bổ sung để mở rộng Google Docs, Sheets, Slides và Forms và xuất bản chúng lên cửa hàng Add-on. Chuyển đổi ứng dụng Android thành một tiện ích bổ sung Android để ứng dụng có thể trao đổi dữ liệu với Google Doc hoặc Sheet của người dùng trên thiết bị di động. Xây dựng Chat bot cho Hangout chat

Hiện tại Apps Scripts Google Script có thể lập trình để thao tác với hầu hết các dịch vụ của Google:

Khi sử dụng Google SpreadSheet, đôi khi chúng ta phải lưu trữ rất nhiều thông tin và thực hiện các tác vụ phức tạp. Trong bài này, mình sẽ giới thiệu 2 phương pháp giúp tự động hóa Google SpreadSheet, để chúng ta có thể sử dụng nó một cách chuyên nghiệp, tiện lợi hơn.

2 phương pháp sau đây sẽ sử dụng Google Apps Script. Vậy trước hết mình sẽ giới thiệu qua về Google Apps Script.

1. Giới thiệu về Google Apps Script

Google Apps Script là ngôn ngữ lập trình dựa trên Javascript, với trình biên tập, biên dịch nằm trên máy chủ của google. Do đó, nó rất tiện lợi để có thể tạo các ứng dụng web nhẹ. Chúng ta có thể mở trình biên tập Google Apps Script trên Google SpreadSheet bằng cách chọn Tool -> Script Editor.

Một số điều Google Apps Script có thể làm:

• Custom menu, popup cho Google Docs, Google Sheets, Google Forms.
• Viết custom function cho Google Sheets.
• Tương tác giữa Google Sheets với các dịch vụ khác của Google như: Google Drive, Gmail, Google Slide,....

Và nhiều điều khác nữa…

Sau đây, mình sẽ giới thiệu phương pháp tự động hóa Google SpreadSheet bằng cách sử dụng hàm Trigger của Google Apps Script và phương pháp chạy Cronjob.

2. Một số loại hàm Triggers

• onOpen(e) : tự động chạy khi chúng ta mở bảng tính. (Người mở phải có quyền chỉnh sửa).
• onInstall(e): tự động chạy khi chúng ta cài đặt Editor Add-on.
• onEdit(e): tự động chạy khi chúng ta thay đổi giá trị trong bảng tính.
• onSelectionChange(e): tự động chạy khi chúng ta thay đổi selection trong bảng tính.
• doGet(e): tự động chạy khi chúng ta mở một web app hoặc chương trình gửi HTTP GET request đến web app.
• doPost(e): tự động chạy khi chương trình gửi HTTP POST request đến web app.

Sau đây mình sẽ làm một ví dụ với hàm doEdit()

Bài toán

Chúng ta đang lập danh sách những đồ cần mua cho chuyến đi picnic sắp tới. Nội dung file như sau:

Để thuận tiện cho việc tính toán số tiền tiêu tốn, chúng ta muốn sheet tự động tính tổng giá tiền những món đã được chọn.

Hướng giải quyết

Dùng hàm onEdit() để kiểm tra sự thay đổi của cột C. Khi cột C có thay đổi, kiểm tra nếu checkbox của ô đang thao tác được check thì lấy giá trị ô bên trái nó (ô giá tiền) và cộng vào Tổng tiền. Ngược lại, nếu checkbox không được check thì lấy Tổng tiền trừ đi giá tiền món đồ đó.

Code

function onEdit() {
  //Lấy ra file SpreadSheet đang thao tác
  var activeSpreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  //Lấy ra sheet đang thao tác
  var activeSheet = activeSpreadSheet.getActiveSheet();
  //Lấy ra ô đang được chọn
  var activeRange = SpreadsheetApp.getActiveRange();
  //Lấy ra cột của ô đó
  var activeColumn = activeRange.getColumn();
  //Khi ô được chọn thuộc cột 3 (Cột checkbox)
  if(activeColumn == 3){
    //Lấy ra hàng của ô đó
    var activeRow = activeRange.getRow();
    //Giá tiền của đồ được chọn là giá trị của ô bên trái ô này
    var price = SpreadsheetApp.getActiveSheet().getRange(activeRow,activeColumn-1).getValue();
    //Tổng giá tiền là giá trị của ô E2
    var sumCell = activeSheet.getRange("E2");
    var sumValue = sumCell.getValue();
    //Nếu checkbox được check thì + giá tiền vào sum, và ngược lại
    if(activeRange.isChecked()){
      sumValue += price;
    }else{
      sumValue -= price;
    }
    //Ghi lại giá trị sum vừa tính được vào ô E2 
    sumCell.setValue(sumValue);
   }
}

Lưu lại, nhập tên Project và chọn Run. Lần đầu chạy thì Google sẽ yêu cầu xác minh. Sẽ có cảnh báo an toàn nhưng không sợ đâu, code mình tự viết mà

Cuối cùng thì quay lại sheet và tận hưởng thành quả nào.

3. Cronjob

Bài toán

Chúng ta tạo 1 file SpreadSheet để quản lý thông tin giá BTC. Giá BTC được lấy về bằng việc gọi API. Vì giá BTC biến động liên tục nên chúng ta muốn hệ thống tự chạy hàm lấy giá BTC theo một chu kì cố định, nhờ vậy giá BTC trong file sẽ luôn được cập nhật.

Nội dung file sheet sẽ có dạng như sau:

Code

function btc_price(){
  var url = "https://api.coinbase.com/v2/prices/spot?currency=USD";
  var rsp = UrlFetchApp.fetch(url);
  var json = JSON.parse(rsp.getContentText());
  return json.data.amount;
}

Viết hàm trên vào trong Script editor, sau đó gọi hàm btc_price trong bảng tính để lấy giá BTC ở thời điểm hiện tại.

Nếu chỉ thế này thôi thì giá BTC sẽ không được cập nhật mỗi khi ta vào lại. Để nó tự động lấy giá trị mới nhất thì ta sẽ cho hàm btc_price chạy cronjob theo các bước dưới đây.

Tạo Cronjob

B1: Nhấn vào biểu tượng Current project's triggers (Các kích hoạt của dự án hiện tại)

B2: Nhấn vào Add Trigger (Thêm trình kích hoạt) ở bên dưới màn hình. Chọn hàm chạy là btc_price, chọn thời gian chạy hàm tùy theo nhu cầu. Như trong hình bên dưới thì mình cho hàm chạy vào khoảng từ 0h-1h mỗi ngày.

Như vậy là đã tạo xong Cronjob. Hàm được chọn sẽ tự động chạy theo thời gian được cài đặt.

Kết

Trên đây là 2 phương pháp giúp chúng ta sử dụng spreadsheet tiện lợi, chuyên nghiệp hơn. Ngoài 2 phương pháp trên, Google Apps Script còn rất nhiều ứng dụng thú vị khác. Nếu có thời gian hãy tìm hiểu thêm nhé.