Hướng dẫn google sheet filter by color - bộ lọc google sheet theo màu

Google Sheets sắp xếp theo màu sắc và bộ lọc theo màu sắc là các kỹ thuật hữu ích để sắp xếp dữ liệu của bạn dựa trên màu sắc của văn bản hoặc ô trong dữ liệu.

Ví dụ: bạn có thể nêu bật các hàng dữ liệu liên quan đến một khách hàng quan trọng. Google Sheets sắp xếp theo màu sắc và bộ lọc theo màu cho phép bạn mang những hàng được tô sáng đó lên đỉnh của bộ dữ liệu của bạn hoặc thậm chí chỉ hiển thị các hàng đó.

Họ cũng thực sự hữu ích cho việc loại bỏ các bản sao trong Google Sheets.

Như một phần thưởng, họ rất dễ sử dụng. Hãy để xem làm thế nào:

Giả sử bạn có một bộ dữ liệu với các hàng được tô sáng, ví dụ như tất cả các căn hộ trong bộ dữ liệu này:

Thêm bộ lọc (biểu tượng kênh trong thanh công cụ, được hiển thị màu đỏ trong hình trên).

Trên bất kỳ cột nào, nhấp vào bộ lọc và chọn tùy chọn Sắp xếp theo màu của màu.

Bạn có thể lọc theo màu nền của ô (như màu vàng trong ví dụ này) hoặc bằng màu của văn bản.

Kết quả của việc áp dụng loại này là tất cả các hàng màu sẽ được đưa lên đầu bộ dữ liệu của bạn.

Điều này là siêu hữu ích nếu bạn muốn xem lại tất cả các mục cùng một lúc. Một lý do khác có thể là nếu chúng là các hàng trùng lặp mà bạn đã làm nổi bật mà bây giờ bạn có thể xóa.

Bộ lọc google sheets theo màu sắc

Bộ lọc Google Sheets theo phương pháp màu rất giống với phương pháp sắp xếp theo phương thức màu.

Với các bộ lọc được thêm vào bộ dữ liệu của bạn, nhấp vào một để hiển thị menu. Chọn Bộ lọc bằng màu theo màu và sau đó chọn để lọc trên màu ô nền hoặc màu văn bản.

Trong ví dụ này, tôi đã sử dụng bộ lọc Google Sheets theo màu để chỉ hiển thị các hàng được tô sáng màu vàng, giúp bạn thực sự dễ dàng xem xét chúng.

Có một tùy chọn để loại bỏ bộ lọc theo màu bằng cách đặt nó thành none, được tìm thấy trong menu màu theo bộ lọc. Tùy chọn này không được tìm thấy cho phương pháp sắp xếp theo phương thức màu.

Giải pháp tập lệnh ứng dụng

Khi tôi ban đầu xuất bản bài viết này, sắp xếp theo màu sắc và bộ lọc theo màu sắc không có sẵn trong Google Sheets, vì vậy tôi đã tạo một tập lệnh nhỏ để thêm chức năng này vào một tờ.

Chúng đã được thêm vào ngày 11 tháng 3 năm 2020. Đọc thêm tại đây trong blog cập nhật không gian làm việc của Google.

Đây là giải pháp tập lệnh ứng dụng ban đầu của tôi, còn lại ở đây vì lợi ích chung.

Với một vài dòng đơn giản của tập lệnh ứng dụng, chúng tôi có thể triển khai phiên bản của riêng mình.

Bài viết này sẽ chỉ cho bạn cách thực hiện cùng một tính năng đó trong Google Sheets.

Nó là một ý tưởng khá cơ bản.

Chúng ta cần biết màu nền của ô chúng ta muốn sắp xếp hoặc lọc bằng (Người dùng nhập 1). Sau đó, chúng ta cần biết cột nào sẽ sử dụng để thực hiện việc sắp xếp hoặc lọc (Người dùng đầu vào 2). Cuối cùng chúng ta cần phải thực hiện các loại hoặc bộ lọc.

Vì vậy, bước một là để nhắc người dùng nhập ô và cột.

Tôi đã triển khai Google Sheets này bằng màu bằng cách sử dụng hộp thoại không được mô tả, cho phép người dùng nhấp vào các ô trong tờ Google Sheet độc lập với hộp nhắc nhở. Khi người dùng đã chọn ô hoặc cột, chúng tôi lưu trữ dịch vụ này bằng dịch vụ thuộc tính để truy xuất khi chúng tôi đến để sắp xếp hoặc lọc dữ liệu.

Ứng dụng script sắp xếp theo màu

Ở cấp độ cao, chương trình của chúng tôi có các thành phần sau:

