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

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ự.

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:

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.