Viết code trong Google Sheets giúp làm việc nhanh và hiệu quả

Google Sheets ngày càng phát triển mạnh mẽ và trở thành một công cụ vô giá đối với hầu hết các cơ quan và tổ chức. Nhưng việc lặp đi lặp lại một số tác vụ trên Google Sheets khiến bạn khó chịu khiến bạn muốn có một công cụ để tự động hóa các tác vụ. Dưới đây là các đoạn Viết code trong google sheet giúp loại bỏ các tác vụ lặp đi lặp lại, giúp công việc của bạn nhanh hơn và hiệu quả hơn nhiều. Hãy cùng Công Ty Quảng Cáo Marketing Online Limoseo tìm hiểu nhé!

1. Google Sheet là gì?

Google Sheet – Ứng dụng web G Suite của Google không còn xa lạ với dân văn phòng hay dân web muốn sử dụng Excel miễn phí trên nền tảng web. Mặc dù được xây dựng trên nền tảng của Google Sheet, nhưng nó đã hỗ trợ nhiều tính năng cơ bản của Excel. Limoseo không chắc liệu Google Sheet có thể thay thế hoàn toàn Microsoft Excel hay không nhưng nó có thể thay thế khoảng 70-80% tính năng của Microsoft Excel.

2. Google Sheets script là gì?

Google Sheets script là các đoạn code mà bạn có thể viết trong Google Sheet để tăng sức mạnh cho trang của mình. Google Sheets script được viết bằng JavaScript và vì JavaScript đang trở nên phổ biến hơn nên bạn có thể quen với nó. 

Viết Google Scripts rất giống với viết VBA trong Microsoft Excel để tạo chương trình. “Linh hồn” của tập lệnh Google Sheets là Google Apps Script và nó cũng hoạt động với các dịch vụ khác của Google. Dưới đây là 4 tập lệnh thực sự thể hiện sức mạnh của Viết code trong google sheet.

Một ví dụ về việc sử dụng Apps Script trong Google Sheet Giả sử ta đặt giá trị thành 10.000 và ô đầu tiên trên Sheet: 

• Trên màn hình trang Google Sheet, nhấp vào Tools > Script edit. 
• Màn hình Apps Script sẽ xuất hiện nơi bạn có thể nhập mã JavaScript. 
• Màn hình Apps Script Editor xuất hiện. Tên project nên lưu lại cho dễ nhớ – ở đây mình lưu ví dụ này dưới dạng Demo Apps script. 
• Sau khi mã hoàn tất, ta bấm Run để chạy thử chức năng. Hãy bắt đầu code những dòng code đầu tiên để chúng ta có thể tương tác với Google Sheet.

