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

GiGiKing·Tháng tư 2, 2024·0 comments
Video how to sort google sheet by color

Bạn có biết cách sắp xếp và lọc dữ liệu dựa trên màu sắc trong Google Sheets không? Tính năng tiện ích này giúp bạn tổ chức dữ liệu dựa trên màu sắc của văn bản hoặc ô trong bảng tính.

Hãy tưởng tượng rằng bạn đã làm nổi bật những dòng dữ liệu liên quan đến một khách hàng quan trọng. Với việc sắp xếp và lọc theo màu sắc trong Google Sheets, bạn có thể dễ dàng đưa những dòng này lên đầu danh sách dữ liệu của mình hoặc thậm chí chỉ hiển thị những dòng này.

Các phương pháp này cũng rất hữu ích để loại bỏ các bản sao trong Google Sheets.

Và tất cả điều tuyệt vời nhất là chúng rất dễ sử dụng. Hãy xem cách thực hiện:

Sắp xếp theo màu trong Google Sheets

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

Ensemble de données Google Sheets

Thêm bộ lọc (biểu tượng hình cái nơ treo trong thanh công cụ, như được đánh dấu bằng màu đỏ trong hình ả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”.

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

Kết quả của việc áp dụng sắp xếp này là tất cả các dòng được tô màu sẽ được đặt lên đầu danh sách dữ liệu của bạn.

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

Lọc theo màu trong Google Sheets

Phương pháp lọc theo màu trong Google Sheets rất giống với việc sắp xếp theo màu.

Sau khi bạn đã thêm bộ lọc vào tập dữ liệu của mình, hãy nhấp vào nó để hiển thị menu. Chọn “Lọc theo màu” và sau đó chọn lọc theo màu nền ô hoặc màu văn bản.

Trong ví dụ này, tôi đã sử dụng lọc theo màu trong Google Sheets để chỉ hiển thị những dòng được làm nổi bật màu vàng, điều này giúp dễ dàng xem xét chúng.

Có một tùy chọn để xóa bộ lọc theo màu bằng cách đặt nó thành “không có”, được tìm thấy trong menu “Lọc theo màu”. Tùy chọn này không có sẵn đối với phương pháp sắp xếp theo màu.

Giải pháp với Apps Script

Khi tôi ban đầu công bố bài viết này, việc sắp xếp và lọc theo màu không được hỗ trợ mặc định trong Google Sheets, vì vậy tôi đã tạo một đoạn mã nhỏ để thêm tính năng này vào một bảng tính.

Chúng đã được thêm vào vào ngày 11 tháng 3 năm 2020. Bạn có thể đọc thêm thông tin tại đây trong blog về cập nhật của Google Workspace.

Đây là giải pháp gốc của tôi với Apps Script, tôi để lại ở đây cho lợi ích chung.

Với một vài dòng mã đơn giản của Apps Script, chúng ta có thể triển khai phiên bản riêng của 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.

Sắp xếp theo màu bằng Apps Script

Hoạt động chung của chương trình của chúng ta:

  1. Tạo menu tùy chỉnh để chạy chương trình sắp xếp theo màu trong Google Sheets.
  2. Hộp thoại yêu cầu người dùng chọn ô màu.
  3. Lưu ô màu bằng cách sử dụng dịch vụ Properties.
  4. Hộp thoại thứ hai yêu cầu người dùng chọn cột sắp xếp.
  5. Lưu cột sắp xếp bằng cách sử dụng dịch vụ Properties.
  6. Hiển thị lựa chọn màu và cột và xác nhận.
  7. Truy xuất màu nền từ cột sắp xếp.
  8. Thêm một cột trợ giúp vào dữ liệu bảng với những 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 bộ nhớ của Document Properties.

Hãy xem xét từng phần này chi tiết hơn.

Thêm một menu tùy chỉnh (bước 1)

Đây là mã Apps Script chuẩn để thêm menu tùy chỉnh vào bảng tính Google của bạn:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Outils màu')
    .addItem('Sắp xếp theo màu...', 'sortByColorSetupUi')
    .addItem('Xóa phạm vi', 'clearProperties')
    .addToUi();
}

Yêu cầu người dùng chọn ô và cột (các bước 2, 4 và 6)

Tôi sử dụng hộp thoại không chế độ để hỏi người dùng với các câu hỏi, cho phép người dùng tương tác với bảng tính và nhấp trực tiếp vào ô mà họ muốn chọn.

