# Sort Data

## Content

You can sort data in a spreadsheet and specify the column or row index to sort on as well as the sort criteria. You can also specify multiple sort keys (sort by a specified column or row first, then another column or row, and so on).

## Sort Range in Ascending or Descending Order

The [sortRange](/spreadjs/api/v16/classes/GC.Spread.Sheets.Worksheet#sortRange) method is used to sort data. The `sortInfo` object in the **sortRange** method specifies sort keys and ascending or descending order.

The following GIF illustrates a column sorting in ascending and descending order.

![](https://gcdocumentsitekrblob.blob.core.windows.net/document-site-files/images/8d606653-16a0-474d-b9dc-e2b4d01c2446/images/codesort.gif)

```javascript
var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"),{sheetCount:3});
var activeSheet = spread.getActiveSheet();
activeSheet.setValue(0, 0, 10);
activeSheet.setValue(1, 0, 100);
activeSheet.setValue(2, 0, 50);
activeSheet.setValue(3, 0, 40);
activeSheet.setValue(4, 0, 80);
activeSheet.setValue(5, 0, 1);
activeSheet.setValue(6, 0, 65);
activeSheet.setValue(7, 0, 20);
activeSheet.setValue(8, 0, 30);
activeSheet.setValue(9, 0, 35);

$("#button1").click(function(){
     //Sort Column1 by ascending at every button click.
    activeSheet.sortRange(-1, 0, -1, 1, true, [{index:0, ascending:true}]);
   });
 $("#button2").click(function(){
    //Sort Column1 by descending at every button click.
     activeSheet.sortRange(-1, 0, -1, 1, true, [
       {index:0, ascending:false}
     ]);
 });
//Add button controls to the page
```

## Sort Multiple Ranges

You can sort multiple ranges by specifying multiple sort keys in the `sortInfo` object.

![](https://gcdocumentsitekrblob.blob.core.windows.net/document-site-files/images/8d606653-16a0-474d-b9dc-e2b4d01c2446/images/sort-multiple-keys.gif)

```javascript
$(document).ready(function ()
{
    var spread =
    new GC.Spread.Sheets.Workbook(document.getElementById("ss"), {sheetCount:3});
    var activeSheet = spread.getActiveSheet();
    activeSheet.setRowCount(6);
    activeSheet.setValue(0, 0, 10);
    activeSheet.setValue(1, 0, 100);
    activeSheet.setValue(2, 0, 100);
    activeSheet.setValue(3, 0, 10);
    activeSheet.setValue(4, 0, 5);
    activeSheet.setValue(5, 0, 10);
    activeSheet.setValue(0, 1, 10);
    activeSheet.setValue(1, 1, 40);
    activeSheet.setValue(2, 1, 10);
    activeSheet.setValue(3, 1, 20);
    activeSheet.setValue(4, 1, 10);
    activeSheet.setValue(5, 1, 40);
    $("#button1").click(function()
    {
        // Create a SortInfo object where 1st Key:Column1 / 2nd Key:Column2.
        var sortInfo =
        [
            {index:0, ascending:true},
            {index:1, ascending:false}
        ];
        // Execute sorting which targets all rows based on the sorting conditions.
        spread.getActiveSheet().sortRange(0, 0, 6, 2, true, sortInfo);
    });
});

//Add button control to page
```

## Sort by Cell Background Color

You can sort the cells by background color using the `sortInfo.backColor` option. The cells are grouped by position at the top or bottom of the selected range using the `sortInfo.order` option.

The following GIF illustrates the green-colored cells being sorted at the bottom.

![](https://gcdocumentsitekrblob.blob.core.windows.net/document-site-files/images/8d606653-16a0-474d-b9dc-e2b4d01c2446/images/sort-backcolor.gif)

```javascript
sheet.setArray(0, 0, [
    ["Order No.","Order Date","Product Type","Qty","Customer ID","Delivery Status"],
    ["1234", "11-Jul", "Electronic", "1", "861861", "Delivered"],
    ["1235", "12-Jul", "Clothing",   "4", "530317", "Past Due"],
    ["1236", "13-Jul", "Cleaning",   "2", "753904", "Past Due"],
    ["1237", "14-Jul", "Food Item",  "6", "623424", "Delivered"],
    ["1238", "15-Jul", "Electronic", "3", "214403", "Past Due"],
    ["1239", "16-Jul", "Clothing",   "7", "105146", "Due in 4 Days"],
    ["1240", "17-Jul", "Cleaning",   "3", "860876", "Past Due"],
    ["1241", "18-Jul", "Food Item",  "8", "126990", "Delivered"],
    ["1242", "19-Jul", "Electronic", "2", "505788", "Delivered"],
    ["1243", "20-Jul", "Clothing",   "5", "298332", "Due in 4 Days"]
]);
            
spread.getSheet(0).getCell(1, 5).backColor("#E2EFDA");            
spread.getSheet(0).getCell(2, 5).backColor("#FCE4D6");            
spread.getSheet(0).getCell(3, 5).backColor("#FCE4D6");            
spread.getSheet(0).getCell(4, 5).backColor("#E2EFDA");            
spread.getSheet(0).getCell(5, 5).backColor("#FCE4D6");            
spread.getSheet(0).getCell(6, 5).backColor("#FFF2CC");            
spread.getSheet(0).getCell(7, 5).backColor("#FCE4D6");            
spread.getSheet(0).getCell(8, 5).backColor("#E2EFDA");            
spread.getSheet(0).getCell(9, 5).backColor("#E2EFDA");            
spread.getSheet(0).getCell(10, 5).backColor("#FFF2CC");

var ColorList = new GC.Spread.Sheets.CellTypes.RadioButtonList();
ColorList.items([
    { text: "Green", value: "green" },
    { text: "Red", value: "red" }, 
    { text: "Yellow", value: "yellow" }
]);
spread.getSheet(0).getCell(12, 2).cellType(ColorList);

var OrderList = new GC.Spread.Sheets.CellTypes.RadioButtonList();
OrderList.items([
    { text: "Top", value: "top" },
    { text: "Bottom", value: "bottom" }
]);
spread.getSheet(0).getCell(12, 3).cellType(OrderList);
  
sortBackColor(spread.getSheet(0));
    
function sortBackColor (sheet){
    const CELL_COLOR_MAPPING = {
        green: "#E2EFDA",
        red: "#FCE4D6",
        yellow: "#FFF2CC",
    }
    sheet.setColumnWidth(3,120);
    var style = new GC.Spread.Sheets.Style();
    style.cellButtons = [{
    caption: "Sort By Cell Color",
    useButtonStyle:true,
    width: 120,
    command: function (sheet) {
        var value = sheet.getValue(12,2);
        var order = sheet.getValue(12,3);
        value = value ? value : "green";
        order = order ? order : "top";
        var color = CELL_COLOR_MAPPING[value];
        sheet.sortRange(1,0,10,6,true,[{
            index:5,
            backColor:color,
            order:order,
        }])
    }
    }];
    sheet.setStyle(13,3,style);
}
```

## Using SpreadJS Designer

You can sort by background color by choosing the "Cell Color" option in the "SortOn" dropdown. The Sort dialog can be accessed from Home > Editing > Sort & Filter > "Custom Sort" option.

![](https://gcdocumentsitekrblob.blob.core.windows.net/document-site-files/images/8d606653-16a0-474d-b9dc-e2b4d01c2446/images/designer-sort-backcolor.png)

## Sort by Font Color

You can sort the cells by font color using the `sortInfo.foreColor` option. The cells are grouped by position at the top or bottom of the selected range using the `sortInfo.order` option.

The following GIF illustrates red-colored cells being sorted at the top.

![](https://gcdocumentsitekrblob.blob.core.windows.net/document-site-files/images/8d606653-16a0-474d-b9dc-e2b4d01c2446/images/sort-forecolor.gif)

```javascript
spread.getSheet(1).setArray(0, 0, [
    ["Order No.","Order Date","Product Type","Qty","Customer ID","Delivery Status"],
    ["1234", "11-Jul", "Electronic", "1", "861861", "Delivered"],
    ["1235", "12-Jul", "Clothing",   "4", "530317", "Past Due"],
    ["1236", "13-Jul", "Cleaning",   "2", "753904", "Past Due"],
    ["1237", "14-Jul", "Food Item",  "6", "623424", "Delivered"],
    ["1238", "15-Jul", "Electronic", "3", "214403", "Past Due"],
    ["1239", "16-Jul", "Clothing",   "7", "105146", "Due in 4 Days"],
    ["1240", "17-Jul", "Cleaning",   "3", "860876", "Past Due"],
    ["1241", "18-Jul", "Food Item",  "8", "126990", "Delivered"],
    ["1242", "19-Jul", "Electronic", "2", "505788", "Delivered"],
    ["1243", "20-Jul", "Clothing",   "5", "298332", "Due in 4 Days"]
]);

spread.getSheet(1).name("Sort by font color");
spread.getSheet(1).getCell(1, 5).foreColor("#548235");            
spread.getSheet(1).getCell(2, 5).foreColor("#C65911");            
spread.getSheet(1).getCell(3, 5).foreColor("#C65911");            
spread.getSheet(1).getCell(4, 5).foreColor("#548235");            
spread.getSheet(1).getCell(5, 5).foreColor("#C65911");            
spread.getSheet(1).getCell(6, 5).foreColor("#BF8F00");            
spread.getSheet(1).getCell(7, 5).foreColor("#C65911");            
spread.getSheet(1).getCell(8, 5).foreColor("#548235");            
spread.getSheet(1).getCell(9, 5).foreColor("#548235");            
spread.getSheet(1).getCell(10, 5).foreColor("#BF8F00");

var FontColorList = new GC.Spread.Sheets.CellTypes.RadioButtonList();
FontColorList.items([
    { text: "Green", value: "green" },
    { text: "Red", value: "red" }, 
    { text: "Yellow", value: "yellow" }
]);
spread.getSheet(1).getCell(12, 2).cellType(FontColorList);

var FontColorOrder = new GC.Spread.Sheets.CellTypes.RadioButtonList();
FontColorOrder.items([
    { text: "Top", value: "top" },
    { text: "Bottom", value: "bottom" }
]);
spread.getSheet(1).getCell(12, 3).cellType(FontColorOrder);
  
sortFontColor(spread.getSheet(1));

function sortFontColor (sheet){
    const FONT_COLOR_MAPPING = {
        green:"#548235",
        red:"#C65911",
        yellow: "#BF8F00"
    }
    sheet.setColumnWidth(3,120);
    var style = new GC.Spread.Sheets.Style();
    style.cellButtons = [{
            caption:"Sort By Font Color",
            useButtonStyle:true,
            width:120,
            command:function (sheet){
                var value = sheet.getValue(12,2);
                var order = sheet.getValue(12,3);
                value = value ? value : "green";
                order = order ? order : "top";
                var color = FONT_COLOR_MAPPING[value];
                sheet.sortRange(1,0,10,6,true,[{
                    index:5,
                    fontColor:color,
                    order:order
                }])
            }
        }];
    sheet.setStyle(13,3,style);
}
```

## Using SpreadJS Designer

You can sort by font color by choosing the "Font Color" option in the "SortOn" dropdown. The Sort dialog can be accessed from Home > Editing > Sort & Filter > "Custom Sort" option. ![](https://gcdocumentsitekrblob.blob.core.windows.net/document-site-files/images/8d606653-16a0-474d-b9dc-e2b4d01c2446/images/designer-sort-forecolor.png)

## Ignore Hidden Data while Sorting

You can also skip hidden data while sorting by using the `ignoreHidden` option in API. A cell or a range is considered to be hidden when:

* A row's height or column's width is 0
* A row or column is hidden
* A row or column is filtered out
* Some rows or columns are grouped and unexpanded

The below example contains hidden rows (cells A5:A6) and displays how the data is sorted with different values of `ignoreHidden` :

| **Original Data** | **ignoreHidden is true** | **ignoreHidden is false** |
| ------------- | -------------------- | --------------------- |
| ![](https://gcdocumentsitekrblob.blob.core.windows.net/document-site-files/images/8d606653-16a0-474d-b9dc-e2b4d01c2446/images/Orig_hidden_data.png) | ![](https://gcdocumentsitekrblob.blob.core.windows.net/document-site-files/images/8d606653-16a0-474d-b9dc-e2b4d01c2446/images/hiddenTrue.png) | ![](https://gcdocumentsitekrblob.blob.core.windows.net/document-site-files/images/8d606653-16a0-474d-b9dc-e2b4d01c2446/images/hiddenFalse.PNG) |

The following code sample shows how to sort data by skipping hidden rows.

```js
// get the activesheet
var activeSheet = spread.getSheet(0);
// Set data
activeSheet.setValue(0, 0, "Name");
activeSheet.setValue(0, 1, "Age");
activeSheet.setValue(1, 0, "Rick");
activeSheet.setValue(1, 1, 52);
activeSheet.setValue(2, 0, "Tim");
activeSheet.setValue(2, 1, 50);
activeSheet.setValue(3, 0, "Jerry");
activeSheet.setValue(3, 1, 46);
activeSheet.setValue(4, 0, "Jack");
activeSheet.setValue(4, 1, 98);
activeSheet.setValue(5, 0, "Sandy");
activeSheet.setValue(5, 1, 99);
activeSheet.setValue(6, 0, "Smith");
activeSheet.setValue(6, 1, 42);
activeSheet.setValue(7, 0, "Greg");
activeSheet.setValue(7, 1, 41);
activeSheet.setValue(8, 0, "Sia");
activeSheet.setValue(8, 1, 36);
activeSheet.setValue(9, 0, "Jennet");
activeSheet.setValue(9, 1, 38);
activeSheet.setValue(10, 0, "Rachel");
activeSheet.setValue(10, 1, 37);
// Hide Row
activeSheet.setRowHeight(4, 0.0, GC.Spread.Sheets.SheetArea.viewport);
activeSheet.setRowHeight(5, 0.0, GC.Spread.Sheets.SheetArea.viewport);

// Sort range i.e. "Age" column with ignoreHidden set to true
activeSheet.sortRange(1, 1, 10, 1, true, [{ index: 1, ascending: true }], { ignoreHidden: true });
```

SpreadJS also allows you to sort grouped data by using `groupSort` enumeration. The grouped and unexpanded data is considered as hidden rows or columns. To know more about sorting grouped data, refer to [Sort with Grouped Data](/spreadjs/docs/v16/features/sort-data/sort-with-grouped-data).

The behavior of data when **groupSort** and **ignoreHidden** are used together, is explained in the below table:

|  | **ignoreHidden=true** | **ignoreHidden=false** | **ignoreHidden undefined** |
| --- | ----------------- | ------------------ | ---------------------- |
| **groupSort (group, child or full)** | Group sort works, and hidden values are not ignored |
| **groupSort (flat)** | Ignores hidden values | Does not ignore hidden values | Ignores hidden values |
| **groupSort undefined** | Ignores hidden values | Does not ignore hidden values | If the sorting range contains a group, then group sort is applied.<br> If the sorting range does not contain a group, then hidden values are ignored |

The following code sample shows how to use `ignoreHidden` and `groupSort` together.

```javascript
// set data
 activeSheet.setArray(3, 0, [
     [6221], [5125], ['Samsung'], [4348], [3432], ['LG'], [1928], [2290], ['Oppo'], [8939], [7006], ['Apple']
 ]);
 activeSheet.rowOutlines.group(3, 2);
 activeSheet.rowOutlines.group(6, 2);
 activeSheet.rowOutlines.group(9, 2);
 activeSheet.rowOutlines.group(12, 2);
 spread.resumePaint();

 // set rowFilter
 activeSheet.rowFilter(new GC.Spread.Sheets.Filter.HideRowFilter(new GC.Spread.Sheets.Range(3, 0, 13, 1)));

 // hide rows
 activeSheet.setRowHeight(4, 0.0, GC.Spread.Sheets.SheetArea.viewport);
 activeSheet.setRowHeight(5, 0.0, GC.Spread.Sheets.SheetArea.viewport);

 //1) If you want to use filter dialog to sort the data with enhanced group feature and ignoreHidden, you should use RangeSorting event
 spread.bind(GC.Spread.Sheets.Events.RangeSorting, function (e, info) {
     // set GroupSort to full
     info.groupSort = GC.Spread.Sheets.GroupSort.full;
     // set ignoreHidden to true
     info.ignoreHidden = true;
 });

 //2) If you want to use api to sort the data with enhanced group feature and ignoreHidden, you should use this code
 // activeSheet.sortRange(3, 0, 13, 1, true, [{ index: 0, ascending: true }], { ignoreHidden: true, groupSort: GC.Spread.Sheets.GroupSort.full });
```