Hướng dẫn cách sắp xếp theo màu và lọc màu trong Google Sheets với Apps Script

Trần Văn Huệ
Trần Văn Huệ
Aug 12 2020

Thay vì sử dụng các thao tác dài dòng để sắp xếp dữ liệu trong Google Sheets theo màu và lọc theo màu gốc. Dưới đây Gitiho sẽ hướng dẫn sử dụng Apps Script để thực hiện.

Như hình ảnh dưới đây cho thấy việc sử dụng Apps Script cho phép bạn sắp xếp và lọc các tập dữ liệu theo màu ô vô cùng đơn giản. Nếu thích bạn cũng có thể tự tạo Apps Script riêng để thực hiện chức năng tương tự. 

Hướng dẫn cách sắp xếp theo màu và lọc màu trong Google Sheets với Apps Script

Vì vậy, thông qua bài viết này Gitiho sẽ hướng dẫn các bạn cách triển khai tính năng tương tự đó trong Google Sheets với Apps Script riêng và chương trình có các phần sau:

1. Menu tùy chỉnh để chạy chương trình Google Sheets sắp xếp theo màu

2. Nhắc người dùng cung cấp ô màu

3. Lưu ô màu bằng Properties Service

4. Hỏi người dùng về sắp xếp hoặc lọc cột

5. Lưu cột sắp xếp hoặc lọc bằng Properties Service

6. Hiển thị màu và cột, lựa chọn và xác nhận

7. Lấy màu nền của cột sắp xếp hoặc lọc

8. Thêm cột trợ giúp vào dữ liệu trong Sheets với các màu nền này

9. Sắp xếp hoặc lọc cột trợ giúp này, dựa trên ô màu

10. Xóa các giá trị trong kho thuộc tính tài liệu

Bây giờ chúng ta hãy xem xét từng phần

Thêm một menu tùy chỉnh (1)

Đây chỉ đơn giản là mã Apps Script soạn sẵn để thêm menu tùy chỉnh vào Google Sheets tính của bạn:

/**
 * Create custom menu
 */
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Color Tool')
        .addItem('Sort by color...', 'sortByColorSetupUi')
        .addItem('Clear Ranges','clearProperties')
        .addToUi();
}


Nhắc người dùng về các lựa chọn ô và cột (2, 4 và 6 ở trên)

Câu lệnh dưới đây cho phép người dùng vẫn tương tác với Sheets và nhấp trực tiếp vào các ô họ muốn chọn.

/**
 * Sort By Color Setup Program Flow
 * Check whether color cell and sort columnn have been selected
 * If both selected, move to sort the data by color
 */
function sortByColorSetupUi() {
   
  var colorProperties = PropertiesService.getDocumentProperties();
  var colorCellRange = colorProperties.getProperty('colorCellRange');
  var sortColumnLetter = colorProperties.getProperty('sortColumnLetter');
  var title='No Title';
  var msg = 'No Text';
   
  //if !colorCellRange
  if(!colorCellRange)  {
    title = 'Select Color Cell';
    msg = '

Please click on cell with the background color you want to sort on and then click OK

'
;
    msg += '';
    dispStatus(title, msg);
  }
   
  //if colorCellRange and !sortColumnLetter
  if (colorCellRange && !sortColumnLetter) {
       
      title = 'Select Sort Column';
      msg = '

Please highlight the column you want to sort on, or click on a cell in that column. Click OK when you are ready.

'
;
      msg += '';
      dispStatus(title, msg);
  }
   
  // both color cell and sort column selected
  if(colorCellRange && sortColumnLetter) {
     
    title= 'Displaying Color Cell and Sort Column Ranges';
    msg = '

Confirm ranges before sorting:

'
;
    msg += 'Color Cell Range: ' + colorCellRange + '
Sort Column: '
+ sortColumnLetter + '
'
;
    msg += '
'
;
    msg += '

'
;
    dispStatus(title,msg);
     
  }
}
 
