Bài 9: Ghi công thức Google Sheet vào ô

https://hocggsheet.com/ghi-cong-thuc-google-sheet-vao-o/

Trong Excel hay Google Sheet, chúng ta đã biết đến các công thức được cung cấp sẵn như tính tổng sum, trung bình average, điều kiện if,… Hôm nay, HocGgSheet.com sẽ chia sẻ đến với các bạn cách ghi công thức này vào trong Google Sheet bằng App Script

Các hàm sẽ sử dụng

• Trigger onEdit()

• setFormula() – ghi công thức trong ngoặc vào range phía trước

Ví dụ đơn giản

Cho dải ô như hình

Dải ô ví dụ

Chương trình dưới đây sẽ ghi công thức: =sum(B1:G1) vào ô H1

function vidu1() {
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var sheet0 = ss.getSheetByName("sheet0");
 
   ss.getRange('H1').setFormula('=sum(B1:G1)');
 }

Như vậy, cách dùng setFormula() là vô cùng đơn giản, chỉ cần gõ công thức bạn muốn vào trong ngoặc và để trong dấu ngoặc kép “” hoắc dấu nháy ‘ ‘ là được.

Ví dụ nâng cao – setFormula kết hợp nối chuỗi

Cũng cùng dải ô trên, nhưng dải ô A1:A6 đã được xác thực dữ liệu. Có 2 lựa chọn: “Duyệt” hoặc “Không duyệt”

Nhiệm vụ của ta bây giờ là: mỗi khi chọn “Duyệt”, ô cùng hàng ở bên cột H sẽ ghi hàm tính tổng của dải ô bên trong ra. Ví dụ, mình chọn “Duyệt” cho ô A6, thì ô H6 sẽ viết hàm tính tổng =sum(B6:G6)

Mỗi khi đổi giá trị cho các ô ở A1:A6 thì sẽ thực hiện những câu lệnh tương ứng. Vì vậy, ta sẽ dùng trigger onEdit()

function onEdit() {
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var sheet0 = ss.getSheetByName("sheet0");
   var activeCell = ss.getActiveCell();

   if (activeCell.getValue() == "Duyệt") {
     sheet0.getRange(activeCell.getRow(), 8).setFormula("=sum(B6:G6)");
   }
 }

Chú thích:

• activeCell dùng để lấy ô chúng ta đang bấm vào

• Trong hàm getRange(), 8 là số thứ tự của cột H

Kết quả

Tuy nhiên thì nếu viết như trên thì dù ta chọn “Duyệt” ở ô nào thì ô bên cột H cũng chỉ tính mỗi tổng của dải ô B6:G6. Nói cách khác, đoạn code đã cố định công thức tính tổng.

Dù chọn Duyệt ở đâu thì cột H cũng chỉ tính tổng B6:G6

Bây giờ, việc của ta là phải điều chỉnh làm sao để có thế lấy ra được dải ô tương ứng. Giải pháp chính là kĩ thuật nối chuỗi.

function onEdit() {
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var sheet0 = ss.getSheetByName("sheet0");
   var activeCell = ss.getActiveCell();
 
  if (activeCell.getValue() == "Duyệt") {
     var activeRange = sheet0.getRange(activeCell.getRow(), 2, 1, 6).getA1Notation();
     sheet0.getRange(activeCell.getRow(), 8).setFormula("=sum(" + activeRange + ")");
   }
 }

Kết quả nối chuỗi thành công

Chú thích

• activeRange dùng để lấy dải ô cần tính tổng

• getA1Notation() trả về kết quả là “mô tả” của dải ô đó. Ví dụ như nếu dùng getA1Notation cho dải ô B6:G6 thì kết quả trả về sẽ là “B6:G6”. Các bạn có thể thử kiểm tra hàm này bằng Logger.log()

• Trong hàm setFormula() có tất cả là 3 chuỗi đã được nối với nhau:

  1. =sum(

  2. “mô tả” của dải ô cần tính tổng -> activeRange

  3. )

• Các chuỗi trên được nối với nhau bằng dấu cộng +

Cải tiến code cho ví dụ nâng cao để ghi công thức

Khi tiến hành ghi công thức tính sum vào ô ở cột H, chúng ta đã dùng getRange. Tuy nhiên thì App Script có 1 hàm hay hơn để làm được điều đó

Đó là hàm offset(rowOffset, columnOffset) – trả về ô hoặc dải ô tương ứng với ô/dải ô đứng phía trước. Các ô/dải ô tương ứng được xác định bởi số hàng và số cột tính từ ô gốc.

Lưu ý: ví dụ ô gốc là

function onEdit() {
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var sheet0 = ss.getSheetByName("sheet0");
   var activeCell = ss.getActiveCell();
   
   if (activeCell.getValue() == "Duyệt") {     
     var activeRange = sheet0.getRange(activeCell.getRow(), 2, 1, 6).getA1Notation();     
     activeCell.offset(0, 7).setFormula("=sum(" + activeRange + ")");
   }
 }

Chú thích:

• offset(0, 7) – ô ở cùng hàng với activeCell thì rowOffset = 0, cột H là cột thứ 7 tính từ cột của activeCell -> columnOffset = 7

File luyện tập Bài 9 – Ghi công thức setFormula

https://docs.google.com/spreadsheets/d/1qkolnHAXPxrHdPQpH4qOtR-xejNXoo1R0tCM4lodPdE/edit?usp=sharing

Để xem code App Script, các bạn hãy Tạo bản sao (Make a copy) về Drive của các bạn nhé!

Nếu có bất kì thắc mắc nào thì các bạn hãy để lại comment ở bên dưới post này, hoặc ở trong post Help – Giải đáp thắc mắc qua comment