function myFunction() { // lấy sheet đang active var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); // lấy giá trị tại cell đầu tiên của sheet đang active var v = ss.getRange(1, 1).getValue(); // Hiện thị giá trị đó bằng Dialog SpreadsheetApp.getUi().alert(v);}

• Copy và dán mã code ở trên và nhấp vào Run. 

Chú ý: Lần đầu tiên bạn chạy code, Google cần bạn cấp quyền để chạy code. 

• Sau khi nhấn nút Run, quay trở lại tab Sheet, trên màn hình sẽ hiển thị kết quả. Vậy là đã kết nối thành công. 

Ta có thể tạo một menu trong Google Sheet ngay trên màn hình và khi sử dụng ta chỉ cần nhấp chuột vào thôi. 

Bây giờ ta sẽ tạo một menu cho hàm của mình. Ở đây ta sẽ tạo một menu có tên “Cloud ACE Demo” và nó có một menu phụ có tên là “My Function”.

// Hàm sẽ tự động gọi khi Sheet được open.function onOpen() { var ui = SpreadsheetApp.getUi(); ui.createMenu(“Cloud ACE Demo “) .addItem(“My Function “, “myFunction”) .addToUi();}
Bạn hãy copy mã code ở trên vào. Và quay lại màn hình Sheet, nhấn F5 để làm mới trang. Chúng ta có thể thấy rằng menu Sheet có một menu khác. Điều đó thật tuyệt phải không? Bây giờ ta click vào My Function và ta cũng sẽ nhận được kết quả như mong đợi. 

Với ví dụ đơn giản này, bạn có thể phát triển ứng dụng tạo báo cáo hàng ngày hoặc gửi danh sách địa chỉ email qua email hoặc tạo các chức năng bổ sung dành riêng cho doanh nghiệp của mình… ngay trong Google Sheet . Dưới đây Limoseo sẽ “mách nhỏ” cho bạn 4 google script giúp bạn Viết code trong google sheet thuận tiện hơn nhé!

3. Top 4 Google Script giúp cho Google Sheets mạnh mẽ hơn

3.1. Tạo hàm tùy chỉnh của riêng bạn

Một trong những cách dễ nhất để tạo Google Scripts có thể cải thiện đáng kể trải nghiệm Viết code trong google sheet của bạn là tạo các hàm tùy chỉnh. Google Trang tính đã có rất nhiều tính năng. 

Để xem những tính năng phổ biến nhất, hãy nhấp vào biểu tượng menu > Function. 

Bấm vào More Function sẽ hiển thị 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 chức năng khác. Tuy nhiên, Google Script cung cấp cho bạn sự linh hoạt để tạo các công thức tùy chỉnh, cá nhân hóa chính mình. Giả sử bạn thường nhập dữ liệu từ bộ điều nhiệt kỹ thuật số trong khi làm việc, nhưng bộ điều nhiệt được đặt thành độ Celsius (°C). Bạn có thể tạo công thức tùy chỉnh của riêng mình để chuyển đổi độ Celsius sang độ Fahrenheit (°F) để bạn có thể tự động chuyển đổi tất cả các giá trị đã nhập bằng một cú nhấp chuột. 

Tạo hàm tùy chỉnh đầu tiên của bạn bằng cách mở trình chỉnh sửa Script. Để thực hiện việc này, hãy nhấp vào Tools rồi chọn Script Editor. Bạn sẽ thấy một màn hình dự án nơi bạn có thể nhập mã JavaScript của mình. Thay thế những thứ có  trong cửa sổ này bằng hàm tùy chỉnh của bạn. Tên của hàm giống với tên bạn nhập trong ô Google Trang tính sau ký hiệu “=” để gọi được công thức. Hàm chuyển đổi độ Celsius thành độ Fahrenheit trông giống như sau:

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

Paste hàm trên vào cửa sổ code, chọn File và Save, đặt tên của nó thành “CelsiusConverter” và bấm vào OK. Đó là tất cả những gì bạn cần làm! Bây giờ, khi bạn muốn sử dụng hàm mới, hãy nhập ký hiệu = theo sau là hàm với số lượng đầu vào cần chuyển đổi và nhấn Enter để xem kết quả.

3.2. Biểu đồ tự động tạo

Giả sử bạn phải làm việc với một bảng dữ liệu mới mỗi tháng. Nếu bạn muốn tự động tạo biểu đồ bằng cách sử dụng dữ liệu từ một trang tính, bạn có thể làm như vậy bằng cách tạo một hàm tạo biểu đồ mới dựa trên dữ liệu từ một trang tính đang mở.

Giả sử bạn là giáo viên và vào cuối năm, bạn có một bảng liệt kê điểm kiểm tra hàng tháng của từng học sinh. Bạn chỉ muốn chạy một chức năng trên bảng tính đó và tạo biểu đồ trong vài giây. Script để làm điều này sẽ là:

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ở bảng tính từng học sinh và tự động tạo biểu đồ bằng cách nhấp vào biểu tượng Run trong menu Google Scripts. Mỗi lần bạn nhấp vào biểu tượng Run, script được tạo trong bảng tính sẽ được kích hoạt (mở ra tab trình duyệt hiện tại). Đối với các báo cáo bạn cần tạo thường xuyên, ví dụ: hàng tuần hoặc hàng tháng, các loại email được tạo tự động này thực sự có thể tiết kiệm rất nhiều thời gian.

3.3. Tạo menu tùy chỉnh

Nếu bạn không muốn mở script để tự động tạo biểu đồ thì sao? Điều gì sẽ xảy ra nếu bạn muốn hàm này ngay trong tầm tay của mình trong hệ thống danh mục Google Trang tính? May mắn thay, bạn cũng có thể làm được. Để tạo một danh mục tùy chỉnh, hãy “ra lệnh” cho bảng tính thêm một mục menu mới mỗi khi nó mở ra. Bạn có thể làm điều này bằng cách tạo một hàm onOpen() phía trên hàm Gradechart mới được tạo trong cửa sổ Script Editor.

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

Lưu script và tải mới lại bảng tính. Bạn sẽ thấy rằng một mục menu mới hiện xuất hiện với tên được chỉ định trong script. Nhấp vào menu và bạn sẽ thấy mục menu cho hàm của mình. Nhấp vào mục menu và hàm sẽ được chạy giống như nhấp vào biểu tượng Run trong Google Script!

3.4. Gửi báo cáo tự động

Ví dụ script cuối cùng trong bài viết này là về gửi email từ Google Sheet. Phương pháp này có thể hữu ích nếu bạn quản lý một nhóm lớn người và cần gửi nhiều email về cùng một chủ đề. Có thể bạn đã thực hiện đánh giá hiệu suất cho từng thành viên trong nhóm của mình và ghi lại nhận xét về đánh giá của từng người trong bảng tính Google. 

Sẽ thật tuyệt nếu chỉ cần chạy một script và tự động gửi nhận xét cho 50 hoặc 60 nhân viên cùng một lúc mà không cần phải tạo thủ công từng email riêng lẻ phải không? Đây là sức mạnh của Viết code google sheet. Cũng giống như cách bạn tạo script ở trên, bạn có thể tạo chúng bằng cách vào phần soạn thảo script và tạo hàm sendEmails() như sau:

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);    }    }