/**
 * display the modeless dialog box
 */
function dispStatus(title,html) {
   
  var title = typeof(title) !== 'undefined' ? title : 'No Title Provided';
  var html = typeof(html) !== 'undefined' ? html : '

No html provided.

'
;
  var htmlOutput = HtmlService
     .createHtmlOutput(html)
     .setWidth(350)
     .setHeight(200);
  
  SpreadsheetApp.getUi().showModelessDialog(htmlOutput, title);
 
}
 
/**
 * helper function to switch between dialog box 1 (to select color cell) and 2 (to select sort column)
 */
function sortByColorHelper(mode) {
   
  var mode = (typeof(mode) !== 'undefined')? mode : 0;
  switch(mode)
  {
    case 1:
      setColorCell();
      sortByColorSetupUi();
      break;
    case 2:
      setSortColumn();
      sortByColorSetupUi();
      break;
    default:
      clearProperties();
  }
}


Trong đó các nút trên hộp thoại sử dụng API google.script.run phía máy khách để gọi các chức năng Apps Script phía máy chủ.

Sau đó, google.script.host.close () cũng là một JavaScript API phía máy khách đóng hộp thoại hiện tại.

Lưu lựa chọn ô và cột (3 và 5)

Hai hàm này lưu các phạm vi ô và cột mà người dùng đánh dấu vào cửa hàng thuộc tính (Property store) của Sheets:

/**
 * saves the color cell range to properties
 */
function setColorCell() {
   
  var sheet = SpreadsheetApp.getActiveSheet();
  var colorCell = SpreadsheetApp.getActiveRange().getA1Notation();
  var colorProperties = PropertiesService.getDocumentProperties();
  colorProperties.setProperty('colorCellRange', colorCell);
 
}
 
/**
 * saves the sort column range in properties
 */
function setSortColumn() {
   
  var sheet = SpreadsheetApp.getActiveSheet();
  var sortColumn = SpreadsheetApp.getActiveRange().getA1Notation();
  var sortColumnLetter = sortColumn.split(':')[0].replace(/\d/g,'').toUpperCase(); // find the column letter
  var colorProperties = PropertiesService.getDocumentProperties();
  colorProperties.setProperty('sortColumnLetter', sortColumnLetter);
   
}


Kết quả của việc chạy các chức năng này sẽ cho chúng ta địa chỉ ô màu (trong ký hiệu A1) và ký tự cột sắp xếp hoặc lọc được lưu trong cửa hàng thuộc tính (Property store) để truy cập trong tương lai.

Sắp xếp dữ liệu (7, 8 và 9 ở trên)

Khi đã chọn cả ô màu và cột sắp xếp, tiếp theo chương trình sẽ hướng dẫn sắp xếp dữ liệu. Đây là nút trong hộp thoại thứ ba, khi được nhấp vào nó sẽ chạy lệnh gọi:

google.script.run.sortData();.

Trong đó hàm sortData được xác định như sau:

/**
 * sort the data based on color cell and chosen column
 */
function sortData() {
   
  // get the properties
  var colorProperties = PropertiesService.getDocumentProperties();
  var colorCell = colorProperties.getProperty('colorCellRange');
  var sortColumnLetter = colorProperties.getProperty('sortColumnLetter');
 
  // extracts column letter from whatever range has been highlighted for the sort column
   
  // get the sheet
  var sheet = SpreadsheetApp.getActiveSheet();
  var lastRow = sheet.getLastRow();
  var lastCol = sheet.getLastColumn();
   
  // get an array of background colors from the sort column
  var sortColBackgrounds = sheet.getRange(sortColumnLetter + 2 + ":" + sortColumnLetter + lastRow).getBackgrounds(); // assumes header in row 1
   
  // get the background color of the sort cell
  var sortColor = sheet.getRange(colorCell).getBackground();
   
  // map background colors to 1 if they match the sort cell color, 2 otherwise
  var sortCodes = sortColBackgrounds.map(function(val) {
    return (val[0] === sortColor) ? [1] : [2];
  });
   
  // add a column heading to the array of background colors
  sortCodes.unshift(['Sort Column']);
   
  // paste the background colors array as a helper column on right side of data
  sheet.getRange(1,lastCol+1,lastRow,1).setValues(sortCodes);
  sheet.getRange(1,lastCol+1,1,1).setHorizontalAlignment('center').setFontWeight('bold').setWrap(true);
   
  // sort the data
  var dataRange = sheet.getRange(2,1,lastRow,lastCol+1); 
  dataRange.sort(lastCol+1);
   
  // add new filter across whole data table
  sheet.getDataRange().createFilter();
 
  // clear out the properties so it's ready to run again
  clearProperties();
}


