Hướng dẫn cách làm việc nhanh hơn trong Google Sheets với 4 Google Scripts

Google Sheets là một phần của Google Suite và cho đến nay là một trong những công cụ miễn phí mạnh mẽ nhất mà bạn từng sử dụng. Google Sheets cho phép bạn theo dõi, phân tích hoặc ghi lại bất cứ thứ gì. Nhưng điều khiến nó trở nên đặc biệt chính là bạn có thể sử dụng Google Scripts để nâng cao khả năng của Google Sheets. Viết mã trong Google Sheets nghe có vẻ hơi đáng sợ. Bạn có thể nghĩ rằng bất cứ thứ gì có từ "script" đều yêu cầu kiến thức lập trình nâng cao. Nhưng không phải lúc nào cũng đúng, đặt biệt là trong trường hợp này.

Google Sheets Scripts là những đoạn mã bạn có thể viết bên trong Google Sheets để tăng thêm sức mạnh cho trang tính của mình. Google Sheets Scripts được viết bằng JavaScript và do JavaScript là một ngôn ngữ lập trình phổ biến, bạn có thể đã quen với nó. Nếu không, bạn chỉ cần đơn giản copy và past những dòng mã bên dưới để sử dụng mà thôi.

Viết Google Scripts rất giống với viết VBA trong Microsoft Excel để tạo các thủ tục (Sub), các hàm (Function). Tập lệnh trong Google Sheets của bạn được cung cấp bởi Google Apps Script, tập lệnh này cũng hoạt động với các dịch vụ khác của Google. Dưới đây là bốn tập lệnh thực sự chứng minh sức mạnh của tập lệnh của Google Sheets Scripts

Tạo các hàm tùy chỉnh trong Google Sheets

Một trong những cách dễ nhất để Google Script có thể cải thiện đáng kể trải nghiệm của bạn là tạo các hàm tùy chỉnh. Google Sheets đã cung cấp một danh sách dài các hàm. Bạn có thể xem qua những hàm phổ biến nhất bằng cách nhấp vào biểu tượng Menu > Functions.

Nhấp vào dòng more function, bạn sẽ thấy một danh sách dài các hàm toán học, thống kê, tài chính, văn bản, kỹ thuật và các hàm khác. Tuy nhiên, Google Scripts cung cấp cho bạn sự linh hoạt để tạo các công thức được cá nhân hóa của riêng bạn.

Ví dụ, bạn có thể tạo công thức tùy chỉnh của riêng mình để chuyển đổi độ C sang độ F.

Để tạo hàm tùy chỉnh đầu tiên của mình, bạn cần mở trình chỉnh sửa tập lệnh Script editor. Để thực hiện việc này, hãy nhấp vào Tools > Script Editor.

Bạn sẽ thấy màn hình dự án, nơi bạn sẽ viết mã JavaScript của mình.

Tại đây, hãy bắt đầu viết hàm tùy chỉnh của riêng bạn. Tên hàm giống với tên mà bạn sẽ bắt đầu nhập vào một ô trong Google Sheets sau ký hiệu "=", sau đó hãy viết công thức của bạn. Một hàm để chuyển đổi độ C sang độ F sẽ trông giống như sau:

function CSTOFH (input) {
  return input * 1.8 + 32;
}

Dán hàm ở trên vào cửa sổ mã, sau đó chọn File > Save, đặt tên cho dự án là "CelsiusConverter" và nhấp vào OK .

Bây giờ, tất cả những gì bạn phải làm để sử dụng hàm mới của mình là gõ dấu "=", sau đó viết tên hàm tùy chỉnh vừa tạo của bạn để sử dụng. Quá nhanh, quá đơn giản phải không?

Với các thao tác tương tụ, bạn có thể dễ dàng viết bất kỳ công thức tùy chỉnh nào mà mình muốn, để làm việc nhanh chóng hơn.

Tự động tạo biểu đồ trong Google Sheets

Giả sử bạn đang có một sheets dữ liệu và muốn tự động tạo biểu đồ bằng cách sử dụng dữ liệu trong bảng tính. Bạn có thể tạo một hàm để một biểu đồ mới cho bạn, dựa trên dữ liệu trong bảng tính hiện tại mà bạn đã mở.

Ví dụ, bạn là giáo viên và vào cuối năm, bạn có một bảng tính với danh sách điểm thi. Bạn muốn tạo một hàm tùy chỉnh, để chỉ cần chạy hàm đó, một biểu đồ sẽ được tạo tự động.

Dưới đây là đoạn mã bạn cần.

function GradeChart()
{ var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); var sheet = spreadsheet.getSheets()[0]; var gradechart = sheet.newChart() .setChartType(Charts.ChartType.LINE) .addRange(sheet.getRange('A1:B11')) .setPosition(5, 5, 0, 0) .build(); sheet.insertChart(gradechart); }