function sortByColorSetupUi() {
  var colorProperties = PropertiesService.getDocumentProperties();
  var colorCellRange = colorProperties.getProperty('colorCellRange');
  var sortColumnLetter = colorProperties.getProperty('sortColumnLetter');
  var title = 'Không có tiêu đề';
  var msg = 'Không có nội dung';

  // Nếu colorCellRange không tồn tại
  if (!colorCellRange) {
    title = 'Chọn ô màu';
    msg = '<p>Vui lòng nhấp vào ô có màu nền mà bạn muốn sắp xếp, sau đó nhấp vào OK.</p>';
    msg += '<input type="button" value="OK" onclick="google.script.run.sortByColorHelper(1); google.script.host.close();" />';
    dispStatus(title, msg);
  }

  // Nếu colorCellRange tồn tại và sortColumnLetter không tồn tại
  if (colorCellRange && !sortColumnLetter) {
    title = 'Chọn cột sắp xếp';
    msg = '<p>Vui lòng chọn cột mà bạn muốn sắp xếp hoặc nhấp vào một ô trong cột đó. Nhấp vào OK khi bạn đã sẵn sàng.</p>';
    msg += '<input type="button" value="OK" onclick="google.script.run.sortByColorHelper(2); google.script.host.close();" />';
    dispStatus(title, msg);
  }

  // Nếu colorCellRange và sortColumnLetter tồn tại
  if (colorCellRange && sortColumnLetter) {
    title = 'Hiển thị phạm vi ô màu và cột sắp xếp';
    msg = '<p>Xác nhận phạm vi trước khi sắp xếp:</p>';
    msg += 'Phạm vi ô màu: ' + colorCellRange + '<br />';
    msg += 'Cột sắp xếp: ' + sortColumnLetter + '<br />';
    msg += '<br /><input type="button" value="Sắp xếp theo màu" onclick="google.script.run.sortData(); google.script.host.close();" />';
    msg += '<br /><br /><input type="button" value="Xóa lựa chọn và thoát" onclick="google.script.run.clearProperties(); google.script.host.close();" />';
    dispStatus(title, msg);
  }
}

// Hiển thị hộp thoại không chế độ
function dispStatus(title, html) {
  var title = typeof (title) !== 'undefined' ? title : 'Không có tiêu đề';
  var html = typeof (html) !== 'undefined' ? html : '<p>Không có nội dung.</p>';
  var htmlOutput = HtmlService
    .createHtmlOutput(html)
    .setWidth(350)
    .setHeight(200);
  SpreadsheetApp.getUi().showModelessDialog(htmlOutput, title);
}

// Hàm trợ giúp để chuyển từ hộp thoại 1 (chọn ô màu) sang hộp thoại 2 (chọn cột sắp xếp)
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 hàm phía máy chủ Apps Script.

Sau đó, google.script.host.close() cũng là một 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 bộ lưu trữ thuộc tính (các bước 3 và 5)

Hai hàm này lưu các phạm vi ô màu và cột sắp xếp được người dùng chọn vào bộ lưu trữ thuộc tính của bảng tính:

// Lưu phạm vi ô màu vào thuộc tính
function setColorCell() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var colorCell = SpreadsheetApp.getActiveRange().getA1Notation();
  var colorProperties = PropertiesService.getDocumentProperties();
  colorProperties.setProperty('colorCellRange', colorCell);
}

// Lưu phạm vi cột sắp xếp vào thuộc tính
function setSortColumn() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var sortColumn = SpreadsheetApp.getActiveRange().getA1Notation();
  var sortColumnLetter = sortColumn.split(':')[0].replace(/(d)/g, '').toUpperCase(); //Tìm chữ cái của cột
  var colorProperties = PropertiesService.getDocumentProperties();
  colorProperties.setProperty('sortColumnLetter', sortColumnLetter);
}

Bằng cách chạy những hàm này, chúng ta sẽ lưu địa chỉ ô màu (theo định dạng A1) và chữ cái của cột sắp xếp / lọc vào bộ lưu trữ thuộc tính của bảng tính để sử dụng sau này.

Sắp xếp dữ liệu (các bước 7, 8 và 9)

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

Hàm sortData được định nghĩa như sau:

// Sắp xếp dữ liệu dựa trên ô màu và cột đã chọn
function sortData() {
  // Lấy các thuộc tính
  var colorProperties = PropertiesService.getDocumentProperties();
  var colorCell = colorProperties.getProperty('colorCellRange');
  var sortColumnLetter = colorProperties.getProperty('sortColumnLetter');

  // Trích xuất chữ cái cột từ phạm vi đã chọn cho cột sắp xếp
  var sheet = SpreadsheetApp.getActiveSheet();
  var lastRow = sheet.getLastRow();
  var lastCol = sheet.getLastColumn();

  // Lấy mảng màu nền từ cột sắp xếp
  var sortColBackgrounds = sheet.getRange(sortColumnLetter + 2 + ":" + sortColumnLetter + lastRow).getBackgrounds();

  // Giả sử hàng đầu tiên là tiêu đề
  // Lấy màu nền của ô sắp xếp
  var sortColor = sheet.getRange(colorCell).getBackground();

  // Ánh xạ màu nền thành 1 nếu phù hợp với màu nền của ô sắp xếp, 2 nếu không phù hợp
  var sortCodes = sortColBackgrounds.map(function (val) {
    return (val[0] === sortColor) ? [1] : [2];
  });

  // Thêm một tiêu đề cột vào mảng màu nền
  sortCodes.unshift(['Cột sắp xếp']);

  // Dán mảng màu nền làm cột trợ giúp bên phải bảng dữ liệu
  sheet.getRange(1, lastCol + 1, lastRow, 1).setValues(sortCodes);
  sheet.getRange(1, lastCol + 1, 1, 1).setHorizontalAlignment('center').setFontWeight('bold').setWrap(true);

  // Sắp xếp dữ liệu
  var dataRange = sheet.getRange(2, 1, lastRow, lastCol + 1);
  dataRange.sort(lastCol + 1);

  // Tạo một bộ lọc mới cho toàn bộ bảng dữ liệu
  sheet.getDataRange().createFilter();

  // Xóa các giá trị trong bộ nhớ của Document Properties
  clearProperties();
}

Và cuối cùng, chúng ta muốn có một cách để đặt lại bộ nhớ thuộc tính để có thể bắt đầu lại.

Xóa bộ nhớ thuộc tính (bước 10)

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 bộ nhớ thuộc tính của bảng tính:

// Xóa thuộc tính
function clearProperties() {
  PropertiesService.getDocumentProperties().deleteAllProperties();
}

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

Nếu bạn tập hợp tất cả các đoạn mã này vào tệp Code.gs của mình, bạn nên có thể chạy onOpen, cho phép script và sau đó chạy công cụ sắp xếp theo màu từ menu tùy chỉnh mới.

Dưới đây là công cụ sắp xếp theo màu hoạt động trong Google Sheets:

Trier par couleur dans Google Sheets

Bạn có thể thấy cách tất cả các dòng màu xanh lá cây được sắp xếp ở đầu danh sách 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 cấu hình để hoạt động với các tập dữ liệu bắt đầu tại ô A1 (vì nó sử dụng phương thức getDataRange() làm điều tương tự). Một số cải tiến có thể bao gồm làm cho nó tổng quát hơn (hoặc yêu cầu người dùng làm nổi bật tập dữ liệu ban đầu). Tôi cũng không bao gồm xử lý lỗi, có chủ ý để làm cho đoạn mã càng đơn giản càng tốt để dễ hiểu. Tuy nhiên, đây là một điều bạn cần xem xét nếu bạn muốn làm cho giải pháp này mạnh mẽ hơn.

Lọc theo màu bằng Apps Script

Luồng chương trình gần như giống nhau, chỉ khác là chúng tôi lọc dữ liệu thay vì sắp xếp. Mã hầu như giống nhau, chỉ khác tên biến và hiện thực bộ lọc thay vì sắp xếp.

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

Filtrer par couleur dans Google Sheets

Phần mã liên quan đến bộ lọc sẽ trông như thế này:

// Xóa bộ lọc hiện có khỏi phạm vi dữ liệu nếu cần
if (sheet.getFilter() !== null) {
  sheet.getFilter().remove();
}

// Thêm bộ lọc mới cho toàn bộ tập dữ liệu
var newFilter = sheet.getDataRange().createFilter();

// Tạo các tiêu chí lọc mới
var filterCriteria = SpreadsheetApp.newFilterCriteria();
filterCriteria.whenTextEqualTo(filterColor); // Áp dụng giá trị màu của bộ lọc dưới dạng giá trị bộ lọc
newFilter.setColumnFilterCriteria(lastCol + 1, filterCriteria);

Nếu bạn thích thách thức, hãy thử thay đổi mã sắp xếp để hoạt động với ví dụ lọc.

Mẫu lọc theo màu với Apps Script

Hãy nhớ sao chép mẫu lọc theo màu trong Google Sheets tại đây.

Hoặc ghé thăm trang GitHub này để lấy mã trực tiếp từ kho lưu trữ GitHub.

Bây giờ bạn đã biết cách sắp xếp và lọc dữ liệu của mình theo màu trong Google Sheets. Chúc bạn vui vẻ!

GiGiKing

Related posts