1. Menu tùy chỉnh để chạy chương trình Google Sheets by Color
2. Nhanh chóng yêu cầu người dùng cho ô màu
3. Lưu ô màu bằng dịch vụ thuộc tính
4. Lời nhắc thứ hai để hỏi người dùng cho cột sắp xếp/bộ lọc
5. Lưu cột Sắp xếp/Bộ lọc bằng Dịch vụ Thuộc tính
6. Hiển thị các lựa chọn màu sắc và cột và xác nhận
7. Truy xuất màu nền của cột Sắp xếp/Bộ lọc
8. Thêm cột trợ giúp vào dữ liệu trong trang tính với các màu nền này
9. Sắp xếp/Lọc cột trợ giúp này, dựa trên ô màu
10. Xóa các giá trị trong lưu trữ thuộc tính tài liệu

Hãy lần lượt nhìn vào từng phần này.

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

Đây chỉ đơn giản là mã tập lệnh ứng dụng Boilerplate để thêm menu tùy chỉnh vào trang Google 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 cho các lựa chọn di động và cột (2, 4 và 6 ở trên) ở trên)

Tôi sử dụng các hộp thoại không mô tả cho các lời nhắc, cho phép người dùng vẫn tương tác với trang tính 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();
  }
}

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 tập lệnh ứng dụng phía máy chủ.

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

Lưu các lựa chọn ô và cột trong cửa hàng tài sản (3 và 5)

Hai chức năng này lưu các phạm vi ô và cột mà người dùng nhấn mạnh vào kho lưu trữ tài sản của tờ:

/** 
 * 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, chúng tôi có địa chỉ ô màu (trong ký hiệu A1) và chữ cái sắp xếp/bộ lọc được lưu trong kho tài sản để truy cập trong tương lai.

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

Khi chúng tôi đã chọn cả cột màu và cột màu, luồng chương trình sẽ hướng dẫn chúng tôi thực sự đi trước và sắp xếp dữ liệu. Đây là nút trong hộp thoại thứ ba, khi được nhấp, chạy cuộc gọi này google.script.run.sortData();.

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

Và cuối cùng, chúng tôi muốn một cách để xóa các thuộc tính lưu trữ để chúng tôi có thể bắt đầu lại.

Xóa cửa hàng bất động sản (10 ở trên)

Hàm đơ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 lưu trữ thuộc tính của tờ:

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

Chạy Sắp xếp Google Sheets theo tập lệnh màu

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

Ở đây, loại công cụ màu theo hành động trong Google Sheets:

Bạn có thể thấy làm thế nào tất cả các hàng bóng màu xanh lá cây được sắp xếp trên đỉnh của bộ dữ liệu của tôi.

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 bộ dữ liệu bắt đầu trong ô A1 (vì nó dựa vào phương thức getDatarange (), tương tự).

Một số cải tiến sẽ là làm cho nó khái quát hơn (hoặc nhắc người dùng làm nổi bật bộ dữ liệu ban đầu). Tôi cũng không bao gồm bất kỳ xử lý lỗi nào, cố ý giữ cho kịch bản đơn giản nhất có thể để hỗ trợ sự hiểu biết. Tuy nhiên, đây là điều mà bạn muốn xem xét nếu bạn muốn làm cho giải pháp này mạnh mẽ hơn.

Ứng dụng script sắp xếp theo mẫu màu

Tại đây, mẫu Google Sheet để bạn sao chép.

. Chế độ, bạn sẽ có thể xem trang tính và sao chép tập lệnh trực tiếp từ trình chỉnh sửa tập lệnh.)

Nếu GitHub là thứ của bạn, thì đây là loại mã màu trong repo tập lệnh ứng dụng của tôi trên GitHub.

Bộ lọc tập lệnh ứng dụng theo màu sắc

Luồng chương trình gần như giống hệt nhau, ngoại trừ việc chúng tôi lọc dữ liệu chứ không phải sắp xếp nó. Mã gần như chính xác cũng giống nhau, ngoài các tên biến khác nhau và thực hiện bộ lọc thay vì một loại.

Thay vì sắp xếp dữ liệu, chúng tôi tạo và thêm bộ lọc vào bộ dữ liệu để chỉ hiển thị các hàng được tô màu với các màu phù hợp:

Phần bộ lọc của mã trông như thế này:

// remove existing filter to the data range
if (sheet.getFilter() !== null) {
  sheet.getFilter().remove();
}

// add new filter across whole data table
var newFilter = sheet.getDataRange().createFilter();

// create new filter criteria
var filterCriteria = SpreadsheetApp.newFilterCriteria();
filterCriteria.whenTextEqualTo(filterColor);

// apply the filter color as the filter value
newFilter.setColumnFilterCriteria(lastCol + 1, filterCriteria);

Nếu bạn muốn một thử thách, hãy xem liệu bạn có thể sửa đổi mã sắp xếp để làm việc với ví dụ bộ lọc không.

Bộ lọc tập lệnh ứng dụng theo mẫu màu

Vui lòng sao chép bộ lọc Google Sheets theo mẫu tại đây.

. Chế độ, bạn sẽ có thể xem trang tính và sao chép tập lệnh trực tiếp từ trình chỉnh sửa tập lệnh.)

Nếu GitHub là thứ của bạn, thì đây là loại mã màu trong repo tập lệnh ứng dụng của tôi trên GitHub.