Bây giờ, hãy mở từng bảng tính của học sinh và nhấp vào biểu tượng Run trong menu trong Google Scripts để tự động tạo biểu đồ.

Bất cứ khi nào bạn nhấp vào biểu tượng này, chạy tập lệnh mà bạn đã tạo sẽ được thực thi trên bảng tính "đang hoạt động" (bảng tính đang mở trên tab trình duyệt hiện tại)

Tương tự như vậy, với với các báo cáo mà bạn phải tạo thường xuyên, như hàng tuần hoặc hàng tháng, loại hàm tạo biểu đồ được tạo tự động này thực sự có thể giúp bạn tiết kiệm rất nhiều thời gian.

Tạo menu tùy chỉnh theo ý muốn trong Google Sheets

Điều gì sẽ xảy ra nếu bạn không muốn mở tập lệnh để tự động tạo biểu đồ đó? Liệu bạn có thể tạo một nút ngay trên menu chính của Google Sheets để bạn có thể click vào và tạo biểu đồ ngay lập tức không? Đương nhiên là bạn có thể làm được.

Để tạo menu tùy chỉnh, bạn cần yêu cầu bảng tính thêm mục menu mới mỗi khi nó mở ra. Bạn thực hiện việc này bằng cách tạo một hàm onOpen () trong cửa sổ Script editor phía trên hàm Gradechart mà bạn vừa tạo:

function onOpen() {
  var spreadsheet = SpreadsheetApp.getActive();
  var menuItems = [
    { name: 'Create Grade Chart...', functionName: 'GradeChart' }
  ];
  spreadsheet.addMenu('Charts', menuItems);
}

Lưu tập lệnh, sau đó tải lại bảng tính của bạn. Bạn sẽ phát hiện ra rằng bây giờ mục Menu mới của bạn đã có thêm một nút mới với tên bạn đã đặt trong đọn code trên.

Nhấp vào mục menu và hàm sẽ được chạy giống như khi bạn nhấn vào biểu tượng "Play" từ bên trong trình chỉnh sửa Google Scripts.

Gửi email tự động bằng Google Sheets

Trong phần này, chúng ta sẽ tạo một tập lệnh tự động gửi email từ bên trong Google Sheets. Ví dụ này có thể hữu ích là nếu bạn đang quản lý một nhóm lớn người dùng và cần gửi nhiều email một lúc.

Tương tự như cách bạn tạo các tập lệnh ở trên, bạn sẽ tạo một tập lệnh bằng cách vào Script editor  và tạo một hàm có tên sendEmails () , như sau:

function sendEmails() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2; // First row of data to process
  var numRows = 7; // Number of rows to process
  var dataRange = sheet.getRange(startRow, 1, numRows, 3)
  var data = dataRange.getValues();
  for (i in data) {
    var row = data[i];
    var emailAddress = row[1]; // Second column
    var message = row[2]; // Third column
    var subject = "My review notes";
    MailApp.sendEmail(emailAddress, subject, message);
  }
}

Giả sử bạn có bảng tính của mình được tổ chức như được hiển thị bên dưới.

Tập lệnh ở trên sẽ hoạt động theo từng hàng trong bảng tính và gửi một email đến địa chỉ trong cột thứ hai cùng với thông báo bạn đã nhập vào cột thứ ba.
 

Các hàm SendEmail trong Google Script cho đến nay một trong những hàm mạnh mẽ nhất trên Google Script vì sẽ giúp bạn tiết kiệm vô số thời gian soạn và gửi Email.

Tham khảo: Hướng dẫn 6 phương pháp gửi Email từ Excel bằng VBA

Tập lệnh này cho bạn thấy sức mạnh thực sự của Google Apps Script, kết hợp Gmail với Google Sheets Scripts để tự động hóa một tác vụ. 

Tất cả những ví dụ trên đây chỉ là các đoạn code với vài dòng đơn giản, Google Scripts có khả năng tự động hóa một phần hoặc toàn bộ Google Sheets và mở rộng hơn nữa là toàn bộ Google Suite.

Các hàm này có thể được thiết lập để chạy theo lịch trình hoặc bạn có thể chạy chúng theo cách thủ công bất cứ khi nào bạn cần. Các tập lệnh của Google có thể tự động hóa các tác vụ nhàm chán như gửi email hoặc gửi hóa đơn từ Google Sheets,...

Hy vọng qua bài viết này hữu ích với bạn! Bên cạnh đó, đừng quên theo dõi Gitiho.com ngay hôm nay nhé, chúng tôi còn nhiều mẹo và thủ thuật tin học văn phòng thú vị khác chờ bạn khám phá.