Hàm SendEmail trong Google Scripts là một trong những hàm vô cùng mạnh mẽ trong Google Scripts vì nó mở ra cả một thế giới tự động hóa email tiết kiệm thời gian. Script này cho bạn thấy sức mạnh thực sự của Google Apps Scripting bằng cách kết hợp Gmail với Google Sheets Script để tự động hóa một tác vụ. Mặc dù bạn đã thấy Script trong Google Trang tính, nhưng tốt nhất bạn nên tận dụng sức mạnh của script trong Google Suite.

4. Code tạo macro trong Google Sheets

Dưới đây là 7 code google sheets giúp bạn làm việc hiệu quả hơn, cùng thực hiện nhé.

4.1. Viết mã code tạo macro chuyển công thức thành giá trị trên trang Google Sheets hiện tại

//1. convert all formulas to values in the active sheet function formulasToValuesActiveSheet() {  var sheet = SpreadsheetApp.getActiveSheet();  var range = sheet.getDataRange();  range.copyValuesToRange(sheet, 1, range.getLastColumn(), 1, range.getLastRow()); };

4.2. Viết mã code để tạo macro chuyển đổi công thức thành giá trị trên bất kỳ trang tính nào trong Workbook Google Sheets

//2. convert all formulas to values in every sheet of the Google Sheet function formulasToValuesGlobal() {  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();  sheets.forEach(function(sheet) {    var range = sheet.getDataRange();    range.copyValuesToRange(sheet, 1, range.getLastColumn(), 1, range.getLastRow());  }); };

4.3. Viết mã code tạo macro giúp sắp xếp trang tính theo thứ tự

//3. sort sheets alphabetically function sortSheets() {  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();  var sheets = spreadsheet.getSheets();  var sheetNames = [];  sheets.forEach(function(sheet,i) {    sheetNames.push(sheet.getName());  });  sheetNames.sort().forEach(function(sheet,i) {    spreadsheet.getSheetByName(sheet).activate();    spreadsheet.moveActiveSheet(i + 1);  }); };

4.4. Viết mã code tạo macro hiện hàng và cột ẩn trong trang tính hiện tại của Google Sheets

//4. unhide all rows and columns in current Sheet data range function unhideRowsColumnsActiveSheet() {  var sheet = SpreadsheetApp.getActiveSheet();  var range = sheet.getDataRange();  sheet.unhideRow(range);  sheet.unhideColumn(range); }

4.5. Viết mã code tạo macro hiện hàng và cột ẩn trong tất cả các trang tính trong Workbook Google Sheets

//5. unhide all rows and columns in data ranges of entire Google Sheet function unhideRowsColumnsGlobal() {  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();  sheets.forEach(function(sheet) {    var range = sheet.getDataRange();    sheet.unhideRow(range);    sheet.unhideColumn(range);  }); };

4.6. Viết mã code tạo macro chuyển tất cả màu thẻ trang tính Google Sheets (sheet tab) thành màu đỏ

//6. set all Sheets tabs to red function setTabColor() {  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();  sheets.forEach(function(sheet) {    sheet.setTabColor(“ff0000”);  }); };

4.7. Viết mã code tạo macro hiện tất cả các trang tính ẩn trong Google Sheets

//9. Unhide all sheets function unhideAllSheets() {  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();  sheets.forEach(function(sheet) {    sheet.showSheet();  }); };

Ở bài viết trên, Công Ty Quảng Cáo Marketing Online Limoseo chia sẻ cùng bạn các đoạn Viết code trong google sheet để tạo macro giúp công việc của bạn năng suất, hiệu quả và tiết kiệm thời gian hơn bằng cách tránh lặp lại nhàm chán. Limoseo chúc bạn thành công trong việc áp dụng điều này vào công việc của mình!