Cuối cùng là xóa kho lưu trữ thuộc tính (Properties store) để có thể bắt đầu lại.

Xóa các giá trị trong kho thuộc tính tài liệu (10 ở trên)

Chức năng đơn giản này sẽ xóa tất cả các cặp khóa/giá trị được lưu trữ trong kho thuộc tính của Sheets:

/**
 * clear the properties
 */
function clearProperties() {
  PropertiesService.getDocumentProperties().deleteAllProperties();
}


Chạy Google Sheets sắp xếp màu theo Script

Nếu đặt tất cả các đoạn mã này lại với nhau trong tệp Code.gs, bạn có thể chạy onOpen, cấp quyền cho tập lệnh và sau đó chạy công cụ sắp xếp theo màu từ menu tùy chỉnh mới.

Hình ảnh dưới đây cho thấy công cụ sắp xếp theo màu đang hoạt động trong Google Sheets:

Hướng dẫn cách sắp xếp theo màu và lọc màu trong Google Sheets với Apps Script

Bạn có thể thấy cách tất cả các hàng màu xanh lá cây được sắp xếp lên đầu tập dữ liệu.

Lưu ý rằng tính năng sắp xếp theo màu này được thiết lập để hoạt động với các tập dữ liệu bắt đầu trong ô A1 (vì nó dựa vào phương thức getDataRange () và cách này cũng hoạt động tương tự).

CÓ THỂ BẠN CŨNG QUAN TÂM
Hướng dẫn tạo biểu đồ đường trong Google Sheets
Cách kiểm tra xem có bao nhiêu bản sao trong Google Sheets
Cách khắc phục lỗi về ô và dòng trong Google Sheets

Cuối cùng, đừng quên tham khảo khóa học Google Sheets của Gitiho bạn nhé! Hẹn gặp lại các bạn trong những bài viết tiếp theo. 


0/5 - (0 bình chọn)

0/5 - (0 bình chọn)

Bài viết liên quan

Hướng dẫn tạo danh sách tùy chọn Drop List trong Google Sheets

Hướng dẫn tạo danh sách tùy chọn Drop List trong Google Sheets

Hướng dẫn cách cố định hàng/cột trong Google Sheets

Hướng dẫn cách cố định hàng/cột trong Google Sheets

Hướng dẫn cách bảo vệ trang tính trong Google Sheets

Hướng dẫn cách bảo vệ trang tính trong Google Sheets

Cách khắc phục lỗi về ô và dòng trong Google Sheets

Cách khắc phục lỗi về ô và dòng trong Google Sheets

Tổng hợp các phím tắt thông dụng trong Google Sheets

Tổng hợp các phím tắt thông dụng trong Google Sheets

Hướng dẫn tô màu xen kẽ giữa các hàng trong Google Sheets

Hướng dẫn tô màu xen kẽ giữa các hàng trong Google Sheets

@ 2020 - Bản quyền của Công ty cổ phần công nghệ giáo dục Gitiho Việt Nam
Giấy chứng nhận Đăng ký doanh nghiệp số: 0109077145, cấp bởi Sở kế hoạch và đầu tư TP. Hà Nội